88

Курсы валют с сайта ЦБ на VBA (с автообновлением)

В комментариях к прошлому посту возникла жалоба одного пользователя Excel на отсутствие рабочих примеров макросов, которые загружают курсы валют с сайта ЦБ, и при этом формулы обновлялись бы автоматически, подтягивая актуальный курс на текущую дату. Там же в комментариях набросал вариант решения (вот готовый пример), но подумал, что отдельный пост на эту тему поможет и другим пользователям, столкнувшимся с подобной задачей.

Курсы валют с сайта ЦБ на VBA (с автообновлением) Vba, Курс валют, Автоматизация, Длиннопост

Итак, на сайте ЦБ РФ находим официальные API для загрузки динамики курсов (на случай, если нам потребуется загружать курс на определенную дату в прошлом):

HTTP GET-запрос вида

http://www.cbr.ru/scripts/XML_dynamic.asp?date_req1=02/03/20...

где R01235 - это код валюты (доллар США). Список кодов валют для указания в запросе также доступен.

API возвращает данные в XML-формате, и для нас это даже удобнее, чем JSON - так как в VBA есть поддержка XML, а для парсинга JSON нужны сторонние библиотеки. Вот пример ответа:

Курсы валют с сайта ЦБ на VBA (с автообновлением) Vba, Курс валют, Автоматизация, Длиннопост

Как видим, мы получили список курсов на каждую дату в интервале, с указанием кода валюты - именно то, что нам нужно.


Теперь попробуем сделать такой запрос из VBA. Для начала подключим в нашей книге ссылку на зависимость MSXML 6.0 (если у вас доступна другая версия, ничего страшного - могут немного поменяться имена типов, в целом логика такая же)

Курсы валют с сайта ЦБ на VBA (с автообновлением) Vba, Курс валют, Автоматизация, Длиннопост

Заодно добавим ссылку на Microsoft Scripting Runtime - оттуда мы сможем использовать тип Dictionary для хранения соответствий кодов валют (USD -> R01235).

Итак, создадим простую функцию, которая отправит запрос и загрузит ответ в XML-документ (код примера я выложил на гитхабе: https://github.com/navferty/CBR-VBA-Currencies ):

Курсы валют с сайта ЦБ на VBA (с автообновлением) Vba, Курс валют, Автоматизация, Длиннопост

Кстати, неплохо расписан механизм XmlHttpRequest с примерами авторизации тут: https://codingislove.com/http-requests-excel-vba/


Как видим, у нас есть XML-документ, по которому мы можем пройти в цикле, извлекая значения курса валюты на каждую дату. Давайте попробуем это сделать! Но для начала, добавим класс для того, чтобы хранить эти значения. Нам нужны три свойства (property) - код валюты (строка), дата и собственно значение (для сумм желательно использовать тип decimal). Добавим class module:

Курсы валют с сайта ЦБ на VBA (с автообновлением) Vba, Курс валют, Автоматизация, Длиннопост

Свойства можно добавлять командой Insert -> Procedure... , это автоматически сгенерирует геттеры и сеттеры. Можно обойтись и просто публичными полями - но публичный доступ через свойства позволяет управлять доступом к данным, и вообще считается хорошим тоном =)


Функция CDec - преобразование значения в десятичное число. Настоятельно рекомендую использовать именно этот тип, а не Double, для работы с денежными суммами, это позволит избежать ошибок округления десятичных дробей:

Курсы валют с сайта ЦБ на VBA (с автообновлением) Vba, Курс валют, Автоматизация, Длиннопост

Но вернёмся к нашей задаче. Итак, создадим коллекцию, которую будем наполнять записями типа CurrencyRecord:

Курсы валют с сайта ЦБ на VBA (с автообновлением) Vba, Курс валют, Автоматизация, Длиннопост

Как видно на скриншоте, мы обходим элементы, которые содержат курсы на каждую дату (можно немного переделать обращение к resultXmlDocument.LastChild.ChildNodes с использованием XPath - для более наглядной навигации по XML документу).


Убедившись, что запрос и обработка ответа работают, перепишем нашу функцию для запроса валюты по ее коду, добавив словарь с соответствием кодов валют, а также конструирование пути с запросом с учётом даты начала/окончания и кода валюты:

Курсы валют с сайта ЦБ на VBA (с автообновлением) Vba, Курс валют, Автоматизация, Длиннопост

Также я поменял CurrencyCode при создании записи в цикле - вместо "R01235" запись будет содержать общеизвестный код валюты "USD").


RequestGetXml - вспомогательная функция, которая загружает XML-документ с помощью HTTP-запроса:

Курсы валют с сайта ЦБ на VBA (с автообновлением) Vba, Курс валют, Автоматизация, Длиннопост

Проверим, что вся функция работает как ожидается, добавив временную процедуру для теста:

Курсы валют с сайта ЦБ на VBA (с автообновлением) Vba, Курс валют, Автоматизация, Длиннопост

Теперь добавим публичную функцию, которую можно будет использовать в формулах на листе (так называемую UDF - user-defined function):

