1. First download into Excel the Best Buy (BBY), Activision-Blizzard (ATVI), and S&P 500 Stock Index (GSPC) monthly price data for the 5-year period from the Oct. 1, 2014 to Sept. 30, 2019 using the instructions outlined above. Using the Open and Close price data for each month (the date given is the first trading day for that month: i.e. the open and close for the month) to calculate the monthly percentage holding period returns for each stock and index where each month's Open price is the beginning of the month price and each month's Close price is the end of the month price. You can get rid of the other data provided for each month. In addition, you will need to include dividend payments for each stock to calculate monthly returns for the months that they made a dividend payment (the index doesn't have dividend payments). For example, September's monthly return is (Sept Close - Sept. Open + any Sept Div)/Sept Open. You should have 60 monthly returns for each stock and index from Oct. 2014 to Sept. 2019. Helpful Hint: I strongly urge creating a table of the appropriate opening & closing prices and dividends for each stock along with opening and closing prices for the S&P 500 Index on a separate worksheet and then do your calculations on this fresh worksheet. (6 pts) 2. What is the average monthly return for each stock and the S&P 500 index over the entire time period? Convert this average monthly return for each stock and index into an APR (annualized rate) by multiplying your monthly average for each stock and index by 12. Please convert your answers to percentages (4 pts) 3. Calculate the (population) standard deviation of monthly returns for each stock and the index using Excel. (2 pts) 1. First download into Excel the Best Buy (BBY), Activision-Blizzard (ATVI), and S&P 500 Stock Index (GSPC) monthly price data for the 5-year period from the Oct. 1, 2014 to Sept. 30, 2019 using the instructions outlined above. Using the Open and Close price data for each month (the date given is the first trading day for that month: i.e. the open and close for the month) to calculate the monthly percentage holding period returns for each stock and index where each month's Open price is the beginning of the month price and each month's Close price is the end of the month price. You can get rid of the other data provided for each month. In addition, you will need to include dividend payments for each stock to calculate monthly returns for the months that they made a dividend payment (the index doesn't have dividend payments). For example, September's monthly return is (Sept Close - Sept. Open + any Sept Div)/Sept Open. You should have 60 monthly returns for each stock and index from Oct. 2014 to Sept. 2019. Helpful Hint: I strongly urge creating a table of the appropriate opening & closing prices and dividends for each stock along with opening and closing prices for the S&P 500 Index on a separate worksheet and then do your calculations on this fresh worksheet. (6 pts) 2. What is the average monthly return for each stock and the S&P 500 index over the entire time period? Convert this average monthly return for each stock and index into an APR (annualized rate) by multiplying your monthly average for each stock and index by 12. Please convert your answers to percentages (4 pts) 3. Calculate the (population) standard deviation of monthly returns for each stock and the index using Excel. (2 pts)
