La Bourse pour les nains : les macros de l'optimisation de portefeuille

Sommaire

La frontière efficiente
Cinq valeurs, un univers de portefeuilles
La frontière efficiente
Optimisation
Le principe
Optimisation : la pratique
Activation du solveur
Utilisation du solveur
Le classeur "Optimisation.xls"
Avertissement important
Let's go on...
Feuille "Données"
Feuille "VarCovar"
Feuille "Portefeuille"
Utilisation du classeur
Optimisation d'un portefeuille
Calcul et affichage de la frontiere efficiente
Les macros		<== Vous êtes ici
Création de la matrice de variances/covariances
Optimisation d'un portefeuille
Et après ?

Les macros

Préambule

Mes langages de prédilection étant plutôt le C ou l'assembleur que Visual Basic pour Application, les macros ci-dessous sont probablement perfectibles et ne constituent en aucune manière des modèles de programmation VBA. Si vous avez des remarques à ce sujet, je suis preneur, merci de bien vouloir me laisser un message sur le forum par exemple.

Création de la matrice de variances/covariances

Cette macro génère la table de variances/covariances de la feuille "VarCovar" à partir des cours de la feuille "Données".
Au passage, elle remplit aussi la liste des valeurs de la feuille "Portefeuille", ainsi que leurs volatilités et leurs rendements historiques.

'****************************************************************************
'*                     Macro CreeMatriceVarCovar                            *
'****************************************************************************
'*                                                                          *
'* DESCRIPTION : cree une matrice de variances/covariances a partir des     *
'*               donnees de la feuille 'Données'. Les valeurs dont le       *
'*               nombre de periodes est inférieur a 36 sont affichees sur   *
'*               fond rouge pour signifier leur manque de maturite.         *
'*                                                                          *
'* ENTREE..... : Indirectement les rendements de toutes les periodes de     *
'*               de toutes les valeurs de la feuille 'Données'.             *
'*                                                                          *
'* SORTIE..... : Feuille 'VarCovar' remplie.                                *
'*                                                                          *
'* RETOUR..... : Neant.                                                     *
'*                                                                          *
'****************************************************************************
Sub CreeMatriceVarCovar()

'----- Constantes -----
Const LIGNE_TITRE_DONNEES = 1
Const PREMIERE_COLONNE_SRC = 3
Const PREMIERE_LIGNE_SRC = 4
Const PREMIERE_COLONNE_DEST = 2
Const PREMIERE_LIGNE_DEST = 5
Const NB_DATAS_MINI = 36

Const COLONNE_NOM_VALEURS_PF = 2
Const PREMIERE_LIGNE_DEST_PF = 2
Const COLONNE_VOLATILITE_VALEURS_PF = 5
Const COLONNE_RENDEMENT_VALEURS_PF = 4

'----- Variables -----
iLigSrc = PREMIERE_LIGNE_SRC
iColSrc = PREMIERE_COLONNE_SRC
iLigDest = PREMIERE_LIGNE_DEST
iColDest = PREMIERE_COLONNE_DEST
iNbSeries = 0     ' Nombre de series de donnees
Dim iTabLigDeb(200, 2) As Integer

' ----- On cherche le nombre de series -----
While (Not IsEmpty(Worksheets("Données").Cells(LIGNE_TITRE_DONNEES, iColSrc - 1)))
  iNbSeries = iNbSeries + 1
  iColSrc = iColSrc + 2
Wend
iColSrc = PREMIERE_COLONNE_SRC

' ----- Pour chaque serie de donnees, on va stocker le debut et la fin -----
For i = PREMIERE_COLONNE_SRC To PREMIERE_COLONNE_SRC + (iNbSeries - 1) * 2 Step 2
  iLigSrc = PREMIERE_LIGNE_SRC
  While (IsEmpty(Worksheets("Données").Cells(iLigSrc, i)))
    iLigSrc = iLigSrc + 1
  Wend
  iTabLigDeb((i - PREMIERE_COLONNE_SRC) / 2, 0) = iLigSrc
  While (Not IsEmpty(Worksheets("Données").Cells(iLigSrc, i)))
    iLigSrc = iLigSrc + 1
  Wend
  iTabLigDeb((i - PREMIERE_COLONNE_SRC) / 2, 1) = iLigSrc - 1
