Question

1 Approved Answer

Posted on Jul 11, 2024

Collect the daily stock prices over the past year from Yahoo! Finance for SPY, which is the symbol for the exchange traded fund that attempts

Collect the daily stock prices over the past year from Yahoo! Finance for SPY, which is the symbol for the exchange traded fund that attempts to mimic the S&P 500 index, which is often used as the proxy for the market portfolio, and two other stocks of your choice that have been publicly traded firms for at least one year. Follow the guidelines in the Obtaining Historical Returns from Yahoo document.
Use the Excel file I posted called M5 Project 1 Excel Class to calculate the same statistics. See the formulas in the cells as a guide.
- Better yet, try to match your return cells with my return cells so that everything automatically populates.
  - Follow the Obtaining Historical Returns from Yahoo file to see what I mean by this.
Discuss your findings. This should include:
- A discussion of the main statistics, such as
  - the expected return for each stock,
  - the standard deviation for each stock, the expected return of the portfolio of the two stocks you selected using the various percentages allocated to each (50%-50%, 75%-25%, 25%-75%) provided in the sheet,
  - the standard deviation for each portfolio of the two stocks you selected using the various percentages allocated to each (50%-50%, 75%-25%, 25%-75%),
  - the beta of each stock,
  - the beta of a portfolio of the two stocks using the percentages allocated to each (50%-50%, 75%-25%, 25%-75%).
- Be sure to discuss some of the key concepts too (e.g., point out the standard deviation of the portfolio is less than the weighted average of the standard deviations of the stocks in the portfolio)
- Compute the coefficient of variation for the three different allocations
  - Which of the three (i.e., the 50-50%, 75-25%, or 25-75%) offers the best risk-return profile?

