beta assignment on excel

Stock ticker symbol: ___________

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.

7. Download the cover sheet from Blackboard and place it in your workbook. Fill in all of the blanks.

Submit your workbook through the Assignment module on Blackboard.

Bring your assignment to class for discussion.