Day A B C D E Market Index A B C D E 1 570 98.4 669.1 53.9 103.5 333.4 2 569 98.2 715 53.8 103 338.9 -0.001755927 -0.002034589 0.066349017 -0.001857011 -0.004842624 0.016362108 3 563.8 96.6 725 53.2 101.9 346.8 -0.009180855 -0.016427474 0.013889112 -0.011215071 -0.010737048 0.023043166 4 575.3 96.5 716 53.9 100 347.8 0.020192065 -0.001035733 -0.012491488 0.013072082 -0.018821754 0.002879357 5 595.1 97 725 55.6 101.6 350.9 0.033837815 0.00516797 0.012491488 0.031052723 0.015873349 0.008873681 6 602.8 98.4 727.5 57 101.2 348.1 0.012856008 0.014329826 0.003442344 0.024868067 -0.003944778 -0.008011488 7 601.8 99 716.6 54.7 102 349.4 -0.001660303 0.006079046 -0.015096194 -0.041187558 0.007874056 0.003727603 8 601.3 105.4 721.5 55.6 101.6 354.2 -0.000831186 0.062642786 0.006814573 0.016319492 -0.003929278 0.013644328 9 614.8 116.9 718.6 55.9 101.7 361.1 0.022203034 0.103556232 -0.004027504 0.005381179 0.000983768 0.019293203 10 628.1 119.6 717.8 56.5 100.5 372.7 0.021402378 0.022833973 -0.001113896 0.010676258 -0.011869576 0.031618878 11 629 113.2 729.5 56.4 103.4 371.6 0.001431867 -0.054996676 0.016168389 -0.00177148 0.028447235 -0.0029558 12 618.6 109.5 702.6 54.9 102.3 395.9 -0.016672396 -0.033231617 -0.037571628 -0.02695581 -0.010695289 0.063343647 13 638 105 750.8 55 102.8 397.6 0.030879423 -0.041964199 0.066351565 0.001819837 0.00487568 0.004284821 14 656 104.9 789.7 56.6 99.8 406.1 0.027822506 -0.000952835 0.050513822 0.0286758 -0.02961717 0.02115296 15 662 105.3 799.1 56.9 101.4 400.7 0.009104767 0.003805904 0.011832968 0.005286356 0.015904908 -0.013386417 16 669.4 105.7 805 56 100.9 396.6 0.011116233 0.003791474 0.007356183 -0.01594365 -0.004943164 -0.010284802 17 700.7 108.5 870 56.7 95.3 398.2 0.045698047 0.02614528 0.077650934 0.01242252 -0.057100117 0.004026176 18 709 110.3 937.6 57 65.7 400.9 0.011775691 0.016453753 0.074830207 0.005277057 -0.371930885 0.006757628 19 713 112.6 948.8 56.8 99.4 399.1 0.005625894 0.020637789 0.011874609 -0.003514942 0.414053188 -0.004500008 20 708 113.9 951.5 56.5 99.2 401.1 -0.007037327 0.011479155 0.002841658 -0.005295688 -0.002014099 0.004998761 E(ri) 0.011410933 0.007698951 0.018531903 0.002479482 -0.002233347 0.009729884 rf 0.03 p.a. covar_iM -7.66363E-05 -2.00636E-05 -0.000143282 -5.79076E-05 -0.000351217 0.000311237 assumption 250 var_i 0.000267119 0.001223278 0.001071405 0.000329542 0.017556896 0.000311237 rf_p.d. 0.00012 beta alfa beta alfa beta alfa beta alfa beta alfa beta_i -0.246231127 -0.064463954 -0.460363256 -0.186055958 -1.128453695 1 -0.246231127 0.013806734 -0.064463954 0.008326178 -0.460363256 0.023011184 -0.186055958 0.004289785 -1.128453695 0.008746377 0.21660733 0.004275058 0.480576545 0.009484871 0.435921547 0.00860354 0.245452056 0.00484435 1.800924248 0.035543836 E(ri) -0.002246253 -0.000499491 -0.004304038 -0.001667976 -0.010724309 0.009729884 0.070643624 0.016212697 0.001057305 0.035970353 0.061565802 0.032628001 0.032694052 0.018371677 0.022574154 0.134796177 1.292229373 17 0.017993213 17 1.115281849 17 0.574584376 17 0.392623776 17 0.000339665 0.004468476 2.32808E-05 0.021995727 0.001187314 0.018097969 0.000193933 0.005737815 0.007133978 0.308890156 Min.Variance portfolio Covar_M 0.000267119 0.000122378 0.000257158 0.000190277 -0.000253024 0.000122378 0.001223278 -0.000106427 0.00016828 -1.35394E-05 0.000257158 -0.000106427 0.001071405 0.000203278 -0.001495229 0.000190277 0.00016828 0.000203278 0.000329542 -0.000260157 -0.000253024 -1.35394E-05 -0.001495229 -0.000260157 0.017556896 Systém of Matrices vrs 0.000534238 0.000244757 0.000514316 0.000380554 -0.000506048 1 0 0.000244757 0.002446556 -0.000212854 0.00033656 -2.70788E-05 1 0 0.000514316 -0.000212854 0.002142809 0.000406556 -0.002990457 1 0 0.000380554 0.00033656 0.000406556 0.000659083 -0.000520315 1 0 -0.000506048 -2.70788E-05 -0.002990457 -0.000520315 0.035113793 1 0 1 1 1 1 1 0 1 wi 0.518336137 0.091635004 0.073795974 0.285903523 0.030329362 3135.688208 -308.3583546 -723.9039686 -2041.711841 -61.71404383 0.518336137 0.518336137 -308.3583546 450.2517943 127.2654861 -269.300585 0.141659233 0.091635004 0.091635004 7.17675E-05 5.8127E-06 9.83657E-06 2.81979E-05 -3.97774E-06 -723.9039686 127.2654861 713.6362945 -162.9241314 45.92631946 0.073795974 0.073795974 5.8127E-06 1.02718E-05 -7.1969E-07 4.40873E-06 -3.76292E-08 -2041.711841 -269.300585 -162.9241314 2488.708704 -14.77214575 0.285903523 0.285903523 9.83657E-06 -7.1969E-07 5.8347E-06 4.28886E-06 -3.3466E-06 -61.71404383 0.141659233 45.92631946 -14.77214575 30.41821088 0.030329362 0.030329362 2.81979E-05 4.40873E-06 4.28886E-06 2.6937E-05 -2.25589E-06 0.518336137 0.091635004 0.073795974 0.285903523 0.030329362 -0.000430751 -0.000430751 -3.97774E-06 -3.76292E-08 -3.3466E-06 -2.25589E-06 1.61501E-05 proof 1 Rp 0.008628929 Var_p 0.000215376 Sigma_p 0.01467568 total risk = syst. Risk+unsyst. Risk VRS sym risk is beta-p^2*var_m 0.011410933 7518.836014 -396.1822772 -1270.206012 -3395.350697 -50.43558186 0.011290933 beta_p=suma_wi*beta-i 0.007698951 -396.1822772 939.4911725 285.9286541 -416.9589258 13.18743242 0.007578951 0.018531903 -1270.206012 285.9286541 1452.557932 -227.8867038 102.2446465 0.018411903 beta_p -0.254929935 0.002479482 -3395.350697 -416.9589258 -227.8867038 5356.944275 10.71686928 0.002359482 -0.002233347 -50.43558186 13.18743242 102.2446465 10.71686928 65.10742655 -0.002353347 VarP_syst-part 2.02271E-05 0.004497454 1.281090309 0.174568291 0.349080762 -0.8372666 0.032527239 Unsyst_part 0.000195149 0.013969556 Zi wi 50.61254302 1.281090309 0.000438394 2.73684E-05 0.000115002 -0.000204094 -1.05436E-05 6.896738729 0.174568291 2.73684E-05 3.72783E-05 -6.48549E-06 -2.45958E-05 -7.68798E-08 13.79127214 0.349080762 0.000115002 -6.48549E-06 0.000130559 -5.94128E-05 -1.69778E-05 -33.07822372 -0.8372666 -0.000204094 -2.45958E-05 -5.94128E-05 0.000231014 7.08512E-06 1.285066521 0.032527239 -1.05436E-05 -7.68798E-08 -1.69778E-05 7.08512E-06 1.85756E-05 sum_Zi 39.50739669 1 Rp 0.020282927 Var_p 0.000510358 Sigma_p 0.022591111 faster way 0.000510358 0.022591111 minVar TangPort return_p/risk 0.587974718 0.897827804 ##### Sheet/List 2 ##### Date KO MCD Goog Amzn Close 4/9/2018 44.509998 161.729996 1029.27002 1430.790039 93.080002 4/16/2018 43.740002 158.770004 1072.959961 1527.48999 95 -0.017450777 -0.018471614 0.041571315 0.065399092 0.020417532 0.000458322 9.36801E-05 7.86634E-05 0.000163308 0.000108569 4/23/2018 43.310001 158.300003 1030.050049 1572.619995 95.82 -0.009879483 -0.002964653 -0.040813756 0.029117156 0.00859454 9.36801E-05 0.000548866 0.000190125 0.000420289 0.00030665 4/30/2018 42.360001 165.029999 1048.209961 1580.949951 95.160004 -0.022179034 0.041635284 0.017476518 0.005282886 -0.006911704 7.86634E-05 0.000190125 0.00085119 0.000896116 0.000568118 5/7/2018 42.139999 165.389999 1098.26001 1602.910034 97.699997 -0.00520716 0.002179046 0.046643208 0.013794847 0.026341801 0.000163308 0.000420289 0.000896116 0.001817269 0.000907142 5/14/2018 42.18 160.979996 1066.359985 1574.369995 96.360001 0.00094879 -0.027026206 -0.029476153 -0.017965559 -0.01381034 0.000108569 0.00030665 0.000568118 0.000907142 0.000736159 5/21/2018 42.400002 163.210007 1075.660034 1610.150024 98.360001 0.005202234 0.013757649 0.008683493 0.022472168 0.020543039 5/28/2018 43.119999 159.160004 1119.5 1641.540039 100.790001 0.016838494 -0.025127747 0.039947699 0.019307492 0.024404927 6/4/2018 43.950001 168.910004 1120.869995 1683.98999 101.629997 0.019065743 0.059456042 0.001223008 0.025531122 0.008299583 MIN VARIANCE 6/11/2018 44.119999 166.460007 1152.26001 1715.969971 100.129997 0.003860525 -0.01461097 0.027620075 0.01881253 -0.014869426 VRS 6/18/2018 43.25 164.550003 1155.47998 1715.670044 100.410004 -0.019915938 -0.011540589 0.002790585 -0.000174801 0.002792532 0.000916643 0.00018736 0.000157327 0.000326616 0.000217139 1 0 6/25/2018 43.860001 156.690002 1115.650024 1699.800049 98.610001 0.014005532 -0.04894515 -0.035078609 -0.009293075 -0.018089157 0.00018736 0.001097731 0.000380249 0.000840577 0.000613299 1 0 7/2/2018 44.639999 159.419998 1140.170044 1710.630005 101.160004 0.01762753 0.017272873 0.021740196 0.006351101 0.025530775 0.000157327 0.000380249 0.001702379 0.001792233 0.001136237 1 0 7/9/2018 44.740002 158.509995 1188.819946 1813.030029 105.43 0.002237705 -0.005724565 0.04178376 0.058137768 0.041343764 0.000326616 0.000840577 0.001792233 0.003634538 0.001814284 1 0 7/16/2018 45.279999 157.970001 1184.910034 1813.699951 106.269997 0.011997411 -0.003412503 -0.003294322 0.000369436 0.007935772 0.000217139 0.000613299 0.001136237 0.001814284 0.001472317 1 0 7/23/2018 46.209999 157.479996 1238.5 1817.27002 107.68 0.020330791 -0.003106707 0.044234119 0.001966455 0.013180869 1 1 1 1 1 0 1 0.441497776 0.329964818 0.26679593 -0.192967748 0.154709223 7/30/2018 46.619999 156.210007 1223.709961 1823.290039 108.040001 0.008833409 -0.00809714 -0.012013774 0.003307197 0.003337672 wi 8/6/2018 46.080002 158.679993 1237.609985 1886.300049 109 -0.011650552 0.015688251 0.011294892 0.033974681 0.008846344 740.6306883 -428.8754259 -223.2810202 130.6369562 -219.1111984 0.441497776 0.441497776 8.93362E-05 1.36472E-05 9.26575E-06 -1.3913E-05 7.4157E-06 8/13/2018 46.599998 161.149994 1200.959961 1882.219971 107.580002 0.011221438 0.015446019 -0.030060884 -0.002165349 -0.013113107 -428.8754259 1008.730151 13.3568353 7.9730817 -601.1846426 0.329964818 0.329964818 1.36472E-05 5.97587E-05 1.67373E-05 -2.67609E-05 1.5654E-05 8/20/2018 45.630001 159.380005 1220.650024 1905.390015 108.400002 -0.021035081 -0.011044252 0.016262319 0.012234804 0.007593332 -223.2810202 13.3568353 1281.857562 -306.3810148 -765.5523622 0.26679593 0.26679593 9.26575E-06 1.67373E-05 6.05877E-05 -4.61348E-05 2.34495E-05 8/27/2018 44.57 162.229996 1218.189941 2012.709961 112.330002 -0.02350443 0.017723737 -0.002017421 0.054795333 0.035612878 130.6369562 7.9730817 -306.3810148 762.7066521 -594.9356752 -0.192967748 -0.192967748 -1.3913E-05 -2.67609E-05 -4.61348E-05 6.76688E-05 -2.70817E-05 9/3/2018 45.720001 163.899994 1164.829956 1952.069946 108.209999 0.025474874 0.010241392 -0.044790986 -0.030591733 -0.037367211 -219.1111984 -601.1846426 -765.5523622 -594.9356752 2180.783878 0.154709223 0.154709223 7.4157E-06 1.5654E-05 2.34495E-05 -2.70817E-05 1.76199E-05 9/10/2018 45.990002 160.839996 1172.530029 1970.189941 113.370003 0.005888164 -0.018846392 0.006588716 0.009239635 0.046583058 0.441497776 0.329964818 0.26679593 -0.192967748 0.154709223 -0.000479059 -0.000479059 9/17/2018 46.580002 165.300003 1166.089966 1915.01001 114.260002 0.012747282 0.027351966 -0.005507589 -0.028407105 0.007819738 proof 1 9/24/2018 46.189999 167.289993 1193.469971 2003 114.370003 -0.008408005 0.011966769 0.023208763 0.044923207 0.000962262 10/1/2018 45.880001 166.570007 1157.349976 1889.650024 112.129997 -0.006733989 -0.004313108 -0.030732118 -0.058254417 -0.019779947 10/8/2018 44.68 163.820007 1110.079956 1788.609985 109.57 -0.02650334 -0.016647377 -0.041700843 -0.054952866 -0.023095271 Rr 0.001900587 Var_p 0.00023953 Sigma_p 0.015476747 10/15/2018 46.330002 167.490005 1096.459961 1764.030029 108.660004 0.036263768 0.022155371 -0.012345272 -0.013837793 -0.008339836 0.000833368 10/22/2018 45.919998 173.339996 1071.469971 1642.810059 106.959999 -0.008889034 0.034331283 -0.023055263 -0.071192755 -0.015768854 0.002966912 10/29/2018 48 176.75 1057.790039 1665.530029 106.160004 0.044300302 0.019481344 -0.012849647 0.013735182 -0.007507496 0.002832695 11/5/2018 49.68 185.940002 1066.150024 1712.430054 109.57 0.034401427 0.050687747 0.007872188 0.027770037 0.031616187 0.004809689 11/12/2018 50.169998 187.589996 1061.48999 1593.410034 108.290001 0.009814761 0.008834657 -0.004380479 -0.072037051 -0.011750792 0.004692956 11/19/2018 49.02 181.929993 1023.880005 1502.060059 103.07 -0.023188821 -0.03063675 -0.036074235 -0.059038857 -0.049404454 11/26/2018 50.400002 188.509995 1094.430054 1690.170044 110.889999 0.027762837 0.03552907 0.066634392 0.117991603 0.073130341 Rf 0.03 p.a. 12/3/2018 49.09 182.960007 1036.579956 1629.130005 104.82 -0.026335867 -0.029883441 -0.054306939 -0.036783009 -0.056294117 0.0006 p.w. VRS 12/10/2018 49.34 183.289993 1042.099976 1591.910034 106.029999 0.005079763 0.001801972 0.005311095 -0.023111559 0.01147747 0.000233368 12/17/2018 47.57 174.149994 979.539978 1377.449951 98.230003 -0.036532801 -0.051152597 -0.061910113 -0.144700646 -0.076410365 0.002366912 12/24/2018 47.200001 175.559998 1037.079956 1478.02002 100.389999 -0.007808397 0.008063891 0.057081257 0.07046944 0.021750893 0.002232695 12/31/2018 47.57 178.279999 1070.709961 1575.390015 101.93 0.007808397 0.015374489 0.031912914 0.063799502 0.015223712 0.004209689 1/7/2019 47.34 182.369995 1057.189941 1640.560059 102.800003 -0.004846706 0.022682221 -0.012707554 0.040534813 0.008499079 0.004092956 1/14/2019 47.610001 182.570007 1098.26001 1696.199951 107.709999 0.00568724 0.001096136 0.038112729 0.033352743 0.046657039 1/21/2019 47.369999 184 1090.98999 1670.569946 107.169998 -0.005053748 0.007802058 -0.006641587 -0.015225573 -0.005026081 Zi -0.180136957 0.289540736 -0.315785536 -0.141026026 1.347407782 1/28/2019 48.700001 176.720001 1110.75 1626.22998 102.779999 0.027689955 -0.040369193 0.017949931 -0.026900413 -0.041825568 2295.023851 -249.5643526 45.19245541 -94.40151966 -153.0645382 -0.978098811 -0.180136957 2/4/2019 49.5 174.75 1095.060059 1588.219971 105.669998 0.016293619 -0.011210184 -0.014226253 -0.023650566 0.02773024 -249.5643526 2472.004263 394.2391523 -249.8770993 -989.2491608 1.572134088 0.289540736 1.48722E-05 -4.88607E-06 4.47474E-06 4.14867E-06 -2.63518E-05 2/11/2019 45.240002 179.970001 1113.650024 1607.949951 108.220001 -0.089990974 0.029433795 0.01683372 0.012346171 0.02384519 45.19245541 394.2391523 2860.881056 -827.6957461 -1358.78459 -1.714636815 -0.315785536 -4.88607E-06 4.60135E-05 -1.73836E-05 -1.71616E-05 0.000119633 2/18/2019 45.279999 183.169998 1110.369995 1631.560059 110.970001 0.000883716 0.017624497 -0.002949642 0.014576603 0.025093702 -94.40151966 -249.8770993 -827.6957461 1680.870231 -1314.506791 -0.765736199 -0.141026026 4.47474E-06 -1.73836E-05 8.48811E-05 3.99076E-05 -0.00024173 2/25/2019 45.380001 185.050003 1140.98999 1671.72998 112.529999 0.00220609 0.010211403 0.027203009 0.024322358 0.01395994 -153.0645382 -989.2491608 -1358.78459 -1314.506791 4461.492498 7.316088688 1.347407782 4.14867E-06 -1.71616E-05 3.99076E-05 3.61425E-05 -0.000172375 3/4/2019 44.84 179.5 1142.319946 1620.800049 110.510002 -0.011970903 -0.030450867 0.001164937 -0.030939122 -0.018113811 -2.63518E-05 0.000119633 -0.00024173 -0.000172375 0.001336502 3/11/2019 45.299999 185.330002 1184.459961 1712.359985 115.910004 0.010206412 0.031962823 0.036225707 0.054952642 0.04770803 sum_Zi 5.429750951 1 3/18/2019 45.93 186.809998 1205.5 1764.77002 117.050003 0.013811488 0.007954016 0.017607477 0.030147854 0.009787157 3/25/2019 46.860001 189.899994 1173.310059 1780.75 117.940002 0.020045956 0.016405539 -0.027065554 0.009014242 0.007574818 4/1/2019 46.470001 190.710007 1207.150024 1837.280029 119.889999 -0.00835749 0.0042564 0.028433365 0.031251609 0.01639861 Rp 0.005459431 VarP 0.000894964 sigma_p 0.029915947 4/8/2019 46.549999 189.850006 1203.839966 1849.859985 119.93 0.001720018 -0.004519668 -0.00274581 0.00682372 0.000333592 faster way to find varP 0.000894964 4/9/2019 46.52 189.270004 1196 1846.219971 119.364998 -0.000644655 -0.00305973 -0.006533764 -0.001969663 -0.00472223 E(ri) 0.000833368 0.002966912 0.002832695 0.004809689 0.004692956 globa.Min Tang_port return/risk 0.122802719 0.182492336