Atnaujintas valiutos kursas programoje Excel

Ne kartą analizavau būdus, kaip importuoti duomenis į „Excel“ iš interneto su vėlesniu automatiniu atnaujinimu. Visų pirma:

• Senesnėse 2007–2013 m. „Excel“ versijose tai galima padaryti tiesiogine žiniatinklio užklausa.
• Nuo 2010 m. tai labai patogiai galima padaryti naudojant Power Query priedą.
  

Prie šių metodų naujausiose „Microsoft Excel“ versijose dabar galite pridėti dar vieną – duomenų importavimą iš interneto XML formatu naudojant įmontuotas funkcijas.

XML (eXtensible Markup Language = Extensible Markup Language) yra universali kalba, skirta apibūdinti bet kokio tipo duomenis. Tiesą sakant, tai yra paprastas tekstas, tačiau prie jo pridėtos specialios žymos, skirtos duomenų struktūrai pažymėti. Daugelis svetainių teikia nemokamus savo duomenų srautus XML formatu, kad kiekvienas galėtų juos atsisiųsti. Mūsų šalies centrinio banko svetainėje (www.cbr.ru), visų pirma, naudojant panašią technologiją, pateikiami duomenys apie įvairių valiutų kursus. Iš Maskvos biržos svetainės (www.moex.com) tokiu pat būdu galite atsisiųsti akcijų, obligacijų kotiruotes ir daug kitos naudingos informacijos.

Nuo 2013 m. versijos „Excel“ turi dvi funkcijas, skirtas tiesiogiai įkelti XML duomenis iš interneto į darbalapio langelius: INTERNETO PASLAUGA (INTERNETO PASLAUGA) и FILTRAS.XML (FILTERXML). Jie dirba poromis – pirmiausia funkcija INTERNETO PASLAUGA įvykdo užklausą norimai svetainei ir grąžina atsakymą XML formatu, o tada naudoja funkciją FILTRAS.XML „išanalizuojame“ šį atsakymą į komponentus, išgaudami iš jo mums reikalingus duomenis.

Pažvelkime į šių funkcijų veikimą pasitelkdami klasikinį pavyzdį – bet kurios mums reikalingos valiutos kurso importavimą tam tikram datos intervalui iš mūsų šalies centrinio banko svetainės. Kaip ruošinį naudosime šią konstrukciją:

Čia:

• Geltonuose langeliuose yra mus dominančio laikotarpio pradžios ir pabaigos datos.
• Mėlynajame yra išskleidžiamasis valiutų sąrašas naudojant komandą Duomenys – Patvirtinimas – Sąrašas (Duomenys – patvirtinimas – sąrašas).
• Žaliuosiuose langeliuose naudosime savo funkcijas, kad sukurtume užklausos eilutę ir gautume serverio atsakymą.
• Lentelėje dešinėje yra nuoroda į valiutų kodus (jos mums prireiks šiek tiek vėliau).
  

Eime!

1 veiksmas. Užklausos eilutės formavimas

Norėdami gauti reikiamą informaciją iš svetainės, turite jos teisingai paklausti. Einame į www.cbr.ru ir atidarome nuorodą pagrindinio puslapio poraštėje. Techniniai ištekliai - Duomenų gavimas naudojant XML (http://cbr.ru/development/SXML/). Slenkame šiek tiek žemiau ir antrame pavyzdyje (2 pavyzdys) bus tai, ko mums reikia – gauti tam tikros datos intervalo valiutų kursus:

Kaip matote iš pavyzdžio, užklausos eilutėje turi būti pradžios datos (data_req1) ir pabaigos (data_req2) mus dominančio laikotarpio ir valiutos kodą (VAL_NM_RQ), kurio normą norime gauti. Pagrindinius valiutų kodus galite rasti toliau pateiktoje lentelėje:

Išsamų valiutų kodų vadovą taip pat galima rasti Centrinio banko svetainėje – žr. http://cbr.ru/scripts/XML_val.asp?d=0

Dabar lapo langelyje suformuosime užklausos eilutę su:

• teksto sujungimo operatorius (&), kad jį sujungtumėte;
  
• Savybės VPR (ŽIŪRĖTI)kataloge rasti mums reikalingos valiutos kodą;
  
• Savybės TEKSTAS (TEKSTAS), kuris konvertuoja datą pagal pateiktą modelį diena-mėnuo-metai per pasvirąjį brūkšnį.

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

2 veiksmas. Vykdykite užklausą

Dabar mes naudojame funkciją INTERNETO PASLAUGA (INTERNETO PASLAUGA) su generuota užklausos eilute kaip vieninteliu argumentu. Atsakymas bus ilga XML kodo eilutė (geriau įjungti žodžių laužymą ir padidinti langelio dydį, jei norite jį matyti visą):

3 veiksmas. Atsakymo analizė

Kad būtų lengviau suprasti atsakymų duomenų struktūrą, geriau naudoti vieną iš internetinių XML analizatorių (pvz., http://xpather.com/ arba https://jsonformatter.org/xml-parser), kuri gali vizualiai formatuoti XML kodą, pridedant prie jo įtraukas ir paryškinant sintaksę spalva. Tada viskas taps daug aiškiau:

Dabar aiškiai matote, kad kurso vertės yra įrėmintos mūsų žymomis ..., o datos yra atributai Data žymose .

Norėdami juos išskleisti, lape pasirinkite dešimties (ar daugiau – jei tai daroma su parašte) tuščių langelių stulpelį (nes buvo nustatytas 10 dienų datos intervalas) ir formulės juostoje įveskite funkciją FILTRAS.XML (FILTRASXML):

Čia pirmasis argumentas yra nuoroda į langelį su serverio atsaku (B8), o antrasis yra užklausos eilutė XPath, specialioje kalboje, kurią galima naudoti norint pasiekti reikiamus XML kodo fragmentus ir juos išgauti. Pavyzdžiui, daugiau apie XPath kalbą galite perskaityti čia.

Svarbu, kad įvedus formulę nespausti įeitiir spartusis klavišas "Ctrl"+perėjimas+įeiti, ty įveskite jį kaip masyvo formulę (aplink jį esantys skliaustai bus pridėti automatiškai). Jei turite naujausią „Office 365“ versiją su „Excel“ dinaminių masyvų palaikymu, tai paprasta įeiti, ir nereikia iš anksto pasirinkti tuščių langelių – pati funkcija užims tiek langelių, kiek jai reikia.

Norėdami išgauti datas, darysime tą patį – pasirinksime keletą tuščių langelių gretimame stulpelyje ir naudosime tą pačią funkciją, bet su kita XPath užklausa, kad gautume visas datos atributų reikšmes iš įrašo žymų:

=FILTER.XML(B8;”//Įrašyti/@Data)

Ateityje, keičiant datas pradiniuose langeliuose B2 ir B3 arba pasirenkant kitą valiutą langelio B3 išskleidžiamajame sąraše, mūsų užklausa bus automatiškai atnaujinta, kreipiantis į Centrinio banko serverį dėl naujų duomenų. Norėdami priverstinai atnaujinti rankiniu būdu, galite papildomai naudoti sparčiuosius klavišus "Ctrl"+Kitas+F9.

• Importuokite bitcoin kursą į „Excel“ naudodami „Power Query“.
• Importuokite valiutų kursus iš interneto senesnėse „Excel“ versijose