Gérer un portefeuille de titres cotés en bourse

Pour partager cette vidéo sur les réseaux sociaux ou sur un site, voici son url :


Sujets que vous pourriez aussi aimer :


Gérer ses titres en bourse avec Excel

Cette mise en pratique propose de bâtir un tableau Excel automatisé, pour la gestion et le suivi de titres porteurs côtés en bourse. L'objectif, au travers de l'analyse de l'évolution des cotations, est de pouvoir déclencher la vente lorsque les indicateurs ne sont pas bons, avant que les pertes ne soient trop conséquentes.



Ce tableau présente pour chacun des titres, le prix d'achat unitaire et la quantité détenue. Il propose, pour chaque mois, de suivre l'évolution des cours en bourse, de semaine en semaine. En fonction de ces variations et évolutions, des calculs sont proposés afin de faire ressortir les valeurs remarquables. De ces calculs, doivent se déclencher automatiquement des interprétations et des ordres de reventes pour minimiser les pertes, lorsque le cas survient.

Analyse mensuelle des cours
Pour débuter cette étude automatisée de l'analyse de l'évolution des cours en bourse, comme toujours, nous partons d'un classeur existant :

• Télécharger le classeur Excel gerer-portefeuille-titres.xlsx en cliquant sur son lien,
• L'ouvrir dans Excel et cliquer sur le bouton Activer le contenu si nécessaire,

La section Analyse mensuelle des cours, propose pour chaque titre, de réaliser différents calculs qui permettront de mettre en lumière certaines variations, afin de maîtriser au mieux les investissements.

Il s'agit dans un premier temps de faire ressortir pour chaque titre, la cotation la plus haute et la plus basse, enregistrées au cours du mois. Ce sont les fonctions statistiques Max() et Min() qui permettent d'extraire respectivement, la plus grande et la plus petite valeur d'une plage de cellules.

• Sélectionner la plage de cellules I6:I10,
• Taper le symbole = pour débuter le calcul,
• Saisir le nom de la fonction pour extraire le plus grand nombre, suivi d'une parenthèse ouvrante, soit Max(,
• Sélectionner toutes les cotations du mois pour le premier titre, soit la plage E6:H6,
• Fermer la parenthèse et valider le calcul par CTRL + Entrée,

Les calculs sont instantanément répliqués sur toute la plage présélectionnée grâce au raccourci clavier CTRL + Entrée. Pour que ces résultats soient visuellement exploitables, nous choisissons d'appliquer une mise en forme conditionnelle. Il s'agit d'attirer l'oeil si ce résultat est équivalent à celui de la dernière semaine et qu'il est supérieur au prix d'achat unitaire. Instantanément ce format dynamique révèlera à son porteur que le titre semble en forme et que sa cotation a augmenté. La plage de cellule étant toujours sélectionnée :

• Cliquer sur le bouton Mise en forme conditionnelle du ruban Accueil,
• Dans la liste, choisir Nouvelle règle,
• Dans la boîte de dialogue qui suit, choisir le dernier type de règle : Utiliser une formule,
• Cliquer dans la zone de saisie pour taper la formule,

Deux conditions dynamiques sont à vérifier pour déclencher la mise en forme des cellules. La fonction Excel ET() permet de lister, entre ses parenthèses, tous les critères à recouper. La fonction Excel Si en revanche n'est pas nécessaire, puisque l'expression d'une condition est l'essence même de la mise en forme conditionnelle.

• Taper le symbole = pour débuter l'expression,
• Saisir le nom de la fonction suivi d'une parenthèse ouvrante, soit ET(,
• Sélectionner la plus grande des valeurs pour le premier titre, soit la cellule I6,
• Enfoncer plusieurs fois la touche F4 du clavier jusqu'à ce que les dollars disparaissent,

Les dollars permettent de figer une cellule de manière à ce que ses références ne se déplacent pas en même temps que le calcul est répliqué. Or, la mise en forme que nous sommes en train de bâtir, dépend ligne à ligne des conditions vérifiées par les cellules correspondant aux titres. Les cellules du calcul doivent donc se déplacer.

