Suivi connecté des valeurs boursières VBA Excel

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


Sujets que vous pourriez aussi aimer :


Suivi connecté des cotations boursières

Dans la formation précédente, nous avions appris à récupérer les données d'un site Internet en VBA Excel pour réaliser des calculs de conversion en fonction des taux de change. Nous souhaitons ici pousser plus loin l'application connectée en réalisant le suivi des fluctuations des valeurs boursières en temps réel.



Plus précisément, comme l'illustre la capture ci-dessus, nous souhaitons récupérer la valeur du taux de change entre le dollar et l'euro chaque minute et représenter graphiquement ses variations. Tout cela doit être géré par le code Visual Basic Excel.

Application Excel connectée à Internet
En partant de l'application connectée précédente qui récupère le taux de change depuis le site Boursorama.com, l'objectif est de réaliser le suivi des variations de ces cotations.

• Télécharger le classeur Excel suivi-connecte-vba.xlsm en cliquant sur son lien,
• L'ouvrir dans Excel et cliquer sur le bouton Activer la modification si nécessaire,

Le classeur est cette fois constitué de deux feuilles : Cours en bourse et Suivi connecté. La première des deux correspond au développement de l'application précédente. Le code VBA se charge de télécharger le code HTML d'une page Web issue du site boursorama.com pour en extraire le taux de change et l'inscrire en cellule E10. Ce code Visual Basic est déclenché à l'ouverture du classeur grâce à la procédure événementielle Workbook_Open(). Il est de même actif dans une boucle infinie afin de mettre à jour le taux de change à intervalles de temps réguliers grâce à la fonction VBA Excel Timer. Pour poursuivre le développement, il s'agit dans un premier temps de stopper l'exécution de ce code.

• Réaliser le raccourci clavier ALT + F11 pour basculer dans l'éditeur de code VBA Excel,
• En haut de la fenêtre, cliquer sur le bouton Réinitialiser de la barre d'outils,

L'exécution du code est ainsi stoppée et le développement peut reprendre.

• Réaliser de nouveau la combinaison ALT + F11 pour revenir sur le classeur,
• En bas de la fenêtre Excel, cliquer sur l'onglet de la feuille Suivi connecté,

Vous notez la présence d'un tableau, vide pour l'instant, ainsi que d'un graphique avec un titre mais sans source de données.

Suivi de l'évolution des valeurs boursières
A chaque incrément de temps, le nouveau taux de change doit être inscrit en colonne D avec l'heure précise correspondante en colonne C. En colonne E, un code VBA doit se charger des variations, soit des écarts entre chaque valeur de taux de change prélevé. Et c'est encore un code Visual Basic qui se chargera d'appliquer une mise en forme conditionnelle sur cette rangée, sous forme d'icônes, afin de rendre les variations visuellement parlantes. Enfin, la source de données du graphique sera définie dynamiquement, toujours par le code, sur la plage de données en constante évolution.

Comme les données doivent être inscrites les unes en dessous des autres, nous avons besoin d'une variable publique, capable de mémoriser l'indice de ligne en cours.

• Basculer dans l'éditeur de code VBA Excel (ALT + F11),
• Dans l'explorateur de projets sur la gauche, double cliquer sur l'élément Module1,
• Dans sa page de code au centre de l'écran, sous l'instruction Option Explicit,ajouter la déclaration publique suivante :

Dim num_ligne As Integer

En effet, toutes les fonctions qui traitent de la récupération des données Web, sont écrites dans ce module. La variable num_ligne ainsi déclarée en dehors de toute procédure aura une durée de vie équivalente à celle du module, donc au-delà des bornes de chacune des fonctions. Elle permettra ainsi de porter la valeur de l'indice de ligne en cours, afin d'écrire à la suite les valeurs des taux de change récupérés.

Les fonctions recuperer_web() et appel_recurrent() sont appelées à l'ouverture du classeur. Les indices de lignes ne sont donc pas encore connus, car jamais traités et doivent donc être initialisés.

• Ajouter l'instruction suivante avec l'affectation des variables pour les deux fonctions :

If num_ligne = 0 Then num_ligne = 6