Курсы валют с сайта ЦБ на VBA (с автообновлением) Vba, Курс валют, Автоматизация, Длиннопост

С помощью функции DateAdd определяем интервал дат для загрузки - от вчера до сегодня. Получив две записи, возьмём значение из последней:

Set currItem = col.Item(col.Count) ' не забываем, что в VBA нумерация с 1


Обратите внимание на второй аргумент - volatileArg. Его значение не используется, но он понадобится для того, чтобы заставить Excel пересчитывать значение при изменении значения этого аргумента в формуле ячейки:

=GetTodayCurrency("USD"; NOW())

Чтобы убедиться, что обновление курса было выполнено, также добавим Debug.Pring с сообщением об успешной загрузке. После успешной отладки этот вызов можно будет удалить, чтобы не засорять окно Immediate.


Теперь проверим, что при открытии книги будет выполнена загрузка курса:

Курсы валют с сайта ЦБ на VBA (с автообновлением) Vba, Курс валют, Автоматизация, Длиннопост

Как мы видим, в окне Immediate есть сообщение об успешной загрузке курса - сразу после открытия книги. Тем не менее, такое же сообщение будет появляться и при любом изменении на листе - Excel будет пересчитывать значение формулы. Поэтому если у Вас в книге будет много ячеек с вызовом этой функции, лучше добавить кэш - например, статическую переменную, в которой будет словарь (Dictionary). Ключом может выступать сочетание кода валюты и даты, значение - собственно CurrencyRecord. Это поможет избежать множества одинаковых запросов на сайт ЦБ. Если кому-то будет интересно - напишите, добавлю его в комментарии или отдельным постом =)


Спасибо всем кто дочитал, предлагайте в комментариях варианты доработки и улучшения предложенного решения! За идею поста спасибо @AlexeyE30 !

Напоследок упомяну о надстройке для MS Excel - я недавно писал о ней. Проект с открытым исходным кодом, опубликован под свободной лицензией MIT (неограниченное право на использование, копирование, изменение).

Курсы валют с сайта ЦБ на VBA (с автообновлением) Vba, Курс валют, Автоматизация, Длиннопост

Инструкции по установке и описание функций можно найти тут: Надстройка для MS Excel


Страница проекта на гитхабе:

https://github.com/navferty/NavfertyExcelAddIn

Найдены дубликаты

+3

Язык эльфов немного сложный.

раскрыть ветку 2
+2
Иллюстрация к комментарию
раскрыть ветку 1
+1

Перевод на русский для людей и других существ, не знакомых с эльфийским:

"Мне кажется, язык макросов не такой сложный

каким его представляют"

+1

Упрощу вам задачу:

Можно эту хрень тупо формулой забить:


=ФИЛЬТР.XML(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ВЕБСЛУЖБА("http://www.cbr.ru/scripts/XML_dynamic.asp?date_req1="&ТЕКСТ(СЕГОДНЯ();"ДД.ММ.ГГГГ")&"&date_req2="&ТЕКСТ(СЕГОДНЯ();"ДД.ММ.ГГГГ")&"&VAL_NM_RQ=R01235");".";",");"=""1,0";"=""1.0");"//ValCurs//Record//Value")

раскрыть ветку 5
0

Код у меня с другим апи используется, где десятичные числа точкой разделены, поэтому есть извращения с формулой подставить...

В вашем случае формулу можно сильно упростить, убрав обе функции Подставить. Но я комп уже выключил, с телефона пишу.

0

Ps обновление данных через Alt+Ctrl+F9

раскрыть ветку 3
0

Да, это вполне рабочее решение - и в каких-то случаях оно смотрится более выигрышно - за счёт отсутствия макросов. Но давайте представим, что через некоторое время требования к расчёту изменятся, и нужно брать среднее арифметическое за вчерашний и сегодняшний день. Окей, скопируем эту формулу с немного другими параметрами запроса. Потом потребуется брать среднее за неделю, при этом дополнив значениями выходные дни (они не приходят в ответе, но курс мы берем "пятничный"). И вот у нас уже целый лист для расчёта курса, заполненный многоэтажными формулами.

Таким образом, формулы выигрывают сравнительной простотой написания, но для сложной логики и возможности гибко изменять решение под новые требования. Всему свой инструмент)

раскрыть ветку 2
+1

Список кодов валют - не прикрепилась ссылка, вот она:

http://www.cbr.ru/scripts/XML_val.asp?d=0

Иллюстрация к комментарию
+1

Еще раз хотел бы предостеречь от прямого использования механизма автообновления, если в Вашей книге часто происходит пересчет ячеек, а функция загрузки используется во множестве ячеек. Вы рискуете заDDOSить сайт ЦБ, причём сайту скорее всего ничего страшного не будет, а вот Вас могут и заблокировать (например, по IP). Я бы советовал не экспериментировать с этим =)

0

нахера это все, заходишь на яндекс там тебе и курс и хуюрс и погода

раскрыть ветку 2
0

