Calculating Beta for a Stock
Using Data from the Internet
1. Go to Yahoo!Finance on the internet at URL, http://finance.yahoo.com/
2. Enter the ticker symbol for the stock (this gives a recent quote of the stock price).
3. To the left of the stock price quote box, press historical quotes (this gives you access to the raw price data for the stock).
4. Choose Daily, Weekly, or Monthly data on the radio buttons.
5. Enter the start date and end date for the stock price data that you want.
6. Press Get Historical Data. (This retrieves the stock price data requested).
7. To download the data to your local computer, press Download Spreadsheet Format at the bottom of the Historical Data page.
8. The next screen is a panel to save the data. You can change the name at this point to something you will recognize, such as
“Stockname daily.csv” or “Ticker daily.xls”. Note the file location where the data is being downloaded.
9. Start Excel. Press “file”, “open” and change the “Look in” box to the location on your data storage drive where the data was
downloaded. If you used the .csv extension when you saved, you will need to adjust the “Files of type” box in Excel when you try to
open the file so it will read “all files”. However, the data will automatically be parsed (accessible). If you used the .xls
extension, the file can be immediately opened. However, you need to parse the data by undoing the radio button that uses a tab delimit
and marking the radio button that delimits the data with a comma.
10. This Excel file should then be saved (you again have the chance to change the file name to a recognizable name).
11. Return to Yahoo!Finance where you downloaded the historical stock data and change the ticker symbol to one of the stock indexes
of the market. The S&P 500 index of the market has a ticker symbol of ^GSPC.
12. Press Get Historical Data for the same time period for which you downloaded the stock price data. At the bottom of this page,
press Download Spreadsheet Format, change the name to something recognizable as before, save the file, open it with Excel as before
(comma delimit, if necessary) and save as an Excel file.
13. With both the stock price file and index file in Excel, delete all columns except date and closing price (adjusted closing
price corrects for dividends) in both files. Copy the two columns in the index file and paste them in the stock file with two blank
columns in between. Check to make sure that the dates line up correctly (no missing dates or extra rows).
14. Calculate the returns for the stock prices and the market index in the columns to the right of the prices. The formula is
(Pricetoday – Priceyesterday)/Priceyesterday.
Copy this formula to the right of the closing price data for the stock and for the market index.
15. Regress the returns of the stock against the returns of the market index. In Excel, choose Data, Data Analysis, and choose
Regression in the panel box. This brings up a Regression panel. Enter the range for the returns of the stock as the “Y values” and
the range for the returns of the market index as the “X values”. Then press “OK”. This creates a panel of the regression results. At
the bottom left side of this panel are the Intercept and Slope Coefficients. The Beta of the stock is the Slope Coefficient.
16. A shortcut of number 15 is to enter into a cell =slope(y-values,x-values). This will give you the beta without going through
the regression panel (it will also dynamically change the value of beta if you change any of the input data – the regression panel in
item 15will not change if you change the input data).
17. It is always useful to prepare a scatter diagram of the data with a trend line to show the regression fit. To do this easily,
copy the stock returns data to column H on the Excel spreadsheet. Highlight the market index returns and the stock returns. Then
press the Chart Wizard icon. Choose x-y plot, and step through the Chart Wizard to enter the titles you want to appear on the chart,
delete the legend, and save the chart as a new worksheet. To include a trend line, select the chart, choose Chart, and Add Trend Line.
This automatically adds a trend line to your chart.
18. Return to Step 4 and use weekly data to calculate a weekly beta. Also return to Step 4 and use monthly data to calculate a
monthly beta.
19. For the daily, weekly, and monthly beta calculations, print out (1) the data you used (1 page as an example for daily, but all
the data for weekly and monthly), (2) the regression panel results with beta identified, and (3) a chart of the scatter diagram of
returns with a best-fit trendline.
20. Your assignment is to calculate a daily, weekly, and monthly beta for a company. Use one complete year of daily data (about
253 returns) using closing prices. For weekly data, use 2 years, or 104 returns. For monthly data, use 60 returns, including the last
complete month (note that if a month is not yet completed it should not be included in your analysis, even though you will still get a
number for the closing price so far in the month. To make grading easier for me, summarize your results on a cover page (Daily,
Weekly, and Monthly beta), then include your data, the SCL charts, and the Regression Panels.
TAKE ADVANTAGE OF OUR PROMOTIONAL DISCOUNT DISPLAYED ON THE WEBSITE AND GET A DISCOUNT FOR YOUR PAPER NOW!