Обновляемый курс валют в Excel

Я уже неоднократно разбирал способы импорта данных в Excel из интернета с последующим автоматическим обновлением. В частности:

К этим способам в последних версиях Microsoft Excel теперь можно добавить ещё один - импорт данных из интернета в формате XML с помощью встроенных функций.

XML (eXtensible Markup Language = Расширяемый Язык Разметки) – это универсальный язык, созданный для описания любых данных. По сути, он представляет собой обычный текст, но с добавленными в него специальными тегами для разметки структуры данных. Многие сайты совершенно бесплатно предоставляют всем желающим потоки своих данных в XML-формате для загрузки. На сайте Центрального Банка России (www.cbr.ru), в частности, с помощью подобной технологии отдаются данные по курсам различных валют. С сайта Московской Биржи (www.moex.com) таким же образом можно загрузить котировки акций, облигаций и множество другой полезной информации.

Начиная с версии 2013 в Excel появились две функции для прямой загрузки XML-данных из интернета в ячейки листа: ВЕБСЛУЖБА (WEBSERVICE) и ФИЛЬТР.XML (FILTERXML). Работают они в паре - сначала функция ВЕБСЛУЖБА выполняет запрос к нужному сайту и возвращает его ответ в формате XML, а затем с помощью функции ФИЛЬТР.XML мы «разбираем» этот ответ на составляющие, извлекая из него нужные нам данные.

Давайте рассмотрим работу этих функций на классическом примере – импорте курса любой нужной нам валюты на заданный интервал дат с сайта Центробанка России. В качестве заготовки будем использовать вот такую конструкцию:

Заготовка

Здесь:

  • В жёлтых ячейках находятся даты начала и окончания интересующего нас периода.
  • В синей сделан выпадающий список валют при помощи команды Данные - Проверка - Список (Data - Validation - List).
  • В зеленых ячейках мы будем использовать наши функции, чтобы создать строку запроса и получить ответ сервера.
  • Таблица справа - справочник по кодам валют (потребуется нам чуть позже).
Поехали!

Шаг 1. Формируем строку запроса

