Discussion :

[bol45]

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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.



Merci d'avance pour votre aide.

Cordialement.

[clementmarcotte]

Bonjour,

Apparemment, tu peux télécharger un exemple à partir de cette page.

http://www.solver.com/optimization-s...ement-examples


Sauf qu'il faut que tu fournisses des informations dans la colonne de droite. Mais comme ce sont des Américains, il ne réaliseront probablement pas que tu te moques d'eux si tu leur dit que tu t'appelles Cestpas Devosoignons et que tu résides à Allezaudiable.

[bol45]

Bonjour,

Merci pour ta réponse, j'ai suivi ton conseil mais malheureusement cela ne répond pas à mes questions dans la mesure où le solver qu'ils utilisent fait directement référence à des cellules.