Next

' ----- On va generer la matrice de variance/covariance -----
iLigSrc = PREMIERE_LIGNE_SRC
iColSrc = PREMIERE_COLONNE_SRC
For i = 0 To iNbSeries - 1
  iColDest = PREMIERE_COLONNE_DEST + i
  
  ' Nom valeur
  Worksheets("VarCovar").Cells(PREMIERE_LIGNE_DEST, iColDest).Formula = "=Données!" + _
    Worksheets("Données").Cells(iLigSrc - 3, i * 2 + PREMIERE_COLONNE_SRC - 1).Address
  Worksheets("VarCovar").Cells(PREMIERE_LIGNE_DEST + iColDest - PREMIERE_COLONNE_DEST + 1, _
    PREMIERE_COLONNE_DEST - 1).Formula = "=Données!" + _
    Worksheets("Données").Cells(iLigSrc - 3, i * 2 + PREMIERE_COLONNE_SRC - 1).Address
  If (iTabLigDeb(i, 1) - iTabLigDeb(i, 0) < NB_DATAS_MINI) Then
    ' On met le fond en rouge
    Worksheets("VarCovar").Cells(PREMIERE_LIGNE_DEST, iColDest).Interior.Color = RGB(255, 0, 0)
    Worksheets("VarCovar").Cells(PREMIERE_LIGNE_DEST + iColDest - PREMIERE_COLONNE_DEST + 1, PREMIERE_COLONNE_DEST - 1).Interior.Color = RGB(255, 0, 0)
  End If
  
  ' Variance
  Worksheets("VarCovar").Cells(iLigDest + 1, iColDest).Formula = "=VARP(Données!" + _
    Worksheets("Données").Cells(iTabLigDeb(i, 0), i * 2 + PREMIERE_COLONNE_SRC).Address + ":" + _
    Worksheets("Données").Cells(iTabLigDeb(i, 1), i * 2 + PREMIERE_COLONNE_SRC).Address + ")"
  Worksheets("VarCovar").Cells(iLigDest + 1, iColDest).Interior.Color = RGB(255, 255, 128)
  
  If (iTabLigDeb(i, 0) > iTabLigDeb(j, 0)) Then iLigneDeb = iTabLigDeb(i, 0) Else iLigneDeb = iTabLigDeb(j, 0)
  If (iTabLigDeb(i, 1) > iTabLigDeb(j, 1)) Then iLigneFin = iTabLigDeb(i, 1) Else iLigneFin = iTabLigDeb(j, 1)
  Set plage1 = Range(Worksheets("Données").Cells(iLigneDeb, i * 2 + PREMIERE_COLONNE_SRC), Worksheets("Données").Cells(iLigneFin, i * 2 + PREMIERE_COLONNE_SRC))
  
  'Covariances
  For j = i + 1 To iNbSeries - 1
    iColDest = iColDest + 1
    Set plage2 = Range(Worksheets("Données").Cells(iLigneDeb, j * 2 + PREMIERE_COLONNE_SRC), Worksheets("Données").Cells(iLigneFin, j * 2 + PREMIERE_COLONNE_SRC))
    If (iLigneFin - iLigneDeb < NB_DATAS_MINI) Then lColorTexte = RGB(128, 128, 128) Else lColorTexte = RGB(0, 0, 0)

    Worksheets("VarCovar").Cells(iLigDest + 1, iColDest).Value = Application.WorksheetFunction.Covar(plage1, plage2)
    Worksheets("VarCovar").Cells(iLigDest + 1, iColDest).Interior.Color = RGB(255, 255, 255)
    Worksheets("VarCovar").Cells(iLigDest + 1, iColDest).Font.Color = lColorTexte
    
    '-----Seconde partie de la matrice-----
    Worksheets("VarCovar").Cells(PREMIERE_LIGNE_DEST + iColDest - PREMIERE_COLONNE_DEST + 1, _
      PREMIERE_COLONNE_DEST + iLigDest - PREMIERE_LIGNE_DEST).Formula = "=" + Worksheets("VarCovar").Cells(iLigDest + 1, iColDest).Address
    Worksheets("VarCovar").Cells(PREMIERE_LIGNE_DEST + iColDest - PREMIERE_COLONNE_DEST + 1, _
      PREMIERE_COLONNE_DEST + iLigDest - PREMIERE_LIGNE_DEST).Interior.Color = RGB(200, 200, 200)
    Worksheets("VarCovar").Cells(PREMIERE_LIGNE_DEST + iColDest - PREMIERE_COLONNE_DEST + 1, _
      PREMIERE_COLONNE_DEST + iLigDest - PREMIERE_LIGNE_DEST).Font.Color = lColorTexte
  Next j
  iLigDest = iLigDest + 1

  '-----------------------------------------------------------------------------
  ' On va recopier les noms des valeurs dans la seconde colonne du portefeuille
  '-----------------------------------------------------------------------------
  ' ----- Nom valeur -----
  Worksheets("Portefeuille").Cells(PREMIERE_LIGNE_DEST_PF + i, COLONNE_NOM_VALEURS_PF).Formula = "=Données!" + _
    Worksheets("Données").Cells(iLigSrc - 3, i * 2 + PREMIERE_COLONNE_SRC - 1).Address
  If (iTabLigDeb(i, 1) - iTabLigDeb(i, 0) < NB_DATAS_MINI) Then
    ' On met le fond en rouge
    Worksheets("Portefeuille").Cells(PREMIERE_LIGNE_DEST_PF + i, COLONNE_NOM_VALEURS_PF).Interior.Color = RGB(255, 0, 0)
  Else
    Worksheets("Portefeuille").Cells(PREMIERE_LIGNE_DEST_PF + i, COLONNE_NOM_VALEURS_PF).Interior.ColorIndex = xlColorIndexNone
  End If
  
  ' ----- Rendements historiques -----
  Worksheets("Portefeuille").Cells(PREMIERE_LIGNE_DEST_PF + i, COLONNE_RENDEMENT_VALEURS_PF).Formula = "=(Données!" + Worksheets("Données").Cells(iLigneFin + 2, i * 2 + PREMIERE_COLONNE_SRC).Address + ")^12-1"

  ' ----- Volatilité mensuelle -----
  Worksheets("Portefeuille").Cells(PREMIERE_LIGNE_DEST_PF + i, COLONNE_VOLATILITE_VALEURS_PF).Formula = "=StDevP(Données!" + _
    Worksheets("Données").Cells(iLigneDeb, i * 2 + PREMIERE_COLONNE_SRC).Address + ":" + _
    Worksheets("Données").Cells(iLigneFin, i * 2 + PREMIERE_COLONNE_SRC).Address + ")"

