Optimisation de portefeuille financier : les macros

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
- Annexe : les macros Vous êtes ici !
  - Création de la matrice de variances/covariances
  - Optimisation d'un portefeuille
- Et après ?
Le levier
- Cas d'un portefeuille mono-valeur
  - Portefeuilles situés au-delà de la frontière efficiente
- Levier et Optimisation de portefeuille
- Retour sur le risque
  - Le levier : une arme à double tranchant
  - Dissymétrie des gains et des pertes
- Morale de l'histoire

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.

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 = "$56"
Const szCelluleMinimumParLigne = "$55"
Const szCelluleObjectifRendement = "$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...

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