Cursul de schimb actualizat în Excel

Am analizat în mod repetat modalități de a importa date în Excel de pe Internet cu actualizarea automată ulterioară. În special:

• În versiunile mai vechi de Excel 2007-2013, acest lucru se poate face cu o solicitare web directă.
• Începând din 2010, acest lucru se poate face foarte convenabil cu programul de completare Power Query.
  

La aceste metode din cele mai recente versiuni de Microsoft Excel, acum puteți adăuga o alta - importul de date de pe Internet în format XML folosind funcții încorporate.

XML (eXtensible Markup Language = Extensible Markup Language) este un limbaj universal conceput pentru a descrie orice tip de date. De fapt, este text simplu, dar cu etichete speciale adăugate pentru a marca structura datelor. Multe site-uri oferă fluxuri gratuite de date în format XML pe care oricine le poate descărca. Pe site-ul Băncii Centrale a Țării Noastre (www.cbr.ru), în special, cu ajutorul unei tehnologii similare, sunt date date despre cursurile de schimb ale diferitelor valute. De pe site-ul Moscow Exchange (www.moex.com) puteți descărca cotații pentru acțiuni, obligațiuni și multe alte informații utile în același mod.

Din versiunea 2013, Excel are două funcții pentru încărcarea directă a datelor XML de pe Internet în celulele foii de lucru: SERVICIU WEB (SERVICIU WEB) и FILTER.XML (FILTERXML). Ei lucrează în perechi – mai întâi funcția SERVICIU WEB execută o solicitare către site-ul dorit și returnează răspunsul acesteia în format XML, apoi utilizează funcția FILTER.XML „analăm” acest răspuns în componente, extragând datele de care avem nevoie din el.

Să ne uităm la funcționarea acestor funcții folosind un exemplu clasic – importul cursului de schimb al oricărei monede de care avem nevoie pentru un anumit interval de date de pe site-ul Băncii Centrale a Țării Noastre. Vom folosi următoarea construcție ca spațiu liber:

Aici:

• Celulele galbene conțin datele de început și de sfârșit ale perioadei care ne interesează.
• Cel albastru are o listă derulantă de valute folosind comanda Date – Validare – Lista (Date — Validare — Listă).
• În celulele verzi, vom folosi funcțiile noastre pentru a crea un șir de interogare și a obține răspunsul serverului.
• Tabelul din dreapta este o referință la codurile valutare (vom avea nevoie de el puțin mai târziu).
  

Sa mergem!

Pasul 1. Formarea unui șir de interogare

Pentru a obține informațiile necesare de pe site, trebuie să le solicitați corect. Mergem pe www.cbr.ru și deschidem linkul din subsolul paginii principale' Resurse tehnice'- Obținerea datelor folosind XML (http://cbr.ru/development/SXML/). Derulăm puțin mai jos și în al doilea exemplu (Exemplul 2) va fi ceea ce avem nevoie – obținerea cursurilor de schimb pentru un anumit interval de date:

După cum puteți vedea din exemplu, șirul de interogare trebuie să conțină date de început (data_req1) și terminații (data_req2) din perioada care ne interesează și codul monedei (VAL_NM_RQ), rata pe care vrem să o obținem. Puteți găsi principalele coduri valutare în tabelul de mai jos:

Un ghid complet al codurilor valutare este disponibil și pe site-ul Băncii Centrale – vezi http://cbr.ru/scripts/XML_val.asp?d=0

Acum vom forma un șir de interogare într-o celulă de pe o foaie cu:

• operatorul de concatenare a textului (&) pentru a-l pune împreună;
  
• DESCRIERE VPR (CĂUTARE V)pentru a găsi codul monedei de care avem nevoie în director;
  
• DESCRIERE TEXT (TEXT), care convertește data conform modelului dat zi-lună-an printr-o bară oblică.

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

Pasul 2. Executați cererea

Acum folosim funcția SERVICIU WEB (SERVICIU WEB) cu șirul de interogare generat ca singur argument. Răspunsul va fi un lung șir de cod XML (este mai bine să activați încadrarea cuvintelor și să măriți dimensiunea celulei dacă doriți să o vedeți în întregime):

Pasul 3. Analizarea răspunsului

Pentru a facilita înțelegerea structurii datelor de răspuns, este mai bine să utilizați unul dintre analizatorii XML online (de exemplu, http://xpather.com/ sau https://jsonformatter.org/xml-parser), care poate formata vizual codul XML, adăugându-i indentări și evidențiind sintaxa cu culoare. Atunci totul va deveni mult mai clar:

Acum puteți vedea clar că valorile cursului sunt încadrate de etichetele noastre ..., iar datele sunt atribute Data în etichete .

Pentru a le extrage, selectați o coloană de zece (sau mai multe - dacă ați făcut cu o marjă) celule goale pe foaie (deoarece a fost setat un interval de date de 10 zile) și introduceți funcția în bara de formule FILTER.XML (FILTRUXML):

Aici, primul argument este o legătură către o celulă cu un răspuns de server (B8), iar al doilea este un șir de interogare în XPath, un limbaj special care poate fi folosit pentru a accesa fragmentele de cod XML necesare și pentru a le extrage. Puteți citi mai multe despre limbajul XPath, de exemplu, aici.

Este important ca după introducerea formulei să nu apăsați Intrați, și comanda rapidă de la tastatură Ctrl+Schimba+Intrați, adică introduceți-o ca formulă matrice (acoladele din jurul acesteia vor fi adăugate automat). Dacă aveți cea mai recentă versiune de Office 365 cu suport pentru matrice dinamice în Excel, atunci un simplu Intrați, și nu trebuie să selectați celulele goale în avans – funcția în sine va lua atâtea celule cât are nevoie.

Pentru a extrage datele, vom face același lucru – vom selecta mai multe celule goale în coloana alăturată și vom folosi aceeași funcție, dar cu o interogare XPath diferită, pentru a obține toate valorile atributelor Date din etichetele Record:

=FILTER.XML(B8;”//Înregistrare/@Data”)

Acum, în viitor, atunci când schimbați datele în celulele originale B2 și B3 sau alegeți o altă monedă din lista derulantă a celulei B3, interogarea noastră va fi actualizată automat, referindu-se la serverul Băncii Centrale pentru date noi. Pentru a forța o actualizare manual, puteți utiliza suplimentar comanda rapidă de la tastatură Ctrl+Alt+F9.

• Importați rata bitcoin în Excel prin Power Query
• Importați cursuri de schimb de pe Internet în versiuni mai vechi de Excel