• Taper le symbole = pour signifier l'égalité,
• Sélectionner la valeur de la dernière semaine pour le premier titre, soit la cellule H6,
• Enfoncer la touche F4 du clavier jusqu'à ce que les dollars disparaissent,
• Taper un point-virgule pour énumérer un nouveau critère à recouper,
• Sélectionner de nouveau la cellule I6,
• Enlever les dollars à l'aide de la touche F4,
• Taper le symbole Supérieur (>) pour la comparaison,
• Sélectionner le prix d'achat unitaire pour le premier titre, soit la cellule C6,
• Supprimer les dollars de ses références,
• Fermer la parenthèse de la fonction ET,

Nous venons de définir les deux critères qui, s'ils sont recoupés, doivent déclencher une mise en valeur dynamique afin d'attirer l'oeil positivement. La couleur verte est un indicateur rassurant contrairement au rouge par exemple. Donc lorsque la valeur maximale de l'action est identique à celle de la dernière semaine et qu'elle est en même temps supérieure au prix d'achat du titre, nous formatons la cellule sur fond vert clair avec une couleur de police violette par exemple. C'est la raison pour laquelle la boîte de dialogue de mise en forme conditionnelle propose un bouton Format.

• Cliquer sur le bouton Format situé sous la zone de la formule,
• Dans l'onglet Police de la boîte de dialogue qui suit, choisir une police grasse et violette,
• Cliquer ensuite sur l'onglet Remplissage et choisir un fond vert clair,
• Valider cette boîte de dialogue par Ok,

Nous sommes de retour sur la boîte de dialogue de la mise en forme conditionnelle. Et comme l'illustre la capture ci-dessous, Excel indique la mise en valeur susceptible de se déclencher lorsque les conditions du calcul correspondant, sont vérifiées.



Ce format dynamique est effectivement un indicateur intéressant de la bonne tenue des actions engagées. Si par exemple vous modifiez la valeur de la cellule G6 en 225, la mise en valeur dynamique pour le premier titre disparaît automatiquement. En effet, la valeur maximale est toujours supérieure au prix d'achat mais ne correspond plus à la valeur de la dernière semaine. Ceci indique qu'une baisse est à déplorer durant la période et qu'elle est à prendre en considération. Inversement, si vous saisissez 139 en H8, la valeur maximale pour ce troisième titre correspond bien à la valeur de la dernière semaine. Mais la cellule ne change pas de couleur pour autant. En effet, cette valeur reste inférieure au prix d'achat d'origine, signifiant que l'action n'est toujours pas rentable.

Dans la colonne suivante, il s'agit maintenant d'extraire les cours les plus faibles. C'est la fonction Min d'Excel qui permet de restituer le nombre le plus petit dans une plage de cellules. De la même manière, il s'agira d'alerter l'administrateur si d'aventure ces montants étaient identiques à ceux de la dernière semaine et inférieurs au prix d'achat, signifiant probablement une dévaluation irrémédiable du cours.

• Sélectionner toutes les cellules à calculer, soit la plage J6:J10,
• Taper le symbole = pour débuter le calcul,
• Saisir le nom de la fonction suivi d'une parenthèse ouvrante, soit MIN(,
• Sélectionner toutes les cotations pour le premier titre, soit la plage E6:H6,
• Fermer la parenthèse de la fonction Min,
• Puis, valider le calcul par CTRL + Entrée pour le répliquer sur toute la colonne,
• Cliquer sur le bouton Mise en forme conditionnelle du ruban Accueil,
• Dans la liste, choisir Nouvelle Règle,
• Dans la boîte de dialogue qui suit, sélectionner le type : Utiliser une formule,
• Dans la zone de saisie, taper la formule : =ET(J6=H6; J6<C6),

