Download 5 years (60 months) of monthly prices for Home Depot (ticker: HD) from Yahoo finance for the period from the first day in January 2016 through the last day in December 2020 into EXCEL. Then do the same for monthly index values for the S&P 500 Index (ticker: SPX) from Investing.com (register for free) for the same time period into EXCEL. Clean up the data for both time series so that you have only the date and the adjusted closing price for HD and S&P 500.

Data:

a. Calculate beta for Home Depot.

b. Create a chart for the data exactly as done in the video, place the chart as a separate sheet in your workbook, and label it so that the stock (HD) is on the Y-axis, and the market (S&P500) is on the X-axis.

Transcribed Image Text:

S&P 500 Historical Data (1) Oct 20 Sep 20 3,363.00 3,507.44 3,588.11 3,209.45 3,050.72 3,154.26 3,050.72 - Date Price Open High Low Vol. Change % Dec 20 3,756.07 3,645.87 3,760.20 3,633.40 - 3.71% Nov 20 3,621.63 3,296.20 3,645.99 3,279.74 - 10.75% 3,269.96 3,385.87 3,549.85 3,233.94 - -2.77% -3.92% 7.01% 5.51% 1.84% 4.53% 12.68% -12.51% -8.41% -0.16% 2.86% 3.40% 2.04% 1.72% -1.81% 1.31% 6.89% -6.58% 3.93% 1.79% 2.97% 7.87% -9.18% 1.79% -6.94% 0.43% 3.03% 3.60% 0.48% 2.16% 0.27% Aug 20 3,500.31 3,288.26 3,514.77 3,284.53 Jul 20 3,271.12 3,105.92 3,279.99 3,101.17 - Jun 20 3,100.29 3,038.78 3,233.13 2,965.66 May 20 3,044.31 2,869.09 3,068.67 2,766.64 Apr 20 2,912.43 2,498.08 2,954.86 2,447.49 Mar 20 2,584.59 2,974.28 3,136.72 2,191.86 - Feb 20 2,954.22 3,235.66 3,393.52 2,855.84 - Jan 20 3,225.52 3,244.67 3,337.77 3,214.64 Dec 19 3,230.78 3,143.85 3,247.93 3,070.33 Nov 19 3,140.98 Oct 19 3,037.56 Sep 19 Aug 19 Jul 19 Jun 19 May 19 Apr 19 2,945.83 2,848.63 2,949.52 2,848.63 Mar 19 2,834.40 2,798.22 2,860.31 2,722.27 Feb 19 2,784.49 2,702.32 2,813.49 2,681.83 - Jan 19 2,704.10 2,476.96 2,708.95 2,443.96 - Dec 18 2,506.85 2,790.50 2,800.18 2,346.58 Nov 18 2,760.17 2,717.58 2,815.15 2,631.09 Oct 18 2,711.74 2,926.29 2,939.86 2,603.54 - Sep 18 2,913.98 2,896.96 2,940.91 2,864.12 Aug 18 2,901.52 2,821.17 2,916.50 2,796.34 - Jul 18 2,816.29 2,704.95 2,848.03 2.698.95 Jun 18 2,718.37 2,718.70 2,791.47 2,691.99 - May 18 2,705.27 2,643.64 2,742.24 2,594.62 Apr 18 2,648.05 2,633.45 2,717.49 2,553.80 Mar 18 2,640.87 2,715.22 2,801.90 2,585.89 Feb 18 2,713.83 2.816.45 2,835.96 2,532.69 - Jan 18 2,823.81 2,683.73 2,872.87 2,682.36 Dec 17 2,673.61 2,645.10 2,694.97 2,605.52 Nov 17 2,647.58 2,583.21 2,657.74 2,557.45 Oct 17 2,575.26 2,521.20 2,582.98 2,520.40 Sep 17 Aug 17 Jul 17 2,983.69 3,050.10 2,855.94 2,976.74 2,909.01 3,021.99 2,891.85 - 2,926.46 2,980.32 3,013.59 2,822.12 2,980.38 2,971.41 3,027.98 2,952.22 2,941.76 2,751.53 2,964.15 2,728.81 2,752.06 2,952.33 2,954.13 2,750.52 2,519.36 2,474.42 2,519.44 2,446.55 2,471.65 2,477.10 2,490.87 2,417.35 Jun 17 May 17 Apr 17 -2.69% -3.89% 5.62% 0.98% 2.81% 2.22% 1.93% 0.05% 1.93% 0.48% 1.16% 0.91% -0.04% 3.72% 1.79% 1.82% 3.42% -1.94% -0.12% -0.12% 3.56% 0.09% 1.53% Mar 17 Feb 17 Jan 17 Dec 16 Nov 16 Oct 16 Sep 16 Aug 16 2,362.72 2,380.13 2,400.98 2,322.25 2,363.64 2,285.59 2,371.54 2,271.65 2,278.87 2,251.57 2,300.99 2,245.13 - 2,238.83 2,200.17 2,277.53 2,187.44 2,198.81 2,128.68 2,214.10 2,083.79 - 2,126.15 2,164.33 2,169.60 2,114.72 - 2,168.27 2.171.33 2,187.87 2,119.12 2,170.95 2,173.15 2,193.81 2,147.58 - 2,173.60 2,099.34 2,177.09 2,074.02 - 2,098.86 2,093.94 2.120.55 1.991.68 2,096.96 2,067.17 2,103.48 2,025.91 - Jul 16 Jun 16 May 16 Apr 16 2,065.30 2,056.62 2.111.05 2,033.80- 2,059.74 1,937.09 2,072.21 1,937.09 Mar 16 1,932.23 1,936.94 1,962.96 1,810.10 Feb 16 Jan 16 1,940.24 2,038.20 2,038.20 1,812.29 - 2,470.30 2,431.39 2,484.04 2,407.70 2,423.41 2,415.65 2,453.82 2,405.70 - 2,411.80 2,388.50 2,418.71 2,352.72 2,384.20 2,362.34 2,398.16 2,328.95 - 0.27% 6.60% -0.41% -5.07% HD Date High Open Adj Close Volume 2016-01-01 130.110001 131.940002 113.589996 125.760002 110.106827 147050100 2016-02-01 124.919998 127.750000 109.620003 124.120003 108.670929 135914000 2016-03-01 124.779999 134.289993 123.980003 133.429993 116.822113 94146700 2016-04-01 133.100006 137.000000 131.750000 133.889999 117.869476 82951100 2016-05-01 134.369995 137.820007 130.020004 132.119995 116.311279 112394600 2016-06-01 132.119995 132.729996 123.620003 127.690002 112.993431 114046600 2016-07-01 128.289993 138.720001 128.119995 138.240005 122.329193 78399700 2016-08-01 138.059998 139.000000 133.600006 134.119995 118.683357 91331300 2016-09-01 134.479996 135.880005 125.349998 128.679993 114.453415 107739700 2016-10-01 128.199997 130.449997 121.620003 122.010002 108.520851 97360300 2016-11-01 121.690002 132.139999 119.199997 129.399994 115.093819 133565300 2016-12-01 129.339996 137.320007 128.679993 134.080002 119.889648 99553700 2017-01-01 135.100006 139.369995 133.050003 137.580002 123.019211 83138300 2017-02-01 137.660004 146.330002 136.330002 144.910004 129.573456 75930200 2017-03-01 146.720001 150.149994 145.830002 146.830002 131.290237 92996000 2017-04-01 146.940002 156.270004 145.759995 156.100006 140.428589 75716700 2017-05-01 156.220001 160.860001 153.279999 153.509995 138.098633 93676400 2017-06-01 153.520004 159.220001 150.750000 153.399994 138.797165 100510600 2017-07-01 154.389999 154.789993 144.250000 149.600006 135.358871 102500900 2017-08-01 150.240005 156.050003 146.889999 149.869995 135.603180 114385100 2017-09-01 150.259995 163.610001 149.759995 163.559998 148.865128 110280500 2017-10-01 164.199997 167.940002 161.509995 165.779999 150.885696 71346000 2017-11-01 166.419998 180.669998 160.529999 179.820007 163.664261 103227500 2017-12-01 180.320007 191.490005 176.699997 189.529999 173.375778 103713100 2018-01-01 190.210007 207.610001 187.820007 200.899994 183.776657 92074000 2018-02-01 199.339996 202.250000 175.419998 182.270004 166.734573 127014100 2018-03-01 182.750000 184.399994 171.559998 178.240005 163.048050 109321100 2018-04-01 177.149994 187.800003 170.419998 184.800003 170.013016 101369800 2018-05-01 184.729996 191.649994 181.199997 186.550003 171.622971 103145700 2018-06-01 187.210007 201.600006 186.520004 195.100006 180.493729 93713000 2018-07-01 193.820007 204.250000 192.119995 197.520004 182.732590 67987900 2018-08-01 196.860001 203.550003 191.089996 200.770004 185.739243 88542700 2018-09-01 200.690002 215.429993 200.500000 207.149994 192.623932 83962700 2018-10-01 208.520004 209.789993 170.910004 175.880005 163.546722 133142600 2018-11-01 176.839996 188.690002 167.000000 180.320007 167.675354 132549500 2018-12-01 183.289993 183.500000 158.089996 171.820007 160.727127 117667300 2019-01-01 169.710007 184.669998 168.210007 183.529999 171.681076 98771800 2019-02-01 184.029999 193.419998 182.449997 185.139999 173.187164 90471400 2019-03-01 185.820007 192.190002 179.520004 191.889999 179.501404 95115500 2019-04-01 192.990005 208.300003 192.850006 203.699997 191.967834 71203800 2019-05-01 203.199997 203.520004 186.270004 189.850006 178.915512 94651500 2019-06-01 189.520004 211.990005 188.750000 207.970001 195.991913 90786400 2019-07-01 209.699997 219.300003 208.169998 213.690002 202.795013 64167400 2019-08-01 214.139999 229.270004 199.050003 227.910004 216.289993 99184500 2019-09-01 226.449997 235.490005 220.669998 232.020004 220.190460 75292900 2019-10-01 233.009995 238.990005 222.119995 234.580002 223.979355 62599000 2019-11-01 236.070007 239.309998 216.880005 220.509995 210.545212 102466800 2019-12-01 220.899994 222.000000 210.610001 218.380005 208.511444 111759300 2020-01-01 219.080002 236.529999 216.399994 228.100006 219.185150 97265400 2020-02-01 230.300003 247.360001 212.330002 217.839996 209.326157 86009000 2020-03-01 219.979996 241.320007 140.630005 186.710007 179.412857 221563200 2020-04-01 175.910004 224.220001 174.000000 219.830002 212.651672 120713600 2020-05-01 216.770004 252.229996 215.210007 248.479996 240.366135 115230300 2020-06-01 249.410004 259.290009 234.309998 250.509995 242.329834 103047000 2020-07-01 249.649994 269.070007 246.220001 265.489990 258.354187 66948200 2020-08-01 266.730011 292.950012 263.839996 285.040009 277.378723 74350500 2020-09-01 284.029999 288.040009 262.809998 277.709991 270.245728 80493700 2020-10-01 279.440002 292.649994 262.029999 266.709991 260.910065 59503400 2020-11-01 270.149994 289.000000 268.519989 277.410004 271.377411 82679200 2020-12-01 278.730011 278.950012 258.730011 265.619995 259.843781 92268500 Low Close S&P 500 Historical Data (1) Oct 20 Sep 20 3,363.00 3,507.44 3,588.11 3,209.45 3,050.72 3,154.26 3,050.72 - Date Price Open High Low Vol. Change % Dec 20 3,756.07 3,645.87 3,760.20 3,633.40 - 3.71% Nov 20 3,621.63 3,296.20 3,645.99 3,279.74 - 10.75% 3,269.96 3,385.87 3,549.85 3,233.94 - -2.77% -3.92% 7.01% 5.51% 1.84% 4.53% 12.68% -12.51% -8.41% -0.16% 2.86% 3.40% 2.04% 1.72% -1.81% 1.31% 6.89% -6.58% 3.93% 1.79% 2.97% 7.87% -9.18% 1.79% -6.94% 0.43% 3.03% 3.60% 0.48% 2.16% 0.27% Aug 20 3,500.31 3,288.26 3,514.77 3,284.53 Jul 20 3,271.12 3,105.92 3,279.99 3,101.17 - Jun 20 3,100.29 3,038.78 3,233.13 2,965.66 May 20 3,044.31 2,869.09 3,068.67 2,766.64 Apr 20 2,912.43 2,498.08 2,954.86 2,447.49 Mar 20 2,584.59 2,974.28 3,136.72 2,191.86 - Feb 20 2,954.22 3,235.66 3,393.52 2,855.84 - Jan 20 3,225.52 3,244.67 3,337.77 3,214.64 Dec 19 3,230.78 3,143.85 3,247.93 3,070.33 Nov 19 3,140.98 Oct 19 3,037.56 Sep 19 Aug 19 Jul 19 Jun 19 May 19 Apr 19 2,945.83 2,848.63 2,949.52 2,848.63 Mar 19 2,834.40 2,798.22 2,860.31 2,722.27 Feb 19 2,784.49 2,702.32 2,813.49 2,681.83 - Jan 19 2,704.10 2,476.96 2,708.95 2,443.96 - Dec 18 2,506.85 2,790.50 2,800.18 2,346.58 Nov 18 2,760.17 2,717.58 2,815.15 2,631.09 Oct 18 2,711.74 2,926.29 2,939.86 2,603.54 - Sep 18 2,913.98 2,896.96 2,940.91 2,864.12 Aug 18 2,901.52 2,821.17 2,916.50 2,796.34 - Jul 18 2,816.29 2,704.95 2,848.03 2.698.95 Jun 18 2,718.37 2,718.70 2,791.47 2,691.99 - May 18 2,705.27 2,643.64 2,742.24 2,594.62 Apr 18 2,648.05 2,633.45 2,717.49 2,553.80 Mar 18 2,640.87 2,715.22 2,801.90 2,585.89 Feb 18 2,713.83 2.816.45 2,835.96 2,532.69 - Jan 18 2,823.81 2,683.73 2,872.87 2,682.36 Dec 17 2,673.61 2,645.10 2,694.97 2,605.52 Nov 17 2,647.58 2,583.21 2,657.74 2,557.45 Oct 17 2,575.26 2,521.20 2,582.98 2,520.40 Sep 17 Aug 17 Jul 17 2,983.69 3,050.10 2,855.94 2,976.74 2,909.01 3,021.99 2,891.85 - 2,926.46 2,980.32 3,013.59 2,822.12 2,980.38 2,971.41 3,027.98 2,952.22 2,941.76 2,751.53 2,964.15 2,728.81 2,752.06 2,952.33 2,954.13 2,750.52 2,519.36 2,474.42 2,519.44 2,446.55 2,471.65 2,477.10 2,490.87 2,417.35 Jun 17 May 17 Apr 17 -2.69% -3.89% 5.62% 0.98% 2.81% 2.22% 1.93% 0.05% 1.93% 0.48% 1.16% 0.91% -0.04% 3.72% 1.79% 1.82% 3.42% -1.94% -0.12% -0.12% 3.56% 0.09% 1.53% Mar 17 Feb 17 Jan 17 Dec 16 Nov 16 Oct 16 Sep 16 Aug 16 2,362.72 2,380.13 2,400.98 2,322.25 2,363.64 2,285.59 2,371.54 2,271.65 2,278.87 2,251.57 2,300.99 2,245.13 - 2,238.83 2,200.17 2,277.53 2,187.44 2,198.81 2,128.68 2,214.10 2,083.79 - 2,126.15 2,164.33 2,169.60 2,114.72 - 2,168.27 2.171.33 2,187.87 2,119.12 2,170.95 2,173.15 2,193.81 2,147.58 - 2,173.60 2,099.34 2,177.09 2,074.02 - 2,098.86 2,093.94 2.120.55 1.991.68 2,096.96 2,067.17 2,103.48 2,025.91 - Jul 16 Jun 16 May 16 Apr 16 2,065.30 2,056.62 2.111.05 2,033.80- 2,059.74 1,937.09 2,072.21 1,937.09 Mar 16 1,932.23 1,936.94 1,962.96 1,810.10 Feb 16 Jan 16 1,940.24 2,038.20 2,038.20 1,812.29 - 2,470.30 2,431.39 2,484.04 2,407.70 2,423.41 2,415.65 2,453.82 2,405.70 - 2,411.80 2,388.50 2,418.71 2,352.72 2,384.20 2,362.34 2,398.16 2,328.95 - 0.27% 6.60% -0.41% -5.07% HD Date High Open Adj Close Volume 2016-01-01 130.110001 131.940002 113.589996 125.760002 110.106827 147050100 2016-02-01 124.919998 127.750000 109.620003 124.120003 108.670929 135914000 2016-03-01 124.779999 134.289993 123.980003 133.429993 116.822113 94146700 2016-04-01 133.100006 137.000000 131.750000 133.889999 117.869476 82951100 2016-05-01 134.369995 137.820007 130.020004 132.119995 116.311279 112394600 2016-06-01 132.119995 132.729996 123.620003 127.690002 112.993431 114046600 2016-07-01 128.289993 138.720001 128.119995 138.240005 122.329193 78399700 2016-08-01 138.059998 139.000000 133.600006 134.119995 118.683357 91331300 2016-09-01 134.479996 135.880005 125.349998 128.679993 114.453415 107739700 2016-10-01 128.199997 130.449997 121.620003 122.010002 108.520851 97360300 2016-11-01 121.690002 132.139999 119.199997 129.399994 115.093819 133565300 2016-12-01 129.339996 137.320007 128.679993 134.080002 119.889648 99553700 2017-01-01 135.100006 139.369995 133.050003 137.580002 123.019211 83138300 2017-02-01 137.660004 146.330002 136.330002 144.910004 129.573456 75930200 2017-03-01 146.720001 150.149994 145.830002 146.830002 131.290237 92996000 2017-04-01 146.940002 156.270004 145.759995 156.100006 140.428589 75716700 2017-05-01 156.220001 160.860001 153.279999 153.509995 138.098633 93676400 2017-06-01 153.520004 159.220001 150.750000 153.399994 138.797165 100510600 2017-07-01 154.389999 154.789993 144.250000 149.600006 135.358871 102500900 2017-08-01 150.240005 156.050003 146.889999 149.869995 135.603180 114385100 2017-09-01 150.259995 163.610001 149.759995 163.559998 148.865128 110280500 2017-10-01 164.199997 167.940002 161.509995 165.779999 150.885696 71346000 2017-11-01 166.419998 180.669998 160.529999 179.820007 163.664261 103227500 2017-12-01 180.320007 191.490005 176.699997 189.529999 173.375778 103713100 2018-01-01 190.210007 207.610001 187.820007 200.899994 183.776657 92074000 2018-02-01 199.339996 202.250000 175.419998 182.270004 166.734573 127014100 2018-03-01 182.750000 184.399994 171.559998 178.240005 163.048050 109321100 2018-04-01 177.149994 187.800003 170.419998 184.800003 170.013016 101369800 2018-05-01 184.729996 191.649994 181.199997 186.550003 171.622971 103145700 2018-06-01 187.210007 201.600006 186.520004 195.100006 180.493729 93713000 2018-07-01 193.820007 204.250000 192.119995 197.520004 182.732590 67987900 2018-08-01 196.860001 203.550003 191.089996 200.770004 185.739243 88542700 2018-09-01 200.690002 215.429993 200.500000 207.149994 192.623932 83962700 2018-10-01 208.520004 209.789993 170.910004 175.880005 163.546722 133142600 2018-11-01 176.839996 188.690002 167.000000 180.320007 167.675354 132549500 2018-12-01 183.289993 183.500000 158.089996 171.820007 160.727127 117667300 2019-01-01 169.710007 184.669998 168.210007 183.529999 171.681076 98771800 2019-02-01 184.029999 193.419998 182.449997 185.139999 173.187164 90471400 2019-03-01 185.820007 192.190002 179.520004 191.889999 179.501404 95115500 2019-04-01 192.990005 208.300003 192.850006 203.699997 191.967834 71203800 2019-05-01 203.199997 203.520004 186.270004 189.850006 178.915512 94651500 2019-06-01 189.520004 211.990005 188.750000 207.970001 195.991913 90786400 2019-07-01 209.699997 219.300003 208.169998 213.690002 202.795013 64167400 2019-08-01 214.139999 229.270004 199.050003 227.910004 216.289993 99184500 2019-09-01 226.449997 235.490005 220.669998 232.020004 220.190460 75292900 2019-10-01 233.009995 238.990005 222.119995 234.580002 223.979355 62599000 2019-11-01 236.070007 239.309998 216.880005 220.509995 210.545212 102466800 2019-12-01 220.899994 222.000000 210.610001 218.380005 208.511444 111759300 2020-01-01 219.080002 236.529999 216.399994 228.100006 219.185150 97265400 2020-02-01 230.300003 247.360001 212.330002 217.839996 209.326157 86009000 2020-03-01 219.979996 241.320007 140.630005 186.710007 179.412857 221563200 2020-04-01 175.910004 224.220001 174.000000 219.830002 212.651672 120713600 2020-05-01 216.770004 252.229996 215.210007 248.479996 240.366135 115230300 2020-06-01 249.410004 259.290009 234.309998 250.509995 242.329834 103047000 2020-07-01 249.649994 269.070007 246.220001 265.489990 258.354187 66948200 2020-08-01 266.730011 292.950012 263.839996 285.040009 277.378723 74350500 2020-09-01 284.029999 288.040009 262.809998 277.709991 270.245728 80493700 2020-10-01 279.440002 292.649994 262.029999 266.709991 260.910065 59503400 2020-11-01 270.149994 289.000000 268.519989 277.410004 271.377411 82679200 2020-12-01 278.730011 278.950012 258.730011 265.619995 259.843781 92268500 Low Close

Answer rating: 100% (QA)

Date Adj Close HD Adj close SP Stock HD Market SP Beta 112016 109653305 375607 00131265 0036449 0041... View the full answer