Using ca.Finance.Yahoo.com, download the monthly prices over a 5-year period for 5 Canadian companies of your choice. Calculate the monthly rates of return for each stock using “Adj. Close” (which includes dividends and stock splits).
a. Using the Excel functions for average (AVERAGE) and sample standard deviation (STDEV), calculate the average and the standard deviation of the returns for each of the firms.
b. Using Excel’s correlation function (CORREL), find the correlations between each pair of 5 stocks. What are the highest and lowest correlations?
c. Pick 1 more stock that is in the same industry of one of your other stock. Find correlations between pairs of stocks in the same industry. Are the correlations higher than those you found in part (b)? Is this surprising?