Автоматичне оновлення курсу валют в Excel за допомогою функції ВЕБСЛУЖБА і ФІЛЬТР.XML

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

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

Для початку, необхідно знайти веб сервіс, який повертає дані у форматі XML. Я скористався службами Центробанку, який люб'язно надає свої API для вивантаження даних. За посиланням ви знайдете всі дійсні на поточний момент запити банку.

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

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

Змінюючи будь-який з них, сервіс ЦБ буде повертати потрібну нам інформацію.

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

Зверніть увагу, функція ВЕБСЛУЖБА повернула відповідь в форматі XML, такий же як ми бачили в браузері. Тепер з отриманої відповіді необхідно вибрати потрібні нам дані. У цьому нам допоможе функція ФІЛЬТР.XML, яка використовує в якості аргументів два параметри: перший - XML ​​вміст, другий - об'єкт Xpath - мова запитів до елементів XML-документа, іншими словами, це текстовий рядок, який вказує системі, які конкретні дані необхідно отримати з XML вмісту. Ви можете більш детально прочитати про мову Xpath в статті .

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

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

Отже, у нас є статична таблиця зміни курсу долара до рубля за період з 7/08/2013 по 13/08/2013. Так як наша початкова завдання було - динамічне оновлення даних, нам буде потрібно замінити жорстко зафіксовані дати в рядку запиту на формулу, яка б змінювала своє значення в залежності від поточної дати.

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

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

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

Завантажити файл з прикладом використання функцій ВЕБСЛУЖБА і ФІЛЬТР.XML для вивантаження курсу долара.