Ažuriran kurs u Excel-u

Više puta sam analizirao načine za uvoz podataka u Excel sa interneta uz naknadno automatsko ažuriranje. posebno:

• U starijim verzijama programa Excel 2007-2013, to se može učiniti direktnim web zahtjevom.
• Počevši od 2010. godine, ovo se može vrlo povoljno uraditi sa dodatkom Power Query.
  

Ovim metodama u najnovijim verzijama Microsoft Excel-a sada možete dodati još jednu – uvoz podataka sa Interneta u XML formatu pomoću ugrađenih funkcija.

XML (eXtensible Markup Language = Extensible Markup Language) je univerzalni jezik dizajniran da opiše bilo koju vrstu podataka. U stvari, to je običan tekst, ali sa posebnim oznakama koje su mu dodane za označavanje strukture podataka. Mnoge web stranice pružaju besplatne tokove svojih podataka u XML formatu za svako preuzimanje. Na web stranici Centralne banke naše zemlje (www.cbr.ru), posebno, uz pomoć slične tehnologije, daju se podaci o kursevima raznih valuta. Sa web stranice Moskovske berze (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 direktno učitavanje XML podataka s Interneta u ćelije radnog lista: WEB SERVIS (WEBSERVICE) и FILTER.XML (FILTERXML). Rade u paru – prvo funkcija WEB SERVIS izvršava zahtjev na željenu stranicu i vraća njegov odgovor u XML formatu, a zatim pomoću funkcije FILTER.XML ovaj odgovor „raščlanjamo“ na komponente, izvlačeći iz njega podatke koji su nam potrebni.

Pogledajmo rad ovih funkcija na klasičnom primjeru – uvozom kursa bilo koje valute koja nam je potrebna za određeni datumski interval sa web stranice Centralne banke naše zemlje. Kao prazan prostor koristićemo sljedeću konstrukciju:

Ovdje:

• Žute ćelije sadrže datum početka i završetka perioda koji nas zanima.
• Plava ima padajuću listu valuta pomoću komande Podaci – Validacija – Lista (Podaci — Validacija — Lista).
• U zelenim ćelijama koristićemo naše funkcije da kreiramo niz upita i dobijemo odgovor servera.
• Tabela sa desne strane je referenca na šifre valuta (trebat će nam malo kasnije).
  

Idemo!

Korak 1. Formiranje niza upita

Da biste dobili tražene informacije sa stranice, morate ih ispravno pitati. Idemo na www.cbr.ru i otvaramo link u podnožju glavne stranice' Tehnički resursi'- Dobijanje podataka pomoću XML-a (http://cbr.ru/development/SXML/). Pomičemo se malo niže i u drugom primjeru (Primjer 2) bit će ono što nam treba – dobivanje tečajeva za dati interval datuma:

Kao što možete vidjeti iz primjera, niz upita mora sadržavati datume početka (date_req1) i završetke (date_req2) perioda koji nas zanima i šifru valute (VAL_NM_RQ), čiju stopu želimo dobiti. U tabeli ispod možete pronaći glavne kodove valuta:

Kompletan vodič za šifre valuta dostupan je i na web stranici Centralne banke – pogledajte http://cbr.ru/scripts/XML_val.asp?d=0

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

• operator konkatenacije teksta (&) da ga spoji;
  
• Značajke VPR (Vlookup)da pronađemo šifru valute koja nam je potrebna u imeniku;
  
• Značajke TEKST (TEKST), koji konvertuje datum prema datom obrascu 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 (WEBSERVICE) sa generiranim nizom upita kao jedinim argumentom. Odgovor će biti dugačak red XML koda (bolje je uključiti prelamanje riječi i povećati veličinu ćelije ako želite da je vidite u cijelosti):

Korak 3. Raščlanjivanje odgovora

Da 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 vizuelno formatirati XML kod, dodajući mu uvlake i naglašavajući sintaksu bojom. Tada će sve postati mnogo jasnije:

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

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

Ovdje je prvi argument veza do ćelije sa odgovorom servera (B8), a drugi je string upita u XPath-u, posebnom jeziku koji se može koristiti za pristup potrebnim fragmentima XML koda i njihovo izdvajanje. Više o XPath jeziku možete pročitati, na primjer, ovdje.

Važno je da nakon unosa formule ne pritiskate ući, i prečicu na tastaturi Ctrl+smjena+ući, tj. unesite ga kao formulu niza (vitičaste zagrade oko njega će se automatski dodati). Ako imate najnoviju verziju Office 365 s podrškom za dinamičke nizove u Excelu, onda jednostavno ući, i ne morate unaprijed birati prazne ćelije – sama funkcija će uzeti onoliko ćelija koliko joj je potrebno.

Da bismo izdvojili datume, učinit ćemo isto – izabrat ćemo nekoliko praznih ćelija u susjednoj koloni i koristiti istu funkciju, ali s drugačijim XPath upitom, da dobijemo sve vrijednosti atributa datuma 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 u padajućoj listi ćelije B3, naš upit će se automatski ažurirati, upućivanjem na server Centralne banke za nove podatke. Da biste ručno izvršili ažuriranje, možete dodatno koristiti prečicu na tastaturi Ctrl+alt+F9.

• Uvezite bitcoin stopu u Excel putem Power Queryja
• Uvezite kurseve sa Interneta u starije verzije Excel-a