Bonjour,
Ci-dessous un sujet d'optimisation de portefeuille VBA.
Nous avons à réaliser ce TD.
Nous avons quelques problèmes de macro de la question 5 à 12
Pouvez-vous s'il vous plait nous aider à réaliser ces macros ?
Merci d'avance.
Projet – Sélection de portefeuille et minimisation de variance.
Le but de ce projet est dans un premier temps de construire des portefeuilles optimaux à partir de
la donnée de la rentabilité, du risque et de la matrice des covariances de plusieurs actifs. Pour ceci
veuillez réviser la constitution des portefeuilles de Markowitz (par exemple lire http://www.essec.fr/
faculty/showDeclFileRes.do?declId=9129&key=Publication-Content). L’objectif est de concevoir un
code VBA qui renvoie le portefeuille qui minimise le risque en assurant un niveau de rentabilité
spécifié par l’utilisateur. Dans un deuxième temps, le logiciel tracera la frontière des portefeuilles
optimaux pour l'ensemble des actifs.
Dans une première phase on utilisera un scénario avec des actions prédéfinies, mais dans la
version finale on récupèrera des cours d'actions sous forme de tableurs Excel depuis Yahoo
Finance. A partir de ces cours et d'un horizon d'investissement, on calcule la rentabilité, le risque et
la matrice des covariances pour ces actifs, de fait il deviendra possible de calculer des
portefeuilles optimaux à partir de ces actifs et de faire un outil opérationnel de gestion de
portefeuille.
Vous devez rendre pour chaque binôme les fichiers EXCELs avec une interface graphique simple
ainsi qu’un rapport faisant une analyse des portfolios que vous proposerez à la fin du sujet ainsi
qu’une analyse comparative avec le CAC40.
Voici les étapes à mettre en oeuvre.
1) On dispose de la feuille SelectionPortefeuille.xls. On commencera par interpréter
cette feuille : quelles sont les valeurs qui sont déterminées, quelles sont celles qui sont
introduites par l'utilisateur ?
Nous utiliserons ensuite le solveur pour trouver un portefeuille optimal (minimisant le risque)
pour une espérance de gain de 7%.
2) On écrira ensuite une macro en on utilisant l'enregistreur de macros permettant de récupérer la
liste des fonctions qui permettent d'obtenir le portefeuille optimal à partir de l'objectif de
rentabilité. Regarder le code obtenu et interprétez ce qu’il fait. Etant donné que les contraintes
ne sont pas réinitialisée d'une utilisation du solveur à l’autre, il faut appeler la procédure dans
la macro SolveurReset avant d'utiliser le solveur.
3) Ré a l i s e r u n e p r o c é d u r e d é r i v é e d e l a p r o c é d u r e Te s tMa c r o , a p p e l l é e
CalculPortefeuilleOptimal(objRenta as Double)
Cette procédure prend une valeur réelle en paramètre, elle consiste à mettre la valeur de la
rentabilité espérée en L6, puis à lancer le calcul de l'optimisation, pour obtenir un portefeuille
optimal. Rattachez cette macro à un bouton qui permet de lancer le calcul d’optimisation.
4) Réaliser une macro de calcul de la frontière optimale. Cette procédure fonctionne ainsi : elle
passe en revue différents niveaux de rentabilité. Pour chacun des niveaux de rentabilité, elle
c a l c u l e l e p o r t e f e u i l l e o p t i m a l e n u t i l i s a n t l a f o n c t i o n :
CalculPortefeuilleOptimal(objRenta as Double).
Après chaque exécution de la procédure, elle récupère l'écart type du portefeuille optimal
obtenu. L'espérance et l'écart type sont reportés sur une seconde feuille de calcul : on obtient
pour différents niveaux d'espérance de retour, un niveau de risque associé. Il reste à tracer le
graphique de l'approximation de la frontière optimale.
5) Comment faudrait il modifier le solveur pour que les pondérations des actifs soient toutes
positives ?
Dans la suite nous allons développer une autre démarche de choix de portefeuille et
d'approximation de la frontière optimale qui ne repose pas sur l'utilisation du solveur. On tire des
portefeuilles au hasard et on cherche celui qui est le plus proche du portefeuille optimal souhaité.
6) Construire une procédure de tirage d'un portefeuille au hasard. Dans l’example que nous
avons ce problème se ramène au fait de tirer 3 valeurs comprises dans [0:1] dont la somme est
égale à 1. On pourra par exemple tirer 3 valeurs dans [0;1], calculer leur somme S et diviser
ensuite chacune des 3 valeurs par S.
7) Tirer des portefeuilles au hasard et calculer pour chacun d'entre eux leur espérance et leur
risque. Parmi tous les portefeuilles tirés, trouver le portefeuille le plus proche des critères de
l’utilisateur.
8) A partir du tirage de nombreux portefeuilles, on peut retenir les portefeuilles qui sont sur la
frontière optimale. Il existe plusieurs manières de procéder.
1. On tire un très grand nombre de portefeuille en repérant pour différents intervalles de
rentabilité le risque minimum,
2. On tire un moins grand nombre de portefeuilles que dans le cas précédent. Pour chaque
portefeuille, on tente de l'optimiser. Par exemple, un portefeuille tel qu'il existe un actif de
risque inférieur et de rentabilité supérieure est inefficace : on pourrait lui substituer un
portefeuille de 100% de l'actif, donc pour chaque portefeuille il serait possible de l'améliorer.
On peut concevoir différents filtres d'amélioration des portefeuilles.
Jusqu’ici nous avons travaillé sur des valeurs qui n’était pas réelles. Dans la suite nous allons
rendre plus réaliste l’analyse.
9) Commencez par récupérer les valeurs des 40 actions qui constituent le CAC 40 (vous pouvez
pour ceci utiliser les valeurs dans http://www.boursorama.com/bourse/actions/palmares/
capitalisations.phtml et dans http://www.abcbourse.com/download/ [...] spx?f=ex).
Voyez comment le CAC 40 est calculé (voir par exemple http://www.investir-blog.com/2013/03/
quelle-est-la-formule-pour-calculer-le.html) . Ecrire une macro qui calcule chaque jour à
l’ouverture et à la fermeture des cours le portfolio équivalent au CAC 40. Calculer le rendement
de ce portfolio et le risque associé à celui ci.
10) Récupérer les cours de bourse de plusieurs actions sur Yahoo!Finance (vous pouvez
télécharger ceux-ci à la main ou utilisé la macro présenté ici http://www.codeproject.com/
Articles/740069/VBA-Macros-Provide-Yahoo-Stock-Quote-Downloads-in). A partir de ces cours,
établir une interface permettant de calculer pour cette action sa rentabilité et son écart type sur
une période d’investissement donnée par l’utilisateur (1jour, 30 jours, 360 jours etc…). Vous
devez calculer pour ces actions un vecteur d'espérances de gain, un vecteur de risques et une
matrice de covariances. Utilisez ces valeurs pour calculer le portfolio optimal en utilisant
CalculPortefeuilleOptimal(objRenta as Double)que vous avez calculé
précédemment.
11) Choisissez maintenant les 10 premières valeurs du CAC par ordre alphabétique. Récupérer
leurs cours de bourse. Trouvez le portfolio optimal avec ces actifs. Comparer la performance et
le risque de ce portfolio avec celui induit par le CAC 40.
12) Proposez un portfolio d’action. Le jour de la correction je vérifierai la performance de votre
portfolio par rapport au CAC à partir de la date de remise du projet et j’ajouterai un point pour
chaque 1% de performance supérieur au CAC 40. Si vous faite 10% de mieux que le CAC 40
vous aurez 10 points de plus.