Sans oublier de supprimer les dollars qui figent les cellules, nous exprimons une double condition grâce à la fonction Excel ET. Pour qu'une mise en valeur d'alerte se déclenche, le montant minimum doit être celui de la dernière semaine et inférieur au prix d'achat du titre. Le rouge est une couleur d'alerte conventionnelle.

• Cliquer sur le bouton Format en bas de la boîte de dialogue,
• Dans l'onglet Police, définir une police grasse et rouge vif,
• Dans l'onglet Remplissage, choisir un fond rouge pâle,
• Valider par Ok pour revenir sur la boîte de dialogue du format dynamique,

Dynamiquement, l'utilisateur sera ainsi alerté de la vraisemblable chute des cours en bourse des actions ainsi mises en valeur.

• Saisir la valeur 36 en E10 et la valeur 35 en H10,

Vous remarquez que le format dynamique disparaît pour le dernier titre. L'action n'est ni digne d'intérêt ni inquiétante. Son montant le plus haut n'est pas celui de la dernière semaine, indiquant que le titre n'est pas sur une série ascendante. Son montant le plus faible correspond en revanche à la valeur de la dernière semaine mais n'est pas inférieur au prix d'achat. Le titre n'a donc pas fait perdre d'argent.

Il s'agit maintenant de faire ressortir l'évolution des cours en bourse. Les pourcentages obtenus révèleront sans ambiguïté la tendance à considérer. Le calcul d'une évolution est le suivant :

Evolution = (Valeur_de_fin - Valeur_de_debut) / Valeur_de_debut

La valeur de fin est le montant du titre pour la dernière semaine tandis que la valeur de départ correspond à son prix d'achat unitaire. La division étant prioritaire sur la soustraction en mathématiques, ce calcul doit être factorisé à l'aide des parenthèses afin que ce soit le résultat de la soustraction qui soit divisé par la valeur initiale.

• Sélectionner toutes les cellules des évolutions à calculer, soit la plage K6:K10,
• Taper le symbole = pour initier le calcul,
• Ouvrir une parenthèse pour la factorisation de la formule,
• Sélectionner la dernière valeur pour le premier titre, soit la cellule H6,
• Taper le symbole moins (-) du pavé numérique pour la soustraction,
• Sélectionner la valeur d'achat pour le premier titre, soit la cellule C6,
• Fermer la parenthèse et taper le slash (/) du pavé numérique pour la division,
• Sélectionner de nouveau la cellule C6 et valider la formule par CTRL + Entrée,
• Cliquer sur le bouton Mise en forme conditionnelle du ruban Accueil,
• Dans la liste, pointer sur Jeux d'icônes et cliquer sur 3 Flèches (en couleur),

Ce format dynamique est particulièrement adapté pour faire ressortir instantanément les tendances des variations et évolutions. Deux titres semblent s'effondrer assez nettement tandis que les autres semblent progresser favorablement. L'évolution du premier titre est de bon augure alors que le format conditionnel sur les valeurs maximales ne le mettait pas en avant. Toutes ces astuces visuelles complémentaires aident à interpréter rapidement les résultats, alors que les calculs conditionnels destinés à livrer les conclusions n'ont pas encore été mis en oeuvre.

La dernière colonne de l'analyse des cours consiste à livrer un résultat statistique, celui de la moyenne des cotations tout au long de la période. Son résultat sera intéressant à observer selon qu'il est au-dessus ou en-dessous du prix d'achat de l'action.

• Sélectionner toutes les moyennes à calculer, soit la plage de cellules L6:L10,
• Taper le symbole = pour débuter la formule,
• Saisir le nom de la fonction suivi d'une parenthèse ouvrante, soit Moyenne(,
• Sélectionner toutes les cotations du premier titre, soit la plage de cellules E6:H6,
• Fermer la parenthèse de la fonction Moyenne,
• Puis valider et répliquer le calcul par la combinaison CTRL + Entrée,

