Tasso di cambio aggiornato in Excel

Ho analizzato ripetutamente modi per importare dati in Excel da Internet con successivo aggiornamento automatico. In particolare:

• Nelle versioni precedenti di Excel 2007-2013, ciò poteva essere eseguito con una richiesta Web diretta.
• A partire dal 2010, questo può essere fatto molto comodamente con il componente aggiuntivo Power Query.
  

A questi metodi nelle ultime versioni di Microsoft Excel, ora puoi aggiungerne un altro: importare dati da Internet in formato XML utilizzando le funzioni integrate.

XML (eXtensible Markup Language = Extensible Markup Language) è un linguaggio universale progettato per descrivere qualsiasi tipo di dato. In effetti, è un testo normale, ma con tag speciali aggiunti per contrassegnare la struttura dei dati. Molti siti forniscono flussi gratuiti dei propri dati in formato XML che chiunque può scaricare. Sul sito web della Banca Centrale del Nostro Paese (www.cbr.ru), in particolare, con l'ausilio di una tecnologia simile, vengono forniti i dati sui tassi di cambio delle varie valute. Dal sito web della Borsa di Mosca (www.moex.com) puoi scaricare allo stesso modo quotazioni di azioni, obbligazioni e molte altre informazioni utili.

Dalla versione 2013, Excel ha due funzioni per caricare direttamente i dati XML da Internet nelle celle del foglio di lavoro: SERVIZIO WEB (SERVIZIO WEB) и FILTRO.XML (FILTROXML). Funzionano in coppia: prima la funzione SERVIZIO WEB esegue una richiesta al sito desiderato e restituisce la sua risposta in formato XML, quindi utilizzando la funzione FILTRO.XML "analizziamo" questa risposta in componenti, estraendo da essa i dati di cui abbiamo bisogno.

Diamo un'occhiata al funzionamento di queste funzioni utilizzando un classico esempio: importare il tasso di cambio di qualsiasi valuta di cui abbiamo bisogno per un determinato intervallo di date dal sito web della Banca Centrale del Nostro Paese. Useremo la seguente costruzione come spazio vuoto:

Qui:

• Le celle gialle contengono le date di inizio e fine del periodo di nostro interesse.
• Quello blu ha un elenco a discesa di valute utilizzando il comando Dati – Convalida – Elenco (Dati — Convalida — Elenco).
• Nelle celle verdi, useremo le nostre funzioni per creare una stringa di query e ottenere la risposta del server.
• La tabella a destra è un riferimento ai codici valuta (ci servirà poco dopo).
  

Andiamo!

Passaggio 1. Formazione di una stringa di query

Per ottenere le informazioni richieste dal sito, è necessario richiederle correttamente. Andiamo su www.cbr.ru e apriamo il link nel footer della pagina principale' Risorse tecniche'- Ottenere dati utilizzando XML (http://cbr.ru/development/SXML/). Scorriamo un po' più in basso e nel secondo esempio (Esempio 2) ci sarà ciò di cui abbiamo bisogno: ottenere i tassi di cambio per un determinato intervallo di date:

Come puoi vedere dall'esempio, la stringa di query deve contenere date di inizio (data_req1) e finali (data_req2) del periodo di nostro interesse e il codice valuta (VAL_NM_RQ), il tasso di cui vogliamo ottenere. Puoi trovare i principali codici valuta nella tabella seguente:

Una guida completa ai codici valuta è disponibile anche sul sito web della Banca Centrale – vedere http://cbr.ru/scripts/XML_val.asp?d=0

Ora formeremo una stringa di query in una cella su un foglio con:

• l'operatore di concatenazione del testo (&) per metterlo insieme;
  
• Caratteristiche VPR (CERCA.VERT)per trovare il codice della valuta che ci serve nell'elenco;
  
• Caratteristiche TESTO (TESTO), che converte la data in base allo schema dato giorno-mese-anno tramite una barra.

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

Passaggio 2. Eseguire la richiesta

Ora usiamo la funzione SERVIZIO WEB (SERVIZIO WEB) con la stringa di query generata come unico argomento. La risposta sarà una lunga riga di codice XML (è meglio attivare il ritorno a capo automatico e aumentare le dimensioni della cella se vuoi vederlo nella sua interezza):

Passaggio 3. Analisi della risposta

Per facilitare la comprensione della struttura dei dati di risposta, è meglio utilizzare uno dei parser XML online (ad esempio, http://xpather.com/ o https://jsonformatter.org/xml-parser), che può formattare visivamente il codice XML, aggiungendo rientri ed evidenziando la sintassi con il colore. Allora tutto diventerà molto più chiaro:

Ora puoi vedere chiaramente che i valori del corso sono incorniciati dai nostri tag ...e le date sono attributi Data nei tag .

Per estrarli, seleziona una colonna di dieci (o più, se fatto con un margine) celle vuote sul foglio (perché è stato impostato un intervallo di date di 10 giorni) e inserisci la funzione nella barra della formula FILTRO.XML (FILTROXML):

Qui, il primo argomento è un collegamento a una cella con una risposta del server (B8) e il secondo è una stringa di query in XPath, un linguaggio speciale che può essere utilizzato per accedere ai frammenti di codice XML necessari ed estrarli. Puoi leggere di più sul linguaggio XPath, ad esempio, qui.

È importante che dopo aver inserito la formula, non premere entraree la scorciatoia da tastiera Ctrl+Shift+entrare, ovvero inserirlo come una formula di matrice (le parentesi graffe attorno ad esso verranno aggiunte automaticamente). Se hai l'ultima versione di Office 365 con supporto per gli array dinamici in Excel, allora un semplice entraree non è necessario selezionare in anticipo le celle vuote: la funzione stessa richiederà tutte le celle necessarie.

Per estrarre le date, faremo lo stesso: selezioneremo diverse celle vuote nella colonna adiacente e utilizzeremo la stessa funzione, ma con una query XPath diversa, per ottenere tutti i valori degli attributi Date dai tag Record:

=FILTRO.XML(B8;”//Registra/@Data”)

Ora in futuro, quando si cambiano le date nelle celle originali B2 e B3 o si sceglie una valuta diversa nell'elenco a discesa della cella B3, la nostra query verrà aggiornata automaticamente, facendo riferimento al server della Banca centrale per i nuovi dati. Per forzare un aggiornamento manualmente, puoi anche utilizzare la scorciatoia da tastiera Ctrl+altro+F9.

• Importa bitcoin rate in Excel tramite Power Query
• Importa i tassi di cambio da Internet nelle versioni precedenti di Excel