Array input en array output in Excel VBA

[jobdebob]

Hee allen,

Ik heb een vraag over vba. Voor mijn studie moet ik enkele dingen kunnen programeren in VBA.
Het gaat over aandelen portefolio's. De theorie is niet van belang in dit geval.

Ik ben al een heel eind opweg, maar ik kom ergens niet uit. De lastigheid zit hem gedeeltelijk in het feit dat de input matrices zijn op basis van aandelen data, maar het aantal aandelen is niet altijd gelijk. De matrices verschillen dus van grootte.
Het is al gelukt om 4 waardes die nodig zijn te berekenen met matrices als input, en deze 4 waardes zijn de output van de functie letters. Dit zijn altijd 4 waardes (genaamd A, B, C en D), ongeacht de grootte van de input.

Om de gewichten van de aandelen in een bepaalde portefolio te berekenen moet er een formule toe worden gepast, in excel ziet deze er zo uit: '=MMULT(inverse covariance matrix, 1-vector) / C. Het resultaat zijn de benodigde gewichten van de aandelen in de portefolio, maar ik heb geen idee hoe ik dit als output kan krijgen van een functie, omdat de groote van de output variabel is. Via de onderstaande link kan je het bestand downloaden waarin het hopelijk allemaal een stuk duidelijker wordt. Daarin staat ook de code letters, en de code minvar (een poging tot de oplossing van het beschreven probleem).

Ik hoop dat het probleem een beetje duidelijk is, het is lastig te omschrijven. Schroom niet om vragen te stellen!

Alvast bedankt voor alle tips!
https://files.fm/u/78xxzz7d

[breew]

Ik snap de ballen van wat je precies wil berekenen, maar ik weet wel wat van vba.. dus.. daar gaan we

Allereerst: leer jezelf aan om

code:

Option Explicit

boven al je code te zetten. Dit dwingt je om alle gebruikte variabelen te declareren. Geloof me, dit gaat je in de toekomst uuuuuren debuggen schelen!

Dan over je probleem:
Het help mij altijd om dergelijke zaken 'na te lopen' door de functies aan te roepen vanuit de vba-code zelf. Op deze manier van je, via beeld -> venster Lokale Variabelen in de VBA-editor, de waarden van de verschillende variabelen volgen..

Dat zou er in jouw code als volgt uit kunnen zien:

Visual Basic:

Option Explicit

Sub test()
  Dim rngMean As Range
  Dim rngCovar As Range
  Dim rngOnes As Range
  
  Dim tmp As Variant
  Dim tmp2 As Variant
  
  Set rngMean = ActiveSheet.Range("B2:B7")
  Set rngCovar = ActiveSheet.Range("C20:H25")
  Set rngOnes = ActiveSheet.Range("K2:K7")
  
  tmp = letters(rngMean, rngCovar, rngOnes)
  tmp2 = minvar(rngMean, rngCovar, rngOnes)
End Sub

Wat zijn de 'assets' trouwens in de code van je functie "minvar"? die zie ik nergens terugkomen.
Ik heb die functie even verbouwd naar:

Visual Basic:

Function minvar(mean, covar, ones) As Variant

Dim A As Double, B As Double, C As Double, D As Double
Dim E() As Variant

A = WorksheetFunction.SumProduct(mean, WorksheetFunction.MMult(covar, mean))
B = WorksheetFunction.SumProduct(ones, WorksheetFunction.MMult(covar, mean))
C = WorksheetFunction.SumProduct(ones, WorksheetFunction.MMult(covar, ones))
D = A * C - B ^ 2

E = WorksheetFunction.MMult(covar, ones)

End Function

Klopt het dat je elke waarde uit de array E wilt delen door de waarde C?

[breew]

volgens mij kom ik op de waarden die je wilt berekenen.. lang leve reverse engineeren

Visual Basic:

Function minvar(mean, covar, ones) As Variant

Dim A As Double, B As Double, C As Double, D As Double
Dim E() As Variant
Dim element As Variant
Dim i As Long
Dim uitkomst() As Variant

