Оновлений курс валют в 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) буде те, що нам потрібно – отримання курсів валют для заданого інтервалу дат:

Як видно з прикладу, рядок запиту повинен містити дати початку (date_req1) і закінчення (date_req2) періоду, який нас цікавить, і код валюти (VAL_NM_RQ), курс якого ми хочемо отримати. Ви можете знайти основні коди валют у таблиці нижче:

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

Тепер ми сформуємо рядок запиту в комірці на аркуші за допомогою:

• оператор конкатенації тексту (&), щоб об’єднати його;
  
• риси VPR (ВПР)знайти в довіднику код потрібної нам валюти;
  
• риси 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+Shift+ Що натомість? Створіть віртуальну версію себе у , тобто введіть його як формулу масиву (фігурні дужки навколо нього буде додано автоматично). Якщо у вас остання версія Office 365 з підтримкою динамічних масивів в Excel, то просто Що натомість? Створіть віртуальну версію себе у , і вам не потрібно заздалегідь виділяти порожні комірки – функція сама візьме стільки комірок, скільки їй потрібно.

Щоб отримати дати, ми зробимо те ж саме – ми виберемо кілька порожніх клітинок у сусідньому стовпці та використаємо ту ж функцію, але з іншим запитом XPath, щоб отримати всі значення атрибутів Date з тегів Record:

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

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

• Імпортуйте курс біткойна в Excel за допомогою Power Query
• Імпорт курсів валют з Інтернету в старих версіях Excel