Rapport
publicité
Dr. Emmanuel Fragnière Assistant : Giuseppe Catenazzo Juewen FU Dick Vargas Professeur : Dr. Emmanuel Fragnière Application Financière VBA pour Excel Sujet : Appel automatique de la routine SOLVER de EXCEL pour optimiser des portefeuilles simples (jusqu’à 5 actions). Production automatique des courbes d’efficience. Juewen FU – Dick Vargas 2/20 Professeur : Dr. Emmanuel Fragnière AVANT-PROPOS Ce rapport est un court ouvrage d’initialisation à Visual Basic pour Applications (VBA) dont l’objectif a été d’apprendre comment exploiter le langage VBA pour Excel et plus précisément sur un thème du Risk Management en finances, à savoir, l’optimisation d’un portefeuille. La grande utilité de ce travail a été aussi de nous permettre de pouvoir exploiter la puissance du tableur de façon très significative. Mais, pour cela nous avons dû apprendre quelques bases de programmation. Juewen FU – Dick Vargas 3/20 Professeur : Dr. Emmanuel Fragnière TABLE DES MATIERE 1. VBA ........................................................................... 5 1.1. Définition du VBA ........................................................................... 5 1.2. Avantages de VBA .......................................................................... 5 1.3. Désavantages................................................................................. 5 2. OPTIMISATION DE PORTEFEUILLE ............................ 6 2.1. Modèle de Markowitz ..................................................................... 6 2.2. Critère ............................................................................................ 6 2.3. Efficience ....................................................................................... 6 2.4. CAPM (Capital Asset Pricing Model)....................................... 8 2.4.1. 2.4.2. 2.4.3. 2.4.4. 2.4.5. 2.4.6. 2.4.7. Relation Rendement / Risque :.................................................................. 8 Utilisations : ..................................................... Erreur ! Signet non défini. Bêta Sharpe : ......................................................................................... 9 Critiques du CAPM ................................................................................... 9 Ratio de Sharpe ................................................ Erreur ! Signet non défini. Optimisation de portefeuille sur VBA ........................................................ 10 Calcul du rendement.............................................................................. 13 3. CODAGE DU SOLVEUR SUR VBA ................................13 3.1. Codage du VBA ............................................................................. 13 3.2. Automatisation du solveur ........................................................... 14 3.3. Codage de l’UserForm sur VBA ..................................................... 15 3.3.1. Fenêtre d’accueil ................................................................................... 15 4. CONCLUSION............................................................16 5. PIÈGES À ÉVITER......................................................17 6. ANNEXES ..................................................................18 6.1. Annexe 1. Installation & Fonctionnement VBA ............................. 18 6.2. Annexe 2 Interface de l’Optimisateur........................................... 19 7. RÉFÉRENCES BIBLIOGRAPHIQUES ...........................20 Juewen FU – Dick Vargas 4/20 Professeur : Dr. Emmanuel Fragnière 1. VBA 1.1. DÉFINITION DU VBA Le Visual Basic pour Application est un langage qui a été conçu par Microsoft et il permet de développer des programmes capables de contrôler Excel. Comme nous le savons tous Excel sert à exécuter d’innombrables tâches telle que : • Gérer un budget d’une PME • Analyser des données statistiques • Créer des graphiques à partir d’ensembles de données… • Mais aussi, optimiser un portefeuille composé de N titres Les domaines d’utilisation sont vastes et celui qui nous intéresse est la finance. Cependant, tous les utilisateurs ont en commun le besoin d’automatiser certaines fonctions d’Excel Pour exécuter une tâche sur VBA, soit on écrit du langage, soit on se met en mode enregistrement et pour visionner le code il suffit d’éditer les modules VBA avec Visual Basic Editor (VBE). Il convient de souligner qu’un module est fait de procédures « Sub » qui exécute une action sur des objets. Mais un module peut comporter des procédures « Fonction » qui ne retourne qu’une seule valeur. 1.2. AVANTAGES DE VBA Il exécute les tâches automatisées beaucoup plus rapidement que si l’on ferait manuellement. Les macros sont toujours exécutées avec précision et peuvent être exécutées par quelqu’un qui, enfin de compte, ne connaît rien à VBA et même à Excel. Les tâches sont toujours exécutées de la même manière. 1.3. DÉSAVANTAGES Mis à part le fait qu’il faut apprendre la programmation et posséder Excel, VBA ne possède pas beaucoup de défauts. Cependant, selon la littérature informatique, parfois les choses tournent mal. « On n’a jamais la certitude que notre programme VBA fonctionnera dans tous les cas de figure ». Et nous ne pouvons que nous rallier à cette critique puisque nous même avons rencontré quelques problèmes dont nous en parlerons plus tard. Pour mieux comprendre le sujet de notre projet, il convient, d’aider le lecteur à comprendre ce que signifie une optimisation de portefeuille à travers une simple théorie développée par quelques théoriciens éminents du monde de la finance. Juewen FU – Dick Vargas 5/20 Professeur : Dr. Emmanuel Fragnière 2. OPTIMISATION DE PORTEFEUILLE 2.1. MODÈLE DE MARKOWITZ Il vise à la constitution rationnelle d’un portefeuille arbitrant entre les gains et les pertes. Il s’agit d’une répartition de portefeuille par une méthode mathématique assurant soit : - Le meilleur rendement - Le plus petit risque à rendement donné 2.2. CRITÈRE Entre deux investissements possédant le même rendement espéré, l’investisseur préfèrera celui qui est le moins risqué. Entre deux investissements de même risque, l’investisseur préfèrera celui dont le rendement espéré est le plus grand. Mais si le rendement espéré de X est supérieur ou égal à celui de Y et que le risque de X est strictement inférieur à celui de Y, on préfèrera X. Son étude est théorique car elle prend l’hypothèse que l’on connaît le couple espérance de gain (obtenue par les rendements historiques du titre ?) et risque de chaque titre (mesurée par l’écart type des données du cours du titre qui interprète explicitement sa volatilité), information qui ne se trouve pas telle quelle dans la presse économique, et qui fait une large part à l’appréciation du gestionnaire. A noter que le risque et le rendement espéré sont mesurés par la variance 1 de la variable aléatoire. 2.3. EFFICIENCE Un investissement est dit efficient s’il n’est pas dominé par un autre. La 1ère variance est < que la 2ème variance. Ce constat n’est pas significatif car tout d’un coup il y a eu une montée de la valeur du titre en 1 jour, 1h. La 1ère variance est < que la 3ème variance. Pourtant la 2ème variance et la 3ème sont les mêmes alors que ce n’est pas le même risque. Pour contrer ce problème il existe quelques solutions à cette problématique. Solution : si on calcule la semi variance, on n’aurait pas la même constatation. 1 La variance d’un titre indique le profit ou perte qu’un investisseur peut réaliser Juewen FU – Dick Vargas 6/20 Professeur : Dr. Emmanuel Fragnière Pour renforcer cette idée d’optimisation de portefeuille, la TMP 2 expose comment des investisseurs rationnels utilisent la diversification 3 (élément absent dans la théorie de Markowitz) afin d’optimiser leur portefeuille et quel devrait être le prix d'un actif étant donné son risque par rapport au risque moyen du marché. Cette théorie fait appel aux concepts de frontière efficiente 4 , modèle d'évaluation des actifs financiers, bêta, droite de marché des capitaux et droite de marché des titres. Dans ce modèle, le rendement d'un actif est une variable aléatoire et un portefeuille est une combinaison linéaire pondérée d'actifs qui ont une même variance et leur corrélation est identique. Par conséquent, le rendement d'un portefeuille est également une variable aléatoire et possède une espérance et une variance. Le risque Systématique est lié au marché ; c’est le risque qu’on ne peut pas réduire. Risk Spécifique Par ex, j’achète une action du SMI, quelque soit le CAN ou les perspectives du titre, l’action sera assujettie au Risk Systématique SMI. Si après une forte progression des valeurs, les actionnaires décident de prendre leurs bénéfices, l’ensemble des valeurs sera touché (Ex. Crash boursier de Shanghai). En bref, il est lié au risque de faillite de l’entreprise. Le RS disparaît si on possède beaucoup de titres dans le portefeuille. Le risque spécifique (ou intrinsèque) n’est pas lié à l’évolution du marché. Par ex, les médicaments de Novartis ne sont pas approuvés. Si l’entreprise n’atteint pas ses objectifs de croissance qu’elle s’était fixée, le cours de l’action chutera brusquement. Cette chute peut se ressentir sur les autres valeurs du secteur si l’entreprise est suffisamment importante. Le risque spécifique peut être réduit si on achète les actions d’un indice ou d’un secteur. Seulement le risque systématique est rémunéré Le risque systématique est souvent plus élevé que le risque spécifique. D’où l’importance du choix du marché au profit des valeurs. Mauvaise gestion, incendie, procès, etc. Moins il y a de corrélation (ex : -1) entre titres d’un portefeuille, moins il y a de risque (0%). Plus la corrélation augmente entre titres, moins le portefeuille est diversifié, donc on profite peu de la diversification. Lorsque la corrélation est = 1 entre deux titres d’un portefeuille : si un titre gagne l’autre perd et vice versa. Trouver le portefeuille de variance minimale parmi ceux qui ont la rentabilité espérée fixée 2 3 4 Théorie Moderne de Portefeuille Il s’agit de répartir son investissement sur plusieurs titres dans le but d’en réduire le risque Lieu des investissements efficients dans le plan de Markowitz où le rendement est optimal Juewen FU – Dick Vargas 7/20 Professeur : Dr. Emmanuel Fragnière 2.4. CAPM (CAPITAL ASSET PRICING MODEL) Ce modèle, développé par William Sharpe (1960), Litner, Mossin, propose de quantifier la relation risqué / rendement Hypothèses : - Le temps est concentré entre deux instants. Les mêmes actifs financiers sont à la disposition de tous les investisseurs et il y a un actif sans risque. - Tous les investisseurs utilisent le modèle de Markowitz pour constituer leurs portefeuilles. - Tous les investisseurs ont le même horizon de temps et les mêmes anticipations. - Les investisseurs ne peuvent pas influencer individuellement les prix (concurrence parfaite). - Le marché est sans friction (pas de coût de transaction ou d’information, pas de taxe, les ventes à découvert sont autorisées, les titres sont parfaitement divisibles). Si le CAPM est vrai le risque systémique = 0 2.4.1. Relation Rendement / Risque : La rentabilité espérée E(R) = somme du taux d’intérêt sans risque + bêta de l’actif * excès de rentabilité du marché CAPM: Sur, Sous-évaluation E(R) Le titre avec un E(R) =16% est surévalué par rapport à celui du CAMP = 7%. Le marché lui accorde beaucoup trop d’importance, par conséquent, il est trop cher. 16% Droite théorique 8% Le titre est sous évalué avec un E(R) = 6% alors que le CAPM indique 8% (sa vraie valeur théorique). Titre typique recherché par les analystes. 7% 6% Risque (ß) La relation entre rentabilité espérée et risque est linéaire. Selon le CAPM seul le risque systématique est rémunéré Juewen FU – Dick Vargas 8/20 Professeur : Dr. Emmanuel Fragnière 2.4.2. Utilisation - Estimation du coût du capital - Mesure du risque (β), de la performance (alpha de Jensen et ration de Treynor) - Détection de titres sous-évalués (stock picking) 2.4.3. Bêta Sharpe : Le β est le risque général d’investissement sur un grand marché, comme le NYSE dont le β = 1 par définition. Chaque entreprise a un β, si celui-ci = 3, alors il est 3 x plus risqué que le risque général du marché. Le β indique la volatilité de la valeur boursière, relativement à la classe de la valeur d’actif. En bref, il mesure la sensibilité d'un fonds aux variations du marché dans son ensemble. Un bêta de 1,1 indique un fonds dont le niveau de performance est supérieur ou inférieur de 10% à son indice de référence (1.1 : 1.0). Si le bêta d'une action est de 0.8, son cours a varié en moyenne dans la période précédente de 0,8 % quand le marché variait de 1 %. S'il est de 1.5, il a varié de 1.5%. Lorsqu’on anticipe une baisse du SMI, au lieu de vendre toutes nos positions (actions suisses) soit on achète des futures sur le SMI, soit on achète des puts et le β nous indique combien nous devons en acheter. Le β d’un portefeuille est la somme pondérée des β des constituants du portefeuille. On prend généralement portefeuille de marché Le bêta se calcule par régression linéaire des rendements historiques du titre sur ceux de l’indice boursier choisi Mais quelle longueur et quelle fréquence choisir pour les données historiques ? 2.4.4. un indice boursier représentatif comme Critiques du CAPM Tests empiriques controversés. Il y a des anomalies inexpliquées (effet janvier, rendement de petites capitalisations ≠ rendements de grandes capitalisations) - Le β des options est très élevé car leurs prix sont très petits (1 ; 2 cts.), s’il y a une Δ de 1 ou 2 cts, la Δ est de 100%. Plus le β est élevé, plus on peut attendre un rendement élevé. Le β du marché est = 1 car c’est la covariance du marché justement. Le β est négatif : si le marché monte et le portefeuille plonge Æ Cas ABB et SMI Si le β d’un portefeuille = 0 et que le marché Δ de 10% Æ le portefeuille ne varie pas, mais ça ne veut pas dire que le Juewen FU – Dick Vargas 9/20 Professeur : Dr. Emmanuel Fragnière portefeuille n’a pas de risque, le seul risque que le portefeuille possède est le risque spécifique. 2.4.5. Ratio de Sharpe Mesure l’excès de rentabilité (relativement au taux sans risque) par unité de risque Plus ce ratio est élevé, meilleure est la performance Limites du ratio de Sharpe - Dépend de l’indice de marché et du taux sans risque utilisés Difficile à interpréter lorsqu’il est négatif … Ne détecte ni le «stock picking 5 », ni le «market timing» 2.4.6. Optimisation de portefeuille sur VBA Pour mettre en place et développer notre petit logiciel sur Excel, nous avons du procéder par étapes : Tout d’abord nous avons dû choisir quels titres allaient composer notre portefeuille. Cette démarche a été plutôt subjective puisqu’on a introduit essentiellement des valeurs vedettes du SMI et auxquelles nous portons une certaine préférence. (Image n°1) La deuxième étape se caractérise par la recherche de données. Nous avons téléchargées toutes les données historiques des titres choisis. Les données journalières brutes étant trop nombreuses, il a fallu procéder, premièrement, à un pré filtrage. C’est-à-dire, nous avons pris les cours mensuels (clôture de chaque mois) à l’aide de « RECHERCHEV ». Dans notre exemple, nous avons pris 108 clôtures mensuelles qui vont du 31.12.1197 au 29.12.2006 (Image n°2) 5 Le stock picking, ou « sélection de valeurs », est à la fois un style et une philosophie d’investissement. Le stock picking repose sur la conviction que ce sont les titres et non l’allocation d’actif qui apportent le plus de valeur ajoutée dans un portefeuille. Donc il faut chercher des titres sous-évalués à fort potentiel de croissance. Juewen FU – Dick Vargas 10/20 Professeur : Dr. Emmanuel Fragnière Finalement, nous avons éliminés tous les cours de clôture tombant sur le weekend avec la fonction « JOURSEM ». L’image 2 montre l’étape finalisée de ce processus. Cette démarche permet d’éviter d’avoir une basse de donnée tronquée. Pour procéder au calcul du rendement mensuel nous avons calculé de la façon suivante : 7906.5 − 7908.3 = −0.0002 7908.3 (Image n°3) Le rendement moyen annualisé, qui se calcule sur la moyenne de l’ensemble des rendements mensuels du titre en question, est le suivant : (Image n°4) Dans la feuille de calcul Excel « VarCovar », nous avons crée une matrice qui comporte la covariance de tous nos titres pour pouvoir calculer la variance du portefeuille. 6 =COVARIANCE (RENDEMENT_MENSUEL_SMI ; RENDEMENT_MENSUEL_DJIA)1 (Image n°5) Remarque : la phase de construction d’une matrice est très importante et délicate à la fois pour pouvoir créer tous les autres indicateurs tel que le rendement, la volatilité, le bêta, la covariance, etc. Si la matrice est mal conçue, les valeurs des autres indicateurs seront faussées automatiquement. L’image 8 6 7 $E$18:$K$18 = Rendement Mensuel Moyen $E$15:$K$15 = Allocation de chaque titre (%) par rapport au portefeuille Juewen FU – Dick Vargas 11/20 Professeur : Dr. Emmanuel Fragnière illustre de manière globale le tableau que l’on a construit, mettant en place plusieurs indicateurs mentionnés ci-dessus. (Image n°8) Ici, nous avons mis en place des indicateurs tels que le rendement attendu du portefeuille. Le rendement est calculé en fonction des proportions des valeurs et leurs rendements attendus individuels. La cellule « Portfolio Variance » indique tout simplement la variance attendu du portefeuille tandis que la cellule « Portfolio Volatility » n’est tout simplement que son écart-type. 7 =SOMMEPROD($E$18:$K$18;$E$15:$K$15 ) 6 7 =SOMMEPROD(PRODUITMAT(E15:K15 ;VarCovar!C6:I12);E15:K15 ) =RACINE(0.000831) =VAR.P(RENDEMENT_MENSUEL_SMI) =∑ DES β DE CHAQUE TITRE PONDERE PAR SON POIDS (Image n°6) (Image n°7) Nous avons fait migrer les rendements moyens des titres de la feuille Data1 vers la feuille portefeuille, comme le montre l’image 7 pour que l’utilisateur ait un meilleur aperçu de l’ensemble. La pondération de chaque titre a été introduite dans les contraintes du solveur (voir image n°8) ainsi que d’autres critères pour lancer le solveur. Tout d’abord on a ciblé la cellule C22 qui contient la variance du portefeuille, les cellules variables E15 :K15, correspondant à la pondération de chaque titre et finalement les 13 contraintes indispensables pour optimiser le rendement du portefeuille. (Image n°8) Juewen FU – Dick Vargas 12/20 Professeur : Dr. Emmanuel Fragnière 2.4.7. Calcul du rendement (L’objectif de notre petit logiciel est qu’il nous trouve la variance minimale pour un rendement optimale) A présent que nous avons mis en place tous les outils nécessaires à la bonne démarche de notre optimisateur, nous pouvons lancer le solveur automatiquement en appuyant, seulement, sur une touche. Au préalable, nous avons enregistrée une macro à partir de l’option Outils qui se trouve dans la barre menu d’Excel. (Image n°9) (Image n°10) Et pour finaliser, nous avons nommée cette macro « Solveur » (image n°10). 3. CODAGE DU SOLVEUR SUR VBA 3.1. CODAGE DU VBA Sub Solveur() ' variable permettant de récupérer le résultat Dim Cancel As Boolean Ici, nous avons introduit une alerte qui demande à la personne si elle veut continuer à utiliser le solveur ou si elle désire annuler l’exécution. If MsgBox("You have launched the solver, would you like to continue ?", vbOKCancel) = vbCancel Then Cancel = True Exit Sub Si la personne désire continuer la tâche alors le solveur se met en route. Else SolverOk SetCell:="$C$22", ByChange:="$E$15:$K$15""" MaxMinVal:=2, ValueOf:="0", SolverAdd CellRef:="$E$15:$K$15""", Relation:=3, FormulaText:="0" Juewen FU – Dick Vargas 13/20 Professeur : Dr. Emmanuel Fragnière SolverAdd CellRef:="$L$15", Relation:=2, FormulaText:="1""" SolverAdd CellRef:="$C$21", Relation:=3, FormulaText:="$F$21""" SolverAdd CellRef:="$E$15", Relation:=1, FormulaText:="0.15""" SolverAdd CellRef:="$F$15", Relation:=1, FormulaText:="0.15""" SolverAdd CellRef:="$G$15", Relation:=1, FormulaText:="0.15""" SolverAdd CellRef:="$H$15", Relation:=1, FormulaText:="0.35""" SolverAdd CellRef:="$I$15", Relation:=1, FormulaText:="0.35""" SolverAdd CellRef:="$I$15", Relation:=1, FormulaText:="0.05""" SolverAdd CellRef:="$J$15", Relation:=1, FormulaText:="0.15""" SolverAdd CellRef:="$K$15", Relation:=1, FormulaText:="0.15""" SolverAdd CellRef:="$J$15", Relation:=3, FormulaText:="0.05""" SolverAdd CellRef:="$H$15", Relation:=3, FormulaText:="0.05""" SolverAdd CellRef:="$I$15", Relation:=3, FormulaText:="0.05""" SolverSolve Module1.TracerFrontiereEfficiente End If End Sub Ceci montre ce que VBA retranscrit en codage dans la fenêtre « Code » lorsqu’on enregistre une macro pour le solveur. 3.2. AUTOMATISATION DU SOLVEUR Après avoir stoppé notre enregistrement nous avons crée un bouton commande depuis la Boîte à outils Contrôles (image 11&12) qu’on l’a appelé « Launch Solver ». Pour ce faire, soit on affiche le VBE, démarche un peu plus longue, soit on prend le raccourci, qui est surtout plus simple à effectuer : on active Visual Basic dans la barre à outils qui se trouve dans Affichage, ensuite on fait un right click sur le bouton de commande, on click sur Propriétés et puis on va sur « Caption » et on remplace Commande Bouton par Launch solver. (Image n°11) (Image n°12) (Image n°13) Finalement, l’image 13 nous illustre comment nous avons affecté la Macro Solveur au bouton de commande. Juewen FU – Dick Vargas 14/20 Professeur : Dr. Emmanuel Fragnière A remarquer que nous avons réalisé cette même démarche pour tous les Boutons Commande tel que Print Screen et Top Performance qui se trouvent sur notre Classeur TP-VBA. 3.3. 3.3.1. CODAGE DE L’USERFORM SUR VBA Fenêtre d’accueil Nous avons crée une fenêtre d’accueil qui s’affiche automatiquement lorsqu’on click sur le fichier Gd-TP-VBA. Tout d’abord on a inséré un UserForm dans Feuille de notre VBAProject (Image n°14) (Image n°15) (Image n°16) Ensuite, on a créé une boîte « Cadre » qu’on a inséré dans notre UserForm1 grâce à la Boîte à outils. Puis on a chargé une image déjà travaillée à partir de MouseIcon qui se trouve dans Propriétés de notre UserForm. Les diverses mises en pages ont été réalisées aussi à partir de la fenêtre Propriétés. Finalement, pour qu’Excel lance la fenêtre au démarrage, on a introduit un petit codage de 4 lignes pour charger la page d’accueil. Private Sub Workbook_Open() Load Information Information.Show End Sub (Image n°17) Pour fermer la fenêtre d’accueil on a dû introduire le code suivant : Private Sub CommandButton1_Click() Unload Information End Sub Private Sub Image1_Click() End Sub En ce qui concerne la mise en place de la toupie, pour rendre plus agréable l’utilisation de l’optimisateur, s’est avéré un peu délicat. Lorsqu’on voulait un Juewen FU – Dick Vargas 15/20 Professeur : Dr. Emmanuel Fragnière rendement de 10% (10.00), en fait Excel traduisait ce chiffre en 1000%. Pour réparer ce défaut nous avons dû effectuer quelques petits changements dans la fenêtre code Private Sub SpinButton1_SpinDown() Feuil5.Cells(21, 6) = Feuil5.Cells (21, 6) - 0.01 End Sub Private Sub SpinButton1_SpinUp() Feuil5.Cells(21, 6) = Feuil5.Cells(21, 6) + 0.01 End Sub Nous pourrions nous attarder longuement sur le codage des divers modules et feuilles, mais, malheureusement, nous arrivons à la limite du plafond en termes de quantité de travail. 4. CONCLUSION Malgré la grande disponibilité et le nombre d’heures requises pour la réalisation de ce travail, VBA nous a réellement permis de développer nos connaissances sur Excel qui, en fin de compte, s’avère un outil très puissant dont les dimensions semblent très vastes. L’apprentissage du codage sur VBA n’a pas été très simple puisque nous n’avions jamais appris à coder. Aux longs préliminaires de notre projet, la tâche semblait donc quelque peu titanesque, mais après un peu d’entraînement la réalisation du travail commençait à prendre forme. Par conséquent, il ne faut pas être un informaticien chevronné pour développer un programme sur VBA, qui peut s’avérer très outil à l’exécution de tâches journalières de façon très simple et automatique. Ainsi, la conception d’un outil de gestion nous a permis non seulement d’appliquer notre savoir faire en finances mais aussi d’assimiler des connaissances solides dans le domaine de l’informatique pour pouvoir exploiter la puissance du tableur de manière considérable. Finalement, l’aboutissement de ce travail a été la grande récompense de notre investissement et notre volonté de voir réussir et mener notre projet à bien. Juewen FU – Dick Vargas 16/20 Professeur : Dr. Emmanuel Fragnière 5. PIÈGES À ÉVITER Certaines versions francisées du fichier SOLVER.XLA posent des problèmes à l'exécution des macros faisant appel au solveur. Si lors de l'exécution des macros d'optimisation sous Office XP vous obtenez un message d'erreur du style "Erreur de compilation: Sub ou Fonction non définie", il faut activer ce fichier. Pour réaliser cette tâche il faut parcourir le chemin suivant : D:\Program Files\Microsoft Office\OFFICE11\Bibliothèque\SOLVER Après, il faut double cliquer sur le fichier « SOLVER.XLA » Après avoir enregistré une macro qui exécute automatiquement le solveur, il se peut que certaines contraintes soient doublées dans la case qui est prévu à cet effet : Comme le montre l’image ci-dessus, la contrainte $E$15 :$K$15 a été copié deux fois et cela continue autant de fois que vous lancez le solveur. La solution est d’aller sur VBE et aller chercher la ligne qui correspond à chaque doublon(SolverAdd CellRef:="$E$15:$K$15", Relation:=3, FormulaText:="0") Ensuite il faut tripler les apostrophes qui concernent la cellule ou groupe de cellule (SolverAdd CellRef:="$E$15:$K$15""", Relation:=3, FormulaText:="0") De cette façon, Excel n’affiche plus qu’une contrainte dans cette case. Lorsque vous voulez exécuter le solveur et que celui-ci n’est pas disponible sur votre classeur Excel, il suffit de l’activer. Pour cela il faut aller dans la barre menu, Outil/Macro Complémentaire et cocher la case Complément Solveur. Finalement, l’un des derniers obstacles que nous avons rencontré a été lorsque tout semblait marcher de façon plutôt correcte. Quelques fois, il se peut qu’Excel refuse d’exécuter le calcul du rendement lorsqu’on lance le solveur. La machine nous avertit d’un manque probable de mémoire. La solution « artisanale » a été de lancer une fois le solveur manuellement : Menu – Outils – Solveur – Résoudre. Une fois que cette tâche a été réalisée, on peut utiliser l’optimisateur normalement. A ce jour, nous n’avons pas encore percé le mystère de ce bogue. Juewen FU – Dick Vargas 17/20 Professeur : Dr. Emmanuel Fragnière 6. ANNEXES 6.1. ANNEXE 1. INSTALLATION & FONCTIONNEMENT VBA Notre classeur, nommé « Gd-TP-VBA», est composé de trois feuilles. On trouvera ci-dessous un descriptif sommaire de chacune d'entre elles. Notice importante : les données de rendement et de variance des variations de rendement (ie risque) utilisées dans ce classeur sont des données historiques allant du 31.12.1997 au 29.12.2006. Il faut garder à l'esprit qu'aussi bien les rendements que les variances de leurs variations ne sont pas constantes dans le temps. Leurs pouvoirs prédictifs sont donc théoriques. L'optimisation de portefeuille est un moyen, un outil pour mieux encadrer le risque. La feuille Data1 contient comme son nom l'indique les données nécessaires aux calculs, c'est à dire les cours des valeurs, ainsi que les rendements de chaque période. Notre exemple comprend deux grands indices mondiaux, une valeur du CAC40 ainsi que 4 valeurs du SMI, avec leurs cours et rendement mensuels respectif. (la première colonne, comprenant les cours et la seconde le rentabilité sur la période écoulée). La feuille VarCovar est destinée à recevoir la matrice de variance/covariance des valeurs dont les données sont contenues dans la feuille précédente. Enfin, la feuille Portefeuille permet d'optimiser un portefeuille constitué des valeurs retenues, c'est à dire de trouver le portefeuille présentant le meilleur rendement pour une volatilité donnée, ou l'inverse. Cette feuille permet aussi de tracer la frontière efficiente composée par l'ensemble des portefeuilles présentant les meilleurs couples rendements/risque. Accessoirement, elle vous permet aussi de voir où se situe dans l'espace (risque, rendement) un portefeuille donné. Le principe de fonctionnement. Suivre les étapes suivantes : Pour minimiser la variance en fonction d'un rendement donné : Dans la cellule F21, saisissez le rendement souhaité pour votre portefeuille à l’aide de la toupie. Cliquez ensuite sur le bouton 'Launch Solver'. L'optimiseur calculera alors le portefeuille présentant la volatilité la plus faible pour le rendement souhaité. Accessoirement, vous pouvez imprimer le résultat ainsi que les graphiques qui montrent le résultat du calcul. A noter que les graphiques se modifient automatiquement selon le rendement souhaité de l’investisseur. Juewen FU – Dick Vargas 18/20 Professeur : Dr. Emmanuel Fragnière 6.2. ANNEXE 2 : INTERFACE DE L’OPTIMISATEUR Juewen FU – Dick Vargas 19/20 Professeur : Dr. Emmanuel Fragnière 7. RÉFÉRENCES BIBLIOGRAPHIQUES VBA pour Excel POUR LES NULS. (John Walkenbach. éditeur First Interactive 2003) La bourse pour les nains. www.bnains.org http://fr.finance.yahoo.com/ http://www.swx.com/index.html http://fr.wikipedia.org/wiki/Accueil http://campus.hesge.ch/fragnieree/pages/Finance-RiskManagement/Finance-gestion-des-risques.asp Juewen FU – Dick Vargas 20/20
