VBA-code with array-input and array output

heggalompen

New Member
Joined
Nov 29, 2017
Messages
1
Hi all,I am trying to program something in VBA. The inputs are matrices which varies on the amount of stocks, which is unfortunately not the same all the time.I already managed to get VBA to calculate 4 constants (A, B, C, D) with the matrices as input, there will always be only 4 constants, but they depend on the input of course. These 4 constants are calculated with the function letters (see attachment). The next thing is that I want to calculate minimum variance portfolio weights, which could be done with the following excel formula: MMULT(inverse covariance matrix, 1-vector) / C. I tried to program this into VBA with the help of google but it is not really working. I hope the problem is a bit clear by my text and the attachted excel file including the vba code letters and my attempts for the portfolio weights (minvar and minvar2).

Link to excel/vba-file:

https://files.fm/u/6djneetk#_All tips are more than welcome!Thanks in advance,
- Thomas
 

Some videos you may like

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

StephenCrump

MrExcel MVP
Joined
Sep 18, 2013
Messages
4,052
Office Version
  1. 365
Platform
  1. Windows
Welcome to the Forum!

To divide an array X by a constant C, you need to loop through each element, i.e, rather than saying: NewArray = X / C

So one way to do this:

L28:L33 = {=Minvar(C20:H25,I28:I33)}

Code:
Function MinVar(covar As Range, ones As Range) As Variant

    X = WorksheetFunction.MMult(covar, ones)
    C = WorksheetFunction.Sum(X)
    
    For i = 1 To UBound(X)
        X(i, 1) = X(i, 1) / C
    Next i
    
    MinVar = X

End Function

As an alternative to passing ones as an argument, you could also generate it in the function:

Code:
ones = Evaluate("Row(1:" & covar.Rows.Count & ")^0")

<b>Excel 2010</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th><th>J</th><th>K</th><th>L</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">27</td><td style="text-align: right;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-bottom: 1px solid black;;"></td><td style="font-weight: bold;;">1-vector</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="font-weight: bold;;">Results</td></tr><tr ><td style="color: #161120;text-align: center;">28</td><td style="text-align: right;border-top: 1px solid black;border-left: 1px solid black;;">39.29057</td><td style="text-align: right;border-top: 1px solid black;;">13.46507</td><td style="text-align: right;border-top: 1px solid black;;">5.100492</td><td style="text-align: right;border-top: 1px solid black;;">6.399951</td><td style="text-align: right;border-top: 1px solid black;;">13.76665</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;;">4.687612</td><td style="text-align: right;border-left: 1px solid black;;">1</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">-0.02187</td></tr><tr ><td style="color: #161120;text-align: center;">29</td><td style="text-align: right;border-left: 1px solid black;;">13.46507</td><td style="text-align: right;;">118.7604</td><td style="text-align: right;;">-2.89447</td><td style="text-align: right;;">47.24421</td><td style="text-align: right;;">52.46669</td><td style="text-align: right;border-right: 1px solid black;;">12.03087</td><td style="text-align: right;border-left: 1px solid black;;">1</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">-0.19522</td></tr><tr ><td style="color: #161120;text-align: center;">30</td><td style="text-align: right;border-left: 1px solid black;;">5.100492</td><td style="text-align: right;;">-2.89447</td><td style="text-align: right;;">17.10598</td><td style="text-align: right;;">-6.775</td><td style="text-align: right;;">-16.4519</td><td style="text-align: right;border-right: 1px solid black;;">5.273497</td><td style="text-align: right;border-left: 1px solid black;;">1</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">0.21604</td></tr><tr ><td style="color: #161120;text-align: center;">31</td><td style="text-align: right;border-left: 1px solid black;;">6.399951</td><td style="text-align: right;;">47.24421</td><td style="text-align: right;;">-6.775</td><td style="text-align: right;;">27.41679</td><td style="text-align: right;;">35.43786</td><td style="text-align: right;border-right: 1px solid black;;">3.439729</td><td style="text-align: right;border-left: 1px solid black;;">1</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">0.36513</td></tr><tr ><td style="color: #161120;text-align: center;">32</td><td style="text-align: right;border-left: 1px solid black;;">13.76665</td><td style="text-align: right;;">52.46669</td><td style="text-align: right;;">-16.4519</td><td style="text-align: right;;">35.43786</td><td style="text-align: right;;">96.51904</td><td style="text-align: right;border-right: 1px solid black;;">-3.27943</td><td style="text-align: right;border-left: 1px solid black;;">1</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">0.06807</td></tr><tr ><td style="color: #161120;text-align: center;">33</td><td style="text-align: right;border-bottom: 1px solid black;border-left: 1px solid black;;">4.687612</td><td style="text-align: right;border-bottom: 1px solid black;;">12.03087</td><td style="text-align: right;border-bottom: 1px solid black;;">5.273497</td><td style="text-align: right;border-bottom: 1px solid black;;">3.439729</td><td style="text-align: right;border-bottom: 1px solid black;;">-3.27943</td><td style="text-align: right;border-right: 1px solid black;border-bottom: 1px solid black;;">6.743096</td><td style="text-align: right;border-left: 1px solid black;;">1</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;text-decoration: underline;;">0.56784</td></tr><tr ><td style="color: #161120;text-align: center;">34</td><td style="text-align: right;border-top: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="font-weight: bold;text-align: right;;">1.00000</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Sheet1</p><br /><br />
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,123,514
Messages
5,602,093
Members
414,501
Latest member
mdhaumyu

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top