Обновлен обменный курс в 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-данных из Интернета в ячейки листа: ВЕБ-СЕРВИС (ВЕБ-СЕРВИС) и ФИЛЬТР.XML (ФИЛЬТРXML). Они работают парами – сначала функция ВЕБ-СЕРВИС выполняет запрос к нужному сайту и возвращает его ответ в формате XML, а затем с помощью функции ФИЛЬТР.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.

Теперь мы сформируем строку запроса в ячейке на листе с помощью:

• оператор конкатенации текста (&), чтобы соединить его;
  
• Особенности ВПР (ВПР)найти в справочнике код нужной нам валюты;
  
• Особенности ТЕКСТ (ТЕКСТ), который преобразует дату в соответствии с заданным шаблоном день-месяц-год через косую черту.

="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-дневный интервал дат) и введите функцию в строку формул. ФИЛЬТР.XML (ФИЛЬТРXML):

Здесь первый аргумент — это ссылка на ячейку с ответом сервера (B8), а второй — строка запроса в XPath — специальном языке, с помощью которого можно получить доступ к необходимым фрагментам XML-кода и извлечь их. Подробнее о языке XPath можно прочитать, например, здесь.

Важно после ввода формулы не нажимать Enterи сочетание клавиш Ctrl+Shift+Enter, т.е. введите его как формулу массива (фигурные скобки вокруг него будут добавлены автоматически). Если у вас последняя версия Office 365 с поддержкой динамических массивов в Excel, то простой Enter, и вам не нужно заранее выделять пустые ячейки — функция сама возьмет столько ячеек, сколько ей нужно.

Для извлечения дат сделаем то же самое — выделим несколько пустых ячеек в соседнем столбце и воспользуемся той же функцией, но с другим запросом XPath, чтобы получить все значения атрибутов Date из тегов Record:

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

Теперь в дальнейшем при изменении дат в исходных ячейках B2 и B3 или выборе другой валюты в выпадающем списке ячейки B3 наш запрос будет автоматически обновляться, обращаясь к серверу ЦБ за новыми данными. Чтобы принудительно выполнить обновление вручную, вы можете дополнительно использовать сочетание клавиш Ctrl+другой+F9.

• Импортируйте курс биткойнов в Excel через Power Query.
• Импортируйте курсы валют из Интернета в более старых версиях Excel.