Чтобы получить от сайта требуемую информацию, его нужно правильно спросить. Заходим на www.cbr.ru и в подвале главной страницы открываем ссылку’ Технические ресурсы ’- Получение данных, используя XML (http://cbr.ru/development/SXML/). Проматываем чуть ниже и во втором примере (Example 2) будет то, что нам нужно - получение курсов валюты на заданный интервал дат:

Пример строки запроса

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

Валюта

Код

                         

Валюта

Код

Австралийский доллар R01010

Литовский лит

R01435

Австрийский шиллинг

R01015


Литовский талон

R01435

Азербайджанский манат

R01020


Молдавский лей

R01500

Фунт стерлингов

R01035


Немецкая марка

R01510

Ангольская новая кванза

R01040


Нидерландский гульден

R01523

Армянский драм

R01060


Норвежская крона

R01535

Белорусский рубль

R01090


Польский злотый

R01565

Бельгийский франк

R01095


Португальский эскудо

R01570

Болгарский лев

R01100


Румынский лей

R01585

Бразильский реал

R01115


Сингапурский доллар

R01625

Венгерский форинт

R01135


Суринамский доллар

R01665

Гонконгский доллар

R01200


Таджикский сомони

R01670

Греческая драхма

R01205


Таджикский рубл

R01670

Датская крона

R01215


Турецкая лира

R01700

Доллар США

R01235


Туркменский манат

R01710

Евро

R01239


Новый туркменский манат

R01710

Индийская рупия

R01270


Узбекский сум

R01717

Ирландский фунт

R01305


Украинская гривна

R01720

Исландская крона

R01310


Украинский карбованец

R01720

Испанская песета

R01315


Финляндская марка

R01740

Итальянская лира

R01325


Французский франк

R01750

Казахстанский тенге

R01335


Чешская крона

R01760

Канадский доллар

R01350


Шведская крона

R01770

Киргизский сом

R01370


Швейцарский франк

R01775

Китайский юань

R01375


Эстонская крона

R01795

Кувейтский динар

R01390


Югославский новый динар

R01804

Латвийский лат

R01405


Южноафриканский рэнд

R01810

Ливанский фунт

R01420


Вон Республики Корея

R01815




Японская иена

R01820

Полный справочник по кодам валют есть там же, на сайте ЦБ – см. http://cbr.ru/scripts/XML_val.asp?d=0

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

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

Формируем строку запроса

="http://cbr.ru/scripts/XML_dynamic.asp?date_req1="&ТЕКСТ(B2;"ДД/ММ/ГГГГ")&
"&date_req2="&ТЕКСТ(B3;"ДД/ММ/ГГГГ")&"&VAL_NM_RQ="&ВПР(B4;M:N;2;0)

Шаг 2. Выполняем запрос

Теперь используем функцию ВЕБСЛУЖБА (WEBSERVICE) с созданной строкой запроса в качестве единственного аргумента. Ответом будет длинная строка XML-кода (лучше включить перенос по словам и увеличить размер ячейки, если хочется увидеть её целиком):

XML запрос функцией ВЕБСЛУЖБА в Excel

Шаг 3. Разбираем ответ

Чтобы легче было разобраться в структуре данных ответа, лучше воспользоваться одним из онлайн-парсеров XML (например, http://xpather.com/ или https://jsonformatter.org/xml-parser), которые умеют наглядно форматировать XML-код, добавляя к нему отступы и подсвечивая цветом синтаксис. Тогда всё станет существенно понятнее:

XML-ответ сервера ЦБ РФ

Теперь хорошо видно, что значения курса обрамлены у нас тегами <Value>…</Value>, а даты являются атрибутами Date в тегах <Record>.

Чтобы их извлечь курс, выделим на листе столбец из десяти (или более - если делать с запасом) пустых ячеек (т.к. был задан 10-дневный интервал дат) и введём в строку формул функцию ФИЛЬТР.XML (FILTERXML):

Извлекаем курс

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

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

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

=ФИЛЬТР.XML(B8;"//Record/@Date")

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

Ссылки по теме


20.08.2021 16:22:31
Доброго здоровья!
Попытался на ютюбе задать вопрос, тут же мой коммент удалён. Забанил меня?
Вот этот вопрос:
Давно и безуспешно бьюсь над созданием странички с курсом доллара Беларусбанка  
При попытке загрузить данные с сайта этого банка курса доллара (Данные-Получение внешних данных-Из интернета)
https://belarusbank.by/ru/fizicheskim_licam/valuta/kursy-valyut
Exel выдаёт предупреждение:
Ошибка сертификата
В приложении возникла внутренняя ошибка при загрузке библиотек SSL.
После многократного нажатия на кнопку ОК, данные с сайта всё же загружаются в Exel, однако:  
Вместо курса покупки доллара - 2.5060 выдаёт - 01.фев (01.02.5060) Вместо курса продажи - 2.5260 выдаёт - 01.фев (01.02.5260)
Буду весьма признателен за помощь.
С уважением, Дэллюс Аджигитов.
21.08.2021 13:26:16
Вы используете, по-видимому, Эксель без надстройки PowerQuery. Обновитесь или установите надстройку.
И проделайте все то же самое, но используя PowerQuery.
А тип данных в столбцах с курсами изменяйте, используя локаль (десятичное число, английский США).
Если непонятно написал, смотрите в сети. Там все есть.
21.08.2021 15:11:55
Доброго здоровья, уважаемый Николай!
Увы, у меня установлен Офис 2016, в котором, как утверждает интернет, PowerQuery установлен.
Мои дальнейшие действия?
С уважением, Дэллюс Аджигитов.
03.09.2021 11:01:55
Добрый день. Прием работает 1 раз - после введения всех формул. Далее сохраняю файл. Открываю заново - меняю диапазон дат - формулы перестают работать. Причем слетают значения и предыдущих дат, которые в первоначальном файле корректно работали.
Подскажите, в чем может быть проблема ?
Наверх