A = WorksheetFunction.SumProduct(mean, WorksheetFunction.MMult(covar, mean))
B = WorksheetFunction.SumProduct(ones, WorksheetFunction.MMult(covar, mean))
C = WorksheetFunction.SumProduct(ones, WorksheetFunction.MMult(covar, ones))
D = A * C - B ^ 2


E = WorksheetFunction.MMult(covar, ones)

ReDim uitkomst(1 To UBound(E, 1)) As Variant
For Each element In E
  i = i + 1
  uitkomst(i) = element / C
Next element

minvar = uitkomst

End Function

Voor tmp2 zijn dat dezelfde waarden als die je in jouw bestand in L27:L32 hebt staan...
Mission accomplished?

[ Voor 4% gewijzigd door breew op 29-11-2017 20:36 ]

[jobdebob]

Beste Breew,

Ontzettend bedankt voor je snelle en zinvolle reactie!

Ik heb het ook niet echt duidelijk uitgelegd nee . Option explicit kende ik nog niet, inderdaad een stuk handiger dan handmatig debuggen.

Die assets in de code minvar was een kort idee om daarmee het aantal aandelen aan tegeven, zodat ik dan wat kon proberen met een loop oid (lukte niet helaas).

Het enige is nu dat de rngMean ed nu een bepaalde range krijgen angewezen in lijn 11, 12 en 13 van het 1e deel van de code. De groote van deze matrices is variabel omdat het aantal assets varieerd, dit werkt dan helaas niet toch?


En inderdaad klopt het dat ik die waarde van E nog wil delen door de waarde van C (die constant is). Dan heb je het toch nog redeljik volledig begrepen ;)!

Ik vraag me dan ook af hoe ik deze E als matrix uit de functie kan krijgen.

Nogmaals ontzettend bedankt voor de snelle en zeer bruikbare hulp!

[breew]

jobdebob schreef op woensdag 29 november 2017 @ 20:35:
En inderdaad klopt het dat ik die waarde van E nog wil delen door de waarde van C (die constant is). Dan heb je het toch nog redeljik volledig begrepen ;)!

Ik vraag me dan ook af hoe ik deze E als matrix uit de functie kan krijgen.

Zie mijn code hierboven.. je maakt er een array van, die je her-dimensioneert op basis van de afmetingen van de array "E". Vervolgens vul je hem d.m.v. een FOR-loop met waarden.

Visual Basic:

...
ReDim uitkomst(1 To UBound(E, 1)) As Variant
For Each element In E
  i = i + 1
  uitkomst(i) = element / C
Next element

minvar = uitkomst
...

[ Voor 13% gewijzigd door breew op 29-11-2017 20:39 ]

[jobdebob]

Ik drukte op enter en zag dat er nieuwe reactie was, ontzettend bedankt!
Werkt inderdaad compleet, echt top!

Alleen nog een schoonheidsdingetje voor dingen waar ik de uitkomsten later nog voor wil gebruiken. Nu komen ze eruit als horizontale vector, is het eenvoudig om dit verticaal te maken?

[breew]

jobdebob schreef op woensdag 29 november 2017 @ 20:43:
Alleen nog een schoonheidsdingetje voor dingen waar ik de uitkomsten later nog voor wil gebruiken. Nu komen ze eruit als horizontale vector, is het eenvoudig om dit verticaal te maken?

Dan wil je de waarden uit de array weer terugschrijven naar een range.. Wil je van horizontaal naar verticaal, dan moet je de array eerst transponeren.

Visual Basic:

Option Explicit

Sub arrayNaarRange()

    Dim z(1 To 3) as string

    z(1) = "a"
    z(2) = "b"
    z(3) = "c"

    Range("A1:A3").Value = z  'alles is 'a'
    Range("A5:C5").Value = z  'werkt (toevallig), want default is links -> rechts
    Range("A7:A9").Value = Application.Transpose(z) 'werkt als een trein

End Sub