La valeur par défaut d'un entier (Integer) est 0. Donc si la variable n'a pas encore été initialisée, nous l'affectons à l'indice de la première ligne du tableau à renseigner (Cellule C6). En effet, ces deux fonctions sont ensuite appelées régulièrement pour le traitement récursif de la récupération du taux de change à intervalles de temps réguliers.

Cet intervalle de temps doit d'ailleurs être modifié afin d'établir la connexion toutes les minutes et non plus toutes les 30 secondes.

• Dans la fonction appel_recurrent, modifier le critère de temps de 30 à 60 :

Do While Timer < debut + 60
DoEvents
Loop

La fonction qui se charge d'inscrire la valeur du taux récupéré est la fonction recuperer_taux. Comme l'indique sa dernière instruction : Sheets('Cours en bourse').Range('E10').Value = contenu, c'est la cellule E10 de la feuille Cours en bourse qui récupère la valeur traitée. A la suite de ce code, nous devons créer celui capable de renseigner les lignes du tableau de la feuille Suivi connecté, avec la valeur du taux de change et l'heure du prélèvement.

• Après la ligne de code : Sheets('Cours en bourse').Range('E10').Value = contenu, de la fonction recuperer_taux, ajouter les deux instructions suivantes :

Sheets('Suivi connecté').Cells(num_ligne, 4).Value = CDbl(contenu)
Sheets('Suivi connecté').Cells(num_ligne, 3).Value = Hour(Now) & ':' & Minute(Now)

Sur la feuille Suivi connecté (Sheets('Suivi connecté')), pour la ligne en cours de la colonne D (Cells(num_ligne, 4)), nous inscrivons la valeur du taux de change (contenu), convertie en réel double, grâce à la fonction VBA CDbl. Pour la même ligne en colonne C (Cells(num_ligne, 3)),nous reconstituons l'heure précise avec les minutes. Pour ce faire, nous exploitons la fonction VBA Now qui renvoie par défaut la date en cours à la minute près. Les fonctions VBA Hour et Minute permettent respectivement d'extraire d'une date, la partie sur les heures et la partie sur les minutes. Nous concaténons ces deux valeurs avec le symbole deux points ( & ':' & ), pour inscrire l'information dans la cellule, au format Heure.

Mais à ce stade, l'indice de ligne n'évolue pas à chaque traitement. En l'état, le code se contentera d'écraser le contenu en ligne 6. Nous devons incrémenter la valeur de l'indice de ligne, après y avoir inscrit les données.

• A la suite du code, ajouter l'instruction suivante :

num_ligne = num_ligne + 1

Puisque le numéro de ligne est désormais exploité, nous passons au suivant en incrémentant sa variable publique. Il est temps de réaliser un premier essai.

• Enregistrer les modifications (CTRL + S) et fermer le classeur Excel,
• Puis le rouvrir pour simuler le processus d'appel des fonctions à partir du chargement,

Les données se cumulent parfaitement les unes sous les autres. Nous obtenons ainsi le suivi de l'évolution des taux de change avec l'indication sur les intervalles de temps. Le graphique lui-même se remplit automatiquement. Mais sa représentation n'est pas correcte car sa source de données est bâtie sur une plage de cellules fixe, comme l'illustre la capture ci-dessous. De fait, les données paraissent tassées et ne seront plus représentées au-delà de la ligne 21.



Nous devons donc recalibrer dynamiquement la source de données du graphique, strictement sur la plage des cellules renseignées. Pour connaître les propriétés, objets et méthodes employées par VBA, nous allons créer une macro automatique qui nous renseignera sur le code. Nous n'aurons plus qu'à nous en inspirer et l'adapter.

• Basculer dans l'éditeur de code VBA (ALT + F11),
• Cliquer sur le bouton Réinitialiser de la barre d'outils pour stopper son exécution,
• Basculer de nouveau sur la feuille Excel et activer le ruban Développeur,

S'il n'est pas présent dans votre environnement, la formation pour débuter la programmation en VBA Excel explique comment ajouter ce ruban.

