Stock ticker symbol: ______KO_____
Calculate thebeta of your stock using the "market model":RS= a + b * (Rm) + e
Regression equation: E(Y) = a + b(X) +error
(Predicted return on the stock)= intercept + BETA*(Return on market index)
1. Download weekly historical price data in spreadsheet format for your stockusing the range 6/1/2017 to 6/1/2018 from finance yahoo using the 'Historical Prices' function. Save it in CSV (spreadsheet) format. For the same dates, download the values of the S&P 500 Index (use ticker symbol ^GSPC).
2. Open the stock price data file using MS Excel and save it as a workbook (.xlsx, NOT .csv). Copy the closing values of the index into the same spreadsheet as the stock prices; be careful to check that the dates line up and there are no missing observations.Use the "adjusted close". You can discard the other prices and volume figures.
3. Create weekly returns (Rx) from the series of weekly stock closing prices using the formula below and the "adjusted" close prices. BE CAREFUL to check that your formula is correct, and that you haven't accidentally reversed P1 and Po
R1 = (P1/P0) - 1
4. Create weekly returns from the series of weekly index data (Rm) the same way, being careful to line up the dates. (Note: there will be one less observation in each series than you started with.)
5. Estimate the intercept and beta using linear regression following the example at the top of this page (the market model). Select "show the results in a new sheet". Use total returns, not excess returns (i.e., do not subtract the risk-free rate). Use the "regression" tool in the "data analysis" package in the "tools" menu; if it's not already loaded, you'll have to use "add-ins" to add it.
6. Download the summary page spreadsheet from Blackboard and place it in your workbook. Fill in all of the blanks. There should be at least 3 tabs in your workbook: 1) the weekly returns data, 2) the regression output, and 3) the summary page. Submit your workbook through the Assignment module on Blackboard.