Select historical prices from the left navigation menu
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 reliability of the 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 before your end date.
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). You will have 60 months worth of data and 59 HPRs.
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 Adjusted R2
2. The standard error of the regression
3. Both coefficients
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).
Watch the video below for help with this project.
https://notorious-prod.s3.amazonaws.com/originals/o-5wixKXC7yPTnhunue8JQPTyt8hy7buFD/transcodings/t-5xk2vSgBqejWz9zW9jjDob9SYvKvZMVu.mp4?AWSAccessKeyId=AKIAIEH4LE5KKJ5TP42A&Signature=jzOL3kLIFKWhqXvCedTpoQtad3M%3D&Expires=1455156331
Points awarded as follows: 2 points for properly identifying and discussing #1, 3, and 4 from the list above; 1 point for properly identifying and discussing #2 from the list above; 1 point for proper specification of the regression formula; and 2 points for conducting the regression process correctly.
Required Formatting:
You need to write a 1-2 page, single-spaced paper detailing what you have discovered. Your document should be in Times New Roman, 12 point font with 1-inch margins. Type your name and the assignment’s name in the Header (“Insert” tab, then select “Header” in MS Word). Do not include a heading in the body of your text to fill space…I will remove it when determining the length requirement. You need to include 1 image (google “snipping tool” if you do not know how to use it) showing your regression results. This should be on the last page of your paper and does not count towards the length requirement.Discuss both what you have learned from this process and the regression output itself.
