{"text_format":"1","workbook":"xlsx","n_sheets":7,"sheets":["Overview","Dataset","Formula","Slope Function","Regression","Sheet3","VBA"],"not_rendered":{"charts":0,"images":0,"macros":1,"external_links":0,"defined_names":0,"comments":0,"pivot_tables":0},"unopened":[],"bounds":{"document_bytes":16777216,"sheet_cells":100000,"rows_first":2000,"rows_last":500,"rows_between":2000,"halvings":0,"cell_chars":16384,"cell_head_chars":4096},"cells_cut":0,"cells_raw":0,"read":"whole","date_system":"1900"}

{"sheet":"Overview","index":0,"n_rows":22,"n_cols":8,"rows_kept":22,"cut":false,"hidden":false,"merged":[[17,1,17,7],[20,1,20,7],[21,3,21,7],[1,1,1,7],[2,1,2,3],[2,5,2,7],[18,3,18,7]]}
[]
[null,"Overview of Calculating Beta in Excel"]
[null,"ABC Company",null,null,null,"S&P 500"]
[null,"Date","Monthly Closing","Returns",null,"Date","Monthly Closing","Returns"]
[null,"2022-01-01",50,null,null,"2022-01-01",4673.71]
[null,"2022-02-01",55,{"$f":"(C6-C5)/C5","v":0.1},null,"2022-02-01",4796.8100000000004,{"$f":"(G6-G5)/G5","v":2.6338818625888289E-2}]
[null,"2022-03-01",60,{"$f":"(C7-C6)/C6","v":9.0909090909090912E-2},null,"2022-03-01",4661.5600000000004,{"$f":"(G7-G6)/G6","v":-2.8195821806575618E-2}]
[null,"2022-04-01",65,8.3333333333333329E-2,null,"2022-04-01",4684.29,4.8760500776563126E-3]
[null,"2022-05-01",70,7.6923076923076927E-2,null,"2022-05-01",4706.3100000000004,4.7008191209341093E-3]
[null,"2022-06-01",75,7.1428571428571425E-2,null,"2022-06-01",4714.66,1.7742137683236874E-3]
[null,"2022-07-01",80,6.6666666666666666E-2,null,"2022-07-01",4322.12,-8.3259450310308694E-2]
[null,"2022-08-01",85,6.25E-2,null,"2022-08-01",4401.78,1.8430770085050823E-2]
[null,"2022-09-01",90,5.8823529411764705E-2,null,"2022-09-01",4502.96,2.2986155600688878E-2]
[null,"2022-10-01",95,5.5555555555555552E-2,null,"2022-10-01",4501.79,-2.5982909019846339E-4]
[null,"2022-11-01",100,5.2631578947368418E-2,null,"2022-11-01",4671.49,3.7696116433685228E-2]
[null,"2022-12-01",105,0.05,null,"2022-12-01",4642.79,-6.1436500987907114E-3]
[]
[null,"Using COVARIANCE and VARIANCE Functions"]
[null,"Calculated Beta",{"$f":"_xlfn.COVARIANCE.P(D6:D16,H6:H16)/_xlfn.VAR.P(H6:H16)","v":-3.196441803038013E-2},">> COVARIANCE.P(D6:D16,H6:H16)/VAR.P(H6:H16)"]
[]
[null,"Using SLOPE Function"]
[null,"Calculated Beta",{"$f":"SLOPE(D6:D16,H6:H16)","v":-3.1964418030380123E-2},">> SLOPE(D6:D16,H6:H16)"]

