Абноўлены абменны курс у Excel

Мною неаднаразова аналізаваліся спосабы імпарту дадзеных у Excel з інтэрнэту з наступным аўтаматычным абнаўленнем. У прыватнасці:

• У старых версіях Excel 2007-2013 гэта можна было зрабіць з дапамогай прамога вэб-запыту.
• Пачынаючы з 2010 года, гэта можна зрабіць вельмі зручна з надбудовай Power Query.
  

Да гэтых метадаў у апошніх версіях Microsoft Excel цяпер можна дадаць яшчэ адзін - імпарт дадзеных з Інтэрнэту ў фармаце XML з дапамогай убудаваных функцый.

XML (eXtensible Markup Language = пашыраемая мова разметкі) - гэта універсальная мова, прызначаная для апісання любых відаў даных. Па сутнасці, гэта звычайны тэкст, але з дададзенымі ў яго адмысловымі тэгамі для разметкі структуры дадзеных. Многія сайты прадастаўляюць бясплатныя патокі сваіх даных у фармаце XML для загрузкі. На сайце ЦБ нашай краіны (www.cbr.ru), у прыватнасці, з дапамогай аналагічнай тэхналогіі прыводзяцца даныя аб курсах розных валют. З сайта Маскоўскай Біржы (www.moex.com) такім жа чынам можна загрузіць каціроўкі акцый, аблігацый і шмат іншай карыснай інфармацыі.

З версіі 2013 Excel мае дзве функцыі для непасрэднай загрузкі дадзеных XML з Інтэрнэту ў ячэйкі ліста: ВЭБ-СЕРВІС (Інтэрнэт-сэрвіс) и FILTER.XML (FILTERXML). Працуюць у парах – спачатку функцыя ВЭБ-СЕРВІС выконвае запыт да жаданага сайта і вяртае яго адказ у фармаце XML, а затым з дапамогай функцыі FILTER.XML мы «разбіраем» гэты адказ на кампаненты, здабываючы з яго патрэбныя даныя.

Давайце разгледзім працу гэтых функцый на класічным прыкладзе – імпарт з сайта Цэнтрабанка нашай краіны курсу любой патрэбнай нам валюты на зададзены інтэрвал дат. Мы будзем выкарыстоўваць наступную канструкцыю ў якасці нарыхтоўкі:

Тут:

• У жоўтых клетках паказаны даты пачатку і заканчэння перыяду, які нас цікавіць.
• Сіні мае выпадальны спіс валют з дапамогай каманды Дадзеныя – Праверка – Спіс (Дадзеныя — Праверка — Спіс).
• У зялёных клетках мы будзем выкарыстоўваць нашы функцыі для стварэння радка запыту і атрымання адказу сервера.
• Табліца справа - даведка кодаў валют (яна нам спатрэбіцца крыху пазней).
  

Пойдзем!

Крок 1. Фарміраванне радка запыту

Каб атрымаць патрэбную інфармацыю з сайта, трэба яе правільна задаць. Заходзім на www.cbr.ru і адкрываем спасылку ў калантытуле галоўнай старонкі' Тэхнічныя рэсурсы'- Атрыманне дадзеных з дапамогай XML (http://cbr.ru/development/SXML/). Пракручваем крыху ніжэй, і ў другім прыкладзе (Прыклад 2) будзе тое, што нам трэба - атрыманне курсаў валют для зададзенага інтэрвалу дат:

Як відаць з прыкладу, радок запыту павінен утрымліваць даты пачатку (дата_патраб.1) і канчаткі (дата_патраб.2) перыяду, які цікавіць нас, і код валюты (VAL_NM_RQ), курс якога мы хочам атрымаць. Вы можаце знайсці асноўныя коды валют у табліцы ніжэй:

Поўны даведнік па кодах валют таксама даступны на сайце ЦБ - гл. http://cbr.ru/scripts/XML_val.asp?d=0

Цяпер мы сфармуем радок запыту ў вочку на аркушы з:

• аператар канкатэнацыі тэксту (&), каб сабраць яго разам;
  
• Асаблівасці ВПР (ВПР)знайсці ў даведніку код патрэбнай нам валюты;
  
• Асаблівасці TEXT (ТЭКСТ), які пераўтворыць дату ў адпаведнасці з зададзеным шаблонам дзень-месяц-год праз касую рысу.

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

Крок 2. Выканайце запыт

Цяпер мы выкарыстоўваем функцыю ВЭБ-СЕРВІС (Інтэрнэт-сэрвіс) са згенераваным радком запыту ў якасці адзінага аргумента. Адказам будзе доўгі радок XML-кода (лепш уключыць перанос слоў і павялічыць памер ячэйкі, калі вы хочаце бачыць яго цалкам):

Крок 3. Разбор адказу

Каб было прасцей зразумець структуру дадзеных адказу, лепш скарыстацца адным з онлайн-аналізатараў XML (напрыклад, http://xpather.com/ або https://jsonformatter.org/xml-parser), які можа візуальна фарматаваць код XML, дадаючы ў яго водступы і вылучаючы сінтаксіс колерам. Тады ўсё стане значна ясней:

Цяпер вы выразна бачыце, што значэнні курсу аформлены нашымі тэгамі ..., і даты з'яўляюцца атрыбутамі дата у тэгах .

Каб атрымаць іх, вылучыце слупок з дзесяці (ці больш - калі зроблена з запасам) пустых вочак на аркушы (таму што быў усталяваны 10-дзённы інтэрвал даты) і ўвядзіце функцыю ў радок формул FILTER.XML (ФІЛЬТРXML):

Тут першым аргументам з'яўляецца спасылка на вочка з адказам сервера (B8), а другім з'яўляецца радок запыту ў XPath, спецыяльнай мове, з дапамогай якой можна атрымаць доступ да неабходных фрагментаў кода XML і атрымаць іх. Вы можаце прачытаць больш пра мову XPath, напрыклад, тут.

Важна, каб пасля ўводу формулы не націскалі Уводзіць, і спалучэнне клавіш Ctrl+Зрух+Уводзіць, г. зн. увядзіце яго як формулу масіва (фігурныя дужкі вакол яго будуць дададзены аўтаматычна). Калі ў вас апошняя версія Office 365 з падтрымкай дынамічных масіваў у Excel, то просты Уводзіць, і вам не трэба загадзя вылучаць пустыя вочкі – функцыя сама зойме столькі вочак, колькі ёй трэба.

Каб атрымаць даты, мы зробім тое ж самае - вылучым некалькі пустых вочак у суседнім слупку і выкарыстаем тую ж функцыю, але з іншым запытам XPath, каб атрымаць усе значэнні атрыбутаў Date з тэгаў Record:

=FILTER.XML(B8;”//Запіс/@Дата”)

У далейшым пры змене дат у зыходных ячэйках B2 і B3 або выбары іншай валюты ў выпадальным спісе ячэйкі B3 наш запыт будзе аўтаматычна абнаўляцца, звяртаючыся да сервера Цэнтральнага банка для атрымання новых даных. Для прымусовага абнаўлення ўручную вы можаце дадаткова выкарыстоўваць спалучэнне клавіш Ctrl+Alt+F9.

• Імпарт курса біткойнаў у Excel праз Power Query
• Імпарт курсаў валют з Інтэрнэту ў старыя версіі Excel