Актуализиран обменен курс в Excel

Многократно съм анализирал начини за импортиране на данни в Excel от Интернет с последващо автоматично актуализиране. По-специално:

• В по-старите версии на Excel 2007-2013 това може да стане с директна уеб заявка.
• От 2010 г. това може да се направи много удобно с добавката Power Query.
  

Към тези методи в последните версии на Microsoft Excel вече можете да добавите още един – импортиране на данни от интернет в XML формат с помощта на вградени функции.

XML (eXtensible Markup Language = Extensible Markup Language) е универсален език, предназначен да описва всякакъв вид данни. Всъщност това е обикновен текст, но със специални тагове, добавени към него, за да се маркира структурата на данните. Много сайтове предоставят безплатни потоци от своите данни в XML формат за изтегляне от всеки. На уебсайта на Централната банка на нашата страна (www.cbr.ru), по-специално, с помощта на подобна технология се дават данни за обменните курсове на различни валути. От уебсайта на Московската борса (www.moex.com) можете да изтеглите котировки за акции, облигации и много друга полезна информация по същия начин.

От версия 2013 Excel има две функции за директно зареждане на XML данни от интернет в клетки на работен лист: УЕБ СЕРВИЗ (УЕБ СЕРВИЗ) и FILTER.XML (FILTERXML). Те работят по двойки – първо функцията УЕБ СЕРВИЗ изпълнява заявка до желания сайт и връща отговора си в XML формат, след което използва функцията FILTER.XML ние „анализираме“ този отговор на компоненти, извличайки данните, от които се нуждаем.

Нека разгледаме работата на тези функции, като използваме класически пример – импортиране на обменния курс на всяка валута, от която се нуждаем за даден интервал от дата, от уебсайта на Централната банка на нашата страна. Ще използваме следната конструкция като заготовка:

Тук:

• Жълтите клетки съдържат началната и крайната дата на периода, който ни интересува.
• Синята има падащ списък с валути, използващи командата Данни – Валидиране – Списък (Данни — Валидиране — Списък).
• В зелените клетки ще използваме нашите функции, за да създадем низ за заявка и да получим отговора на сървъра.
• Таблицата вдясно е препратка към валутните кодове (ще ни трябва малко по-късно).
  

Да тръгваме!

Стъпка 1. Формиране на низ за заявка

За да получите необходимата информация от сайта, трябва да я зададете правилно. Отиваме на www.cbr.ru и отваряме връзката в долния колонтитул на главната страница" Технически ресурси'- Получаване на данни чрез XML (http://cbr.ru/development/SXML/). Превъртаме малко по-надолу и във втория пример (Пример 2) ще има това, от което се нуждаем – получаване на обменните курсове за даден интервал от дати:

Както можете да видите от примера, низът на заявката трябва да съдържа начални дати (дата_req1) и окончания (дата_req2) на интересуващия ни период и кода на валутата (VAL_NM_RQ), скоростта, която искаме да получим. Можете да намерите кодовете на основните валути в таблицата по-долу:

Пълно ръководство за валутните кодове също е достъпно на уебсайта на Централната банка – вижте http://cbr.ru/scripts/XML_val.asp?d=0

Сега ще формираме низ за заявка в клетка на лист с:

• операторът за конкатенация на текст (&), за да го сглобите;
  
• Характеристики: ВПР (VLOOKUP)да намерим кода на необходимата ни валута в директорията;
  
• Характеристики: ТЕКСТ (ТЕКСТ), който преобразува датата според зададения модел ден-месец-година чрез наклонена черта.

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

Стъпка 2. Изпълнете заявката

Сега използваме функцията УЕБ СЕРВИЗ (УЕБ СЕРВИЗ) с генерирания низ на заявката като единствен аргумент. Отговорът ще бъде дълъг ред XML код (по-добре е да включите пренасянето на думи и да увеличите размера на клетката, ако искате да я видите цялата):

Стъпка 3. Разбор на отговора

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

Сега можете ясно да видите, че стойностите на курса са рамкирани от нашите тагове ..., а датите са атрибути Дата в тагове .

За да ги извлечете, изберете колона от десет (или повече – ако сте направили с поле) празни клетки на листа (тъй като е зададен 10-дневен интервал от дата) и въведете функцията в лентата за формули FILTER.XML (ФИЛТРИРАНЕXML):

Тук първият аргумент е връзка към клетка с отговор на сървъра (B8), а вторият е низ на заявка в XPath, специален език, който може да се използва за достъп до необходимите фрагменти от XML код и тяхното извличане. Можете да прочетете повече за езика XPath например тук.

Важно е след като въведете формулата, да не натискате Въведетеи клавишната комбинация Ctrl+Превключване+Въведете, т.е. въведете го като формула за масив (къдравите скоби около него ще бъдат добавени автоматично). Ако имате най-новата версия на Office 365 с поддръжка за динамични масиви в Excel, тогава просто Въведете, и не е необходимо да избирате празни клетки предварително – самата функция ще вземе толкова клетки, колкото са ѝ необходими.

За да извлечем дати, ще направим същото – ще изберем няколко празни клетки в съседната колона и ще използваме същата функция, но с различна XPath заявка, за да получим всички стойности на атрибутите Date от таговете Record:

=FILTER.XML(B8;”//Запис/@Дата”)

Сега в бъдеще, когато промените датите в оригиналните клетки B2 и B3 или изберете различна валута в падащия списък на клетка B3, нашата заявка ще се актуализира автоматично, препращайки към сървъра на Централната банка за нови данни. За да принудите актуализация ръчно, можете допълнително да използвате клавишната комбинация Ctrl+Друг+F9.

• Импортирайте курса на биткойн в Excel чрез Power Query
• Импортирайте обменни курсове от интернет в по-стари версии на Excel