{"sheet":"Dataset","index":1,"n_rows":16,"n_cols":6,"rows_kept":16,"cut":false,"hidden":false,"merged":[[1,1,1,5],[2,4,2,5],[2,1,2,2]]}
[]
[null,"Monthly Closing of Stock Price of ABC Company and S&P 500"]
[null,"ABC Company",null,null,"S&P 500"]
[null,"Date","Monthly Closing",null,"Date","Monthly Closing"]
[null,"2022-01-01",50,null,"2022-01-01",4673.71]
[null,"2022-02-01",55,null,"2022-02-01",4796.8100000000004]
[null,"2022-03-01",60,null,"2022-03-01",4661.5600000000004]
[null,"2022-04-01",65,null,"2022-04-01",4684.29]
[null,"2022-05-01",70,null,"2022-05-01",4706.3100000000004]
[null,"2022-06-01",75,null,"2022-06-01",4714.66]
[null,"2022-07-01",80,null,"2022-07-01",4322.12]
[null,"2022-08-01",85,null,"2022-08-01",4401.78]
[null,"2022-09-01",90,null,"2022-09-01",4502.96]
[null,"2022-10-01",95,null,"2022-10-01",4501.79]
[null,"2022-11-01",100,null,"2022-11-01",4671.49]
[null,"2022-12-01",105,null,"2022-12-01",4642.79]

{"sheet":"Formula","index":2,"n_rows":28,"n_cols":27,"rows_kept":28,"cut":false,"hidden":false,"merged":[[1,1,1,7],[2,1,2,3],[2,5,2,7]]}
[]
[null,"Using COVARIANCE and VARIANCE Functions"]
[null,"ABC Company",null,null,null,"S&P 500"]
[null,"Date","Monthly Closing","Returns",null,"Date","Monthly Closing","Returns"]
[null,"2022-01-01",50,null,null,"2022-01-01",4673.71]
[null,"2022-02-01",55,{"$f":"(C6-C5)/C5","v":0.1},null,"2022-02-01",4796.8100000000004,{"$f":"(G6-G5)/G5","v":2.6338818625888289E-2}]
[null,"2022-03-01",60,{"$f":"(C7-C6)/C6","v":9.0909090909090912E-2},null,"2022-03-01",4661.5600000000004,{"$f":"(G7-G6)/G6","v":-2.8195821806575618E-2}]
[null,"2022-04-01",65,8.3333333333333329E-2,null,"2022-04-01",4684.29,4.8760500776563126E-3]
[null,"2022-05-01",70,7.6923076923076927E-2,null,"2022-05-01",4706.3100000000004,4.7008191209341093E-3]
[null,"2022-06-01",75,7.1428571428571425E-2,null,"2022-06-01",4714.66,1.7742137683236874E-3]
[null,"2022-07-01",80,6.6666666666666666E-2,null,"2022-07-01",4322.12,-8.3259450310308694E-2]
[null,"2022-08-01",85,6.25E-2,null,"2022-08-01",4401.78,1.8430770085050823E-2]
[null,"2022-09-01",90,5.8823529411764705E-2,null,"2022-09-01",4502.96,2.2986155600688878E-2]
[null,"2022-10-01",95,5.5555555555555552E-2,null,"2022-10-01",4501.79,-2.5982909019846339E-4]
[null,"2022-11-01",100,5.2631578947368418E-2,null,"2022-11-01",4671.49,3.7696116433685228E-2]
[null,"2022-12-01",105,0.05,null,"2022-12-01",4642.79,-6.1436500987907114E-3]
[]
[null,"Calculated Beta",{"$f":"_xlfn.COVARIANCE.P(D6:D16,H6:H16)/_xlfn.VAR.P(H6:H16)","v":-3.196441803038013E-2}]
[]
[]
[]
[]
[]
[]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null," "]
[]
[]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null," "]

