Using the price data, estimate expected returns and standard deviation of returns of BOTH companies and SPY. Use the close prices to calculate returns. Return in any month for a security can be calculated as Close price of that month divided by close price of the previous month minus 1. For example, Returns in month i can be calculated as RWMT,i = (Close pricei / Close pricei-1) -1.

Following the market model regression, calculate beta for your companies. You need to regress your company returns on SPY returns to get company betas. Beta is the coefficient of the regression.

  

B 1 Date Ticker 2 2019-01 MSFT 3 2019-02 MSFT 4 2019-03 MSFT 5 2019-04 MSFT 6 2019-05 MSFT 7 2019-06 MSFT 8 2019-07 MSFT 9 2019-08 MSFT 10 2019-09 MSFT 11 2019-10 MSFT 12 2019-11 MSFT 13 2019-12 MSFT 14 2020-01 MSFT 15 2020-02 MSFT 16 2020-03 MSFT 17 2020-04 MSFT 18 2020-05 MSFT 19 2020-06 MSFT 20 2020-07 MSFT 21 2020-08 MSFT 22 2020-09 MSFT 23 2020-10 MSFT 24 2020-11 MSFT 25 2020-12 MSFT 26 2021-01 MSFT 27 2021-02 MSFT 28 2021-03 MSFT 29 2021-04 MSFT 30 2021-05 MSFT 31 2021-06 MSFT 32 2021-07 MSFT 33 2021-08 MSFT 34 2021-09 MSFT 35 2021-10 MSFT 36 2021-11 MSFT 37 2021-12 MSFT 38 2019-01 SONY 39 2019-02 SONY 40 2019-03 SONY 41 2019-04 SONY 42 2019-05 SONY 43 2019-06 SONY 44 2019-07 SONY 45 2019-08 SONY 46 2019-09 SONY 47 2019-10 SONY 48 2019-11 SONY 49 2019-12 SONY 50 2020-01 SONY 51 2020-02 SONY 52 2020-03 SONY 53 2020-04 SONY 54 2020-05 SONY 55 2020-06 SONY 56 2020-07 SONY 57 2020-08 SONY 58 2020-09 SONY 59 2020-10 SONY 50 2020-11 SONY 51 2020-12 SONY 52 2021-01 SONY 63 2021-02 SONY 54 2021-03 SONY 55 2021-04 SONY 66 2021-05 SONY 57 2021-06 SONY 58 2021-07 SONY 59 2021-08 SONY 70 2021-09 SONY 71 2021-10 SONY 72 2021-11 SONY 73 2021-12 SONY SONY GROUP CORPORATION Company Name MICROSOFT CORP MICROSOFT CORP MICROSOFT CORP MICROSOFT CORP MICROSOFT CORP MICROSOFT CORP MICROSOFT CORP MICROSOFT CORP MICROSOFT CORP MICROSOFT CORP MICROSOFT CORP MICROSOFT CORP MICROSOFT CORP MICROSOFT CORP MICROSOFT CORP MICROSOFT CORP MICROSOFT CORP MICROSOFT CORP MICROSOFT CORP MICROSOFT CORP MICROSOFT CORP MICROSOFT CORP MICROSOFT CORP MICROSOFT CORP MICROSOFT CORP MICROSOFT CORP MICROSOFT CORP MICROSOFT CORP MICROSOFT CORP MICROSOFT CORP MICROSOFT CORP MICROSOFT CORP MICROSOFT CORP MICROSOFT CORP MICROSOFT CORP MICROSOFT CORP SONY GROUP CORPORATION SONY GROUP CORPORATION SONY GROUP CORPORATION SONY GROUP CORPORATION SONY GROUP CORPORATION SONY GROUP CORPORATION SONY GROUP CORPORATION SONY GROUP CORPORATION SONY GROUP CORPORATION SONY GROUP CORPORATION SONY GROUP CORPORATION SONY GROUP CORPORATION SONY GROUP CORPORATION SONY GROUP CORPORATION SONY GROUP CORPORATION SONY GROUP CORPORATION SONY GROUP CORPORATION SONY GROUP CORPORATION SONY GROUP CORPORATION SONY GROUP CORPORATION SONY GROUP CORPORATION SONY GROUP CORPORATION SONY GROUP CORPORATION SONY GROUP CORPORATION SONY GROUP CORPORATION SONY GROUP CORPORATION SONY GROUP CORPORATION SONY GROUP CORPORATION SONY GROUP CORPORATION SONY GROUP CORPORATION SONY GROUP CORPORATION SONY GROUP CORPORATION SONY GROUP CORPORATION SONY GROUP CORPORATION SONY GROUP CORPORATION D High Close Price Price 104.43 107.9 112.03 113.24 117.94 120.82 130.6 131.37 123.68 130.65 133.96 138.4 E 136.27 141.675 137.86 140.9383 139.03 142.37 143.37 145.67 151.38 152.5 157.7 159.55 170.23 174.05 162.01 190.7 157.71 175 179.21 180.4 183.25 187.51 203.51 204.4 205.01 216.38 225.53 231.15 210.33 232.86 202.47 225.21 214.07 228.12 222.42 227.18 231.96 242.64 232.38 246.13 235.77 241.05 252.18 249.68 270.9 284.91 290.15 301.88 305.84 281.92 305.32 332 331.62 330.59 349.67 336.32 344.3 50.12 50.54 47.99 50.04 42.24 48.06 50.37 50.92 68 70.18 62.2 48 53.61 52.39 54.115 56.86 57.41 56.91 57.6 59.13 60.74 60.83 61.77 63.48 63.75 68.47 59.18 64.25 64.76 69.13 77.96 78.64 76.75 83.66 93.29 101.1 95.71 F Low Price 97.2 102.35 108.8 118.1 123.04 119.01 134.67 130.78 134.51 133.22 142.965 146.65 156.51 152 263.19 238.0501 254.35 238.07 271.65 132.52 150.36 173.8 181.35 197.51 203.14 196.25 199.62 200.12 209.11 211.94 227.88 224.26 243 269.6 283.74 281.62 280.25 326.37 317.25 46.89 42.43 41.91 42.0346 47.71 47.29 52.98 53.315 57 56.51 60.15 62.69 67.96 59.35 50.94 72.95 73.86 64.95 64.85 56.645 66.4 61.3515 72.34 78.76 84.145 64.56 68.98 78.2 80.17 84.05 94.5 101.4 105 105.81 118.5 106.01 108.56 100.16 113.5 99.61 101.19 97.22 101.05 104.3 106.12 103.46 105.4 110.58 117.869 115.79 118.93 121.85 125.76 126.4 128.3 117.7445 75.604 72.45 84.04 92.155 95.09 96.94 100.09 100 91.745 94.4203 97.56 95.621 103.84 104.09 120