Nous choisissons de ne pas créer de règle de format dynamique pour ce dernier résultat afin de ne pas noyer le tableau de couleurs. Les mises en valeur sont d'autant plus pertinentes qu'elles sont remarquables.

Gains et pertes - Actions à entreprendre
Pour que le suivi et la gestion des actions engagées soient totalement fonctionnels et dynamiques, la dernière section du tableau propose de livrer des résultats d'interprétation, selon des seuils de tolérance définis. Ainsi pour enrayer les pertes, la vente du titre doit être enclenchée si la perte est supérieure à 20%. Ce critère est défini en cellule E12 pour que les calculs puissent y faire référence. Ainsi si la condition venait à changer, tous les calculs en dépendraient et se mettraient à jour automatiquement. C'est le but d'un modèle dynamique pour assurer sa portabilité.

Dans la colonne A Vendre, doit apparaître l'information Engager lorsque la perte est jugée trop importante afin de donner l'ordre de transaction. La fonction Excel qui permet de vérifier un critère et d'engager une action ou une autre, selon qu'il est vérifié ou non, est la fonction conditionnelle Si, dont la syntaxe est la suivante :

=SI(Crtitere_a_verifier ;Action_alors ; Action_sinon)

Si l'évolution est inférieure à -20% (critère à vérifier), alors il faut inscrire l'information Engager (Action Alors), sinon la cellule doit rester vide (Action sinon).

• Sélectionner toutes les cellules à calculer, soit la plage M6:M10,
• Taper le symbole = pour initier le calcul,
• Saisir le nom de la fonction suivi d'une parenthèse ouvrante, soit SI(,
• Sélectionner l'évolution pour le premier titre, soit la cellule K6,
• Taper le symbole Inférieur (<) pour la comparaison,
• Sélectionner la cellule E12 du seuil de tolérance,
• Enfoncer la touche F4 du clavier pour la figer dans le calcul,

La condition doit en effet être vérifiée pour les évolutions de tous les titres. Donc la cellule K6 doit suivre le déplacement de la formule. Mais chacune de ces évolutions doit être comparée à la seule et même valeur seuille située en E12. Elle ne doit donc pas être déplacée. Les dollars de la touche F4 la transforme en référence absolue.

• Taper un point-virgule pour passer dans le Alors de la fonction Si,
• Saisir le texte de l'action entre guillemets, soit 'Engager',
• Taper un nouveau point-virgule pour passer dans la branche Sinon de la fonction Si,
• Saisir deux guillemets ('') pour laisser la cellule vide quand aucune action n'est à entreprendre,
• Fermer la parenthèse de la fonction SI,
• Valider le calcul par CTRL + Entrée pour le répliquer sur les cellules présélectionnées,

Tous les résultats des conclusions sont livrés instantanément. Ces interprétations sur les actions à engager sont dynamiques puisque tous les calculs sont bâtis sur des variables, grâce notamment aux références absolues. Ainsi, si l'administrateur juge que l'évolution doit dépasser les 30% en valeur négative, il n'a qu'à modifier la contrainte définie en cellule E12. Dans ces conditions-là, le tableau ne livre plus aucun ordre de transaction.

Selon le même principe le calcul des pertes et des gains, doit se faire en considérant cette contrainte. Si une action est à engager, nous calculons la différence entre la moyenne et le prix d'achat. Nous multiplions ce résultat par la quantité détenue des titres pour obtenir les pertes. Le calcul est le même pour les gains potentiels, mais lorsqu'aucune action n'est à entreprendre. C'est le résultat de la différence qui renverra tantôt une valeur négative, tantôt une valeur positive.