• Dans la section Code du ruban, cliquer sur le bouton Enregistrer une macro,
• Dans la boîte de dialogue qui suit, nommer la macro : source_graph,
• Puis cliquer sur le bouton Ok pour démarrer l'enregistrement,

En effet, une macro Excel consiste à enregistrer les actions utilisateur pour les restituer ensuite afin d'automatiser les tâches. Nous devons donc nous concentrer strictement sur les actions nécessaires.

• Cliquer sur le graphique afin de le sélectionner,
• Cliquer sur l'onglet Création en haut de la fenêtre Excel afin d'activer son ruban,
• Dans la section Données, cliquer sur le bouton Sélectionner les données,
• Sélectionner alors les cellules non vides, par exemple C6:D8 et valider par Ok,
• Activer de nouveau le ruban Développeur et cliquer sur le bouton Arrêter l'enregistrement,

La source de données que nous avons définie n'est pas celle qu'adoptera le graphique. Nous avons simulé la modification de la source d'un graphique pendant l'enregistrement de la macro pour qu'Excel nous montre le code VBA.

• Basculer dans l'éditeur de code Visual Basic Excel,
• Dans l'explorateur de projets, double cliquer sur le nouveau module créé par la macro,

Le code fourni par la macro est donc le suivant :

ActiveSheet.ChartObjects('Graphique1').Activate
Application.CutCopyMode = False
ActiveChart.SetSourceData Source:=Range('C6:D8')

ActiveSheet est un objet VBA Excel qui désigne la feuille active. Nous devrons le remplacer par l'objet qui permet de désigner la feuille Suivi connecté. ChartObjects est donc l'objet dérivé de la feuille qui permet de pointer sur le graphique dont le nom lui est passé en paramètre. D'ailleurs, si nous consultons la zone nom depuis la feuille Excel après avoir sélectionné le graphique, nous constatons en effet qu'il se nomme Graphique 1. La méthode Activate permet donc de rendre actif le graphique pour le manipuler par le code. La ligne suivante ne nous intéresse pas. La troisième en revanche est celle qui permet de redéfinir la source de données du graphique grâce à la méthode SetSourceData.

• Supprimer la deuxième ligne de code de cette macro VBA (Application.CutCopyMode = False),
• Sélectionner les deux lignes restantes et les copier (CTRL + C),
• Dans l'explorateur de projets, double cliquer sur l'élément Module1 pour afficher son code,
• Avant l'instruction : num_ligne = num_ligne + 1 de la fonction recuperer_taux, coller ces deux lignes de code (CTRL + V),

Il reste à adapter la source de données du graphique. Cette source est dynamique puisqu'elle évolue continuellement en fonction des valeurs prélevées sur Internet. Nous savons que le début de la plage est la cellule C6 soit avec l'objet Cells en VBA :Cells(6,3). La dernière cellule de la plage est en colonne 4 et à la ligne renvoyée par la variable publique num_ligne, soit Cells(num_ligne,4). L'objet VBA Excel Range permet de définir une plage de cellules en les énumérant les unes à la suite des autres.

• En conséquence, dans le code VBA, remplacer la source de données : Range('C6:D8') par Range(Cells(6, 3), Cells(num_ligne, 4)), ce qui donne :

ActiveChart.SetSourceData Source:=Range(Cells(6, 3), Cells(num_ligne, 4))

• Enregistrer les modifications et fermer le classeur Excel,
• Puis le rouvrir afin de déclencher le code au démarrage et simuler le processus,
• Patienter quelques minutes afin d'empiler les données extraites du Web,

Comme nous pouvons le constater, la zone de graphique s'adapte précisément aux données renseignées automatiquement par le code VBA d'extraction. A chaque fois qu'une valeur est ajoutée dans le tableau, elle est considérée et représentée graphiquement. Nous avons donc bien réussi à adapter dynamiquement la source de données du graphique par le code VBA. Celui-ci présente désormais les variations des valeurs boursières issues du Web, en temps réel.



Un problème subsiste, des valeurs existent déjà à l'ouverture du classeur. Un code devra donc purger le tableau à chaque démarrage.

