For this exercise you would need to use the spreadsheet ps2.xls posted on the Blackboard.
The file contains information about monthly returns of twelve stocks, AMGN, AMZN, STX, MSFT, BA, WMT, PFE, ABB, YHOO, AAPL, VZ, GOOG, as well as the value-weighted market index excess return (MKT-RF), and the risk-free rate (RF). The sample period begins on January 2004 and ends December 2012.
The spreadsheet is set in a way that you would only need to change the values in cells H122 and T123 in order to control the portfolio weights and the stock/portfolio chosen for the regression calculation.
(a) Calculate the Beta and of each stock by running a regression of the monthly returns of the stock (excess of the risk-free rate) on the monthly returns of the value-weighted market index (excess of the risk-free rate ). The latter are denoted MKT-RF and they are located on Column P (notice the risk-free rate has already been subtracted from the index). You can choose different stocks by changing the values in cell T123. This would immediately provide you with the excess returns of each stock in Column T. These returns should be used for the regression.