Calculate the alpha and beta of Stock A  

2)  Please calculate the volatility of the portfolio (stock a and stock b) based on two scenarios (a. equal weight b.  arbitrary weight: stock a  20% stock b 80%

 

Please use Excel to calculate the results and show the Excel formulas used.   Thanks.

 

	Stock A			Stock B
Date	price	Dividend		Price	Dividend
7/1/2019	6.26			55.27	0.603
6/1/2019	5.58			57.13
5/1/2019	5.31	0.012		54.35
4/1/2019	5.81			57.19	0.603
3/1/2019	5.67			59.13
2/1/2019	5.93	0.012		56.92
1/1/2019	5.83			55.06	0.603
12/1/2018	5.71			56.22
11/1/2018	6.23	0.012		60.3
10/1/2018	6.02			57.09	0.603
9/1/2018	6.32			53.39
8/1/2018	7.1	0.011		54.37
7/1/2018	7.02			51.64	0.59
6/1/2018	6.77			50.31
5/1/2018	7.1	0.011		47.67
4/1/2018	6.33			49.35	0.59
3/1/2018	6.24			47.82
2/1/2018	6.28	0.011		47.74
1/1/2018	6.11			54.07	0.59
12/1/2017	5.36			52.93
11/1/2017	5.5	0.011		50.89
10/1/2017	5.44			47.87	0.59
9/1/2017	5.52			49.49
8/1/2017	5.61	0.01		47.97
7/1/2017	5.86			48.4	0.578
6/1/2017	5.47			44.66
5/1/2017	5.25	0.01		46.64
4/1/2017	4.95			45.91	0.578
3/1/2017	5.15			48.75
2/1/2017	5.09	0.01		49.63
1/1/2017	4.72			49.01	0.578
12/1/2016	4.45			53.38
11/1/2016	4.57	0.01		49.9
10/1/2016	4.17			48.1	0.578
9/1/2016	4.17			51.98
8/1/2016	4.14			52.33
7/1/2016	4.39			55.41	0.565
6/1/2016	3.95			55.84
5/1/2016	4.02			50.9
4/1/2016	3.95			50.94	0.565
3/1/2016	3.95			54.08
2/1/2016	3.72			50.73
1/1/2016	3.7			49.97	0.565
12/1/2015	4.07			46.22
11/1/2015	4.11			45.45
10/1/2015	4.08			46.88	0.565
9/1/2015	3.74			43.51
8/1/2015	3.82			46.01
7/1/2015	3.96			46.79	0.55
6/1/2015	3.73			46.61
5/1/2015	3.86			49.44
4/1/2015	3.95			50.44	0.55
3/1/2015	3.82			48.63
2/1/2015	3.89			49.45
1/1/2015	3.55			45.71	0.55
12/1/2014	3.5			46.78
11/1/2014	3.63			50.59
10/1/2014	3.43			50.25	0.55
9/1/2014	3.49			49.99
8/1/2014	3.63			49.82
7/1/2014	3.38			50.42	0.53