а если таблица на пару десятков тысяч строк со сделками за год, сумму каждой из которых нужно перевести в другую валюту по курсу на дату сделки?)

раскрыть ветку 1
0

снимаю шляпу

0

Спасибо за разбор.вариант без макросов как раз предпочтительнее в некоторых случаях.никак с api приватбанка не разберусь.может сможете подсказать?то,что дату в текст конвертировать я понял,а как правильно запрос отправить нет.целый день форумы копаю по запросам xpath.как например вытащить значение saleRateNB для валюты usd из запроса

https://api.privatbank.ua/p24api/exchange_rates?date=01.12.2...



Вот такой вариант сделал,но он ошибочный

=ФИЛЬТР.XML(ВЕБСЛУЖБА("https://api.privatbank.ua/p24api/exchange_rates?date="&ТЕКСТ(СЕГОДНЯ();"ДД.ММ.ГГГГ"));"//exchangerates[@currency='USD']/exchangerates")

раскрыть ветку 4
+1

да, так даже вроде работает)

Иллюстрация к комментарию
раскрыть ветку 2
0

Спасибо огромное.работает.только через 3 обновления идет бан по ip на некоторе время.через power query в принципе подозреваю бан прилетит так же.жаль,что такое жесткое ограничение.гугл и яндекс таких запросов под 50 штук отжавали до бана,приходится из под vpn заходить.но хоть так работает и ладно.спасибо еще раз

раскрыть ветку 1
0

попробуйте такой XPath

"//exchangerates/exchangerate[@currency='USD']/@saleRate"

ведь атрибут мы берем у элемента exchangerate а не у родительского exchangerates


есть онлайн-сервисы для тестирования Xpath https://www.freeformatter.com/xpath-tester.html#ad-output

Иллюстрация к комментарию
0

Добрый день. Спасибо за статьи по Excel что то из них очень познавательно. И использую в гугл таблицах.


Возник вопрос, возможно подскажете. В случае с xml понятно. Но многие ресурсы отдают только json те же эксплореры криптовалют или разного рода котировки в крипто бирж.

С импортом json все понятно. Но вот Проблема с автообновлением данных в ячейке (через google script) мне пока не поддалась.

Нет ли у вас скрипта на обновление данных в ячейке По таймеру .

Почему то параметр now и ему смежные отлично обрабатываются и обновляются через настройки ( обновление по времени)

А тот же importjson стоит на месте. Пока не измениться хоть один параметр внутри.


Благодарю.

раскрыть ветку 1
0

Добрый вечер) Может быть, расшарите и скинете ссылку на пример? С google script не обладаю особенно опытом, так что лучше на примере разбираться)

0
Мужики Вы утомили меряться органами. Народ, судя по рейтингу, не вдупляет как это делать. Залейте готовый файл на известный, специализированный, модерируемый форум.... и народ потестирует. И выдаст Вам лайки, ордена и ачивки/при желании/.
раскрыть ветку 1
+1

Вот ссылка на xlsm книгу:

https://github.com/navferty/CBR-VBA-Currencies/blob/master/C...


там загрузка валют с ЦБ, и бонусом - загрузка организаций по ИНН с сервиса Dadata (правда нужен API-ключ, для этого нужно зарегистрироваться https://dadata.ru )


Честно говоря, увидел, что недавно уже был пост на эту же тему, только после того как опубликовал этот пост))

0

я так понял пост для программистов

раскрыть ветку 2
0

Многие аналитики, работающие с данными, также приходят к осознанию необходимости изучать языки программирования для повышения эффективности своей работы - будь то VBA, Python или, например, R. Это необязательно будет серьёзная разработка больших приложений - но несколько десятков строк кода на VBA иногда могу сэкономить часы работы с чистыми формулами, например.

раскрыть ветку 1
0

о да, я когда то basic учил, на ямахе занимался

0
ТС, спасибо за подробное описание процесса. Может поможете найти ответ, как решить задачу заполнения реквизитов юр лица в таблице, зная только ИНН.
На сайте nalog.ru есть API, но я не могу понять, как сделать рабочий вариант в Excel для выгрузки оттуда данных.
Эта наработка помогла бы сэкономить кучу времени при создании, например, договоров, и иных документов
раскрыть ветку 2
0

Залил на гитхаб пример:

https://github.com/navferty/CBR-VBA-Currencies/blob/master/L...

0

Могу предложить воспользоваться сервисом Dadata.

https://dadata.ru/api/find-party/

Вообще сервис платный, но до определенного количества запросов в день - бесплатно, только надо зарегистрироваться у них на сайте и получить API-ключ. Пример я залью в тот же репозиторий

Иллюстрация к комментарию
0
Огромное вам спасибо!)
раскрыть ветку 1
0

Пожалуйста! Возникнут вопросы - пишите тут)

0
Можно я просто посижу тут?
раскрыть ветку 1
+1

Велкам)

Похожие посты
Похожие посты не найдены. Возможно, вас заинтересуют другие посты по тегам: