Calculate 2 year weekly betas using the linear regression:
Rx = a + b * (Rm) + e
Return on the stock = intercept + BETA * (Return on market index) + error
Step-by-step instructions:
1. Download weekly historical price data in spreadsheet format for your stock using the range 1/1/2014 to 12/31/2015 from finance.yahoo.com 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. 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.
3. Create weekly returns (Rx) from the series of weekly closing prices using the formula below; use the raw closing prices, not the "adjusted" close prices.
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. Select "show the results in a new sheet". Use total returns, not excess returns (i.e., do not subtract the risk-free rate from each return). 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. Save all of your work as an Excel workbook; carefully label each sheet. There should be at least two relevant sheets in your workbook; one with the price and return data and one with the regression outputs. Print out the page that contains your regression output; it should fit on 1 or 2 pages.
Attachment:- Assignment.rar