Ažurirani tečaj u Excelu

Opetovano sam analizirao načine uvoza podataka u Excel s interneta uz naknadno automatsko ažuriranje. Posebno:

• U starijim verzijama Excela 2007-2013 to se moglo učiniti izravnim web zahtjevom.
• Počevši od 2010., to se može učiniti vrlo praktično s dodatkom Power Query.
  

Ovim metodama u najnovijim verzijama Microsoft Excela sada možete dodati još jednu – uvoz podataka s interneta u XML formatu pomoću ugrađenih funkcija.

XML (eXtensible Markup Language = Proširivi označni jezik) je univerzalni jezik dizajniran za opisivanje bilo koje vrste podataka. Zapravo, to je običan tekst, ali s dodanim posebnim oznakama za označavanje strukture podataka. Mnoga mjesta nude besplatne tokove svojih podataka u XML formatu za preuzimanje. Na web stranici Središnje banke naše zemlje (www.cbr.ru), posebno, uz pomoć slične tehnologije, dani su podaci o tečajevima različitih valuta. Na web stranici Moskovske burze (www.moex.com) na isti način možete preuzeti kotacije za dionice, obveznice i mnoge druge korisne informacije.

Od verzije 2013. Excel ima dvije funkcije za izravno učitavanje XML podataka s interneta u ćelije radnog lista: WEB SERVIS (WEBSERVIS) и FILTER.XML (FILTERXML). Rade u parovima – prvo funkcija WEB SERVIS izvršava zahtjev prema željenoj stranici i vraća svoj odgovor u XML formatu, a zatim pomoću funkcije FILTER.XML mi "raščlanjujemo" ovaj odgovor na komponente, izvlačeći iz njega podatke koji su nam potrebni.

Pogledajmo rad ovih funkcija na klasičnom primjeru – uvozom tečaja bilo koje valute koja nam je potrebna za određeni datumski interval s web stranice Središnje banke naše zemlje. Koristit ćemo sljedeću konstrukciju kao prazninu:

Ovdje:

• Žuta polja sadrže datume početka i završetka razdoblja koje nas zanima.
• Plavi ima padajući popis valuta pomoću naredbe Podaci – Provjera – Popis (Podaci — Provjera — Popis).
• U zelenim ćelijama koristit ćemo naše funkcije za stvaranje niza upita i dobivanje odgovora poslužitelja.
• Tablica s desne strane je referenca na kodove valuta (trebat će nam malo kasnije).
  

Idemo!

Korak 1. Formiranje niza upita

Da biste dobili potrebne informacije sa stranice, morate ih ispravno pitati. Idemo na www.cbr.ru i otvorimo vezu u podnožju glavne stranice' Tehnički resursi'- Dobivanje podataka pomoću XML-a (http://cbr.ru/development/SXML/). Skrolamo malo niže i u drugom primjeru (Primjer 2) bit će ono što nam treba – dobivanje tečaja za određeni datumski interval:

Kao što možete vidjeti iz primjera, niz upita mora sadržavati datume početka (datum_req1) i završeci (datum_req2) razdoblja koje nas zanima i kod valute (VAL_NM_RQ), stopu koju želimo dobiti. Glavne kodove valuta možete pronaći u tablici u nastavku:

Kompletan vodič za kodove valuta također je dostupan na web stranici Središnje banke – pogledajte http://cbr.ru/scripts/XML_val.asp?d=0

Sada ćemo oblikovati niz upita u ćeliji na listu sa:

• operator ulančavanja teksta (&) za sastavljanje;
  
• Značajke VPR (VLOOKUP)pronaći šifru valute koja nam je potrebna u imeniku;
  
• Značajke TEKST (TEKST), koji pretvara datum prema zadanom uzorku dan-mjesec-godina kroz kosu crtu.

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

Korak 2. Izvršite zahtjev

Sada koristimo funkciju WEB SERVIS (WEBSERVIS) s generiranim nizom upita kao jedinim argumentom. Odgovor će biti dugačak redak XML koda (bolje je uključiti prelom riječi i povećati veličinu ćelije ako ga želite vidjeti u cijelosti):

Korak 3. Raščlanjivanje odgovora

Kako biste lakše razumjeli strukturu podataka odgovora, bolje je koristiti jedan od online XML parsera (na primjer, http://xpather.com/ ili https://jsonformatter.org/xml-parser), koji može vizualno oblikovati XML kod, dodajući mu uvlake i ističući sintaksu bojom. Tada će sve biti puno jasnije:

Sada možete jasno vidjeti da su vrijednosti tečaja uokvirene našim oznakama ..., a datumi su atributi Datum u oznakama .

Da biste ih izdvojili, odaberite stupac od deset (ili više – ako ste učinili s marginom) praznih ćelija na listu (jer je postavljen datumski interval od 10 dana) i unesite funkciju u traku formule FILTER.XML (FILTARXML):

Ovdje je prvi argument poveznica na ćeliju s odgovorom poslužitelja (B8), a drugi je niz upita u XPathu, posebnom jeziku koji se može koristiti za pristup potrebnim fragmentima XML koda i njihovo izdvajanje. Više o XPath jeziku, na primjer, možete pročitati ovdje.

Važno je da nakon unosa formule ne pritisnete ućii tipkovni prečac Ctrl+smjena+ući, tj. unesite ga kao formulu polja (automatski će se dodati vitičaste zagrade oko njega). Ako imate najnoviju verziju sustava Office 365 s podrškom za dinamičke nizove u Excelu, onda jednostavno ući, a ne morate unaprijed označavati prazne ćelije – funkcija će sama uzeti onoliko ćelija koliko treba.

Da bismo izdvojili datume, učinit ćemo isto – odabrat ćemo nekoliko praznih ćelija u susjednom stupcu i upotrijebiti istu funkciju, ali s drugačijim XPath upitom, kako bismo dobili sve vrijednosti atributa Datum iz oznaka Record:

=FILTER.XML(B8;”//Zapis/@Datum”)

Sada u budućnosti, kada promijenite datume u originalnim ćelijama B2 i B3 ili odaberete drugu valutu na padajućem popisu ćelije B3, naš će se upit automatski ažurirati, pozivajući se na poslužitelj središnje banke za nove podatke. Za ručno prisilno ažuriranje možete dodatno koristiti tipkovni prečac Ctrl+drugo+F9.

• Uvezite tečaj bitcoina u Excel putem Power Queryja
• Uvezite tečajnu listu s interneta u starije verzije Excela