{"sheet":"Slope Function","index":3,"n_rows":18,"n_cols":8,"rows_kept":18,"cut":false,"hidden":false,"merged":[[1,1,1,7],[2,1,2,3],[2,5,2,7]]}
[]
[null,"Using SLOPE Function"]
[null,"ABC Company",null,null,null,"S&P 500"]
[null,"Date","Monthly Closing","Returns",null,"Date","Monthly Closing","Returns"]
[null,"2022-01-01",50,null,null,"2022-01-01",4673.71]
[null,"2022-02-01",55,{"$f":"(C6-C5)/C5","v":0.1},null,"2022-02-01",4796.8100000000004,{"$f":"(G6-G5)/G5","v":2.6338818625888289E-2}]
[null,"2022-03-01",60,{"$f":"(C7-C6)/C6","v":9.0909090909090912E-2},null,"2022-03-01",4661.5600000000004,{"$f":"(G7-G6)/G6","v":-2.8195821806575618E-2}]
[null,"2022-04-01",65,8.3333333333333329E-2,null,"2022-04-01",4684.29,4.8760500776563126E-3]
[null,"2022-05-01",70,7.6923076923076927E-2,null,"2022-05-01",4706.3100000000004,4.7008191209341093E-3]
[null,"2022-06-01",75,7.1428571428571425E-2,null,"2022-06-01",4714.66,1.7742137683236874E-3]
[null,"2022-07-01",80,6.6666666666666666E-2,null,"2022-07-01",4322.12,-8.3259450310308694E-2]
[null,"2022-08-01",85,6.25E-2,null,"2022-08-01",4401.78,1.8430770085050823E-2]
[null,"2022-09-01",90,5.8823529411764705E-2,null,"2022-09-01",4502.96,2.2986155600688878E-2]
[null,"2022-10-01",95,5.5555555555555552E-2,null,"2022-10-01",4501.79,-2.5982909019846339E-4]
[null,"2022-11-01",100,5.2631578947368418E-2,null,"2022-11-01",4671.49,3.7696116433685228E-2]
[null,"2022-12-01",105,0.05,null,"2022-12-01",4642.79,-6.1436500987907114E-3]
[]
[null,"Calculated Beta",{"$f":"SLOPE(D6:D16,H6:H16)","v":-3.1964418030380123E-2}]

{"sheet":"Regression","index":4,"n_rows":16,"n_cols":8,"rows_kept":16,"cut":false,"hidden":false,"merged":[[1,1,1,7],[2,1,2,3],[2,5,2,7]]}
[]
[null,"Applying Regression from Data Analysis Tools"]
[null,"ABC Company",null,null,null,"S&P 500"]
[null,"Date","Monthly Closing","Returns",null,"Date","Monthly Closing","Returns"]
[null,"2022-01-01",50,null,null,"2022-01-01",4673.71]
[null,"2022-02-01",55,{"$f":"(C6-C5)/C5","v":0.1},null,"2022-02-01",4796.8100000000004,{"$f":"(G6-G5)/G5","v":2.6338818625888289E-2}]
[null,"2022-03-01",60,{"$f":"(C7-C6)/C6","v":9.0909090909090912E-2},null,"2022-03-01",4661.5600000000004,{"$f":"(G7-G6)/G6","v":-2.8195821806575618E-2}]
[null,"2022-04-01",65,8.3333333333333329E-2,null,"2022-04-01",4684.29,4.8760500776563126E-3]
[null,"2022-05-01",70,7.6923076923076927E-2,null,"2022-05-01",4706.3100000000004,4.7008191209341093E-3]
[null,"2022-06-01",75,7.1428571428571425E-2,null,"2022-06-01",4714.66,1.7742137683236874E-3]
[null,"2022-07-01",80,6.6666666666666666E-2,null,"2022-07-01",4322.12,-8.3259450310308694E-2]
[null,"2022-08-01",85,6.25E-2,null,"2022-08-01",4401.78,1.8430770085050823E-2]
[null,"2022-09-01",90,5.8823529411764705E-2,null,"2022-09-01",4502.96,2.2986155600688878E-2]
[null,"2022-10-01",95,5.5555555555555552E-2,null,"2022-10-01",4501.79,-2.5982909019846339E-4]
[null,"2022-11-01",100,5.2631578947368418E-2,null,"2022-11-01",4671.49,3.7696116433685228E-2]
[null,"2022-12-01",105,0.05,null,"2022-12-01",4642.79,-6.1436500987907114E-3]

