EXCEL: Загружаем курсы валют с сайта ЦБ через функцию ВЕБСЛУЖБА или VBA

09 марта 2022   Сергей Кикевич   Все авторы

Приложение к статье:
файл для скачивания

В EXCEL 2013 появилась замечательная функция ВЕБСЛУЖБА (WEBSERVICE). А старый способ параметризованных запросов через web query перестал работать. Поэтому я решил обновить информацию о том, как автоматически получать историю курсов валют с сайта Центрального банка ( cbr.ru ).

Впрочем, не всё так радужно … у команды ВЕБСЛУЖБА есть свои проблемы. Но обо всём по порядку.

Загрузка курсов валют через ВЕБСЛУЖБА

Сайт cbr.ru автоматически отдает информацию о курсах валют в формате XLM через нехитрый запрос (справка по API на сайте ЦБ):

http://www.cbr.ru/scripts/XML_dynamic.asp?date_req1=01.01.2022&date_req2=01.02.2022&VAL_NM_RQ=R01235

(можете скопировать в адресную строку браузера и посмотреть, что получится)

В запросе сразу видно, что нужно параметризировать, чтобы задать первую дату истории, последнюю дату и код валюты. R01235 – долл США (справочник по кодам валют ЦБ).

Для успешной загрузки данных в EXCEL необходимо вставить URL запроса в функцию ВЕБСЛУЖБА().

Удобства ради вставим ссылки на ячейки вместо дат и кода валюты:

ВЕБСЛУЖБА(“http://www.cbr.ru/scripts/XML_dynamic.asp?date_req1=”
&ТЕКСТ(A1;”ДД.ММ.ГГГГ”)&
”&date_req2=”
& ТЕКСТ(A2;”ДД.ММ.ГГГГ”)&
”&VAL_NM_RQ=”&A3)

Здесь потребовалось использовать функцию ТЕКСТ() для преобразования даты EXCEL в текстовый формат.

На выходе функции ВЕБСЛУЖБА получаем формат XML, который содержит историю данных котировок валюты.

С XML отлично справляется функция ФИЛЬТР.XML (FILTERXML). Она использует язык запросов XPath. Сам по себе этот язык несложный и очень полезный. Он позволяется легко «вытащить» нужную информацию из узлов XML документов. Если интересно познакомиться с ним поближе, пройдите несколько шагов обучения в W3Schools: XPath Syntax.

После обработки получаем то, за чем пришли:

Магия … если бы не одно «НО».

Сайт cbr.ru на запрос отвечает с некоторой задержкой. Когда, запрашивается небольшое количество данных, задержка небольшая, и всё нормально работает. Но, если глубина запрашиваемой истории значительная, то ВЕБСЛУЖБА реагирует на задержку, как на отказ API. Вместо красивой таблицы с данными получаем сообщение об ошибке: #ЗНАЧ. Похоже, что с этим поделать ничего нельзя, так как timeout в команде ВЕБСЛУЖБА не настраивается.

Получается, что для запроса значительного объема информации этот способ не годится. Тем не менее файл с образцом запроса вы можете скачать в конце статьи.

Загрузка курсов валют через VBA

Как обычно, когда начинаются сложности со встроенными функциями, на помощь приходит VBA. Небольшой скрипт помогает скачать любую глубину истории. Кроме того, можно выбрать между месячными и дневными данными.

Про то, как работает макрос на Visual Basic, я писать не буду. Это явно выходит за рамки этой статьи. Но разобраться в этом несложном скрипте может даже неспециалист.

В следующей статье, я продемонстрирую, как сделать такую же загрузку курсов валют в Таблицах Google (Google Sheets).

Примеры использования ВЕБСЛУЖБА и VBA для загрузки курсов валют ЦБ

Ниже вы можете загрузить примеры использования функции ВЕБСЛУЖБА и вариант с VBA для загрузки официальных курсов валют Центрального банка.

Внимание: прилагаемый файл EXCEL со скриптом VBA содержит макрос и предоставляется в формате .xlsm . Загрузка этого файла может быть заблокирована антивирусом, если он у вас установлен.

 

---

Оставьте комментарий

Написать комментарий

Автор

Email

Введите сумму 10 + 1