Taux de change mis à jour dans Excel

J'ai analysé à plusieurs reprises les moyens d'importer des données dans Excel à partir d'Internet avec une mise à jour automatique ultérieure. En particulier:

• Dans les anciennes versions d'Excel 2007-2013, cela pouvait être fait avec une requête Web directe.
• À partir de 2010, cela peut être fait très facilement avec le complément Power Query.
  

À ces méthodes dans les dernières versions de Microsoft Excel, vous pouvez désormais en ajouter une autre : importer des données depuis Internet au format XML à l'aide de fonctions intégrées.

XML (eXtensible Markup Language = Extensible Markup Language) est un langage universel conçu pour décrire tout type de données. En fait, il s'agit de texte brut, mais avec des balises spéciales ajoutées pour marquer la structure des données. De nombreux sites proposent des flux gratuits de leurs données au format XML que tout le monde peut télécharger. Sur le site Web de la Banque centrale de Notre pays (www.cbr.ru), en particulier, à l'aide d'une technologie similaire, des données sur les taux de change de différentes devises sont fournies. Sur le site Web de la bourse de Moscou (www.moex.com), vous pouvez télécharger des cotations d'actions, d'obligations et de nombreuses autres informations utiles de la même manière.

Depuis la version 2013, Excel dispose de deux fonctions pour charger directement des données XML depuis Internet dans des cellules de feuille de calcul : SERVICE WEB (SERVICE WEB) и FILTRE.XML (FILTREXML). Ils travaillent par paires - d'abord la fonction SERVICE WEB exécute une requête vers le site souhaité et renvoie sa réponse au format XML, puis à l'aide de la fonction FILTRE.XML nous « analysons » cette réponse en composants, en extrayant les données dont nous avons besoin.

Examinons le fonctionnement de ces fonctions à l'aide d'un exemple classique - importer le taux de change de toute devise dont nous avons besoin pour un intervalle de dates donné à partir du site Web de la Banque centrale de notre pays. Nous utiliserons la construction suivante comme blanc :

Ici:

• Les cellules jaunes contiennent les dates de début et de fin de la période qui nous intéresse.
• Le bleu a une liste déroulante de devises en utilisant la commande Données – Validation – Liste (Données — Validation — Liste).
• Dans les cellules vertes, nous utiliserons nos fonctions pour créer une chaîne de requête et obtenir la réponse du serveur.
• Le tableau de droite est une référence aux codes de devise (nous en aurons besoin un peu plus tard).
  

Allons-y!

Étape 1. Former une chaîne de requête

Pour obtenir les informations requises sur le site, vous devez les demander correctement. Nous allons sur www.cbr.ru et ouvrons le lien dans le pied de page de la page principale' Ressources techniques'- Obtenir des données à l'aide de XML (http://cbr.ru/development/SXML/). Nous faisons défiler un peu plus bas et dans le deuxième exemple (Exemple 2) il y aura ce dont nous avons besoin - obtenir les taux de change pour un intervalle de dates donné :

Comme vous pouvez le voir dans l'exemple, la chaîne de requête doit contenir des dates de début (date_req1) et les terminaisons (date_req2) de la période qui nous intéresse et du code devise (VAL_NM_RQ), dont on veut obtenir le taux. Vous pouvez retrouver les principaux codes devises dans le tableau ci-dessous :

Un guide complet des codes de devises est également disponible sur le site Web de la Banque centrale - voir http://cbr.ru/scripts/XML_val.asp?d=0

Nous allons maintenant former une chaîne de requête dans une cellule d'une feuille avec :

• l'opérateur de concaténation de texte (&) pour le mettre ensemble ;
  
• Fonctionnalités: VPR (VLOOKUP)pour trouver le code de la devise dont nous avons besoin dans le répertoire ;
  
• Fonctionnalités: TEXTE (TEXTE), qui convertit la date selon le modèle jour-mois-année donné via une barre oblique.

="http://cbr.ru/scripts/XML_dynamic.asp?date_req1="&ТЕКСТ(B2;"ДД/ММ/ГГГГ")&  "&date_req2="&ТЕКСТ(B3;"ДД/ММ/ГГГГ")&"&VAL_NM_RQ="&ВПР(B4;M:N;2;0)  

Étape 2. Exécutez la demande

Maintenant, nous utilisons la fonction SERVICE WEB (SERVICE WEB) avec la chaîne de requête générée comme seul argument. La réponse sera une longue ligne de code XML (il est préférable d'activer le retour à la ligne et d'augmenter la taille de la cellule si vous voulez le voir dans son intégralité) :

Étape 3. Analyser la réponse

Pour faciliter la compréhension de la structure des données de réponse, il est préférable d'utiliser l'un des analyseurs XML en ligne (par exemple, http://xpather.com/ ou https://jsonformatter.org/xml-parser), qui peut formater visuellement le code XML, en y ajoutant des retraits et en mettant en évidence la syntaxe avec de la couleur. Alors tout deviendra beaucoup plus clair :

Maintenant, vous pouvez voir clairement que les valeurs du cours sont encadrées par nos balises ..., et les dates sont des attributs Date dans les balises .

Pour les extraire, sélectionnez une colonne de dix (ou plus - si cela est fait avec une marge) cellules vides sur la feuille (car un intervalle de date de 10 jours a été défini) et entrez la fonction dans la barre de formule FILTRE.XML (FILTREXML):

Ici, le premier argument est un lien vers une cellule avec une réponse du serveur (B8), et le second est une chaîne de requête en XPath, un langage spécial qui peut être utilisé pour accéder aux fragments de code XML nécessaires et les extraire. Vous pouvez en savoir plus sur le langage XPath, par exemple, ici.

Il est important qu'après avoir entré la formule, n'appuyez pas sur Entrer, et le raccourci clavier Ctrl+Shift+Entrer, c'est-à-dire saisissez-le sous forme de formule matricielle (les accolades qui l'entourent seront ajoutées automatiquement). Si vous disposez de la dernière version d'Office 365 avec prise en charge des tableaux dynamiques dans Excel, un simple Entrer, et vous n'avez pas besoin de sélectionner des cellules vides à l'avance - la fonction elle-même prendra autant de cellules que nécessaire.

Pour extraire les dates, nous ferons de même – nous sélectionnerons plusieurs cellules vides dans la colonne adjacente et utiliserons la même fonction, mais avec une requête XPath différente, pour obtenir toutes les valeurs des attributs Date des balises Record :

=FILTRE.XML(B8;”//Enregistrement/@Date”)

À l'avenir, lors de la modification des dates dans les cellules d'origine B2 et B3 ou du choix d'une devise différente dans la liste déroulante de la cellule B3, notre requête sera automatiquement mise à jour, en se référant au serveur de la Banque centrale pour les nouvelles données. Pour forcer une mise à jour manuellement, vous pouvez en plus utiliser le raccourci clavier Ctrl+autre+F9.

• Importer le taux de bitcoin dans Excel via Power Query
• Importer des taux de change à partir d'Internet dans les anciennes versions d'Excel