Obtain monthly stock-price data for the following stocks from Yahoo Finance (http://finance.yahoo.com/). Watch the video Chapter 12,
Question:
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 |
Thermodynamics An Interactive Approach
ISBN: 978-0130351173
1st edition
Authors: Subrata Bhattacharjee