Mise en forme conditionnelle par le code VBA
Avant de résoudre ce souci, nous allons nous occuper de la colonne Variations. Celle-ci doit afficher l'écart du taux de change en cours par rapport au précédent. Tantôt cet écart sera positif, négatif ou nul. Nous souhaitons alors faire ressortir ces évolutions par des jeux d'icônes du format dynamique. Et cette mise en forme conditionnelle doit être commandée dynamiquement par le code VBA, au fur et à mesure que les écarts sont calculés.

• Basculer dans l'éditeur de code VBA Excel,
• Puis cliquer sur le bouton Réinitialiser pour stopper l'exécution,

Le premier calcul doit être initié en cellule E7 puisqu'il faut déjà avoir inscrit deux taux de change afin d'en déduire le premier écart. La condition à respecter pour déclencher le calcul consiste donc à vérifier que la variable num_ligne est bien supérieure à 6. Comme toujours pour ce faire, nous exploiterons l'instruction VBA conditionnelle If.

• A la suite du code, toujours avant l'incrémentation de la variable num_ligne, ajouter les instructions suivantes :

If (num_ligne > 6) Then
Sheets('Suivi connecté').Cells(num_ligne, 5).Value = Sheets('Suivi connecté').Cells(num_ligne, 4).Value - Sheets('Suivi connecté').Cells(num_ligne - 1, 4).Value
End If

Grâce à l'instruction If nous vérifions d'abord le critère afin de ne déclencher le code du calcul de l'écart, que si nécessaire. Puis au fur et à mesure de l'incrémentation, nous affectons à la cellule en cours de la colonne E (Cells(num_ligne, 5).Value), la différence de valeur qu'il y a entre le taux de change en cours (Cells(num_ligne,4).Value) et le précédent (Cells(num_ligne - 1, 4).Value). Il est temps de vérifier la validité de ce petit bout de code.

• Enregistrer les modifications puis fermer le classeur Excel,
• Le rouvrir pour lancer l'exécution du code VBA,

Les variations se calculent parfaitement au fur et à mesure que les données sont consolidées.



Mais comme les écarts sont infimes, l'interprétation des calculs n'est pas assez parlante. C'est pourquoi, afin de renforcer la lecture du graphique, nous souhaitons combiner un format dynamique spécifique sur cette plage des variations. Une fois encore, c'est une macro automatique qui va nous apprendre les objets VBA nécessaires pour cette mise en oeuvre.

• Basculer dans l'éditeur de code et cliquer sur le bouton Réinitialiser pour stopper l'exécution,
• Puis, revenir sur la feuille Excel,
• Sélectionner le premier calcul de l'écart, soit la cellule E7,
• Cliquer sur l'onglet Développeur en haut de la fenêtre Excel pour activer son ruban,
• Dans la section Code, cliquer sur le bouton Enregistrer une macro,
• Dans la boîte de dialogue qui suit, nommer la macro : mefc,
• Puis, cliquer sur le bouton Ok pour démarrer l'enregistrement,
• Activer le ruban Accueil et cliquer sur le bouton Mise en forme conditionnelle,
• Dans la liste, pointer sur Jeux d'icônes et cliquer sur Autres règles en bas du sous-menu,
• Dans la boîte de dialogue qui suit, avec la liste déroulante des Styles d'icônes, choisir les 3 flèches de couleur,

Il s'agit maintenant de bâtir les règles de mises en valeur dynamique, selon le modèle proposé par la capture ci-dessus.

• Pour la flèche verte, sélectionner l'opérateur >, saisir la valeur 0 et choisir le type Nombre,
• Pour la flèche Orange, sélectionner l'opérateur >=, saisir la valeur 0 et choisir le type Nombre,

La condition sur la flèche rouge se déduit automatiquement des deux précédentes. Concrètement, nous indiquons d'associer une flèche verte montante lorsque l'écart calculé est strictement positif. Une flèche orange horizontale accompagnera les variations nulles. Seule la valeur 0 vérifie en effet à la fois le critère <=0 ET >=0, comme inscrit sur la boîte de dialogue. Enfin, une flèche rouge descendante accompagnera les baisses.

