Автоматическое обновление курса валют в Excel с помощью функции ВЕБСЛУЖБА и ФИЛЬТР.XML

Хотели ли вы когда-нибудь включить данные из интернет-ресурсов в ваши таблицы Excel, таких как курсы валют, погоду, результаты выдачи поисковиков или даже сообщения Twitter. C новым Excel 2013 такая возможность появилась в виде функции ВЕБСЛУЖБА.

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

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

https://www.cbr.ru/scripts/Root.asp?Prtid=SXML

Так как нас интересует недельный тренд доллара США, ищем динамику котировок, она находится под заголовком Example 2. Если мы щелкнем по ссылке этого примера, сервис вернет нам данные по котировкам доллара за период 01/03/2001 по 14/03/2001 в формате XML. Обратите внимание на адресную строку в браузере, здесь находится строка запроса к сервису (она то нам и нужна!!!) с тремя аргументами: Дата С, Дата ПО и идентификационный номер валюты.

сервис центробанка

Изменяя любой из них, сервис ЦБ будет возвращать нужную нам информацию.

Копируем строку с заголовком, открываем книгу Excel, в ячейку B1 вбиваем функцию =ВЕБСЛУЖБА(), где аргументом функции станет, скопированная нами, строка, т.е. =ВЕБСЛУЖБА("https://www.cbr.ru/scripts/XML_dynamic.asp?date_req1=07/08/2013&date_req2=13/08/2013&VAL_NM_RQ=R01235")

синтаксис функции вебслужба

Обратите внимание, функция ВЕБСЛУЖБА вернула ответ в формате XML, такой же как мы видели в браузере. Теперь с полученного ответа необходимо выбрать нужные нам данные. В этом нам поможет функция ФИЛЬТР.XML, которая использует в качестве аргументов два параметра: первый — XML содержимое, второй – объект Xpath — язык запросов к элементам XML-документа, другими словами, это текстовая строка, которая указывает системе, какие конкретные данные необходимо получить из XML содержимого. Вы можете более подробно прочитать о языке Xpath в статье.

Выделяем ячейки A4:A8, жмем F2, вставляем формулу =ФИЛЬТР.XML(B1; "//Record//@Date") и нажимаем сочетание клавиш Ctrl + Shift + Enter, таким образом у нас получилась формула массива, которая выбирает из веб службы даты изменений курсов валют Центробанком. Таким же образом выделяем соседний диапазон B4:B8 и вставляет туда формулу =ФИЛЬТР.XML(B1;»//Value»). У нас должна получиться следующая таблица:

фильтр.xml формула

Так как мой компьютер воспринимает в качестве разделителя дробной части только точку (.), немного подправим формулу, возвращающую курс валют.

замена запятой на точку

Итак, у нас есть статичная таблица изменения курса доллара к рублю за период с 7/08/2013 по 13/08/2013. Так как наша первоначальная задача была – динамическое обновление данных, нам потребуется заменить жестко зафиксированные даты в строке запроса на формулу, которая бы изменяла свое значение в зависимости от текущей даты.

Установим в качестве второго аргумента значение сегодняшней даты плюс один день (т.к. Центробанк устанавливает курс на следующий день), первый аргумент будет равняться второму аргументу минус 6 дней. Зададим обе эти даты в качестве первого и второго аргументов в нашей строке запроса.

динамическая вебслужба

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

график курса доллара excel

Стоит отметить, что данную функцию можно применять для импорта данных с любых веб служб, предоставляющих свои API в свободном доступе. Такими службами являются API Яндекс, Twitter, API Google и т.д.

Скачать файл с примером использования функций ВЕБСЛУЖБА и ФИЛЬТР.XML для выгрузки курса доллара.


6 комментариев

  1. Спасибо Вам большое!
    Очень помогли!
    Работаю с «умными» таблицами, а там нельзя прописать массив. Пришлось привязываться к каждой дате отдельно, прописав всю формулу в одну ячейку.

  2. Есть ощущение, что ЦБ что-то пофиксили. Пример не работает и повторить его самостоятельно не получается. Формула дает ошибку. Словно нельзя больше подменить дату в запросе.

  3. Вы великолепны… пока на Вас не наткнулся, перечитал и пересмотрел 20-30 балбесов. Прошу только исправить ошибочку в тексте «диапазон B4:B8 и вставляет туда формулу =ФИЛЬТР.XML(B1;»//Value»)»

  4. и в скачиваемом файле формула ВЕБСЛУЖБА не понимает где на сайте найти =СЕГОДНЯ()+1 (надо как-нибудь заморочиться и изобрести формулу ПРЕДВИДЕНИЕ)

Добавить комментарий

Ваш e-mail не будет опубликован. Обязательные поля помечены *