1.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.

2. 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.

3. 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?

Note: Do not choose ABC and AMAT as your stocks.

A B D E F G H 1 J K L L M N 0 Date SPY ret Stock 1 ret Stock 2 ret Change Stock 1 and Stock 2 with your ticker symbols Method 1 #DIV/0! Method 2 #DIV/01 Beta Stock 1 Avg. Daily Ret Daily Std. Deviation Daily Variance SPY #DIV/0! #DIV/0! #DIV/0! Stock 1 ret #DIV/0! #DIV/0! #DIV/01 Stock 2 ret #DIV/0! #DIV/01 #DIV/01 Beta Stock 2 # #DIV/0! #DIV/01 SPY-Stock 1 #DIV/0! SPY-AMAT #DIV/01 Correlation coefficient ABC-AMAT #DIV/0! Annual Return Annual Std. Deviation SPY #DIV/0! #DIV/0! ABC #DIV/01 #DIV/0! AMAT #DIV/0! #DIV/0! 7/14/17 7/17/17 7/18/17 7/19/17 7/20/17 7/21/17 7/24/17 7/25/17 7/26/17 7/27/17 7/28/17 7/31/17 8/1/17 8/2/17 8/3/17 8/4/17 8/7/17 8/8/17 8/9/17 8/10/17 8/11/17 8/14/17 8/15/17 8/16/17 SPY-Stock 1 #DIV/0! SPY-Stock 2 #DIV/0! Expected return portfolio Std. Deviation portfolio Stock 1 - Stock 2 #DIV/0! #DIV/01 50%/50% 0.500 SPY-Stock 1 #DIV/0! SPY-Stock 2 #DIV/0! 75%/25% 0.75 0.25 Expected return portfolio Std. Deviation portfolio Stock 1 - Stock 2 #DIV/0! #DIV/0! SPY-Stock 1 #DIV/0! SPY-Stock 2 #DIV/0! 25%/75% 0.25 0.75 Expected return portfolio Std. Deviation portfolio . Stock 1 - Stock 2 #DIV/01 #DIV/0! A B D E F G H 1 J K L L M N 0 Date SPY ret Stock 1 ret Stock 2 ret Change Stock 1 and Stock 2 with your ticker symbols Method 1 #DIV/0! Method 2 #DIV/01 Beta Stock 1 Avg. Daily Ret Daily Std. Deviation Daily Variance SPY #DIV/0! #DIV/0! #DIV/0! Stock 1 ret #DIV/0! #DIV/0! #DIV/01 Stock 2 ret #DIV/0! #DIV/01 #DIV/01 Beta Stock 2 # #DIV/0! #DIV/01 SPY-Stock 1 #DIV/0! SPY-AMAT #DIV/01 Correlation coefficient ABC-AMAT #DIV/0! Annual Return Annual Std. Deviation SPY #DIV/0! #DIV/0! ABC #DIV/01 #DIV/0! AMAT #DIV/0! #DIV/0! 7/14/17 7/17/17 7/18/17 7/19/17 7/20/17 7/21/17 7/24/17 7/25/17 7/26/17 7/27/17 7/28/17 7/31/17 8/1/17 8/2/17 8/3/17 8/4/17 8/7/17 8/8/17 8/9/17 8/10/17 8/11/17 8/14/17 8/15/17 8/16/17 SPY-Stock 1 #DIV/0! SPY-Stock 2 #DIV/0! Expected return portfolio Std. Deviation portfolio Stock 1 - Stock 2 #DIV/0! #DIV/01 50%/50% 0.500 SPY-Stock 1 #DIV/0! SPY-Stock 2 #DIV/0! 75%/25% 0.75 0.25 Expected return portfolio Std. Deviation portfolio Stock 1 - Stock 2 #DIV/0! #DIV/0! SPY-Stock 1 #DIV/0! SPY-Stock 2 #DIV/0! 25%/75% 0.25 0.75 Expected return portfolio Std. Deviation portfolio . Stock 1 - Stock 2 #DIV/01 #DIV/0