В комментариях к прошлому посту возникла жалоба одного пользователя Excel на отсутствие рабочих примеров макросов, которые загружают курсы валют с сайта ЦБ, и при этом формулы обновлялись бы автоматически, подтягивая актуальный курс на текущую дату. Там же в комментариях набросал вариант решения (вот готовый пример), но подумал, что отдельный пост на эту тему поможет и другим пользователям, столкнувшимся с подобной задачей.
Итак, на сайте ЦБ РФ находим официальные API для загрузки динамики курсов (на случай, если нам потребуется загружать курс на определенную дату в прошлом):
HTTP GET-запрос вида
http://www.cbr.ru/scripts/XML_dynamic.asp?date_req1=02%2F03%...
где R01235 - это код валюты (доллар США). Список кодов валют для указания в запросе также доступен.
API возвращает данные в XML-формате, и для нас это даже удобнее, чем JSON - так как в VBA есть поддержка XML, а для парсинга JSON нужны сторонние библиотеки. Вот пример ответа:
Как видим, мы получили список курсов на каждую дату в интервале, с указанием кода валюты - именно то, что нам нужно.
Теперь попробуем сделать такой запрос из VBA. Для начала подключим в нашей книге ссылку на зависимость MSXML 6.0 (если у вас доступна другая версия, ничего страшного - могут немного поменяться имена типов, в целом логика такая же)
Заодно добавим ссылку на Microsoft Scripting Runtime - оттуда мы сможем использовать тип Dictionary для хранения соответствий кодов валют (USD -> R01235).
Итак, создадим простую функцию, которая отправит запрос и загрузит ответ в XML-документ (код примера я выложил на гитхабе: https://github.com/navferty/CBR-VBA-Currencies ):
Кстати, неплохо расписан механизм XmlHttpRequest с примерами авторизации тут: https://codingislove.com/http-requests-excel-vba/
Как видим, у нас есть XML-документ, по которому мы можем пройти в цикле, извлекая значения курса валюты на каждую дату. Давайте попробуем это сделать! Но для начала, добавим класс для того, чтобы хранить эти значения. Нам нужны три свойства (property) - код валюты (строка), дата и собственно значение (для сумм желательно использовать тип decimal). Добавим class module:
Свойства можно добавлять командой Insert -> Procedure... , это автоматически сгенерирует геттеры и сеттеры. Можно обойтись и просто публичными полями - но публичный доступ через свойства позволяет управлять доступом к данным, и вообще считается хорошим тоном =)
Функция CDec - преобразование значения в десятичное число. Настоятельно рекомендую использовать именно этот тип, а не Double, для работы с денежными суммами, это позволит избежать ошибок округления десятичных дробей:
Но вернёмся к нашей задаче. Итак, создадим коллекцию, которую будем наполнять записями типа CurrencyRecord:
Как видно на скриншоте, мы обходим элементы, которые содержат курсы на каждую дату (можно немного переделать обращение к resultXmlDocument.LastChild.ChildNodes с использованием XPath - для более наглядной навигации по XML документу).
Убедившись, что запрос и обработка ответа работают, перепишем нашу функцию для запроса валюты по ее коду, добавив словарь с соответствием кодов валют, а также конструирование пути с запросом с учётом даты начала/окончания и кода валюты:
Также я поменял CurrencyCode при создании записи в цикле - вместо "R01235" запись будет содержать общеизвестный код валюты "USD").
RequestGetXml - вспомогательная функция, которая загружает XML-документ с помощью HTTP-запроса:
Проверим, что вся функция работает как ожидается, добавив временную процедуру для теста:
Теперь добавим публичную функцию, которую можно будет использовать в формулах на листе (так называемую UDF - user-defined function):
С помощью функции DateAdd определяем интервал дат для загрузки - от вчера до сегодня. Получив две записи, возьмём значение из последней:
Set currItem = col.Item(col.Count) ' не забываем, что в VBA нумерация с 1
Обратите внимание на второй аргумент - volatileArg. Его значение не используется, но он понадобится для того, чтобы заставить Excel пересчитывать значение при изменении значения этого аргумента в формуле ячейки:
=GetTodayCurrency("USD"; NOW())
Чтобы убедиться, что обновление курса было выполнено, также добавим Debug.Pring с сообщением об успешной загрузке. После успешной отладки этот вызов можно будет удалить, чтобы не засорять окно Immediate.
Теперь проверим, что при открытии книги будет выполнена загрузка курса:
Как мы видим, в окне Immediate есть сообщение об успешной загрузке курса - сразу после открытия книги. Тем не менее, такое же сообщение будет появляться и при любом изменении на листе - Excel будет пересчитывать значение формулы. Поэтому если у Вас в книге будет много ячеек с вызовом этой функции, лучше добавить кэш - например, статическую переменную, в которой будет словарь (Dictionary). Ключом может выступать сочетание кода валюты и даты, значение - собственно CurrencyRecord. Это поможет избежать множества одинаковых запросов на сайт ЦБ. Если кому-то будет интересно - напишите, добавлю его в комментарии или отдельным постом =)
Спасибо всем кто дочитал, предлагайте в комментариях варианты доработки и улучшения предложенного решения! За идею поста спасибо @AlexeyE30 !
Напоследок упомяну о надстройке для MS Excel - я недавно писал о ней. Проект с открытым исходным кодом, опубликован под свободной лицензией MIT (неограниченное право на использование, копирование, изменение).
Инструкции по установке и описание функций можно найти тут: Надстройка для MS Excel
Страница проекта на гитхабе:
https://github.com/navferty/NavfertyExcelAddIn