• Sélectionner toutes les cellules de pertes à calculer, soit la plage N6:N10,
• Taper le symbole = pour débuter le calcul,
• Saisir la fonction Si avec le même critère que précédemment, soit si(K6<$E$12,
• Taper un point-virgule pour passer dans la branche Alors de la fonction Si,
• Ouvrir la parenthèse pour factoriser le calcul de la soustraction,
• Sélectionner le prix d'achat du premier titre, soit C6,
• Taper le symbole moins (-) du pavé numérique pour la soustraction,
• Sélectionner la valeur moyenne pour le premier titre, soit la cellule L6,
• Fermer la parenthèse et taper le symbole * pour la multiplication,
• Sélectionner les quantités détenues pour le premier titre, soit la cellule D6,
• Taper un point-virgule pour passer dans la branche Sinon de la fonction Si,
• Saisir deux guillemets ('') pour laisser la cellule vide lorsqu'il n'y a pas de perte,
• Fermer la parenthèse de la fonction Si et répliquer le calcul par CTRL + Entrée,

Les calculs des pertes s'affichent fort logiquement en regard des titres pour lesquels la revente est entreprise, à cause d'une trop forte baisse des cours. Le calcul des gains potentiels est le même, il s'agit de réaliser cette différence mais pour le critère inversé, soit lorsque l'évolution est supérieure au seuil.

• Sélectionner toutes les cellules des gains à calculer, soit la plage O6:O10,
• Taper le symbole = pour initier le calcul,
• Saisir le nom de la fonction conditionnelle suivi d'une parenthèse ouvrante, soit SI(,
• Sélectionner l'évolution du premier titre, soit la cellule K6,
• Saisir l'opérateur de comparaison Supérieur ou égal à (>=),
• Sélectionner la cellule de référence pour le seuil des actions à engager, soit E12,
• Enfoncer la touche F4 du clavier pour la transformer en référence absolue,
• Taper un point-virgule pour passer dans la branche Alors de la fonction Si,
• Ouvrir la parenthèse pour factoriser le calcul de la soustraction,
• Sélectionner la moyenne pour le premier titre, soit la cellule L6,
• Taper le symbole Moins (-) pour la soustraction,
• Sélectionner le prix d'achat du premier titre, soit la cellule C6,
• Fermer la parenthèse et taper le symbole * du pavé numérique pour la multiplication,
• Sélectionner la quantité détenue pour le premier titre, soit la cellule D6,
• Taper un point-virgule pour passer dans la branche Sinon de la fonction Si,
• Saisir deux guillemets ('') pour laisser la cellule vide lorsqu'il n'y a pas de gain à calculer,
• Fermer la parenthèse de la fonction Si,
• Valider et répliquer le calcul par la combinaison CTRL + Entrée,

Ces valeurs pourraient apparaître en négatif si nous changions le seuil à -30% par exemple. Les actions à vendre seraient conservées mais en enregistrant une perte à cause de l'évolution des cours. Le calcul du Total du portefeuille se réalise par la différence entre la somme des gains et des pertes.

• Sélectionner la cellule O12,
• Saisir le nom de la fonction pour additionner suivi d'une parenthèse ouvrante, soit SOMME(,
• Sélectionner la plage de cellules des gains, soit O6:O10,
• Fermer la parenthèse de la fonction Somme et taper le symbole Moins (-) pour la soustraction,
• Saisir de nouveau le nom de la fonction suivi d'une parenthèse ouvrante, soit SOMME(,
• Sélectionner cette fois toutes les cellules des pertes, soit la plage N6:N10,
• Fermer la parenthèse de cette seconde fonction Somme,
• Valider le calcul en enfonçant la touche Entrée du clavier,

L'emploi de la fonction Somme offre un double avantage par rapport à l'addition manuelle à l'aide du symbole Plus (+) du pavé numérique. Tout d'abord, elle permet de gagner du temps lorsque les cellules à sommer sont nombreuses. Ensuite, elle est capable d'ignorer les cellules vides pour ne pas les inclure dans le calcul. L'addition manuelle à la place aurait généré une erreur car une cellule vide est considérée comme du texte. Et bien sûr, même si ces cellules sont vides à l'instant t, elles peuvent se remplir, si les contraintes ou les cours de la bourse venaient à évoluer. Donc pour que le tableau reste totalement dynamique et automatique, elles doivent être incluses dans le calcul.