Date Stock 1 ret Stock 2 ret Change Stock 1 and Stock 2 with your ticker symbols Method 1 0.00 Method 2 0.00 Beta Stock 1 Avg. Daily Ret Daily Std. Deviation Daily Variance SPY 242.38597 0.66150 0.43758 Stock 1 ret 0.00064 0.00210 0.00000 Stock 2 ret #DIV/0! #DIV/0! #DIV/0! Beta Stock 2 #DIV/0! #DIV/0! SPY-Stock 1 -0.09713 SPY-AMAT #DIV/0! ABC-AMAT #DIV/0! Correlation Coefficient SPY SPY ret 239.9335 241.0526 241.0231 241.1507 242.4563 242.5643 242.3483 242.2894 242.8784 242.8882 242.6625 242.3778 242.2404 242.7803 242.8981 242.4269 242.8686 243.3202 242.7214 0.004664 -0.000122 0.000529 0.005414 0.000445 -0.000890 -0.000243 0.002431 0.000040 -0.000929 -0.001173 -0.000567 0.002229 0.000485 -0.001940 0.001822 0.001859 -0.002461 Annual Return Annual Std. Deviation 61081.265 10.50091944 7/14/2017 7/17/2017 7/18/2017 7/19/2017 7/20/2017 7/21/2017 7/24/2017 7/25/2017 7/26/2017 7/27/2017 7/28/2017 7/31/2017 8/1/2017 8/2/2017 8/3/2017 8/4/2017 8/7/2017 8/8/2017 8/9/2017 8/10/2017 8/11/2017 8/14/2017 8/15/2017 8/16/2017 ABC 0.162312271 0.033391362 AMAT #DIV/0! #DIV/0! SPY-Stock 1 30540.71366 SPY-Stock 2 #DIV/0! Expected return portfolio Std. Deviation portfolio Stock 1 - Stock 2 #DIV/0! #DIV/0! 50%/50% 0.500 SPY-Stock 1 #DIV/0! SPY-Stock 2 #DIV/0! 75%/25% Expected return portfolio 0.75 Std. Deviation portfolio 0.25 Stock 1 - Stock 2 #DIV/0! #DIV/0! SPY-Stock 1 #DIV/0! SPY-Stock 2 #DIV/0! 25%/75% Expected return portfolio 0.25 Std. Deviation portfolio 0.75 Stock 1 - Stock 2 #DIV/0! #DIV/0! pbtaining Historical Returns from Yahoo! Finance 5. In the excel file that pops open, keep only the "Date" and "Adi Close" columns. Delete the others. We use Adi Close because it includes certain events, such as stock splits, dividends, etc. that can affect the holding period return of the stock. B. Now repeat the process above for the two stocks you select, pasting the returns you calculate into the appropriate place in the M5_Project_1 Excel file. 9. Then all the cells needed for the analysis should be complete and you can answer the questions. 1. Go to https://finance.yahoo.com/ 2. In the search box near the top, enter "SPY" and click search. . Note: See the Project_1_Solved file for an example of how the excel file should look when completed. Then you have to interpret the data and answer the questions. A B 1 Date Adj Close 2 7/13/2017 239.9335 3 7/14/2017 241.0526 # 7/17/2017 241.0231 5 7/18/2017 241.1507 5 7/19/2017 242.4563 Lifestyle Answers Groups Mobile More ance Entertainment YAHOO! spy Search FINANCE ice Home Watchlist My Portfolio My Screeners Markets Industries Personal Finance Technolog 6. In cell c3, calculate the daily return, which is =B3/B2-1 Then copy down the formula into the other cells up to approximately cell 253 (not shown all the way down) 3. Toward the middle right, click "Historical Data". SPDR S&P 500 ETF (SPY) NYSE ca Nasdaq Real Time Price. Currency in USD Add to watchlist 279.29 +2.43 (+0.88%) 279.18 -0.11 (-0.04 Buy Sel At close July 12 4:00PM EDT Pre Market 8 52 EDT Summary Chart Conversations Profile Options Holdings Historical Data Performance Risk A 1 Date Adj Close 2 2 7/13/2017 239.9335 3 7/14/2017 241.0526 0.004664 4 7/17/2017 241.0231 -0.00012 5 7/18/2017 241.1507 0.000529 6 7/19/2017 242.4563 0.005414 7 7/20/2017 242.5643 0.000445 8 7/21/2017 242.3483 -0.00089 9 7/24/2017 242.2894 -0.00024 10 7/25/2017 242.8784 0.002431 11 7/26/2017 242.8882 4.04E-05 12 7/27/2017 242.6625 -0.00093 13 7/28/2017 242.3778 -0.00117 14 7/31/2017 242.2404 -0.00057 15 8/1/2017 242.7803 0.002229 16 8/2/2017 242.8981 0.000485 17 8/3/2017 242.4269 -0.00194 18 8/4/2017 242.8686 0.001822 19 8/7/2017 243.3202 0.001859 20 8/8/2017 242.7214 -0.00246 4. Use the default data settings of one year, daily returns. Click "Apply". Then click "Download Data". Time Period: Jul 13, 2017 - Jul 13, 2018 Show. Hstorical Prices Frequency: Daily Apply Currency USD Download Data Date Open High Low Volume Ad Close 279 37 Jul 12, 2018 278 28 27943 277.60 279.37 59.997 200 Jul 11, 2018 277 15 278.04 27652 276 86 276 86 77054,700 Jul 10, 2018 278 41 279.01 27808 278.90 278.90 51.966,800 7. Now open the M5_Project_1 Excel file. Then go back to the file you were just working on and select and copy the dates from cell A3 down (start at A3 and not A2 since we do not have a return in cz) and paste over the dates in the M5_Project_1 Excel file. Jul 09, 2018 , 276 55 277.96 276.50 277.90 277.90 50.550,400 Note: Use the dates you come up with when following the steps, not the dates shown in the file. Then do the same for the returns from c3 down and paste into the M5_Project_1 file (be sure to "Paste Values", not just Paste) Date Stock 1 ret Stock 2 ret Change Stock 1 and Stock 2 with your ticker symbols Method 1 0.00 Method 2 0.00 Beta Stock 1 Avg. Daily Ret Daily Std. Deviation Daily Variance SPY 242.38597 0.66150 0.43758 Stock 1 ret 0.00064 0.00210 0.00000 Stock 2 ret #DIV/0! #DIV/0! #DIV/0! Beta Stock 2 #DIV/0! #DIV/0! SPY-Stock 1 -0.09713 SPY-AMAT #DIV/0! ABC-AMAT #DIV/0! Correlation Coefficient SPY SPY ret 239.9335 241.0526 241.0231 241.1507 242.4563 242.5643 242.3483 242.2894 242.8784 242.8882 242.6625 242.3778 242.2404 242.7803 242.8981 242.4269 242.8686 243.3202 242.7214 0.004664 -0.000122 0.000529 0.005414 0.000445 -0.000890 -0.000243 0.002431 0.000040 -0.000929 -0.001173 -0.000567 0.002229 0.000485 -0.001940 0.001822 0.001859 -0.002461 Annual Return Annual Std. Deviation 61081.265 10.50091944 7/14/2017 7/17/2017 7/18/2017 7/19/2017 7/20/2017 7/21/2017 7/24/2017 7/25/2017 7/26/2017 7/27/2017 7/28/2017 7/31/2017 8/1/2017 8/2/2017 8/3/2017 8/4/2017 8/7/2017 8/8/2017 8/9/2017 8/10/2017 8/11/2017 8/14/2017 8/15/2017 8/16/2017 ABC 0.162312271 0.033391362 AMAT #DIV/0! #DIV/0! SPY-Stock 1 30540.71366 SPY-Stock 2 #DIV/0! Expected return portfolio Std. Deviation portfolio Stock 1 - Stock 2 #DIV/0! #DIV/0! 50%/50% 0.500 SPY-Stock 1 #DIV/0! SPY-Stock 2 #DIV/0! 75%/25% Expected return portfolio 0.75 Std. Deviation portfolio 0.25 Stock 1 - Stock 2 #DIV/0! #DIV/0! SPY-Stock 1 #DIV/0! SPY-Stock 2 #DIV/0! 25%/75% Expected return portfolio 0.25 Std. Deviation portfolio 0.75 Stock 1 - Stock 2 #DIV/0! #DIV/0! pbtaining Historical Returns from Yahoo! Finance 5. In the excel file that pops open, keep only the "Date" and "Adi Close" columns. Delete the others. We use Adi Close because it includes certain events, such as stock splits, dividends, etc. that can affect the holding period return of the stock. B. Now repeat the process above for the two stocks you select, pasting the returns you calculate into the appropriate place in the M5_Project_1 Excel file. 9. Then all the cells needed for the analysis should be complete and you can answer the questions. 1. Go to https://finance.yahoo.com/ 2. In the search box near the top, enter "SPY" and click search. . Note: See the Project_1_Solved file for an example of how the excel file should look when completed. Then you have to interpret the data and answer the questions. A B 1 Date Adj Close 2 7/13/2017 239.9335 3 7/14/2017 241.0526 # 7/17/2017 241.0231 5 7/18/2017 241.1507 5 7/19/2017 242.4563 Lifestyle Answers Groups Mobile More ance Entertainment YAHOO! spy Search FINANCE ice Home Watchlist My Portfolio My Screeners Markets Industries Personal Finance Technolog 6. In cell c3, calculate the daily return, which is =B3/B2-1 Then copy down the formula into the other cells up to approximately cell 253 (not shown all the way down) 3. Toward the middle right, click "Historical Data". SPDR S&P 500 ETF (SPY) NYSE ca Nasdaq Real Time Price. Currency in USD Add to watchlist 279.29 +2.43 (+0.88%) 279.18 -0.11 (-0.04 Buy Sel At close July 12 4:00PM EDT Pre Market 8 52 EDT Summary Chart Conversations Profile Options Holdings Historical Data Performance Risk A 1 Date Adj Close 2 2 7/13/2017 239.9335 3 7/14/2017 241.0526 0.004664 4 7/17/2017 241.0231 -0.00012 5 7/18/2017 241.1507 0.000529 6 7/19/2017 242.4563 0.005414 7 7/20/2017 242.5643 0.000445 8 7/21/2017 242.3483 -0.00089 9 7/24/2017 242.2894 -0.00024 10 7/25/2017 242.8784 0.002431 11 7/26/2017 242.8882 4.04E-05 12 7/27/2017 242.6625 -0.00093 13 7/28/2017 242.3778 -0.00117 14 7/31/2017 242.2404 -0.00057 15 8/1/2017 242.7803 0.002229 16 8/2/2017 242.8981 0.000485 17 8/3/2017 242.4269 -0.00194 18 8/4/2017 242.8686 0.001822 19 8/7/2017 243.3202 0.001859 20 8/8/2017 242.7214 -0.00246 4. Use the default data settings of one year, daily returns. Click "Apply". Then click "Download Data". Time Period: Jul 13, 2017 - Jul 13, 2018 Show. Hstorical Prices Frequency: Daily Apply Currency USD Download Data Date Open High Low Volume Ad Close 279 37 Jul 12, 2018 278 28 27943 277.60 279.37 59.997 200 Jul 11, 2018 277 15 278.04 27652 276 86 276 86 77054,700 Jul 10, 2018 278 41 279.01 27808 278.90 278.90 51.966,800 7. Now open the M5_Project_1 Excel file. Then go back to the file you were just working on and select and copy the dates from cell A3 down (start at A3 and not A2 since we do not have a return in cz) and paste over the dates in the M5_Project_1 Excel file. Jul 09, 2018 , 276 55 277.96 276.50 277.90 277.90 50.550,400 Note: Use the dates you come up with when following the steps, not the dates shown in the file. Then do the same for the returns from c3 down and paste into the M5_Project_1 file (be sure to "Paste Values", not just Paste)