2 is American Airlines and 3 is advance auto parts

131.12 153.78 82.51 424 108.09 220.38 113.79 158.76 223.61 4047 91.07 58 18 46.89 The Excel spreadsheet called "Problem Set 4 input" shows closing price data for the S&P 500 and for the component stocks from 9/1/2017 to 2/7/2018, 109 trading days. The stock symbols are listed on one worksheet with an alphabetical list and a number from 1 to 505. The S&P 500 index is #506. Pick a stock. Either look up the symbol of a stock in which you have an interest, like American Airlines Group, Inc., AAL, which is #2, Agilent, which is A, #1, Citigroup, which is C, #79, etc., or just pick a column in the spreadsheet and look up what it is. Do not use #49, which has less than 109 observations. Put the closing price data on that stock and on the S&P 500 index into Excel, R, Stata, or whatever program you choose, compute the percentage changes, of which there will be 108, and regress the percentage change of the stock price (y) on the percentage change in the S&P 500 (x) and a constant. You will have to compute the percentage changes. You may use Excel, Stata, R, or any other regression estimation program. The coefficient on x is the beta of the stock. Report (1) what stock you studied, and what its id (1 to 505) is, (2) the 95% confidence interval for the beta, and (3) a test of the hypothesis that the beta = 1. Computer programs test that beta = 0, which is meaningless here in terms of finance. Greater than 1 is more than average risk, less than 1 is less than average risk. (4) Report what program you used. If someone helps you with this assignment, then the assignment is to do it yourself the last time. (5) Report the coefficient of determination (r square), which is probably low; financial analysts do not care what the r square is, and that's correct, they should not. Any r square > 0 represents a chance to optimize investing. (6) Does the constant look like the riskless interest rate? That is what it is supposed to be, the interest rate on a very safe investment, like a 7-day Treasury Bill. This model is called the Capital Asset Pricing Model (CAPM). After this, you will know how betas are estimated in finance, just like this. 131.12 153.78 82.51 424 108.09 220.38 113.79 158.76 223.61 4047 91.07 58 18 46.89 The Excel spreadsheet called "Problem Set 4 input" shows closing price data for the S&P 500 and for the component stocks from 9/1/2017 to 2/7/2018, 109 trading days. The stock symbols are listed on one worksheet with an alphabetical list and a number from 1 to 505. The S&P 500 index is #506. Pick a stock. Either look up the symbol of a stock in which you have an interest, like American Airlines Group, Inc., AAL, which is #2, Agilent, which is A, #1, Citigroup, which is C, #79, etc., or just pick a column in the spreadsheet and look up what it is. Do not use #49, which has less than 109 observations. Put the closing price data on that stock and on the S&P 500 index into Excel, R, Stata, or whatever program you choose, compute the percentage changes, of which there will be 108, and regress the percentage change of the stock price (y) on the percentage change in the S&P 500 (x) and a constant. You will have to compute the percentage changes. You may use Excel, Stata, R, or any other regression estimation program. The coefficient on x is the beta of the stock. Report (1) what stock you studied, and what its id (1 to 505) is, (2) the 95% confidence interval for the beta, and (3) a test of the hypothesis that the beta = 1. Computer programs test that beta = 0, which is meaningless here in terms of finance. Greater than 1 is more than average risk, less than 1 is less than average risk. (4) Report what program you used. If someone helps you with this assignment, then the assignment is to do it yourself the last time. (5) Report the coefficient of determination (r square), which is probably low; financial analysts do not care what the r square is, and that's correct, they should not. Any r square > 0 represents a chance to optimize investing. (6) Does the constant look like the riskless interest rate? That is what it is supposed to be, the interest rate on a very safe investment, like a 7-day Treasury Bill. This model is called the Capital Asset Pricing Model (CAPM). After this, you will know how betas are estimated in finance, just like this