Ажуриран девизен курс во 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) ќе го има она што ни треба - добивање на девизните курсеви за даден интервал на датуми:

Како што можете да видите од примерот, низата за барање мора да содржи датуми за почеток (date_req1) и завршетоци (date_req2) од периодот на интерес за нас и кодот на валутата (VAL_NM_RQ), чија стапка сакаме да ја добиеме. Главните шифри на валута можете да ги најдете во табелата подолу:

Целосен водич за валутни кодови е исто така достапен на веб-страницата на Централната банка - видете http://cbr.ru/scripts/XML_val.asp?d=0

Сега ќе формираме низа за пребарување во ќелија на лист со:

• операторот за поврзување текст (&) за да го состави;
  
• Карактеристики VPR (VLOOKUP)да го пронајдеме кодот на валутата што ни треба во директориумот;
  
• Карактеристики ТЕКСТОТ (ТЕКСТ), кој го претвора датумот според дадената шема ден-месец-година преку коса црта.

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

Чекор 2. Извршете го барањето

Сега ја користиме функцијата ВЕБ СЕРВИС (ВЕБСЕРВИС) со генерирана низа за пребарување како единствен аргумент. Одговорот ќе биде долга линија XML-код (подобро е да го вклучите word wrap и да ја зголемите големината на ќелијата ако сакате да ја видите во целост):

Чекор 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;“//Record/@Date“)

Сега во иднина, при менување на датумите во оригиналните ќелии B2 и B3 или избирање друга валута во паѓачката листа на ќелијата B3, нашето барање автоматски ќе се ажурира, повикувајќи се на серверот на Централната банка за нови податоци. За рачно принудно ажурирање, можете дополнително да ја користите кратенката на тастатурата Ctrl+Alt+F9.

• Увезете стапка на биткоин во Excel преку Power Query
• Увезете девизни курсеви од Интернет во постарите верзии на Excel