• Cliquer sur le bouton Ok de la boîte de dialogue pour valider ces réglages de format dynamique,
• Dans le ruban Développeur, cliquer sur le bouton Arrêter l'enregistrement,
• Basculer dans l'éditeur de code Visual Basic Excel,
• Dans l'explorateur de projets, double cliquer sur le nouveau module créé par la macro,

Selection.FormatConditions.AddIconSetCondition
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1)
.ReverseOrder = False
.ShowIconOnly = False
.IconSet = ActiveWorkbook.IconSets(xl3Arrows)
End With

With Selection.FormatConditions(1).IconCriteria(2)
.Type = xlConditionValuePercent
.Value = 33
.Operator = 7
End With

Selection.FormatConditions.AddIconSetCondition
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1)
.ReverseOrder = False
.ShowIconOnly = False
.IconSet = ActiveWorkbook.IconSets(xl3Arrows)
End With

With Selection.FormatConditions(1).IconCriteria(2)
.Type = xlConditionValueNumber
.Value = 0
.Operator = 7
End With

With Selection.FormatConditions(1).IconCriteria(3)
.Type = xlConditionValueNumber
.Value = 0
.Operator = 5
End With

Comme vous le constatez, le code VBA de la mise en forme conditionnelle, retranscrit par la macro est fort logiquement plus dense que le précédent. Il traduit toutes les actions et paramétrages que nous avons réalisés sur la boîte de dialogue. Nous retenons l'emploi de l'objet VBA Selection qui désigne la cellule ou la plage de cellules sélectionnée au moment des manipulations. Donc nous exploiterons ce code tel quel, en n'omettant pas préalablement de sélectionner par le code, la cellule du calcul de l'écart en cours.

• Sélectionner l'intégralité du code de la macro sans les bornes de la procédure bien sûr,
• Copier ce code (CTRL + C),
• Double cliquer sur l'élément Module1 depuis l'explorateur de projets,
• A la suite du code de la fonction recuperer_taux, dans l'instruction conditionnelle qui vérifie l'incrémentation de la variable num_ligne, soit avant le End If, coller ces lignes (CTRL + V),

L'adaptation n'est pas terminée. Lecode VBA qui règle la mise en forme conditionnelle agit sur la sélection active. Donc à chaque incrémentation de la variable num_ligne, avant que ce code ne soit exécuté, une instruction doit rendre active la dernière cellule pour laquelle l'écart a été calculé.

• Juste avant les lignes précédemment copiées, ajouter l'instruction suivante :

Sheets('Suivi connecté').Cells(num_ligne, 5).select

Nous désignons la cellule active de la colonne E (Cells(num_ligne, 5)) que nous sélectionnons, grâce à la méthode Select de l'objet Cells.

• Enregistrer les modifications et fermer le classeur,
• Puis le rouvrir pour visualiser l'incidence des modifications dans le code VBA,

Grâce à la macro automatique, l'adaptation du code VBA met parfaitement en valeur chacune des cellules calculées au fur et à mesure. Pour l'anecdote, dans le laps de temps de la simulation illustrée par la capture ci-dessus, le dollar a repris du poil de la bête. Nous constatons en effet une dominante de flèches vertes et une hausse du taux de change illustrée par la courbe montante du graphique.

Vous constatez fort logiquement que la dernière cellule calculée est active. Pour ne pas perdre le visuel sur le graphique, au fil du cumul des valeurs, nous devons replacer le focus sur la première cellule du tableau en la sélectionnant.

• Basculer dans l'éditeur de code (ALT + F11) et stopper l'exécution du code,
• Dans la procédure recuperer_taux, avant le End If, ajouter l'instruction suivante :

Range('C6').Select

Nous aurions pu exploiter l'objet Cells mais étant donné que nous connaissons précisément les coordonnées de la cellule à activer, l'objet Range fait parfaitement l'affaire.

Pour parfaire le code, il serait intéressant de formater chacune des cellules renseignées au fur et à mesure, afin de conserver une présentation homogène sur toute la liste. Mais ce n'estpas l'objectif ici. Le code complet de la fonction recuperer_taux est le suivant :

