Obtain monthly stock-price data for the following stocks from Yahoo Finance (http://finance.yahoo.com/). Watch the video Chapter 12, Part 8. Also, the video Using Stock Price Data with Excel (posted with the Chapter 13 material) is also helpful. Please include a list of team members who participated when you turn in your assignment. Those not participating will not receive credit.

Firm Name	Ticker Symbol
Amazon	AMZN
McDonald’s	MCD
Delta Air Lines	DAL

Retrieve the data for the period of 1 December 2015 to 31 March 2018. Open all of the data files in Excel and copy and paste the data into one file - you will only need the adjusted closing price data for your analysis. The adjusted returns are adjusted for stock splits and dividends. Make sure you save your file as an Excel workbook (not CSV). In addition to the stocks listed above, also download monthly values for the S&P 500 Index for the same time period (from the main Yahoo Finance page go down to Markets Summary and select S&P 500 Index – the Yahoo Finance ticker is ^GSPC). Include the S&P 500 index in your Excel files with the stock prices.

a. Calculate monthly returns for each stock and the S&P 500 Index. The return for each month is calculated as:

Return = (D Price + Dividend) / Old Price,

However, because we are using adjusted returns, you will not have a dividend component and the return will be calculated as:

Return = (D Adjusted Price) / Old Adjusted Price,

For example, the return for January is the January close adjusted price minus the December close adjusted price, divided by the December close adjusted price.

b. Calculate the arithmetic average monthly return and standard deviation of returns for all stocks and the S&P 500 index. Make sure to use the sample standard deviation. The Excel function for sample standard deviation is STDEV.S or STDEV.

c. Calculate the covariance and correlation between the returns for all pairs of stocks and between each stock and the S&P 500.

d. Find the beta for each of the stocks. Use the S&P 500 index as the market index. The easiest way to do this is to use the Excel SLOPE function, which will give the slope of the market model. The slope of the market model is the beta.

I cant get the Below date to hoover over the set of numbers but this is the set directly below and they go in order starting with the date and then so on for the rest

1.DAL 2.AMZN 3.MCD 4.SP500 5.DAL RET 6.AMZN RET 7.MCD RET 8.SP500 RET

Date

3/1/2018	54.810001	1447.33997	156.380005	2640.87012	0.02275105	-0.0430493	-0.00235	-0.0268845
2/1/2018	53.590755	1512.44995	156.748367	2713.83008	-0.0505548	0.04242909	-0.0782984	-0.0389474
1/1/2018	56.444286	1450.89002	170.064117	2823.81006	0.01375001	0.24063897	-0.0056937	0.0561787
12/1/2017	55.678703	1169.46997	171.037949	2673.61011	0.06478448	-0.0061866	0.00683895	0.00983163
11/1/2017	52.291054	1176.75	169.876175	2647.58008	0.05776524	0.06466232	0.03031568	0.02808263
10/1/2017	49.435406	1105.28003	164.877792	2575.26001	0.03753634	0.1497166	0.06529237	0.02218814
9/1/2017	47.646915	961.349976	154.772339	2519.36011	0.02842616	-0.0196308	-0.0147555	0.01930298
8/1/2017	46.329933	980.599976	157.090286	2471.6499	-0.0439628	-0.0072689	0.0311332	0.00054643
7/1/2017	48.460388	987.780029	152.347229	2470.30005	-0.0815036	0.02043391	0.01927742	0.01934883
6/1/2017	52.760563	968	149.465912	2423.40991	0.098325	-0.026764	0.01504401	0.00481378
5/1/2017	48.037296	994.619995	147.250671	2411.80005	0.08120595	0.0752765	0.07832494	0.01157625
4/1/2017	44.429367	924.98999	136.555008	2384.19995	-0.0113141	0.04337087	0.07962342	0.00909121
3/1/2017	44.937798	886.539978	126.483925	2362.71997	-0.075857	0.0491101	0.02282784	-0.0003892
2/1/2017	48.626457	845.039978	123.661011	2363.63989	0.05694315	0.02618157	0.04144581	0.03719816
1/1/2017	46.006691	823.47998	118.739746	2278.87012	-0.0396421	0.07131893	0.00698326	0.01788436
12/1/2016	47.905777	768.659973	117.916306	2238.83008	0.02566102	0.02410164	0.02847765	0.01820076
11/1/2016	46.707222	750.570007	114.651306	2198.81006	0.15345943	-0.0496949	0.05951852	0.03417452
10/1/2016	40.493164	789.820007	108.210762	2126.1499	0.06122958	-0.0567173	-0.0241852	-0.0194257
9/1/2016	38.156837	837.309998	110.892723	2168.27002	0.07689042	0.08860319	0.00515755	-0.0012345
8/1/2016	35.432423	769.159973	110.323723	2170.94995	-0.0516129	0.01363975	-0.0169144	-0.0012192
7/1/2016	37.360718	758.809998	112.221893	2173.6001	0.06368383	0.06035326	-0.0151671	0.0356098
6/1/2016	35.123894	715.619995	113.950188	2098.86011	-0.1590576	-0.0099199	-0.0140915	0.00091092
5/1/2016	41.7673	722.789978	115.578865	2096.94995	0.04295671	0.09581702	-0.0350225	0.0153246
4/1/2016	40.047012	659.590027	119.773636	2065.30005	-0.1440018	0.11109428	0.00644487	0.0026994
3/1/2016	46.783989	593.640015	119.006653	2059.73999	0.01232155	0.07442263	0.080571	0.06599111
2/1/2016	46.214554	552.52002	110.133118	1932.22998	0.08918505	-0.0587393	-0.0532396	-0.0041284
1/1/2016	42.430397	587	116.326286	1940.23999	-0.1262576	-0.1315155	0.04774007	-0.0507353
12/1/2015	48.56168	675.890015	111.025902	2043.93994
				Average
				Std Dev

Answer rating: 100% (QA)

Answer a For Return the given formula is directly applied results are same as given in the table in ... View the full answer