Обновляемый курс валют в 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-данных из интернета в ячейки листа: ВЕБСЛУЖБА (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-кода (лучше включить перенос по словам и увеличить размер ячейки, если хочется увидеть её целиком):
Шаг 3. Разбираем ответ
Чтобы легче было разобраться в структуре данных ответа, лучше воспользоваться одним из онлайн-парсеров XML (например, http://xpather.com/ или https://jsonformatter.org/xml-parser), которые умеют наглядно форматировать 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.
Ссылки по теме
- Импорт курса биткойна в Excel через Power Query
- Импорт курса валют из интернета в старых версиях Excel
Попытался на ютюбе задать вопрос, тут же мой коммент удалён. Забанил меня?
Вот этот вопрос:
Давно и безуспешно бьюсь над созданием странички с курсом доллара Беларусбанка
При попытке загрузить данные с сайта этого банка курса доллара (Данные-Получение внешних данных-Из интернета)
Exel выдаёт предупреждение:
Ошибка сертификата
В приложении возникла внутренняя ошибка при загрузке библиотек SSL.
После многократного нажатия на кнопку ОК, данные с сайта всё же загружаются в Exel, однако:
Вместо курса покупки доллара - 2.5060 выдаёт - 01.фев (01.02.5060) Вместо курса продажи - 2.5260 выдаёт - 01.фев (01.02.5260)
Буду весьма признателен за помощь.
С уважением, Дэллюс Аджигитов.
И проделайте все то же самое, но используя PowerQuery.
А тип данных в столбцах с курсами изменяйте, используя локаль (десятичное число, английский США).
Если непонятно написал, смотрите в сети. Там все есть.
Увы, у меня установлен Офис 2016, в котором, как утверждает интернет, PowerQuery установлен.
Мои дальнейшие действия?
С уважением, Дэллюс Аджигитов.
Подскажите, в чем может быть проблема ?
Видимо, придется городить какую-то конструкцию...
Мне необходимо задавать курс доллара и евро на выбранную дату,
подскажите, пожалуйста, в чем ошибка?
строка запроса ввожу:
="="&ТЕКСТ(C2;"ДД/ММ/ГГГГ";)&"&VAL_NM_RQ="&ВПР(C5;J3:K4;2;0)
далее пытаюсь применить функцию ВЕБСЛУЖБА путем проставления в ячейке=ВЕБСЛУЖБА(С7), и она не срабатывает, выдает #ЗНАЧ!
для курса =ФИЛЬТР.XML(C9;"//Value";)
и даты =ФИЛЬТР.XML(C9;"//Record/@Date";)
далее соответственно тоже не срабатывает.
заранее спасибо за помощь!
Настроил выгрузку по данному методу для $ всё работало. Ничего не меняя на следующий день запрос на cbr по той же ссылке не работает (#знач)...при переходе по ссылке через браузер cbr.ru отвечает, "ошибка 500 (К сожалению, при обработке запроса произошла ошибка. Повторите попытку позднее.)"
Как можно это поправить или дело в cbr ???
при любом раскладе столбцы не заполняются значениями.
Может есть какая-то фишка.
Раньше необходимо было выделить массив, написать формулу и ввести <Ctrl+Shift+Enter>
... не срабатывает ни как, даже Ваш пример.
=ФИЛЬТР.XML(B8;"//Record/@Date" =ФИЛЬТР.XML(B8;"///Value"
получить необходимое количество заданных значений.
Оно ограничено. Ввожу как массив Ctrl+Shift+Enter.
При попытке получить курс (Евро) на ту дату, когда он не изменяется (пример - воскресенье) формула выдает ошибку.
Так и должно быть? Или что делаю нет так?
Спасибо.
Хотел добавить Лиры и Тенге - указывается неверный курс, а точнее курс как бы верный, но запятая стоит не там.
Пример:
10.09 курс 3.315
Выдаёт 33.15
Как исправить этот момент?
Как понимаю при передаче данных указывается Номинал «10» , из-за чего данные перетягиваются из-за поля «Nominal» в самой XML сайта ЦБ
Помогите пожалуйста 🙏