Next i

End Sub

Optimisation d'un portefeuille

Le module "Optimisation" contient plusieurs macros :

OptimiseVolPF cherche pour un rendement donné, le portefeuille présentant la variance la plus faible ;
OptimiseRendementPF cherche pour un écart-type donné, le portefeuille présentant le rendement le plus élevé ;
TracerFrontiereEfficiente calcule une série de couples (rendement,ecart-type) pour permettre de tracer la frontiere efficiente.

'****************************************************************************
'*                         Module Optimisation                              *
'****************************************************************************
'*                                                                          *
'* DESCRIPTION........ : Module comprenant les fonctions necessaires à      *
'*                       l'optimisation d'un portefeuille, ainsi qu'au      *
'*                       tracé de la frontière efficiente.                  *
'*                                                                          *
'* AUTEUR............. : "La Bourse pour les nains"                         *
'*                       http://wwww.bnains.org/                            *
'*                                                                          *
'* DATE DE CREATION... : 2000                                               *
'*                                                                          *
'* FONCTIONS EXPORTEES :                                                    *
'*   Neant.                                                                 *
'*                                                                          *
'* MACROS EXPORTEES... :                                                    *
'*   OptimiseVolPF()                                                        *
'*     Cherche la composition de portefeuille permettant d'obtenir la       *
'*     volatilité la plus faible pour un rendement donné.                   *
'*   OptimiseRendementPF()                                                  *
'*     Cherche la composition de portefeuille permettant d'obtenir le       *
'*     rendement le plus élevé pour une volatilité donnée.                  *
'*   TracerFrontiereEfficiente()                                            *
'*     Pour une liste de rendements donnée, cherche les volatilités les     *
'*     plus faibles de manière à produire une liste de couples (rendement,  *
'*     volatilité) permettant de tracer la frontière efficiente d'une liste *
'*     de valeurs.                                                          *
'*                                                                          *
'****************************************************************************
'* MODIFIE LE ../../.... PAR ......................                         *
'* DESCRIPTION DE LA MODIFICATION :                                         *
'*                                                                          *
'****************************************************************************
Const iNbTitres = 48
Const szCelluleMaximumParLigne = "$E$56"
Const szCelluleMinimumParLigne = "$E$55"
Const szCelluleObjectifRendement = "$E$52"
Const szCelluleRendementCalcule = "C51"
Const szCelluleSommePoidsValeurs = "C50"
Const szCelluleVariance = "C52"
Const szCelluleObjectifEcartType = "E53"
Const szCelluleEcartTypeAnnuelCalcule = "C54"