Function recuperer_taux(chemin As String)
Dim contenu As String: Dim taille_fichier As Long
Dim position_fin As Long: Dim position_depart As Long

Open chemin For Input As #1
taille_fichier = LOF(1)
contenu = Input(taille_fichier, 1)
Close #1

position_fin = InStr(1, contenu, 'EUR</span>')
contenu = Left(contenu, position_fin)
position_depart = InStrRev(contenu, '>') + 1

contenu = Mid(contenu, position_depart, position_fin - position_depart)
contenu = Replace(Replace(contenu, ' ', ''), '.',',')

Sheets('Cours en bourse').Range('E10').Value = contenu

Sheets('Suivi connecté').Cells(num_ligne, 4).Value = CDbl(contenu)
Sheets('Suivi connecté').Cells(num_ligne, 3).Value = Hour(Now) & ':' & Minute(Now)
ActiveSheet.ChartObjects('Graphique 1').Activate
ActiveChart.SetSourceData Source:=Range(Cells(6, 3), Cells(num_ligne, 4))

If (num_ligne > 6) Then
Sheets('Suivi connecté').Cells(num_ligne, 5).Value = Sheets('Suivi connecté').Cells(num_ligne, 4).Value - Sheets('Suivi connecté').Cells(num_ligne - 1, 4).Value

Sheets('Suivi connecté').Cells(num_ligne, 5).Select
Selection.FormatConditions.AddIconSetCondition
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1)
.ReverseOrder = False
.ShowIconOnly = False
.IconSet = ActiveWorkbook.IconSets(xl3Arrows)
End With

With Selection.FormatConditions(1).IconCriteria(2)
.Type = xlConditionValueNumber
.Value = 0
.Operator = 7
End With

With Selection.FormatConditions(1).IconCriteria(3)
.Type = xlConditionValueNumber
.Value = 0
.Operator = 5
End With

Range('C6').Select
End If

num_ligne = num_ligne + 1
End Function

Purger les données - Contenu et Format
Comme nous l'avions constaté plus haut, des données persistent à chaque démarrage du classeur. Il nous reste donc à bâtir un code purgeant le contenu et les formats des cellules du tableau, à chaque ouverture, soit sur l'événement Workbook_Open.

• En dehors de toute fonction, ajouter la procédure purge() comme suit :

Sub purge()
Dim ligne As Integer

ligne = Sheets('Suivi connecté').Cells.SpecialCells(xlCellTypeLastCell).Row

Range(Cells(6, 3), Cells(ligne, 5)).Delete
Range(Cells(6, 3), Cells(ligne, 5)).ClearFormats

End Sub

Dans la variable ligne déclarée en début de procédure nous enregistrons l'indice de ligne de la dernière cellule non vide sur la feuille Suivi connecté. C'est la propriété dérivée Row de la propriété SpecialCells de l'objet Cells qui renvoie cette information, lorsque le paramètre xlCellTypeLastCell ou 11 dans son équivalent numérique, lui est passé. Nous avions d'ailleurs exploité cette propriété SpecialCells dans la formation VBA sur les boucles For Next afin de détecter automatiquement la présence des tableaux dans une feuille.

Il ne reste alors plus qu'à supprimer le contenu et les formats de la plage de cellules s'étendant de C6 à la dernière ligne de la colonne E (Cells(ligne, 5)). Et ce sont respectivement les méthodes Delete et ClearFormats d'un objet Range qui le permettent. Bien sûr cette procédure doit être appelée à l'ouverture du classeur et ce, avant l'exécution de tout autre code.

• Dans l'explorateur de projets, double cliquer sur l'élément ThisWorkbook,
• Au début de la procédure Workbook_Open(), ajouter l'appel à la procédure purge(), ce qui donne :

Private Sub Workbook_Open()
purge
recuperer_web
appel_recurrent
End Sub

• Fermer le classeur en l'enregistrant puis le rouvrir,

Comme l'illustre la capture ci-dessous, le tableau des enchaînements de valeurs est cette fois nettoyé avant de commencer une nouvelle analyse.