ࡱ>  | G !"#$%&'()*+,-./0123456789:;<=>?@ABCDEFHIJKLMNOPQRSTUVWXYZ[\]^_`abcdefghijklmnopqrstuvwyz{}~Root Entry FW /Workbook_VBA_PROJECT_CUR" _N P_NVBAY_N`J/_N  !"#$%&'()*+,-./0123456789:;=>?@ABCDEFGHIJKLMNOPQSTUVWXYZ[]^_`abcdefghijlmnopqrstuwxyz{|}~ \phardingegel Ba= ThisWorkbook=ZZX/#8X@"1.Times New Roman1Arial1Arial1Arial"$"#,##0_);\("$"#,##0\)!"$"#,##0_);[Red]\("$"#,##0\)""$"#,##0.00_);\("$"#,##0.00\)'""$"#,##0.00_);[Red]\("$"#,##0.00\)7*2_("$"* #,##0_);_("$"* \(#,##0\);_("$"* "-"_);_(@_).))_(* #,##0_);_(* \(#,##0\);_(* "-"_);_(@_)?,:_("$"* #,##0.00_);_("$"* \(#,##0.00\);_("$"* "-"??_);_(@_)6+1_(* #,##0.00_);_(* \(#,##0.00\);_(* "-"??_);_(@_) 0.0% 0.000% 0.0000%0.0000000000000000%                + ) , *   ! ` Sheet1! returns;" returns4;H" returns5;H" returns6;H FunctionsportfSigma(returns,weights)portfMean(returns,weights)&optimalWeights(returns,expectedReturn)$optimalSigma(returns,expectedReturn)1optimalConstrainedWeights(returns,expectedReturn)/optimalConstrainedSigma(returns,expectedReturn) \0< Sheet_Titlerea:ܼb02CV7MI|0@ Sheet1 </|N/v G%FSBx"86ү#SB/|N/v G%>4ThisWorkbook <lModule1 __SRP_3)__SRP_2Rc(SLSS6"N0{00020819-0000-0000-C000-000000000046}+n: $*\Rffff*3039ff0323h(@$`@ $ $ $0 $( $X $80@hh0ME xpAttribute VB_Name = "ThisWorkbook" Bas0{00020P819-0C$0046} |CreatablFalse ^PredeclaIdTru "@ExposeTemplateD0eriv$eCustomiz2Option /licit rU \,`ay/+,`ay/+<` /////0` !I,!`//,I`. J. ZH//`./ ./0p.(0*80h0#p0!0:(0%0#011 1(1@1X16`111111(F112 2(202H2`2h2`p2 2 2~<2p3x3 3<34 4 (48.b684@4 H4@.bX7x4444f4 5 05@5 P5%p5855>k5<:5 (6  J@6 P6 7 66B ( J8 Z .!Xx kW6%kyP<L*> L<+n: $*\Rffff*1b3a109bbe*\R1*#b8*\R0*#12*\R1*#15c*\R1*#b8*\R1*#20bH90h80n $  `(Skx  I" I$x $$ (h$ Hdh L`H@ R\ H@L `@< 0 `>: `>:`\8H`l6`@t$    @t$ $H ``` $0  (hh ( HdHX0 L`hPxH R\p`@L @< `>:`>:`\8`l6 ( hx  x8`XPx8`XPx8`XP h `OkP "":I"  ` PI$P X0 HdH L` R\p@ @H L @< >@0H `\8 `l6p ( h@ ` ,8 `   $`,$$$$ $8`xHPHP @ `xHPHP @ `xHPHP @ H ```k %%\I"  It $$M `.  HdP  L` h R\@L @ <  >`\8`l6`& >. h   `Uk  )) I" %/ Pxx0Pxx0Pxx0Pxx0`xPHHP ` `xPHHP ` `xPHHP ` `xPHHP ` `xPHHP ` `xPHHP ` `xPHHP ` `xPHHP ` `xPHHP ` `xPHHP ` `xPHHP ` `xPHHP ` `xPHHP ` `xPHHP ` `xPHHP ` `xPHHP ` `xPHHP ` `xPHHP ` `xPHHP ` `xPHHP ` `xPHHP ` `xPHHP ` `xPHHP ` `xPHHP ` `xPHHP ` `xPHHP ` `xPHHP ` `xPHHP ` `xPHHP ` $$It $$$x300@%/ $$$.  @'L @'< `>:($. 8 `\8`l6@@t$ $& & ` @$$$ (08h8'  ' dp'zX' L`''' Rc\'(@'$ 8  % P@&'p'X&H' 8  % P@&'p'X&H' 8  % P@&'p'X&H' 8  % P@&'p'X&H'$2$2 $$ `&} 8  % P@&'p'X&H' 8  % P@&'p'X&H' 8  % P@&'p'X&H'$$@//LL@, (8h,   $((%h ? $`*x'`&@F` $@TT@/*1@l 060% H& / 0X6@\ ,Hx*H@* *p*p*X*@l 6'@L @\ `&3t@l @\ @l p9:@l @\ : : @L € (3 @ 8``F|kh2WWI 3.@\ `@L (@L  @L (I  €P3 @h%€@%  @ €08 8@I" 8: -.; &&' (2`D k4,I 4I 585I, .+4`B+x5H.IB 5I 5I 5I" 5(I$ -& d/8P3 L`085 ; % <8; *p%x%`J<@/.7 85`HcHk7 WW[ I" 08i$7/ d7`;Pk3 OO i$3,7(2h ,(381P3x3/@%0P8485$ $ R\1@/@X9L @8-< `>: `\8 `l6 @t$ 0.`@'`:H.`0:` : 1@1@dl @< @; @Zj @ @ @ @9999H:9x:@69p68;`: ?;h;P;A<<8;@6P9`<<" @8@ @h= =<' $@l @=h='@\ =h=3@L @P@ =P@F@l =h@Fh @\ (@@G@L @l @\  $'@L @l >@\ =@L (= <LX :.D@?  $@l @\ @L `\p7:@? @? h=`l8@@ @ 9@ @@ @h@ @@ @@ @A @@G@@I Ah@J;a@At 8AAQ8A@l H `AAQ @\ AQ@L @d 5@A\ @T @AR @P `tB8A@(Cd <@l @\ @L 5555x5@C(BBB@XCT @CD @pC4 @C$ @C @C `D@C @D @D @0D @ @ `A@l @\ pE@E @L XE@EEFFExD@l >@\ EXE@L E@GxG`T?6@/`\ 4 @/@HF< @0F, @ @ ?E 1>Sh2E>MHGGMxGPIE@j@0G$ @G @`G (@HH @G @G GGHG(@G @ @ H (H8HG@`H xH H@H @H H`H@I HH@H @ HH(I H@(I @II@hI I(IXIG Z @I pMhI@N  $p7?3>h2@0.IHG(IxIp7?3>h2@0.IHG(IxIp7?3>h2@0.IHG(IxIp7?3>h2@0.IHG(IxIp7?3>h2@0.IHG(IxIp7?3>h2@0.IHG(IxIMIMIM(I1@ <` NM@0N HNN@ O O0N $(Mp7?3>h2@0.IHG(IxIM8O0N@HO `O O @O pOHO OHO @O~ OO@| OO OxPHxPQPP QHQPPP@l 8QP9 @HR @P @Zj@@`R @ 8?(>``Q QI@pQ QHQJ@Q QpQJQpQM@Q QQN@Q RQN@ R 8RQO@ xR RO @ @R R RQQR @ @ l (TR @8U @S  $@HP P R`SRRT8TRSR@Z @PU .@ 8?`PTT) @`T xT8T*@T T`T*T`T-@T TT.@T UT.@U (UT/@ (hUU/ @ @U xUU1`T2@ 8 %@VHPx\VhV\\@VVXV@l (V@V@_ @V @Zjx\@_ \\p7?3>h2@0.8V(U@TPVVp7?3>h2@0.8V(U@TPVVp7?3>h2@0.8V(U@TPVVp7?3>h2@0.8V(U@TPVVp7?3>h2@0.8V(U@TPVVp7?3>h2@0.8V(U@TPVVp7?3>h2@0.8V(U@TPVVp7?3>h2@0.8V(U@TPVVp7?3>h2@0.8V(U@TPVV@ I`@\ \\@`^ P^\p7?3>Sh2@0.PV@UXThV\p7?3>Sh2@0.PV@UXThV\?8^@^x^\@^ ^`^@^ ^^@^ ^^@_ 0_^@_ @P_ @_^_^@p_ 0]@ @_ __@` __@ `_@(` @``@ P`(`(` x  a0 a@a(aaaaXaa@l aa[@b @pa @Zj@b @ XN`aaj@a bak@(b bak@ban@Pb hb(bo@xb bPbo@b bxbp@ bbp@ @c cbrPbs@0c @ &0&%0.%/@'%x'H.(epd(ddgddc8gd@l dcZ@d @Xd @Z j@g @ S`ddp@Pg @d edu@e edued@@e @Xe @pe @e @ @e ee@f eefe@(f @ff@`f Pf(fxf(f@f f`f@f ff@f ff@g gf@ (gfgf@d@hg| @gl @\ @gT @L @gJ hg@H hggV P81/;\8ME x *7f`BX`  ! B 0*p# !0:(%@#X  p6(FX`P&  ( O# 68"p  8O# 68"  8 PX ` p (f@    %8 >k!<:P" !  J P0 * 0B X 2K! " X | @8   .( *Xx,8" Z@!x!P)-$$$)$ +0*N* ++2#0+p+x+h+ + $++ Vx -`   !2). H!@"p!H P  4 3%"B %62( f&0%0%0&00&'.LX'.Lp%.L'.L%H%@f& $ `% 'x(0 (0@((.L( ( ( 2 *`*x* 0)X)"Q - l,X!)(" x) 2--+. - ) V, Bx,#)LfH- ) ) ) )) `)B x J :  .X 0#8!H$X%p#(F 0@Xfp  (_ ,f0  `  h p B x N8    .0$   # ! $ % #8 P h    (F    f    (_ &V 0h    B  HP p  .`$  # ! $ % # ( "@H`(Fx f H X  F @H<0 0bp 0bh   B  RH( .$HX#`!p$%#  "((F@ hxf   0 FP @<(H (H ( 0 8@ 8xffhCgiven a matrix of returns, this finds the vector of optimal weightse "!B!:'>x "!8!:'@Z!# assets, # time periods0RhF @H' @ @LoZ toh \ @ \ \8 \ @+ \+H l @ l0Z%0+R $+R 8Z t' 0 ZnZ L7z' H7 7v H7v7z' \ "%87V > \+H \ "%8 l "%87b > \$H l$H  >  \ l+L\(  H7v 7v R7v7'tX t' nh  H7v 7v R7v7't H7 7v H7v7z' L7z' \ l \ "%8 l "%87b > \$H l$H  >  \ l+L l @ \ @ \ \ "%87V > \+H \+H \ @ Z $+R+RR @ @Lx @H "!B!:'> "!8!:'@ZZZ!# assets, # time periodsZZCgiven a matrix of returns, this finds the vector of optimal weights8Cgiven a matrix of returns, this finds the vector of optimal weightsZPZ Z`# time periods, # ZZ "!8!:'@ "!B!:'> @H @ @LR@+R $+R Z \ @ \+H \ "%87V > \+H \ \ @ l @ \ "%8 l "%87b > \$H l$H  >  \ l+L l \ L7z' H7 7v H7v7z'  H7v 7v R7v7'tnh t' fXZZPZZx# time periods, # Z`x "!8!:'@ "!B!:'> @H @ @LR+R $+R Z \ @ \+H \ "%87V > \+H \ \ @ l @ \ "%8 l "%87b > \$H l$H  >  \ l+L l \ L7z' H7 7v H7v7z'nX 'fH4having the smallest st dev given the expected returnHgiven a matrix of returns, this finds the smallest stDev given E(return) ZH  H7v 7v R7v't  t7 L t7v7v#$j' h mtxInvV = .MInverse(v)s fi t!8!: t!B!: E(rZ H NALZx  AZ0# time periods, # Z  LaZ "!8!:'@ "!B!:'> @HX R @ @L AR muP1(1, 1) = 1ES Z \ @FIC \+HCE\ \ "%87V > \+H \.XL \ @C: l @FIL \ "%8 l "%87b > \$H l$H  >  \ l+LY lLL \hX t7 L t7v7v#$j'n 'f Agiven a matrix of returns and a vector of weights, find the stDev?mtx2 = .MInverse(.MMult(.MMult(.Transpose(mu1), mtxInvV), mu1)):weights = .MMult(.MMult(.MMult(mtxInvV, mu1), mtx2), muP1)X muP1(2, 1) = expectedReturn t L t77v7v#$j'Z  PZ ZH# time periods, # Z 8-0Z "!8!:'@ "!B!:'> @Htl @ @LRomuP1(1, 1) = 1{18BmuP1(2, 1) = expectedReturn#2.0#0 Z \ @.TWD#M \+H Objec \ "%87V > \+H \ \ @ l @ \ "%8 l "%87b > \$H l$H  >  \ l+L l \mtxInvV = .MInverse(v)?mtx2 = .MInverse(.MMult(.MMult(.Transpose(mu1), mtxInvV), mu1)):weights = .MMult(.MMult(.MMult(mtxInvV, mu1), mtx2), muP1) t!8!: t!B!: a \ @7 \#$hP nH 'f8 Kgiven a matrix of returns and a vector of weights, find the expected return \ @or of  \$t \$H ' \  \$t \$H '7fXCgiven a matrix of returns, this finds the vector of optimal weights  7v'  7 7v'Z'@'x''Z%%Z%# time periods, # Z&0&ZH& "!8!:'@ "!B!:'> @ @L&R&+R $+R Z \ @ \+ "%87V >' ' \ \ @ l @ \ "%8 l "%87b > \$ l$  >  \ l+L l \ L7z' t' t' t' t'n Shaving the smallest st dev given the expected return, with weights constrained >=0.Z1Xthis will be V^(-1)(1,mu,c1,c2,...,cn) where (c1,c2,...,cn) form the nxn identity matrixZ(34this will be (1,mu,c1,...,cn)'V^(-1)(1,mu,c1,...,cn) @ @ Pinitializes it to zeroesZP3Z@% 'hZ&*J'' \$' Fh \' \$' h \' $  $h' @t 0- $  t @t%ec' \$  Z0.H.@asset index (1,...,n)r than \$  e sxsp $ shP @t +t @t +tsh $  @t` +tsh \ "%87V > \+C mtx2 = .MInverse(.MMult(.MMult(.Transpose(mu1), mtxInvV), mu1))KJ weights = .Transpose(.MMult(.MMult(.MMult(mtxInvV, mu1), mtx2), muP1))Z/ @@hl  @ A@ado the work here!4XLasset list is array(n) containing indices of assets to be included in subset:this recursive subroutine produces all subsets of n assets  +uasset with index nextAssetToTry will be included or not (by inserting at position nextIndexInAssetList), then recurse(first call myself without the next asset    A@$then call myself with the next assetllX '@Z  @A@   A@$ @'Thx Z .now we have at least 2 assests, so get to work ass(Dim n As Integer '# assets is now global(2Nlist of k assets to be excluded using linear constraint to set weight to zero. Z, $'Zx3Z0 L' @h%   A@x!hu85 T T$$j'&Othis asset by itself has the target mean. Let's see if it beats the best so farfinitialize globals:  &  gmas "    $ "     'stmeanZ 1 T+*$ \ &,sqrh Z@0 @*&Z/ $    $L$j  $L$j$' A@eger '(dim n as Integer '# assets is now global \$+F *'# \ @E \$ \+E \H$ \ @E \+tE \ \ @E \+tE \#PToo big. Target expected return cannot be larger than largest expected value of  for asset  .+tTToo small. Target expected return cannot be smaller than smallest expected value of  for asset  .+t$(Dim n As Integer '# assets is now global(Dim n As Integer '# assets is now global(Dim n As Integer '# assets is now global$Z>Z Z>Z??ym l1uc() @  <*   >. l  \ \$ $ \ +>$+> \$ $ \ +>1u$+>$+>$+> \ \ +AThis sets the right-most n columns of mu1c to be the nxn identity \$ l$ $ \ l +>_Now keep only rows & columns corresponding to assets in the list, to be constrained to weight 0ai \$ $ \ +> \$ $ \ +> +! \ (Now do the same for vm1uc l= \ \$  \+< \$  \+< h \+F \ @yes, nm3 l yes, k0' \ l$ $  \ l +<   :&initializes to zero(Ready for calculationsZ. Zn() \yweigh \ifyvm1h`(P*Zp7 Now get sigma#C:\ J  igma F' J'  F7v'H7v J$j'JJ 7'19X*,Now do the same for muP1c; the rest are zeroTEMP\V(++:  +: @+  @Fugwould exclude all assetsh+  @ 5just one single asset - does it have the target mean?ZEE \$ \this is the omitted assettdoh+ \'T'\ \ @ \0000'\E'\now we know which asset it is* \ '\s> T$ /|fBe careful that constraints set exact zero, no roundoff by setting weights to zero for excluded assetsXD#M*<I do not know why this didn't work, so we will do it by handlV, trO trySigma = Sqr(.MMult(.Transpose(tryWeights), .MMult(globalV, tryWeights)))iZS < >7z :7v7v'F@Ce \ @E J \$F \$H 'JE \+-,If any weights are negative, do not continue \ @ \ \$F Fug -P8 '\ '\ " $$'tZ99'Z:s h-$t Z%f \ @E \+*E \`-$t'dXCgiven a matrix of returns, this finds the vector of optimal weightsXmXShaving the smallest st dev given the expected return, with weights constrained >=0.Z8-@6p6X9Z9# time periods(dim n as Integer '# assets is now globalZ99Z:0:ZH:`:@asset index (1,...,n)XZx: "!8!:'@ "!B!:'> @ @ 1initializes it to zeroesXrX @ @L1R3 @4 @6 $'+R $+R Z "%87V >' ''' \ @ \+ \ "%87V > \+ \$  X \$'X \'h+ \$  \$' \'hx+ \ \ +AThis sets the right-most n columns of mu1c to be the nxn identity \ $  PToo big. Target expected return cannot be larger than largest expected value of  for asset  .'ds*h* $  TToo small. Target expected return cannot be smaller than smallest expected value of  for asset  .'d+s)h) $ s(h( $  +t t'sX(hP( \ @ l @ \ "%8 l "%87b > \$ l$  >  \ l+L l \ L7z'initialize globals:  7v' X 7 7v' L' $    $L$j  $L$j$' \ @E \$ \+E \W_X A@C mtx2 = .MInverse(.MMult(.MMult(.Transpose(mu1), mtxInvV), mu1))J weights = .Transpose(.MMult(.MMult(.MMult(mtxInvV, mu1), mtx2), muP1))n%f% \ "%8 \ "%87b > \$ \$  > $j'd * mu1 \ "%8 \ "%87b > \$ \$  > $j'dns(i), & 'd7#copyright (c) 2000 Andrew F. Siegel7oAttribute VB_Name = "Module1" Option Ex@plicit@Base 1 'copyright (c) 2000 Andrew F. Siegel Dim globalVm1uc 'this will by^(-1)(1,mu,c1,c2,...,cn) whereY form the nxn identity( max kCvl keb'V > #BestWeq@sSoFar Sigma As DoubleV Mu()ectedReturnInt@ FuncomalConstrainedG(r#s, exp+) Āgiven aO0 of hfi(ndsB[v@or3A wEhaPving s#lL st dev^ l",@th c0 >= 0.Adata M, mu1cK2v P1Imtx2txInvie TZV '# ti@pe riods'dHs  assets @nowEi, jHEHNA ELMea~, largR{ AA#J  '/ @vex ba^nALisnt(dn`C.ColumnsDun TRdow(Re b6n@, 2 + {'initialize(t to `zeroe".#v(E:71bc)`m l&=,c!mu 1, 1) @Me' KiWiXWorksheetr ! := .S umr'(1)) / T== :=a =& F`vi To n  hi b4-2i ,Ifcqc*e*p'i`Xb$1 'T!b!B-mo@nbdA!a\)C`Next (KM *) I@>"Too bigh. Tt^ cannot!x2r[HvalDueQ " && " x B8[ .p% `it K2i +%oa7Lc: YX-\=-0{=t1!pSqr((q=PrctHi),![+(!?:-]*>L(T -PSY ExN .s p>o-JXQj` B1zY v(TjXvUjY}cjw[rGj9H1 .Merse(v{ys:vCoMMult(c$.TranspoS(zV v9Ab.=Ѝterpola4teT,jjZ, !2v(&;6) 8* :iA2): enumer Subgp|_ S0%@2.e Du@TtO2P II[1aA c /MEڽ2'РthtStfechULh4 ,pC? pbФ Dim T As Integer '# time periods 'dn a assets is now globalXi, jBweight0smalles tMean-Double, largC 4Ag :  ' index (1,.`..,n)+L`ist()(n = returns.ColumQuntTRLow Reqmu1c(n, 2 + n) 'initializet to zeroevK!P1(2, >1U VMu( ExpectedRaWe i1mu@1A1F With WorksheetFunction Ju= .Sum(O(1)`) / T {=u= ) IALFor iA3 HTo D& Bci : E2) +i+ IfF <J U(e'i`Xb$1 'Th`b the rz-most n ccd o$a\be nxn identityCNexVt(kS *) I`$yk$1A:"Too b ig. T!t  z cannot "r $Avalue" &J/& " f?Nr`+@." opalConstraine|dW#OEX2YX: ib": ( Exit KrH, X,VH,Y-Df$-%`T-o---=--fFƯJZ->ba@ Ja _" \"8Y8I ga8"n /1=%1 W_Qj51` v(`j(_ProductkHi),=(j@a-m`*`j2c(T -{a:j i;mtxInvV .Merse<(v~7s:~Vm1ucMult(,R $Cv.Transpo!^ W v9BSigmaSoFa rirpolhateL\,q*, Sqr(v(!?;. -0_)){ :Sum2);Tenumer Subn6c S 'q@2".Me DuT2_sB2P @wfS?!B"1A OWeдteux, a, b, f`fbr3"Ifa(x1a) * (fbf/ (a +` =(ByVal0, n`R#ToTryYIѿIn S{lqarra&y`ntyinrg!ics4includerd sR"tw1 will(bysertAat c9on  ,e1n`IcuBtQreive1 rou݂M0 q- ޱ F>7j'do! w here!Q try_ PQElse'firpc @myselfthp ACo9Ab&01 Ъ|O=R1(stcq f, xt gA 0Ifc80 0(kP-,Mk%+ex +=ded using linear constraint to set weigh4zero. Dim temp As DoPuble *W`sd()= n Then Exit Sub 'would exclude all a's- 1'ju8st /= -@ does )h@ave thaTrgm??%  V* i = 1:X= Fals Whi@le i <,And NotB 4*List(i) <> 6P'thi"s!omitt "iN= Tru! E@IfA,i + 1WendC'now khichG@EiS-glo@balMu(C)AExpec+`ReturjC F 51 byZs@elf ha9t[. Let's Ofebeat beoso f ar= SqTr( V&,m)N) @_ < BSoFa Ren` For@n@ To n::= 0: Nex|t 4ae(2 Gd=I sts&o _worNk En,q+\ 2!fDkK2È1) 'initializOo 'NQkeep`uly r ows &lumnsrrespondaJ`f*nbfl@j,be_@$`hĮ0a tDry1, `=dACv2g] `  @  Kb0 g ax@ b! 4a c#1!dJ#` jl jNnm ,#j)B#BtNjauPdoJsame f`}C3n 'yegti%8{v;lvoowjkm;27t are :=23 Vk*+b'ReadycalculatiWith W HsheetFunctQ.MMult&(0, .MInversee>)Ary a'BB fulQqhtact, noHundoffltaHE`jor ddC'Dtwz#9 (9+#K zd'nysnegve,np *B}`+< 0#/1|!ZAbs >'!kӠyETranZS`snnVbI tydidn'`!jwe wi:havǰ;"osJ{ }ry[U +X (C*yc a  7 7 optimalmrOe 'givкa matrix of CRfindvpU- '~he slbev #!4Cdata),P mu1 vP mtxLP0EVETʤ '# e period?'1nL">BѶ Fn,$.CopuntTR qCf#v(5\Q`(Z^+ A$\3 40L"r.Sum(<) / T?iJ?O0Q6P Fv1;r= (aProductKj))pT * mu1(i, 2)P * mj) / (T - 1) Next j4,i,mtxInvV = .MersXe(vj62 0.@MMult( Transpo1y), K u1)DweightsG/ HV>G=M 2JPKEnd Withopt`imalW\3  FunctiPon  Sigma(returns, expectedRX'given a matrix of this finds the s@lest stDev E.&Dim data() As Doubleo vP1wx$2 TAInteger '# Ee p@eriods'An assed@3now glo(bali , NjHEZ soT 7A n{DM.Columns(untTRowHRen v(nT'HCmu@1,1 B@Ky Works0heetFor i To π1)AA*߀Sum<(i)T ݋Aj vj(.ProductP,eU&j - T??v??6ރ+P= SqrPV#@v, e)(?W"`2 ͋poxrtf̋  aa v @O,B#cF c(% ##7&?Bs.?<'OCȅK?D?DQZK?D?D߆߆?D ''k4_DY'3"oDGtxVysIfuA%pk stdole h%^*\G{00020430-C 0046}#2.0#0#C:\WINDOWS\Syst em32\e2.tlb#OLE Automation`MSForms>SFFrms/z pG18B19C5E-A2F4-11D4-A928-8A44A7ABC632GYSTEM\3.TWD#Micext'o TransposeMInversMInverpMInvebminVXMinMI?^MdmtxInvVfxogetV optimalSigmasigmawigmJ _B_var_wigm}aX_B_var_a portfSigmay theseWeights@4 _B_var_IfP_B_var_expectedReturn_B_var_theseWeights} portfMeanmean@meana _B_var_meanameansmua _B_var_muaroptimalConstrainedWeightsr=vm1cu[cvm1ucumvm1ucmu1c bestWSoFarV]bestSigmaSoFar?bestWeightsSoFar) lowestMeani smallestMean= largestMeanexpectedlargestsmallestMeanAssetlargestMeanAsset/enbdifenbif n assetListJenumerateSubsetsh_B_var_enumerateSubsets enumerateAssetsttnextAssetIndex1 _B_var_SubnextAssetToTrynextIndexInAssetListn nextindexin _B_var_nextindexino{_B_var_x_B_var_n testEnSubsKMsgBoxR tryAssetList_B_var_tryAssetListrkb globalVm1uc globalCvm1ucglobalBestWeightsSoFar-globalBestSigmaSoFarglobalVglobalMu:globalExpectedReturnglobalexpectedreutnrh_B_var_globalexpectedreutnr interpolate_B_var_interpolatefa4]fb5]tempR_B_var_bestWeightsSoFar#z tempWeights: tempWweights_B_var_bestSigmaSoFartryV _B_var_tryV2VtryMu1c_B_var_tryMu1ctryMuP1tryMuP1ctryVm1uc4F tryCVm1ucgtryvm1uք_B_var_tryvm1uZ _B_var_vm1uc tryWeightsk;tempTryWeightstrySigmaP_B_var_Transposei1] _B_var_i1; _B_var_Exit singleAssetAP _B_var_WhileIfnotDone~h _B_var_wenddone  _B_var_donevaretnegative)optimalConstrainedSigma~iIsNumberm _B_var_sigmakl 6@Brosoft E= a Ob Library9P0rPF!Pc:\wi@ndows\@'P\VBE(EX(.E .`M xOfficxOf@icx,,2DF8D04C-5BFA-101B-BDE5xAAw4 UPROGRAM FILES\MICROSOFT OFFICE\MSO97.D LLHZ# 8.0EZ¼6¦ThisWorkbookQN2@1TiEWkbokHB1Bò@B,Bү"B+BCodule1 2ouB1PyM !JSheeVt g2S`eI`4t  8 C" K)^rU~~~~~~~~~a^  *\G{000204EF-0000-0000-C000-000000000046}#3.0#9#C:\PROGRAM FILES\COMMON FILES\MICROSOFT SHARED\VBA\VBA332.dll#Visual Basic For Applications *\G{00020813-0000-0000-C000-000000000046}#1.2#0#C:\Program Files\Microsoft Office\Office\EXCEL8.OLB#Microsoft Excel 8.0 Object Library*\G{00020430-0000-0000-C000-000000000046}#2.0#0#C:\WINDOWS\System32\stdole2.tlb#OLE Automation*\G{18B19C5E-A2F4-11D4-A928-8A44A7ABC632}#2.0#0#C:\WINDOWS\SYSTEM\MSForms.TWD#Microsoft Forms 2.0 Object Library*\G{18B19C5F-A2F4-11D4-A928-8A44A7ABC632}#2.0#0#c:\windows\TEMP\VBE\MSForms.EXD#Microsoft Forms 2.0 Object Library.E .`M *\G{2DF8D04C-5BFA-101B-BDE5-00AA0044DE52}#2.0#0#C:\PROGRAM FILES\MICROSOFT OFFICE\OFFICE\MSO97.DLL#Microsoft Office 8.0 Object Library+n: 6ThisWorkbook 3039ff0323*DүModule1 1b3a109bbe*D`y Sheet1 303a116e2b*D8C0`ޑb8Ng*Ĭqn:v (+[3x(zu_IPxɩ@Q8`x)XJ{Rk?Any As b baseBooleanByVal=Dim@DoBDoubleDelseHendifSFalseUFor`IfkIntegernotoptionReDimsubThenTotruewendWhileWithExcel+VBAWin16~Win32Mac stock data/stdole`MSFormsC VBAProjectOfficeu ThisWorkbook| _EvaluateSheet1Sheet2Sheet3Module1boptimalWeights \returnsexpectedReturn^_B_var_FunctionOdata; _B_var_datarlows _B_var_lowlWorkbookk Worksheethighz _B_var_OptionsColumnsp9Count0vnAssetsTkneRowsUmuK^ _B_var_mumu1 _B_var_mu1rvm_B_var_v _B_var_ReDimmuP1b _B_var_muP1Sum _B_var_SumȒWorksheetFunction&i` _B_var_forjL_B_var_i  SumProductsqrt^% _B_var_sqrtʊcolumneb9Sqr(ja_B_var_j SumSq  expectedr#eweights[MMult _B_var_MMultaMInverse_B_var_MInverseworksheetfnctionv_B_var_worksheetfnctionpdm\ _B_var_dmmtx22 _B_var_mtx2NexttE _B_var_N~~~~~~~ ~ ~~~~ ~~~~~~g 1o:v (+[3   a a ) y  9 I i    ThisWorkbookSheet1Sheet2Sheet3Module1 VBAProjectF=C:\PROGRAM FILES\COMMON FILES\MICROSOFT SHARED\VBA\VBA332.dllVBA Q y(F 3C:\Program Files\Microsoft Office\Office\EXCEL8.OLBExcel i80FC:\WINDOWS\System32\stdole2.tlbstdole qH^(D2C:\WINDOWS\SYSTEM\MSForms.TWDMSForms aAX.E .`M _(D2c:\windows\TEMP\VBE\MSForms.EXD iAhL-[DR 2C:\PROGRAM FILES\MICROSOFT OFFICE\OFFICE\MSO97.DLLOffice 1YxoptimalWeights ColumnsCountRowsF F IFeU˸GF FMTN)"0(DF Worksheet @ optimalSigma VBA332.DLL  f  portfSigma portfMeanoptimalConstrainedWeights 0 ns asset list is    interpolate tryAssetList% GTarget expected return cannot be larger than largest expected value of  for asset  .& ITarget expected return cannot be smaller than smallest expected value of enumerateSubsets testEnSub 0    S 8 ""T 8, ,,P s),<eger ""T0@ &&\ 8H * PToo big. Target expected return cannot be larger than largest expected value of &, TToo small. Target expected return cannot be smaller than smallest expected value of **d 8Hn caL &&\ 8HoptimalConstrainedSigma &&\ 8H wor)rU~} returnsexpectedReturn 9  theseWeightsweights assetListnextAssetToTrynextIndexInAssetListn__SRP_1 :PROJECTwmVPROJECT SummaryInformation(xabfafbkBThisWorkbookThisWorkbookModule1Module1Sheet1Sheet1ID="{4218E643-1C78-4876-8780-E1289DA0BE02}" Document=ThisWorkbook/&H00000000 Module=Module1 Document=Sheet1/&H00000000 Name="VBAProject" HelpContextID="0" CMG="8F8D282F58517755775577557755" DPB="AAA80D542955295529" GC="C5C7627566916791676E" [Host Extender Info] &H00000001={3832D640-CF90-11CF-8E43-00A0C911005A};VBE;&H00000000 [Workspace] ThisWorkbook=0, 0, 0, 0, C Module1=36, 58, 848, 299, Z Sheet1=0, 0, 0, 0, C  Oh+'0@Hdx Historical Quotese Andy Siegel Andy SiegelMicrosoft Excel@Ku ՜.+,D՜.+, PXd lt|DocumentSummaryInformation8CompObjf 1 Sheet1  Worksheets 6> _PID_GUIDAN{4D8E5BA0-E155-11D1-8D1E-24920DC10000} FMicrosoft Excel WorksheetBiff8Excel.Sheet.89q