'****************************************************************************
'*                            Macro OptimiseVolPF                           *
'****************************************************************************
'*                                                                          *
'* DESCRIPTION : cherche les proportions de valeur permettant d'obtenir la  *
'*               variance la plus petite pour le rendement donné.           *
'*                                                                          *
'* ENTREE..... : indirectement, liste des valeurs, minimum et maximum de    *
'*               proportion dans le portefeuille et rendement souhaite.     *
'*                                                                          *
'* SORTIE..... : indirectement, la variance calculée.                       *
'*                                                                          *
'* RETOUR..... : Neant.                                                     *
'*                                                                          *
'****************************************************************************
Public Sub OptimiseVolPF()

'************************************************************************
'* On programme le solveur pour minimiser la variance en jouant sur les *
'* proportions des valeurs                                              *
'************************************************************************

' ----- Préparation de l'environnement de travail -----
' On se positionne dans la bonne feuille
Worksheets("Portefeuille").Activate
' Et dans la bonne cellule (pour éviter un bug du solver avec certaines versions d'Excel)
Range(szCelluleVariance).Select
' Reset du solveur
SolverReset
' On met toutes les proportions de valeur à 0
For iValeur = 2 To iNbTitres + 1
  Cells(iValeur, 3).Value = 0
Next iValeur
' Objectif : Minimiser la variance en faisant varier les proportions des valeurs
SolverOk szCelluleVariance, maxMinVal:=2, byChange:=Range("C2:C49")

' ----- On cree les contraintes -----
'Somme des poids des valeurs = 1 (soit 100%)
SolverAdd cellRef:=szCelluleSommePoidsValeurs, relation:=2, formulaText:=1
' Valeurs interdites (toutes les valeurs non cochées)
For iValeur = 1 To iNbTitres
  If Cells(iValeur, 1).Value = "N" Then SolverAdd Cells(iValeur, 3), relation:=2, formulaText:=0
Next iValeur
'Poids de chaque valeur <= maximum par ligne
SolverAdd cellRef:="C2:C49", relation:=1, formulaText:=szCelluleMaximumParLigne
'Poids de chaque valeur >= minimum par ligne
SolverAdd cellRef:="C2:C49", relation:=3, formulaText:=szCelluleMinimumParLigne
'Rendement attendu = celui demandé
SolverAdd cellRef:=szCelluleRendementCalcule, relation:=2, formulaText:=szCelluleObjectifRendement

' ----- On indique maintenant au solveur qu'il doit bosser... -----
SolverSolve (True)

SolverFinish

End Sub



'****************************************************************************
'*                         Macro OptimiseRendementPF                        *
'****************************************************************************
'*                                                                          *
'* DESCRIPTION : cherche les proportions de valeur permettant d'obtenir le  *
'*               rendement le plus élevé pour la variance donnée.           *
'*                                                                          *
'* ENTREE..... : indirectement, liste des valeurs, minimum et maximum de    *
'*               proportion dans le portefeuille et variance souhaitée.     *
'*                                                                          *
'* SORTIE..... : indirectement, la rendement calculé.                       *
'*                                                                          *
'* RETOUR..... : Neant.                                                     *
'*                                                                          *
'****************************************************************************
Public Sub OptimiseRendementPF()