{"sheet":"Sheet3","index":5,"n_rows":19,"n_cols":8,"rows_kept":19,"cut":false,"hidden":false,"merged":[[1,1,1,7],[3,1,3,2]]}
[]
[null,"SUMMARY OUTPUT"]
[]
[null,"Regression Statistics"]
[null,"Multiple R",6.4308612086197037E-2]
[null,"R Square",4.1355975884529682E-3]
[null,"Adjusted R Square",-0.10651600267949671]
[null,"Standard Error",1.7179923272024323E-2]
[null,"Observations",11]
[]
[null,"ANOVA"]
[null,null,"df","SS","MS","F","Significance F"]
[null,"Regression",1,1.1031206487351411E-5,1.1031206487351411E-5,3.7374946032758351E-2,0.85099624158857579]
[null,"Residual",9,2.656347872693786E-3,2.9514976363264288E-4]
[null,"Total",10,2.6673790791811374E-3]
[]
[null,null,"Coefficients","Standard Error","t Stat","P-value","Lower 95%","Upper 95%"]
[null,"Intercept",6.9885241354558633E-2,5.1799660752877017E-3,13.491447692671834,2.8220843585817405E-7,5.8167343994294851E-2,8.1603138714822415E-2]
[null,"X Variable 1",-3.1964418030380061E-2,0.16533945989634646,-0.19332600971612576,0.85099624158857345,-0.40598826152808687,0.34205942546732671]

{"sheet":"VBA","index":6,"n_rows":18,"n_cols":8,"rows_kept":18,"cut":false,"hidden":false,"merged":[[1,1,1,7],[2,1,2,3],[2,5,2,7]]}
[]
[null,"Creating a User-Defined Function with VBA to Calculate Beta"]
[null,"ABC Company",null,null,null,"S&P 500"]
[null,"Date","Monthly Closing","Returns",null,"Date","Monthly Closing","Returns"]
[null,"2022-01-01",50,null,null,"2022-01-01",4673.71]
[null,"2022-02-01",55,{"$f":"(C6-C5)/C5","v":0.1},null,"2022-02-01",4796.8100000000004,{"$f":"(G6-G5)/G5","v":2.6338818625888289E-2}]
[null,"2022-03-01",60,{"$f":"(C7-C6)/C6","v":9.0909090909090912E-2},null,"2022-03-01",4661.5600000000004,{"$f":"(G7-G6)/G6","v":-2.8195821806575618E-2}]
[null,"2022-04-01",65,8.3333333333333329E-2,null,"2022-04-01",4684.29,4.8760500776563126E-3]
[null,"2022-05-01",70,7.6923076923076927E-2,null,"2022-05-01",4706.3100000000004,4.7008191209341093E-3]
[null,"2022-06-01",75,7.1428571428571425E-2,null,"2022-06-01",4714.66,1.7742137683236874E-3]
[null,"2022-07-01",80,6.6666666666666666E-2,null,"2022-07-01",4322.12,-8.3259450310308694E-2]
[null,"2022-08-01",85,6.25E-2,null,"2022-08-01",4401.78,1.8430770085050823E-2]
[null,"2022-09-01",90,5.8823529411764705E-2,null,"2022-09-01",4502.96,2.2986155600688878E-2]
[null,"2022-10-01",95,5.5555555555555552E-2,null,"2022-10-01",4501.79,-2.5982909019846339E-4]
[null,"2022-11-01",100,5.2631578947368418E-2,null,"2022-11-01",4671.49,3.7696116433685228E-2]
[null,"2022-12-01",105,0.05,null,"2022-12-01",4642.79,-6.1436500987907114E-3]
[]
[null,"Calculated Beta",{"$f":"beta(H6:H16,D6:D16)","v":-3.1964418030380151E-2}]