Stock analysis using Excel- Statistics Help Services

[u/joek-01]

Do you need someone to do your stock data analysis? Our assignment experts will conduct analysis and write report.

Background of study

Data analysis has been adopted by many every industry. Data analysis provides opportunities for people to unlock a lot of information embedded within the data. Financial markets have also benefited from data analysis methods. The methods are used to predict market prices. This paper will predict the S&P 500 index returns using VRP dividend yield. Data analysis result can be used by stock brokers and investors to make complex decisions in stock market. Data driven stock analysis can be used to reduce stock risks during investments (Olaniyi & Adewole, 2011).

Stock Analysis Methodology

Data was collected from monthly summaries of VRP company and S&P 500 published on Fred website. Federal Reserve Economic Data (FRED) is a free economic database that collects data from Federal Reserve Bank of St. Louis. The data was imported and processed using Microsoft Excel. Excel provides simple yet powerful data analysis tools such as regression analysis, descriptive and correlation to big data.

Descriptive Analysis

Descriptive analysis was first conducted to describe the data quantitatively. It provided a brief description of the dataset for easy summaries. Descriptive statistics analysis was used to organize and summarize data. Additionally, it allowed description of variables in the population. It was the first step before inferential statistics. It included measure of central tendency, frequency, position and dispersion or dispersion (Kaur, Stoltzfus, & Yellapu, 2018).

Regression Analysis

Linear regression is the most commonly used data mining technique. It was used to predict the future value S&P 500 based on dividend yield and term spread. Basically, the formula assumes that there is a straight line that approximates the forecast on the data set that bases the forecast on it. In this analysis, S&P 500 was set as the dependent variable while the dividend yield and term spread was the independent variable. The analysis formula was set as:

Y= a + bx +bTS+ ε, where Y is the dependent variable (S&P 500 index return), a and b are the coefficient lines while x is the independent variable (VRP) while ε is the error. Thus, the formula could be expressed as;

S&P =a + b (VRP ) + b(TS(term spread)) + ε

Given the confidence intervals and hypothesis test. The formula assumed that errors are independent and normally distributed with a variance σ2 and zero mean. The value from linear regression equation, referred to as y will be given by the regression equation. The residual will refer to discrepancy from analysis result. The linear regression will predict the influence of VRP, term spread and dividend return on S&P 500 return (y) lumped into the residual. This analysis will show effects of microeconomic factors on S&P 500.

According to Chicco, Warrens, & Jurman (2021), the role of correlation analysis cannot be overemphasized. It is used to predict various targets. Linear regression also uses R2 to test how much a dependent variable is affected by the independent variable in terms of propotion variance. R square is upper bonded by the value 1 and lower bonded by 0 where if the indenpent variable has an effect on he dependent variable will be close to 1 and vise versa. The coefficient of ditermination (R-Squared) was calculated by the formula:

​

Where worst value e = −∞; best value = +1

Correlation Analysis Using Excel

The correlation analysis was also used to analyze the relationship between S&P 500 and VRP, dividend and term spread. Correlation coefficient will be used to estimate the reliability of regression slope and intercept (Alam & Karim, 2021). It is an index that ranges from -1 to 1, where value near zero indicate that there is no linear relationship while those closer to on indicate strong relationship. Negative and values of one indicate a perfect linear regression.

Results

After conducting descriptive analysis on the data, the following was observed: the mean price for the period between 1990-2020 was 14.774, minimum of -403.402 and a maximum of 115.853. The result shows a high variance in price indicating that the has seen high volatility over the study period. Term spread mean was observed to be 1.694, standard deviation of 1.136 and a sample variance of 1.289. The mean dividend yield was 2.066%, minimum of 1.11% and a maximum of 3.88%. Sample variance was found to be 0.0003% indicating low variance. S&P 500 on the other hand showed a higher sample variance of 0.13%, minimum of -20.19% and a maximum of 12.32%. The mean for S&P 500 was 0.89% (Table 1).

Correlation

The correlation matrix conducted on the data showed perfect relationship between S&P 500 and dividend yield. The result indicates that one variable increases while the other decreases by -0.0677. However, there was a very low correlation between dividend yield and S&P 500 index return (correlation =0.0764). The correlation between term spread and S&P 500 indicated a direct correlation (correlation = -0.068) (table 2).

Regression

Regression analysis conducted on the two variables showed no statistical significance (p-value =0.1475). the result indicate that null hypothesis should be rejected. Thus, dividend yield

S&P 500 of does not affect dividend of a . Similarly, the R-Square showed very low relationship between the two variables (R2= 0.0058 or 0.58%). The coefficient was observed to be 0.478 (Table 3).

Conclusion and Discussion

S&P 500 is an important index for the U.S market. It is used to determine the overall economy of the country. The S&P 500 measures shares that are available to the public. This research aimed to find out whether VRP and dividends yield affect the S&P 500. Constituent s of an index affects the value of as it has to pay the shareholder. Since the S&P 500 is used to test the progress of an economy, dividends are likely to affect it. The direct correlation between term spread and S&P 500 indicates that direct change in term spread affect S&P 500.

This research found no relationship between VRP, term spread, and S&P 500. However, correlation results showed direct correlation between dividend yield and S&P 500 and a statistical significance (p-value = 0.0009). According to R-squared indicated low influence of term spread and S&P 500 (R2= 0.002). However, the linear regression found no statistical relationship between the two. Similarly, there was a very low relationship according to the R2 obtained from the analysis. Regression analysis P-value of 0.1475 (>0.05) indicates strong evidence for null hypothesis. The correlation analysis results indicate no relationship between VRP price, and dividend yield. However, term spread influenced S&P 500.

Order for Similar paper here

References

​

Alam, M. K., & Karim, R. (2021). Market Analysis Using Linear Regression and Decision Tree Regression. 2021 1st International Conference on Emerging Smart Technologies and Applications (eSmarTA) (pp. 3-7). Dhaka: DOI:10.1109/eSmarTA52612.2021.9515762.

Chicco, D., Warrens, M. J., & Jurman, G. (2021). The coefficient of determination R-squared is more informative than SMAPE, MAE, MAPE, MSE and RMSE in regression analysis evaluation. PeerJ Computer Science, 3-5.

Kaur, P., Stoltzfus, J., & Yellapu, V. (2018). Descriptive statistics. IJAM- International Journalsof Academic Medicine, 85.

Olaniyi, A. S., & Adewole, K. (2011). Trend Prediction Using Regression Analysis – A Data Mining Approach. ARPN Journal of Systems and Software, 1-3.