Regression
In this special project you will be gathering data on any company that you choose and creating a regression model using Excel. You will be graded both on the correctness of your model and on your comments about the Regression output.
Step 1: Gathering the data...
1. Navigate to Yahoo! Finance and enter the ticker symbol for the stock of your choice.
2. Select "Historical Prices" from the left navigation menu
3. The "End Date" should be the current date, and the "Begin Date" should be exactly 5 years and 1 month before your end date. You want 61 months of data to end up with 60 data points (n=60)
4. Select "Monthly" on the right and then select "Get Prices"
5. The right-most column in the output is labeled "Adj Close*." This is the adjusted closing price...but adjusted for what. If you scroll down to the bottom of the table you will see that the adjustment is for both dividends and stock splits. This is the data you are looking for.
6. Select "Download to Spreadsheet" just below the table of data.
7. All that you need to retain is the date column and the adjusted closing price data...save this to a file on your computer.
8. Navigate back to the Yahoo! Finance homepage and click on the blue link for the S&P 500 Index.
9. Follow the same process as steps 2-7 to gather the data on the benchmark.
10. Now enter the ticker symbol "^TNX" which is the yield on the 10-year Treasury.
11. Follow the same process as steps 2-7 to gather the data on Treasuries.
Step 2: Compile all of the relevant data in one Excel Spreadsheet. (TIP: It works well to have all of the data on Worksheet 1 and eventually your regression results on Worksheet 2).
1. Column A should be the date.
2. Column B should be your chosen stock's adjusted monthly closing price.
3. Column C should be the Holding Period Return for your chosen stock (TIP #3: HPR = (Ending-Beginning)/Beginning.
4. Column D should be the Excess Return of your chosen stock (HPR of stock - 10-Year Treasury Yield [Column C - Column I]).
5. Column E should be S&P 500 adjusted monthly closing data (this is ^GSPC).
6. Column F should be the Holding Period Return for the S&P 500.
7. Column G should be the Excess Return of the S&P.
8. Column H should be the 10-year Treasury Yield data (this is ^TNX)
9. Column I should be the Holding Period Return for the 10-year Treasury Yield
Step 3: Regression Analysis
1. In Excel, look under the "Data" menu in the "Analysis" section, on the far right, for "Data Analysis."
2. If you do not see this then you will need to google how to add the "Analysis Tool Pak" for Excel.
3. If you are using a Mac, there is a similar product, but I think that you need to buy it, so it might be easier to simply use a PC for the regression portion of this project.
4. Once you are able to select "Data Analysis", select this feature and then choose "Regression" from the pull down menu.
5. First, establish your output range as cell A1 on your first empty Workbook (1 or 2).
6. Second, set your "input Y" range as your stock's excess return (Column D). Each column should have a label in row 1. Include row 1 in the data range for each input.
7. Third, set your "input X" range as the S&P 500 Index's Excess Return (Column G). Again include row 1 in the range, which has your column label.
8. Check the box for labels.
9. Run the regression
You should discuss what the specific numbers you found through regression communicate about your data. Be sure to discuss:
1. The R2
2. The alpha
3. The beta
4. The relevant t-value and p-value statistics.
Be specific. Also include at the top of your paper (1) the SCL equation inferred by your regression, (2) the ticker symbol for your stock, and (3) a screen shot of your regression results (the snipping tool works very well).