1 pièce(s) jointe(s)
Utilisation Solver via VBA
Bonjour à tous,
Désolé de vous déranger durant ce dimanche ensoleillé mais j'ai des petis soucis concernant l'utilisation du solver via VBA.
En fait, je dispose des rendements de 4 actifs sur 5 dates et je souhaite trouver le poids optimal à associer à chacun de ses 4 actifs pour construire mon portefeuille de sorte que la volatilité du portefeuille soit minimale.
Mon application est financière mais ma question est d'ordre plus général. En parcourant internet, j'ai pu voir que pour utiliser le solver sur vba, on fait appel à ce genre de code dans lequel on référence les cellules intervenant dans l'optimisation.
Code:
1
2
3
4
5
6
7
8
9
10
11
12
13
| Sub test()
SolverReset
SolverOk SetCell:="$J$12", MaxMinVal:=2, ByChange:="$E$12:$H$12"
SolverAdd CellRef:="$E$13", Relation:=2, FormulaText:="1"
SolverOptions Iterations:=30, AssumeNonNeg:=True
SolverSolve UserFinish:=True
End Sub |
J'ai donc testé ce code (qui fonctionne bien) dans lequel ma cellule cible est celle qui contient la formule permettant de calculer la volatilité du portefeuille que je cherche à minimiser.
Maintenant, je souhaiterais définir ma cellule cible et mes contraintes en faisant appel à des variables précédemment définies dans mon code VBA au lieu d'indiquer la référence d'une cellule. Pour être tout à fait clair, j'ai créé dans vba une variable volatilité et lui est associé la formule permettant de calculer la volatilité et mon objectif serait d'utiliser le solver de cette manière :
Code:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
| Sub test()
'Définition de la plage des rendements
Dim rend() As Variant
rend = Range(Cells(6, 5), Cells(10, 8))
'Définition de la plage des poids associés à chaque actif
Dim poids() As Variant
poids = Range(Cells(12, 5), Cells(12, 8))
'Transposition de la matrice des poids
Dim poids_transpose() As Variant
poids_transpose = Application.WorksheetFunction.Transpose(poids)
'Calcul du rendement du PF pour chaque date en fonction du poids associé à chaque actif
Dim rend_pf() As Variant
rend_pf = Application.WorksheetFunction.MMult(rend, poids_transpose)
Range(Cells(6, 10), Cells(10, 10)) = rend_pf
Dim volatilite() As Variant
volatilite = Application.WorksheetFunction.StDev(rend_pf)
Cells(12, 10) = ecart
SolverReset
SolverOk SetCell:=volatilite, MaxMinVal:=2, ByChange:=poids
SolverAdd CellRef:=somme_poids, Relation:=2, FormulaText:="1"
SolverOptions Iterations:=30, AssumeNonNeg:=True
SolverSolve UserFinish:=True
End Sub |
C'est donc ce dernier code que je n'arrive pas à faire fonctionner, mon objectif étant de tout coder sur vba sans faire appel à des fonctions sur la feuille excel comme je l'ai fait dans le premier morceau de code ci-dessus.
Voici donc à quoi ressemble ma feuille excel et comme on peut le voir la cellule dans laquelle se trouve la volatilité du portefeuille ne correspond pas à une formule puisque le calcul a été fait via une programmation vba.
Pièce jointe 140617
Merci d'avance pour votre aide.
Cordialement.
