Go to finance.yahoo.com and download five years of historical monthly closing prices for any 10 of the 30 Dow Components (https://finance.yahoo.com/quote/%5EDJI/components?p=%5EDJI) and the S&P 500 Index (^GSPC). For Time Period, use Start Date 01/02/2015 and End Date 01/03/2020, and Frequency Monthly. Download the data into an Excel file and use the AdjustedClose prices, which adjust for dividend payments, to calculate the monthly rate of return for each price series. You should have 61 monthly prices and 60 monthly returns for each series. Also download the monthly 13 Week Treasury Bill Rate (^IRX) using Start Date 01/02/2015 and End Date 12/03/2019, and Frequency Monthly. You should have 60 monthly T bill rates. Since Tbill rates are reported as annualized rate and should be in percentages, you should divide Tbill rates by 100 and 12 to get the monthly rate in decimals.

Copy the returns of these 10 stocks and the S&P 500 Index, and the Tbill rates into a single Excel workbook, with the returns (rates) for each series properly aligned. Form a nave equally weighted portfolio of the 10 stocks and calculate the monthly rate of return of this portfolio. Use the full range of available data. Then do the following:

1. Use an XY scatter plot chart with no line joining the points to plot a stock's returns against the S&P 500. Now select one of the data points, and rightclick to obtain a shortcut menu allowing you to enter a trend line. This is the stock's characteristic line, and the slope is the stock's beta. Repeat this process for all stocks. What conclusions can you draw from each company's characteristic line? Also, do it for the nave portfolio and compare the characteristic line of this portfolio with the individual stocks' characteristic lines. What difference do you find?

2. Using the Excel functions for average (AVERAGE) and sample standard deviation (STDEV.S), calculate the average and the standard deviation of all 13 series. Calculate Sharpe ratio for the 10 stocks, the nave portfolio, and the S&P 500 Index.

3. Using Excel's correlation function (CORREL), construct the correlation matrix for the 13 series based on their monthly returns (rates) for the entire period. What are the lowest and the highest individual pairs of correlation coefficients among the 10 stocks? (Alternative: You may use Excel's Data Analysis Tool to generate the correlation matrix.)