Tassu di scambiu aghjurnatu in Excel

Aghju analizatu ripetutamente modi per impurtà dati in Excel da Internet cù l'aghjurnamentu automaticu sussegwente. In particulare:

• In e versioni più vechje di Excel 2007-2013, questu puderia esse fattu cù una dumanda web diretta.
• A partire da 2010, questu pò esse fattu assai cunvene cù l'add-in Power Query.
  

À questi metudi in l'ultime versioni di Microsoft Excel, pudete avà aghjunghje un altru - importazione di dati da Internet in u formatu XML cù e funzioni integrate.

XML (eXtensible Markup Language = Extensible Markup Language) hè una lingua universale pensata per descriverà ogni tipu di dati. In fatti, hè testu chjaru, ma cù tag spiciali aghjuntu à ellu per marcà a struttura di dati. Parechji siti furniscenu flussi gratuiti di e so dati in u furmatu XML per qualcunu per scaricà. In u situ web di u Bancu Centrale di u Nostru Paese (www.cbr.ru), in particulare, cù l'aiutu di una tecnulugia simili, dati nantu à i tassi di scambiu di diverse valute sò datu. Da u situ web di Moscow Exchange (www.moex.com) pudete scaricà quotes per azioni, bonds è assai altre informazioni utili in u listessu modu.

Dapoi a versione 2013, Excel hà duie funzioni per carica direttamente dati XML da Internet in e cellule di u fogliu di travagliu: SERVIZU WEB (SERVIZU WEB) и FILTRE.XML (FILTERXML). U travagliu in parechje - prima a funzione SERVIZU WEB esegue una dumanda à u situ desideratu è torna a so risposta in u formatu XML, è dopu aduprà a funzione FILTRE.XML avemu "parse" sta risposta in cumpunenti, extracting i dati chì avemu bisognu da ellu.

Fighjemu u funziunamentu di sti funzioni cù un esempiu classicu - impurtà u tassu di scambiu di ogni valuta chì avemu bisognu per un intervallu di data da u situ web di u Bancu Centrale di u Nostru Paese. Adupremu a seguente custruzzione cum'è un biancu:

Eccu:

• I celi gialli cuntenenu e date di principiu è di fine di u periodu di interessu per noi.
• U turchinu hà una lista drop-down di muniti chì utilizanu u cumandamentu Dati - Validazione - Lista (Dati - Validazione - Lista).
• In e cellule verdi, useremu e nostre funzioni per creà una stringa di dumanda è uttene a risposta di u servitore.
• A tavula à a diritta hè una riferenza à i codici di valuta (avemu bisognu di un pocu dopu).
  

Andemu!

Passu 1. Formazione di una stringa di quistione

Per uttene l'infurmazioni necessarii da u situ, avete bisognu di dumandà bè. Andemu à www.cbr.ru è apre u ligame in u footer di a pagina principale' Risorse tecniche - Ottene dati cù XML (http://cbr.ru/development/SXML/). Scorremu un pocu più bassu è in u sicondu esempiu (Esempiu 2) ci sarà ciò chì avemu bisognu - ottenendu i tassi di scambiu per un intervallu di data:

Comu pudete vede da l'esempiu, a stringa di dumanda deve cuntene date di iniziu (data_req1) e desinenze (data_req2) di u periodu di interessu per noi è u codice di valuta (VAL_NM_RQ), a tarifa di quale vulemu ottene. Pudete truvà i codici di valuta principali in u tavulu sottu:

Una guida cumpleta di i codici di valuta hè ancu dispunibule nantu à u situ web di u Bancu Centrale - vede http://cbr.ru/scripts/XML_val.asp?d=0

Avà formaremu una stringa di dumanda in una cellula nantu à una foglia cù:

• l'operatore di concatenazione di testu (&) per mette inseme;
  
• Features VPR (CERCA V)per truvà u codice di a valuta chì avemu bisognu in u cartulare;
  
• Features TEXT (TESTU), chì cunverte a data secondu u mudellu datu ghjornu-mese-annu attraversu un slash.

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

Step 2. Execute a dumanda

Avà avemu aduprà a funzione SERVIZU WEB (SERVIZU WEB) cù a stringa di dumanda generata cum'è solu argumentu. A risposta serà una longa fila di codice XML (hè megliu attivà l'embiu di a parolla è aumentà a dimensione di a cellula se vulete vedà in tuttu):

Step 3. Parsing a risposta

Per fà più faciule per capiscenu a struttura di i dati di risposta, hè megliu aduprà unu di l'analizatori XML in linea (per esempiu, http://xpather.com/ o https://jsonformatter.org/xml-parser), chì ponu formate visualmente u codice XML, aghjunghjendu indentazioni è mette in risaltu a sintassi cù u culore. Allora tuttu diventerà assai più chjaru:

Avà pudete vede chjaramente chì i valori di u cursu sò inquadrati da i nostri tag ..., è e date sò attributi Date in tag .

Per estrarli, selezziunate una colonna di deci (o più - se fate cù un marghjenu) celluli vacanti nantu à u fogliu (perchè un intervalu di data di 10 ghjorni hè statu stabilitu) è inserite a funzione in a barra di formula. FILTRE.XML (FILTREXML):

Quì, u primu argumentu hè un ligame à una cellula cù una risposta di u servitore (B8), è u sicondu hè una stringa di query in XPath, una lingua speciale chì pò esse usata per accede à i frammenti di codice XML necessarii è estratti. Pudete leghje più nantu à a lingua XPath, per esempiu, quì.

Hè impurtante chì dopu avè intrutu in a formula, ùn appughjà micca Rinsignate, è a scurciatoia di tastiera Ctrl+Shift+Rinsignate, vale à dì inserisci cum'è una formula di array (i curly braces around it will be added automaticamente). Se avete l'ultima versione di Office 365 cù supportu per arrays dinamichi in Excel, allora un simplice Rinsignate, è ùn avete micca bisognu di selezziunà e cellule viote in anticipu - a funzione stessu piglià tante cellule quant'è bisognu.

Per caccià e date, faremu u listessu - selezziunate parechje cellule viote in a colonna adiacente è aduprà a listessa funzione, ma cù una dumanda XPath differente, per uttene tutti i valori di l'attributi Data da i tags Record:

=FILTER.XML(B8;”//Record/@Date”)

Avà in u futuru, quandu cambiate e date in i celi originali B2 è B3 o scegliendu una valuta diversa in a lista di a lista di a cellula B3, a nostra dumanda serà aghjurnata automaticamente, riferite à u servitore di u Bancu Centrale per novi dati. Per furzà un aghjurnamentu manualmente, pudete ancu aduprà a scurciatoia di tastiera Ctrl+Alt+F9.

• Import bitcoin rate to Excel via Power Query
• Importa i tassi di scambiu da Internet in versioni più vechje di Excel