' ----- Préparation de l'environnement de travail -----
' On se positionne dans la bonne feuille
Worksheets("Portefeuille").Activate
' Et dans la bonne cellule (pour éviter un bug du solver avec certaines versions d'Excel)
Range(szCelluleVariance).Select
' Reset du solveur
SolverReset
' On met toutes les proportions de valeur à 0
For iValeur = 1 To iNbTitres
  Cells(iValeur, 3).Value = 0
Next iValeur
' Objectif : Maximiser le rendement en faisant varier les proportions des valeurs
SolverOk szCelluleRendementCalcule, maxMinVal:=1, byChange:=Range("C2:C49")

' ----- On cree les contraintes -----
'Somme des poids des valeurs = 1 (soit 100%)
SolverAdd cellRef:=szCelluleSommePoidsValeurs, relation:=2, formulaText:=1

' Valeurs interdites (toutes les valeurs non cochées)
For iValeur = 2 To iNbTitres + 1
  If Cells(iValeur, 1).Value = "N" Then SolvAdd Cells(iValeur, 3), relation:=2, formulaText:=0
Next iValeur
'Poids de chaque valeur <= maximum par ligne
SolverAdd cellRef:="C2:C49", relation:=1, formulaText:=szCelluleMaximumParLigne
'Poids de chaque valeur >= minimum par ligne
SolverAdd cellRef:="C2:C49", relation:=3, formulaText:=szCelluleMinimumParLigne
'Volatilité attendue = celle demandée
SolverAdd cellRef:=szCelluleEcartTypeAnnuelCalcule, relation:=2, formulaText:=szCelluleObjectifEcartType

' ----- On indique maintenant au solveur qu'il doit bosser... -----
SolverSolve (True)

SolverFinish

End Sub



'****************************************************************************
'*                       Macro TracerFrontiereEfficiente                    *
'****************************************************************************
'*                                                                          *
'* DESCRIPTION : cherche la variance la plus petite pour une liste de       *
'*               rendements donnés.                                         *
'*                                                                          *
'* ENTREE..... : indirectement, liste des valeurs, minimum et maximum de    *
'*               proportion dans le portefeuille et liste des rendements    *
'*               souhaités.                                                 *
'*                                                                          *
'* SORTIE..... : indirectement, les couples (rendement,variance) calculés.  *
'*                                                                          *
'* RETOUR..... : Neant.                                                     *
'*                                                                          *
'****************************************************************************
Public Sub TracerFrontiereEfficiente()

  Const COLONNE_RENDEMENTS_SOUHAITES = 13
 
  '****************************************************************
  '* Maintenant, on va itérer pour tracer la frontière efficiente *
  '****************************************************************
  bMax = False
  i = 2
  j = COLONNE_RENDEMENTS_SOUHAITES
  Do While (Not (IsEmpty(Cells(i, j))) And bMax = False)
    ' On fixe le rendement souhaite
    Cells(52, 5).Value = Cells(i, j)
    ' On optimise la variance en consequence
    Call OptimiseVolPF
    ' On recupere le rendement
    Cells(i, j + 1).Value = Cells(51, 3)
    ' On recupere la variance annuelle
    Cells(i, j + 2).Value = Cells(54, 3)
    i = i + 1
    ' Si on n'est pas parvenu a ameliorer, alors on arrete
    If i > 3 And Cells(i - 2, j + 1).Value = Cells(i - 1, j + 1).Value Then bMax = True
  Loop
End Sub

Remarques :
La constante "iNbTitres" pourait avantageusement être remplacée par une petite fonction comptant les titres.

Voilà, c'est tout. Tout ceci peut bien sûr être amélioré. A vous de jouer...

Vous avez des questions ? Des remarques ? Des félicitations ou des reproches ? Laissez un message dans un des forums mis à votre disposition. Tout le monde pourra ainsi en profiter.

Début de la page Sommaire du risque Sommaire de l'optimisation Sommaire du site

Votez pour ce site au Weborama

Optimisation de portefeuille

Les macros

Menu

Courtiers et brokers

Livrets

Articles

Bibliographie

Données

Et encore...

Partagez ce site

Sommaire

Les macros

Préambule

Création de la matrice de variances/covariances

Optimisation d'un portefeuille