Description
check the file and see the instructions. You may use this Excel sheetfor assignment as this contains data for five years monthly. checkthe attached file. please be specific. there is one attached Example file. you may just get data from different 2 company do the equations and replace all.
Unformatted Attachment Preview
Date
Open
High
Low
Close
Aplhabet Volume
Date
########
125.34
138.88
124.925
137.92
137.92 2.76E+08
########
########
132.155
142.38
121.46
125.3
125.3 5.15E+08
########
9/1/2023
138.43
139.93
128.19
131.85
131.85
3.9E+08
9/1/2023
8/1/2023
130.855
138.4
127
137.35
137.35 4.63E+08
8/1/2023
7/1/2023
120.32
134.07
115.83
133.11
133.11 5.25E+08
7/1/2023
6/1/2023
123.5
129.55
116.91
120.97
120.97 5.21E+08
6/1/2023
5/1/2023
107.72
127.05
104.5
123.37
123.37
6.2E+08
5/1/2023
4/1/2023
102.67
109.63
102.38
108.22
108.22 4.62E+08
4/1/2023
3/1/2023
90.16
107.51
89.77
104
104 7.25E+08
3/1/2023
2/1/2023
1/1/2023
########
########
########
9/1/2022
8/1/2022
7/1/2022
6/1/2022
5/1/2022
4/1/2022
3/1/2022
2/1/2022
1/1/2022
########
########
########
9/1/2021
8/1/2021
7/1/2021
6/1/2021
5/1/2021
4/1/2021
3/1/2021
2/1/2021
1/1/2021
########
99.74
89.83
101.4
95.59
97.22
109.2
115.53
108.337
114.9315
113.9065
140.01
134.48
137.835
144.4755
144.2125
148.165
133.5545
145.65
135.4845
124.8497
121.1
120.136
104.8975
102.826
92.6785
87.877
88.7185
108.82
101.58
102.59
101.45
105.1
112.64
123.26
120.435
119.3985
123.143
144.0437
144.1625
152.1
146.61
149.605
151.85
149.118
146.8205
146.4895
140.011
127.796
122.1472
122.6189
106.4405
107.634
96.743
92.36
88.86
85.57
86.37
83.45
91.9
96.03
108.8
104.76
105.6285
102.208
113.1243
125.8607
124.7645
124.642
140.25
142.066
131.1665
133
134.1835
124.8497
119.1415
111.5025
104.8445
100.5
92.5465
84.95
84.95
90.3
99.87
88.73
101.45
94.66
96.15
109.15
116.64
109.3725
114.039
114.9665
139.6495
134.891
135.6985
144.6795
142.452
148.2705
133.2655
145.462
135.221
125.316
120.578
120.506
103.4315
101.843
91.787
87.594
90.3
99.87
88.73
101.45
94.66
96.15
109.15
116.64
109.3725
114.039
114.9665
139.6495
134.891
135.6985
144.6795
142.452
148.2705
133.2655
145.462
135.221
125.316
120.578
120.506
103.4315
101.843
91.787
87.594
7.54E+08
5.26E+08
4.99E+08
6.03E+08
5.85E+08
5.33E+08
4.31E+08
6.39E+08
6.27E+08
7.17E+08
5.65E+08
6.37E+08
7.33E+08
6.16E+08
4.79E+08
4.43E+08
4.97E+08
5.05E+08
3.72E+08
4.57E+08
5.49E+08
5.09E+08
6.14E+08
6.96E+08
5.89E+08
6.62E+08
6.29E+08
2/1/2023
1/1/2023
########
########
########
9/1/2022
8/1/2022
7/1/2022
6/1/2022
5/1/2022
4/1/2022
3/1/2022
2/1/2022
1/1/2022
########
########
########
9/1/2021
8/1/2021
7/1/2021
6/1/2021
5/1/2021
4/1/2021
3/1/2021
2/1/2021
1/1/2021
########
########
########
9/1/2020
8/1/2020
7/1/2020
6/1/2020
5/1/2020
4/1/2020
3/1/2020
2/1/2020
1/1/2020
########
########
########
9/1/2019
8/1/2019
7/1/2019
6/1/2019
5/1/2019
4/1/2019
3/1/2019
2/1/2019
1/1/2019
########
81.408
90.903 80.8015
74.2135
84.35
71.8
81.8315
86.659 70.3275
74.332
82.961 72.9325
70.555 79.3495
70.491
70.9195 73.79705 67.3505
66.425
72.05
64.95
56.1 67.9995 53.9905
67.5805 70.5075 50.6768
73.1 76.6053
63.55
67.0775 75.1607 67.0775
65.05
68.25
63.95
63.25 66.77645
63.025
60.95 64.9655 58.1215
58.8515
62.415
58.16
60.7015 61.7055
57.007
54.9 63.2775 54.68515
53.275 56.2055
51.25
59.4025 59.5425
55.009
59.205 64.4635
58.75
56.245 61.5895
56.165
55.62
57.35
54.3
50.8285 55.8665 50.7035
56.157 56.2325 48.5055
88.037
81.0505
73.48
81.709
74.148
70.6805
71.446
67.433
58.1405
66.9665
71.7115
66.851
65.248
63.0055
60.95
59.405
60.834
54.0455
55.1815
59.424
58.6655
55.996
55.8185
51.7805
88.037
81.0505
73.48
81.709
74.148
70.6805
71.446
67.433
58.1405
66.9665
71.7115
66.851
65.248
63.0055
60.95
59.405
60.834
54.0455
55.1815
59.424
58.6655
55.996
55.8185
51.7805
7.01E+08
7.85E+08
8.11E+08
7.57E+08
7.05E+08
7.77E+08
6.37E+08
9.27E+08
1.43E+09
7.42E+08
6.74E+08
5.47E+08
4.98E+08
5.75E+08
5.38E+08
5.78E+08
6.59E+08
6.82E+08
6.66E+08
5.57E+08
6.32E+08
5.79E+08
6.14E+08
8.05E+08
########
########
9/1/2020
8/1/2020
7/1/2020
6/1/2020
5/1/2020
4/1/2020
3/1/2020
2/1/2020
1/1/2020
########
########
########
9/1/2019
8/1/2019
7/1/2019
6/1/2019
5/1/2019
4/1/2019
3/1/2019
2/1/2019
1/1/2019
########
Open
High
Low
Close
MSFT
Volume
339.79
378.87
339.65
377.44 376.6755
316.28
346.2
311.21
338.11 337.4251 5.41E+08
331.31
340.86
309.45
315.75 315.1104 4.17E+08
335.19
338.54
311.55
327.76 326.4051 4.79E+08
339.19
366.78
327
335.92 334.5313 6.67E+08
325.93
351.47
322.5
340.54 339.1322 5.48E+08
306.97
335.94
303.4
328.39 326.3191 6.01E+08
286.52
308.93
275.37
307.26 305.3224 5.51E+08
250.76
289.27
245.61
288.3 286.4819 7.48E+08
248
243.08
253.87
234.6
235.41
258.87
277.82
256.39
275.2
277.71
309.37
296.4
310.41
335.35
335.13
331.36
282.12
302.87
286.36
269.61
251.23
253.4
238.47
235.9
235.06
222.53
214.51
276.76
249.83
263.92
255.33
251.04
267.45
294.18
282
277.69
290.88
315.11
315.95
315.12
338
344.3
349.67
332
305.32
305.84
290.15
271.65
254.35
263.19
241.05
246.13
242.64
227.18
245.47
219.35
233.87
213.43
219.13
232.73
260.66
245.94
241.51
246.44
270
270
271.52
276.05
317.25
326.37
280.25
281.62
283.74
269.6
243
238.07
238.05
224.26
227.88
211.94
209.11
249.42
247.81
239.82
255.14
232.13
232.9
261.47
280.74
256.83
271.87
277.52
308.31
298.79
310.98
336.32
330.59
331.62
281.92
301.88
284.91
270.9
249.68
252.18
235.77
232.38
231.96
222.42
247.2278
245.632
237.7123
252.1869
229.4432
230.2043
257.8962
276.9028
253.3196
267.5309
273.0908
303.3893
293.4146
305.3853
330.2694
324.0497
325.0593
276.3426
295.3423
278.7398
265.0332
243.71
246.1502
230.1326
226.3024
225.8934
216.6029
4E+08
6.16E+08
6.66E+08
5.91E+08
6.15E+08
6.71E+08
5.76E+08
4.77E+08
5.35E+08
6.21E+08
7.43E+08
6.27E+08
7.34E+08
6.97E+08
9.48E+08
6.26E+08
5.1E+08
5.17E+08
5.03E+08
4.41E+08
5.23E+08
5.09E+08
4.95E+08
5.69E+08
7.25E+08
4.91E+08
6.48E+08
5.95E+08
Oct 31, 2023
4,201.27
Sep 30, 2023
4,284.52
Aug 31, 2023
4,530.60
Jul 31, 2023
4,578.83
Jun 30, 2023
4,450.48
May 31, 2023
4,183.03
Apr 30, 2023
4,166.79
Mar 31, 2023
4,102.20
Mar 01, 2023
3,963.34
Feb 01, 2023
4,070.07
Jan 01, 2023
Dec 01, 2022
Oct 31, 2022
Sep 30, 2022
Aug 31, 2022
Jul 31, 2022
Jun 30, 2022
May 31, 2022
Apr 30, 2022
Mar 31, 2022
Mar 01, 2022
Feb 01, 2022
Jan 01, 2022
Dec 01, 2021
Oct 31, 2021
Sep 30, 2021
Aug 31, 2021
Jul 31, 2021
Jun 30, 2021
May 31, 2021
Apr 30, 2021
Mar 31, 2021
Mar 01, 2021
Feb 01, 2021
Jan 01, 2021
Dec 01, 2020
3,853.29
4,087.14
3,901.79
3,609.78
3,936.73
4,112.38
3,781.00
4,149.78
4,130.61
4,540.32
4,363.14
4,519.57
4,778.14
4,602.82
4,610.62
4,317.16
4,528.80
4,406.86
4,300.73
4,216.52
4,191.98
3,992.78
3,842.51
3,731.17
3,764.61
3,645.87
204.29
213.49
225.51
211.52
203.14
182.54
175.8
153
165.31
170.43
158.78
151.81
144.26
139.66
136.61
137
136.63
123.85
130.53
118.95
112.89
103.78
99.55
113
228.12
225.21
232.86
231.15
216.38
204.4
187.51
180.4
175
190.7
174.05
159.55
152.5
145.67
142.37
140.94
141.68
138.4
130.65
131.37
120.82
113.24
107.9
113.42
200.12
199.62
196.25
203.14
197.51
181.35
173.8
150.36
132.52
152
156.51
146.65
142.97
133.22
134.51
130.78
134.67
119.01
123.04
118.1
108.8
102.35
97.2
93.96
214.07
202.47
210.33
225.53
205.01
203.51
183.25
179.21
157.71
162.01
170.23
157.7
151.38
143.37
139.03
137.86
136.27
133.96
123.68
130.6
117.94
112.03
104.43
101.57
207.9269
196.6598
204.2943
218.5298
198.6467
197.1933
177.069
173.1652
152.3904
156.119
164.0401
151.9657
145.3808
137.6882
133.5202
131.9572
130.4353
128.2242
117.9478
124.547
112.4738
106.3834
99.16642
96.45059
5.73E+08
6.32E+08
7.68E+08
6.92E+08
7.7E+08
7.65E+08
6.89E+08
9.85E+08
1.61E+09
8.88E+08
5.59E+08
4.5E+08
3.92E+08
5.5E+08
4.73E+08
5.84E+08
4.84E+08
5.08E+08
5.47E+08
4.33E+08
5.89E+08
4.69E+08
7.14E+08
9.44E+08
Oct 31, 2020
Sep 30, 2020
Aug 31, 2020
Jul 31, 2020
Jun 30, 2020
May 31, 2020
Apr 30, 2020
Mar 31, 2020
Mar 01, 2020
Feb 01, 2020
Jan 01, 2020
Dec 01, 2019
Oct 31, 2019
Sep 30, 2019
Aug 31, 2019
Jul 31, 2019
Jun 30, 2019
May 31, 2019
Apr 30, 2019
Mar 31, 2019
Mar 01, 2019
Feb 01, 2019
Jan 01, 2019
Dec 01, 2018
3,296.20
3,385.87
3,507.44
3,288.26
3,105.92
3,038.78
2,869.09
2,498.08
2,974.28
3,235.66
3,244.67
3,143.85
3,050.72
2,983.69
2,909.01
2,980.32
2,971.41
2,751.53
2,952.33
2,848.63
2,798.22
2,702.32
2,476.96
2,790.50
SP500
4,557.11
4,197.74
4,547.38
4,547.38
55,968,890,000
4,393.57
4,103.78
4,193.80
4,193.80
79,952,900,000
4,541.25
4,238.63
4,288.05
4,288.05
73,482,980,000
4,584.62
4,335.31
4,507.66
4,507.66
86,840,820,000
4,607.07
4,385.05
4,588.96
4,588.96
75,063,200,000
4,458.48
4,171.64
4,450.38
4,450.38
87,983,140,000
4,231.10
4,048.28
4,179.83
4,179.83
88,929,200,000
4,170.06
4,049.35
4,169.48
4,169.48
70,861,260,000
4,110.75
3,808.86
4,109.31
4,109.31 113,094,800,000
4,195.44
3,943.08
3,970.15
3,970.15
80,392,280,000
4,094.21
4,100.96
4,080.11
3,905.42
4,119.28
4,325.28
4,140.15
4,177.51
4,307.66
4,593.45
4,637.30
4,595.31
4,818.62
4,808.93
4,743.83
4,608.08
4,545.85
4,537.36
4,429.97
4,302.43
4,238.04
4,218.78
3,994.41
3,950.43
3,870.90
3,760.20
3,794.33
3,764.49
3,698.15
3,491.58
3,584.13
3,954.53
3,721.56
3,636.87
3,810.32
4,124.28
4,157.87
4,114.65
4,222.62
4,495.12
4,560.00
4,278.94
4,305.91
4,367.73
4,233.13
4,164.40
4,056.88
3,992.78
3,723.34
3,725.62
3,662.71
3,633.40
4,076.60
3,839.50
4,080.11
3,871.98
3,585.62
3,955.00
4,130.29
3,785.38
4,132.15
4,131.93
4,530.41
4,373.94
4,515.55
4,766.18
4,567.00
4,605.38
4,307.54
4,522.68
4,395.26
4,297.50
4,204.11
4,181.17
3,972.89
3,811.15
3,714.24
3,756.07
4,076.60
3,839.50
4,080.11
3,871.98
3,585.62
3,955.00
4,130.29
3,785.38
4,132.15
4,131.93
4,530.41
4,373.94
4,515.55
4,766.18
4,567.00
4,605.38
4,307.54
4,522.68
4,395.26
4,297.50
4,204.11
4,181.17
3,972.89
3,811.15
3,714.24
3,756.07
80,763,810,000
85,249,330,000
92,671,910,000
95,823,760,000
94,241,020,000
92,252,350,000
81,688,320,000
106,116,710,000
108,860,390,000
90,367,840,000
123,546,260,000
92,667,710,000
95,562,890,000
92,750,180,000
88,268,840,000
80,253,600,000
85,528,860,000
80,500,760,000
84,255,620,000
102,544,180,000
88,321,860,000
83,124,090,000
122,371,150,000
99,082,320,000
106,117,800,000
96,375,680,000
3,645.99
3,549.85
3,588.11
3,514.77
3,279.99
3,233.13
3,068.67
2,954.86
3,136.72
3,393.52
3,337.77
3,247.93
3,154.26
3,050.10
3,021.99
3,013.59
3,027.98
2,964.15
2,954.13
2,949.52
2,860.31
2,813.49
2,708.95
2,800.18
3,279.74
3,233.94
3,209.45
3,284.53
3,101.17
2,965.66
2,766.64
2,447.49
2,191.86
2,855.84
3,214.64
3,070.33
3,050.72
2,855.94
2,891.85
2,822.12
2,952.22
2,728.81
2,750.52
2,848.63
2,722.27
2,681.83
2,443.96
2,346.58
3,621.63
3,269.96
3,363.00
3,500.31
3,271.12
3,100.29
3,044.31
2,912.43
2,584.59
2,954.22
3,225.52
3,230.78
3,140.98
3,037.56
2,976.74
2,926.46
2,980.38
2,941.76
2,752.06
2,945.83
2,834.40
2,784.49
2,704.10
2,506.85
3,621.63
3,269.96
3,363.00
3,500.31
3,271.12
3,100.29
3,044.31
2,912.43
2,584.59
2,954.22
3,225.52
3,230.78
3,140.98
3,037.56
2,976.74
2,926.46
2,980.38
2,941.76
2,752.06
2,945.83
2,834.40
2,784.49
2,704.10
2,506.85
101,247,180,000
89,938,980,000
92,310,780,000
82,466,520,000
96,928,130,000
131,458,880,000
107,135,190,000
123,608,160,000
162,185,380,000
84,436,590,000
77,287,980,000
72,325,540,000
72,410,620,000
77,720,640,000
74,178,980,000
80,269,220,000
70,599,470,000
71,250,630,000
77,250,740,000
70,090,370,000
79,159,660,000
70,638,770,000
80,859,870,000
84,162,180,000
Date
GOOG
MSFT
SP500
ABT
Rgoog
Rmsft
Rabt
Rmrkt
11/1/2023
137.92 376.6755 4547.38
101.2 0.100718 0.116323 0.076258 0.08431
10/1/2023
125.3 337.4251
4193.8 94.02949 -0.04968 0.070815 -0.02375 -0.02198
9/1/2023
131.85 315.1104 4288.05 96.31682 -0.04004
-0.0346
-0.0588 -0.04872
8/1/2023
137.35 326.4051 4507.66 102.3335 0.031853 -0.02429 -0.07129 -0.01772
7/1/2023
133.11 334.5313 4588.96 110.1887 0.100355 -0.01357 0.021189 0.031139
6/1/2023
120.97 339.1322 4450.38 107.9023 -0.01945 0.039266 0.068823 0.064728
5/1/2023
123.37 326.3191 4179.83 100.9543 0.139993 0.068769 -0.07206 0.002482
4/1/2023
108.22 305.3224 4169.48 108.7938 0.040577 0.065765 0.090954 0.014642
3/1/2023
104 286.4819 4109.31 99.72352 0.151716 0.158777 -0.00452 0.035052
2/1/2023
90.3 247.2278 3970.15 100.1765 -0.09582 0.006497 -0.07571 -0.02611
1/1/2023
99.87 245.632
4076.6 108.3818 0.125549 0.033317 0.006922 0.061753
12/1/2022
88.73 237.7123
3839.5 107.6367 -0.12538
-0.0574 0.020543 -0.05897
11/1/2022
101.45 252.1869 4080.11 105.4701 0.07173 0.099126 0.092456 0.053753
10/1/2022
94.66 229.4432 3871.98
96.544
-0.0155 -0.00331 0.02253 0.079863
9/1/2022
96.15 230.2043 3585.62 94.41679
-0.1191 -0.10738 -0.05738
-0.0934
8/1/2022
109.15 257.8962
3955 100.1642 -0.06421 -0.06864 -0.05268 -0.04244
7/1/2022
116.64 276.9028 4130.29 105.7343 0.066447 0.093097 0.001749 0.091116
6/1/2022 109.3725 253.3196 3785.38 105.5497 -0.04092 -0.05312
-0.075 -0.08392
5/1/2022 114.039 267.5309 4132.15 114.1083 -0.00807 -0.02036 0.039015 5.32E-05
4/1/2022 114.9665 273.0908 4131.93 109.8236 -0.17675 -0.09987 -0.04106 -0.08796
3/1/2022 139.6495 303.3893 4530.41 114.5262 0.035277 0.033995 -0.01874 0.035773
2/1/2022 134.891 293.4146 4373.94 116.713 -0.00595
-0.0392 -0.05033 -0.03136
1/1/2022 135.6985 305.3853 4515.55 122.8979 -0.06208 -0.07535 -0.09436 -0.05259
12/1/2021 144.6795 330.2694 4766.18 135.7026 0.015637 0.019194 0.119027 0.043613
11/1/2021 142.452 324.0497
4567 121.2684 -0.03924 -0.00311 -0.02044 -0.00833
10/1/2021 148.2705 325.0593 4605.38 123.7987 0.112595 0.176291 0.091086 0.069144
9/1/2021 133.2655 276.3426 4307.54 113.4638 -0.08385 -0.06433 -0.06521 -0.04757
8/1/2021 145.462 295.3423 4522.68 121.3783 0.075735 0.059562 0.048529 0.02899
7/1/2021 135.221 278.7398 4395.26 115.7605 0.07904 0.051717 0.043561 0.022748
6/1/2021 125.316 265.0332
4297.5 110.9284 0.039294 0.087494 -0.00617 0.022214
5/1/2021 120.578
243.71 4204.11 111.6173 0.000598 -0.00991
-0.025 0.005487
4/1/2021 120.506 246.1502 4181.17 114.479 0.16508 0.069602 0.002003 0.052425
3/1/2021 103.4315 230.1326 3972.89 114.2502 0.015598 0.016925 0.000501 0.042439
2/1/2021 101.843 226.3024 3811.15 114.193 0.109558 0.001811 -0.02692 0.026091
1/1/2021
91.787 225.8934 3714.24 117.3525 0.047869 0.042892 0.128779 -0.01114
12/1/2020
87.594 216.6029 3756.07 103.9641 -0.00503 0.041726 0.011735 0.037121
11/1/2020
88.037 207.9269 3621.63 102.7582 0.086199 0.057293 0.03302 0.107546
10/1/2020 81.0505 196.6598 3269.96 99.47363 0.103028 -0.03737 -0.03418 -0.02767
9/1/2020
73.48 204.2943
3363 102.9941 -0.10071 -0.06514 -0.00585 -0.03923
8/1/2020
81.709 218.5298 3500.31 103.5998 0.101972 0.100093 0.091961 0.070065
7/1/2020
74.148 198.6467 3271.12 94.87498 0.049059 0.007371 0.100733 0.055101
6/1/2020 70.6805 197.1933 3100.29 86.19257 -0.01071 0.113652 -0.03677 0.018388
5/1/2020
71.446 177.069 3044.31 89.48265 0.059511 0.022544 0.035071 0.045282
4/1/2020
67.433 173.1652 2912.43 86.45071 0.159828 0.136326 0.167026 0.126844
3/1/2020 58.1405 152.3904 2584.59 74.07782
-0.1318 -0.02388 0.024406 -0.12512
2/1/2020
1/1/2020
12/1/2019
11/1/2019
10/1/2019
9/1/2019
8/1/2019
7/1/2019
6/1/2019
5/1/2019
4/1/2019
3/1/2019
2/1/2019
1/1/2019
12/1/2018
66.9665
71.7115
66.851
65.248
63.0055
60.95
59.405
60.834
54.0455
55.1815
59.424
58.6655
55.996
55.8185
51.7805
156.119
164.0401
151.9657
145.3808
137.6882
133.5202
131.9572
130.4353
128.2242
117.9478
124.547
112.4738
106.3834
99.16642
96.45059
2954.22
3225.52
3230.78
3140.98
3037.56
2976.74
2926.46
2980.38
2941.76
2752.06
2945.83
2834.4
2784.49
2704.1
2506.85
72.31292
81.45786
81.19614
79.87806
77.84596
77.90182
79.43807
80.79281
78.01003
70.61717
73.49799
73.84905
71.70582
67.10819
66.51047
-0.06617
0.072707
0.024568
0.035592
0.033724
0.026008
-0.02349
0.125607
-0.02059
-0.07139
0.012929
0.047673
0.00318
0.077983
-0.04829
0.079455
0.045294
0.055869
0.031216
0.011845
0.011668
0.017244
0.087127
-0.05299
0.107343
0.05725
0.072776
0.028158
-0.11227
0.003223
0.016501
0.026104
-0.00072
-0.01934
-0.01677
0.035672
0.104689
-0.0392
-0.00475
0.029889
0.068511
0.008987
-0.08411
-0.00163
0.02859
0.034047
0.020432
0.017181
-0.01809
0.013128
0.06893
-0.06578
0.039313
0.017924
0.029729
0.078684
AVE
VARIANCE
SD
CV=sd/ave
Beta formula
betaSLOPE
Rgoog
Rmsft
Rabt
Rmrkt
0.019811 0.025312 0.008851 0.011564
Goog
0.006132 0.003997 0.003493 0.002826
Msft
0.078309 0.06322 0.059099 0.05316 stand alone risk
Abt
3.952795 2.497651 6.677036 4.596845 relative risk
1.076389 0.883909 0.706676
1
1.076389 0.883909 0.706676
1
=SLOPE(RABT,RMRKT)
SUMMARY OUTPUT
0.683051 =CORREL(RGOOG,RMSFT)
Regression Statistics
0.56857 =CORREL(RMSFT,RABT)
Multiple R 0.73071
0.42182 =CORREL(RGOOG,RABT)
R Square
0.533937
Adjusted R Square
0.52576
Standard Error0.05439
Observations
59
Investment
$ 5,000.00
$ 5,000.00
$ 5,000.00
ret
sd
ANOVA
df
Regression
Residual
Total
SS
MS
F
Significance F
1 0.19318 0.19318 65.30107 5.04E-11
57 0.168623 0.002958
58 0.361803
Coefficients
Standard Error t Stat
P-value Lower 95% Upper 95% Lower 95.0%
Intercept 0.007363 0.007247 1.016073 0.313888 -0.00715 0.021874 -0.007148
Rsp500
1.076389 0.133201 8.080908 5.04E-11 0.809657 1.34312 0.80965744
SUMMARY OUTPUT
Regression Statistics
Multiple R 0.743259
R Square
0.552433
Adjusted R Square
0.544581
Standard Error0.04303
Observations
59
ANOVA
df
Regression
Residual
Total
SS
MS
F
Significance F
1 0.130268 0.130268 70.35532 1.57E-11
57 0.10554 0.001852
58 0.235808
Coefficients
Standard Error t Stat
P-value Lower 95% Upper 95% Lower 95.0%
Intercept
0.01509 0.005733 2.632071
0.0109 0.00361 0.02657 0.00360956
Rsp500
0.883909 0.10538 8.387808 1.57E-11 0.672889 1.094929 0.67288882
SCATTER PLOT MRKT ABT
0.15
y = 0.5718x + 0.0065
R² = 0.4041
0.1
0.05
-0.15
-0.1
0
-0.05
0
-0.05
-0.1
-0.15
0.05
0.1
0.15
0.2
Upper 95.0%
0.02657002
1.09492934
risk return -efficient fron
0.025
0.02
Port return
Portfolios
Return
Risk
CV
Goog&Msft
0.111710755 0.064985 0.581724
Abt&Goog
0.014331047 0.058158 4.05817
Msft&Abt
0.01708143 0.054171 3.171355
rgoog
rAbt
correl goog abt
0.01981098 0.008851118 0.42182
0.07830873 0.059099232
efficient frontier
w1
w2
Risk port Return port
1
0
0.078309 0.019811
0.95
0.05
0.075687 0.019263
0.9
0.1
0.073167 0.018715
0.85
0.15
0.07076 0.018167
0.8
0.2
0.068477 0.017619
0.75
0.25
0.066331 0.017071
0.7
0.3
0.064336 0.016523
0.65
0.35
0.062506 0.015975
0.6
0.4
0.060856 0.015427
0.55
0.45
0.059402 0.014879
0.5
0.5
0.058158 0.014331
0.45
0.55
0.057137 0.013783
0.4
0.6
0.056352 0.013235
0.35
0.65
0.055813 0.012687
Upper 95.0%
0.3
0.7
0.055527 0.012139
0.02187421
0.25
0.75
0.055498 0.011591
1.34312013
0.2
0.8
0.055725 0.011043
0.15
0.85
0.056207 0.010495
0.1
0.9
0.056937 0.009947
0.05
0.95
0.057905 0.009399
0
1
0.059099 0.008851
0.015
0.01
0.005
0
0
0.01
0.02
0.03
k return -efficient frontier
0.04
0.05
Port risk
0.06
0.07
0.08
0.09
Rmrkt
Rgoog
0.08431 0.100718
-0.02198 -0.04968
-0.04872 -0.04004
-0.01772 0.031853
0.031139 0.100355
0.064728 -0.01945
0.002482 0.139993
0.014642 0.040577
0.035052 0.151716
-0.02611 -0.09582
0.061753 0.125549
-0.05897 -0.12538
0.053753 0.07173
0.079863
-0.0155
-0.0934
-0.1191
-0.04244 -0.06421
0.091116 0.066447
-0.08392 -0.04092
5.32E-05 -0.00807
-0.08796 -0.17675
0.035773 0.035277
-0.03136 -0.00595
-0.05259 -0.06208
0.043613 0.015637
-0.00833 -0.03924
0.069144 0.112595
-0.04757 -0.08385
0.02899 0.075735
0.022748 0.07904
0.022214 0.039294
0.005487 0.000598
0.052425 0.16508
0.042439 0.015598
0.026091 0.109558
-0.01114 0.047869
0.037121 -0.00503
0.107546 0.086199
-0.02767 0.103028
-0.03923 -0.10071
0.070065 0.101972
0.055101 0.049059
0.018388 -0.01071
0.045282 0.059511
0.126844 0.159828
-0.12512
-0.1318
-0.08411 -0.06617
Rgoog
y = 1.0764x + 0.0074
0.2
0.15
0.1
0.05
0
-0.15
-0.1
-0.05
-0.05
-0.1
-0.15
-0.2
0
0.05
-0.00163
0.02859
0.034047
0.020432
0.017181
-0.01809
0.013128
0.06893
-0.06578
0.039313
0.017924
0.029729
0.078684
0.072707
0.024568
0.035592
0.033724
0.026008
-0.02349
0.125607
-0.02059
-0.07139
0.012929
0.047673
0.00318
0.077983
y = 1.0764x + 0.0074
R² = 0.5339
0.05
0.1
0.15
Down load price data (monthly for five years)of any two securities (please select from different
industries) from https://finance.yahoo.com/
Down load S&P 500 index data (this is market data)
Your data should have columns like this:
Date Stock 1, Stock 2 and S&P 500 (Please note that this is price data)
Now you calculate returns data and label each column (ex as Rmsft , Rsp500 )
Now complete this table
Statistic
Average
Variance
Standard Deviation
Coefficient of Variation
Beta of stock using formula
Beta of stock using SLOPE function
rStock1
rStock2
rSP500
•
•
•
•
•
•
•
•
W1
1
0.95
0.90
What is standalone risk? Which security is riskier?
What is relative risk? Which security is riskier?
What is beta? Comment on beta of each security and sp500.
Find correlation between stock 1 and stock2
Assume you invested 50% in stock1 and 50% in stock2
What is the Return on this portfolio?
What is the risk on this portfolio? How important is correlations in portfolio risk?
Construction of efficient frontier
W2
Port Risk
Port Return
= 1-w1
=SQRT((W1sd1)^2+(w2*sd2)^2+2*w1*w2*sd1*sd2*corr1,2)
=w1*r1+w2*r2
= 1-0.95
= 1-0.90
0
1
Now plot scatterplot between port risk and port return
Write lines about efficient frontier citing your scatterplot.
Rubric: Risk and Return Efficient Frontier Assignment
Criteria
Excellent (5)
Proficient (4)
Shows a good
Demonstrates a
understanding of risk
comprehensive understanding
Understanding of
and return principles.
of risk and return concepts.
Risk and Return
Definitions and
Provides clear and precise
explanations are mostly
definitions and explanations.
accurate and clear.
Competent (3)
Displays a basic
understanding of risk and
return concepts.
Provides a good analysis
of the efficient frontier Offers a basic analysis of
and its relevance in
the efficient frontier.
portfolio theory.
Analysis of
Efficient Frontier
Presents a detailed and
insightful analysis of the
efficient frontier.
Calculation and
Interpretation
Accurately calculates and
Calculates and interprets Conducts calculations of
interprets risk measures (e.g.,
risk measures and
risk measures and returns
standard deviation, beta) and
returns accurately.
with some accuracy.
returns
Date
Walmart JP Morgan Chase
12/1/2023 154.34
156.84
11/1/2023 155.69
156.08
10/1/2023 163.41
139.06
9/1/2023 159.93
145.02
8/1/2023 162.61
146.33
7/1/2023 159.86
157.96
6/1/2023 157.18
145.44
5/1/2023 146.87
135.71
4/1/2023 150.97
138.24
3/1/2023 147.45
130.31
2/1/2023 142.13
143.35
1/1/2023 143.87
139.96
12/1/2022 141.79
134.1
11/1/2022 152.42
138.18
10/1/2022 142.33
125.88
9/1/2022
129.7
104.5
8/1/2022 132.55
113.73
7/1/2022 132.05
115.36
6/1/2022 121.58
112.61
5/1/2022 128.63
132.23
4/1/2022 152.99
119.36
3/1/2022 148.92
136.32
2/1/2022 135.16
141.8
1/1/2022 139.81
148.6
12/1/2021 144.69
158.35
11/1/2021 140.63
158.83
10/1/2021 149.42
169.89
9/1/2021 139.38
163.69
8/1/2021
148.1
159.95
7/1/2021 142.55
151.78
6/1/2021 141.02
155.54
5/1/2021 142.03
164.24
4/1/2021 139.91
153.81
3/1/2021 135.83
152.23
2/1/2021 129.92
147.17
1/1/2021 140.49
128.67
12/1/2020 144.15
127.07
11/1/2020 152.79
117.88
10/1/2020 138.75
98.04
9/1/2020 139.91
96.27
8/1/2020 138.85
100.19
7/1/2020
129.4
96.64
6/1/2020 119.78
94.06
5/1/2020 124.06
97.31
4/1/2020 121.55
95.76
S&P 500
4594.63
4547.38
4193.8
4288.05
4507.66
4588.96
4450.38
4179.83
4169.48
4109.31
3970.15
4076.6
3839.5
4080.11
3871.98
3585.62
3955
4130.29
3785.38
4132.15
4131.93
4530.41
4373.94
4515.55
4766.18
4567
4605.38
4307.54
4522.68
4395.26
4297.5
4204.11
4181.17
3972.89
3811.15
3714.24
3756.07
3621.63
3269.96
3363
3500.31
3271.12
3100.29
3044.31
2912.43
RWAL
RJPM RMRKT
-0.00867 0.00487 0.01039
-0.04724 0.12239 0.08431
0.02176 -0.04110 -0.02198
-0.01648 -0.00895 -0.04872
0.01720 -0.07363 -0.01772
0.01705 0.08608 0.03114
0.07020 0.07170 0.06473
-0.02716 -0.01830 0.00248
0.02387 0.06085 0.01464
0.03743 -0.09097 0.03505
-0.01209 0.02422 -0.02611
0.01467 0.04370 0.06175
-0.06974 -0.02953 -0.05897
0.07089 0.09771 0.05375
0.09738 0.20459 0.07986
-0.02150 -0.08116 -0.09340
0.00379 -0.01413 -0.04244
0.08612 0.02442 0.09112
-0.05481 -0.14838 -0.08392
-0.15923 0.10783 0.00005
0.02733 -0.12441 -0.08796
0.10181 -0.03865 0.03577
-0.03326 -0.04576 -0.03136
-0.03373 -0.06157 -0.05259
0.02887 -0.00302 0.04361
-0.05883 -0.06510 -0.00833
0.07203 0.03788 0.06914
-0.05888 0.02338 -0.04757
0.03893 0.05383 0.02899
0.01085 -0.02417 0.02275
-0.00711 -0.05297 0.02221
0.01515 0.06781 0.00549
0.03004 0.01038 0.05243
0.04549 0.03438 0.04244
-0.07524 0.14378 0.02609
-0.02539 0.01259 -0.01114
-0.05655 0.07796 0.03712
0.10119 0.20237 0.10755
-0.00829 0.01839 -0.02767
0.00763 -0.03913 -0.03923
0.07303 0.03673 0.07006
0.08031 0.02743 0.05510
-0.03450 -0.03340 0.01839
0.02065 0.01619 0.04528
0.06979 0.06365 0.12684
Regression Statis
Regression Statis
113.62
107.68
114.49
118.84
119.09
117.26
118.68
114.26
110.38
110.49
101.44
102.84
97.53
98.99
95.83
W1
W2
1
0.95
0.9
0.85
0.8
0.75
0.7
0.65
0.6
0.55
0.5
0.45
0.4
0.35
0.3
0.25
0.2
0.15
0.1
0.05
0
0
0.05
0.1
0.15
0.2
0.25
0.3
0.35
0.4
0.45
0.5
0.55
0.6
0.65
0.7
0.75
0.8
0.85
0.9
0.95
1
90.03
116.11
132.36
139.4
131.76
124.92
117.69
109.86
116
111.8
105.96
116.05
101.23
104.36
103.5
2584.59
2954.22
3225.52
3230.78
3140.98
3037.56
2976.74
2926.46
2980.38
2941.76
2752.06
2945.83
2834.4
2784.49
2704.1
0.05516 -0.22461 -0.12512
-0.05948 -0.12277 -0.08411
-0.03660 -0.05050 -0.00163
-0.00210 0.05798 0.02859
0.01561 0.05476 0.03405
-0.01196 0.06143 0.02043
0.03868 0.07127 0.01718
0.03515 -0.05293 -0.01809
-0.00100 0.03757 0.01313
0.08922 0.05512 0.06893
-0.01361 -0.08695 -0.06578
0.05444 0.14640 0.03931
-0.01475 -0.02999 0.01792
0.03298 0.00831 0.02973
PORTFOLIO RISK PORTFOLIO RETURN
0.051222093 0.009432313
0.049636712 0.009474142
0.048378063 0.009515971
0.047472142
0.0095578
0.046939376 0.009599629
0.046792513 0.009641458
0.047035167 0.009683287
0.04766139 0.009725116
0.048656374 0.009766945
0.049998109 0.009808774
0.051659583 0.009850603
0.053611077 0.009892432
0.055822183 0.009934261
0.058263351
0.00997609
0.060906925 0.010017919
0.063727722 0.010059748
0.06670326 0.010101577
0.069813758 0.010143406
0.073041975 0.010185235
0.076372986 0.010227064
0.079793918 0.010268893
-0.20000
Risk Return Efficien
Port Return
3/1/2020
2/1/2020
1/1/2020
12/1/2019
11/1/2019
10/1/2019
9/1/2019
8/1/2019
7/1/2019
6/1/2019
5/1/2019
4/1/2019
3/1/2019
2/1/2019
1/1/2019
0.0104
0.0103
0.0102
0.0101
0.01
0.0099
0.0098
0.0097
0.0096
0.0095
0.0094
0.0093
0
0.01
AVE
VARIANCE
SD
CV=sd/ave
Beta formula
betaSLOPE
RWAL
RJPM
RMRKT
0.009432313
0.010268893 0.010406961
0.002623703
0.006367069 0.002748326
0.051222093
0.079793918 0.052424477
5.43049122
7.770449589 5.037443308
0.496177586
1.115178489
1
0.496177586
1.115178489
1
CO-RELATION WALMART AND JPM
0.206017939
SUMMARY OUTPUT WALMART VS MARKET
Regression Statistics
Multiple R
0.50782482
R Square
0.257886048
Adjusted R Square
0.244866505
Standard Error
0.044893266
Observations
59
ANOVA
df
Regression
Residual
Total
Intercept
X Variable 1
1
57
58
SS
MS
F
Significance F
0.039920365 0.039920365 19.80761134 4.03336E-05
0.114878102 0.002015405
0.154798467
Coefficients Standard Error
t Stat
P-value
Lower 95%
0.004268612
0.005958653 0.716371948 0.476686637 -0.007663386
0.496177586
0.111486194 4.450574271 4.03336E-05 0.272930333
SUMMARY OUTPUT JP MORGAN VS MARKET
Regression Statistics
Multiple R
0.732670488
R Square
0.536806044
Adjusted R Square
0.528679834
Standard Error
0.055250957
Observations
59
ANOVA
df
Regression
Residual
Total
1
57
58
Coefficients
SS
MS
F
Significance F
0.201654996 0.201654996 66.05860049 4.21977E-11
0.174002093 0.003052668
0.375657089
Standard Error
t Stat
P-value
Lower 95%
Intercept
X Variable 1
-0.001336726
1.115178489
0.007333423 -0.18227857 0.856010848 -0.016021652
0.137208084 8.127644215 4.21977E-11 0.840424041
Scatter Plot JP Morgan Chase and S&
Scatter Plot of Walmart and S&P 500
0.15000
y = 0.5197x + 0.0055
R² = 0.2579
0.15000
y = 0.4814x + 0.0055
R² = 0.5368
0.10000
0.05000
0.05000
0.00000
-0.20000 -0.15000 -0.10000 -0.05000 0.00000
-0.05000
0.05000
0.10000
0.15000
-0.10000
0.03
0.04
0.05
Portfolio Risk
-0.20000
0.00000
-0.10000
-0.05000
-0.15000
Risk Return Efficient Frontier
0.02
-0.30000
-0.10000
-0.15000
0.01
0.10000
0.06
0.07
0.08
0.09
ARKET
Upper 95% Lower 95.0% Upper 95.0%
0.01620061 -0.007663386 0.01620061
0.719424838 0.272930333 0.719424838
N VS MARKET
Upper 95%
Lower 95.0%
Upper 95.0%
0.013348201 -0.016021652 0.013348201
1.389932938 0.840424041 1.389932938
Plot JP Morgan Chase and S&P 500
0.15000
0.10000
0.05000
0.00000
-0.10000
0.00000
-0.05000
-0.10000
-0.15000
0.10000
0.20000
0.30000
Purchase answer to see full
attachment
