Ажуриран курс у Екцел-у

Више пута сам анализирао начине за увоз података у Екцел са Интернета са накнадним аутоматским ажурирањем. Нарочито:

• У старијим верзијама програма Екцел 2007-2013, то се може урадити директним веб захтевом.
• Почевши од 2010. године, ово се може врло згодно урадити са додатком Повер Куери.
  

Овим методама у најновијим верзијама Мицрософт Екцел-а сада можете додати још једну – увоз података са Интернета у КСМЛ формату помоћу уграђених функција.

КСМЛ (еКстенсибле Маркуп Лангуаге = Ектенсибле Маркуп Лангуаге) је универзални језик дизајниран да опише било коју врсту података. У ствари, то је обичан текст, али са посебним ознакама које су му додане да би се означила структура података. Многе веб локације пружају бесплатне токове својих података у КСМЛ формату за свако преузимање. На сајту Централне банке наше земље (ввв.цбр.ру), посебно, уз помоћ сличне технологије, дају се подаци о курсевима разних валута. Са сајта Московске берзе (ввв.моек.цом) можете преузети котације за акције, обвезнице и многе друге корисне информације на исти начин.

Од верзије 2013, Екцел има две функције за директно учитавање КСМЛ података са Интернета у ћелије радног листа: ВЕБ СЕРВИС (ВЕБ СЕРВИС) и ФИЛТЕР.КСМЛ (ФИЛТЕРКСМЛ). Раде у паровима – прво функција ВЕБ СЕРВИС извршава захтев до жељеног сајта и враћа његов одговор у КСМЛ формату, а затим помоћу функције ФИЛТЕР.КСМЛ ми „рашчланимо“ овај одговор на компоненте, извлачећи из њега податке који су нам потребни.

Погледајмо рад ових функција на класичном примеру – увозом курса било које валуте која нам је потребна за дати интервал датума са сајта Централне банке наше земље. Користићемо следећу конструкцију као празан простор:

Овде:

• Жуте ћелије садрже датум почетка и завршетка периода који нас занима.
• Плава има падајућу листу валута помоћу команде Подаци – Валидација – Листа (Подаци — Валидација — Листа).
• У зеленим ћелијама користићемо наше функције да креирамо стринг упита и добијемо одговор сервера.
• Табела са десне стране је референца на шифре валута (требаће нам мало касније).
  

Идемо!

Корак 1. Формирање стринга упита

Да бисте добили потребне информације са сајта, потребно је да их исправно питате. Идемо на ввв.цбр.ру и отварамо везу у подножју главне странице' Технички ресурси'- Добијање података помоћу КСМЛ-а (хттп://цбр.ру/девелопмент/СКСМЛ/). Померамо се мало ниже и у другом примеру (Пример 2) биће оно што нам треба – добијање курсева за дати интервал датума:

Као што можете видети из примера, стринг упита мора да садржи датуме почетка (дате_рек1) и завршеци (дате_рек2) периода који нас занима и шифру валуте (ВАЛ_НМ_РК), чију стопу желимо да добијемо. У табели испод можете пронаћи главне кодове валута:

Комплетан водич за шифре валута је такође доступан на веб страници Централне банке – погледајте хттп://цбр.ру/сцриптс/КСМЛ_вал.асп?д=0

Сада ћемо формирати низ упита у ћелији на листу са:

• оператор конкатенације текста (&) да га састави;
  
• Карактеристике ВПР (ВЛООКУП)да пронађемо шифру валуте која нам је потребна у именику;
  
• Карактеристике ТЕКСТ (ТЕКСТ), који конвертује датум према датом обрасцу дан-месец-година кроз косу црту.

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

Корак 2. Извршите захтев

Сада користимо функцију ВЕБ СЕРВИС (ВЕБ СЕРВИС) са генерисаним стрингом упита као јединим аргументом. Одговор ће бити дугачак ред КСМЛ кода (боље је укључити преламање речи и повећати величину ћелије ако желите да је видите у целини):

Корак 3. Рашчлањивање одговора

Да бисте лакше разумели структуру података одговора, боље је користити један од онлајн КСМЛ парсера (на пример, хттп://кпатхер.цом/ или хттпс://јсонформаттер.орг/кмл-парсер), који може визуелно да форматира КСМЛ код, додајући му увлаке и наглашавајући синтаксу бојом. Тада ће све постати много јасније:

Сада можете јасно видети да су вредности курса уоквирене нашим ознакама ..., а датуми су атрибути датум у ознакама .

Да бисте их издвојили, изаберите колону од десет (или више – ако је урађено са маргином) празних ћелија на листу (јер је постављен интервал од 10 дана) и унесите функцију у траку формуле ФИЛТЕР.КСМЛ (ФИЛТЕРКСМЛ):

Овде је први аргумент веза до ћелије са одговором сервера (Б8), а други је стринг упита у КСПатх-у, посебном језику који се може користити за приступ неопходним фрагментима КСМЛ кода и њихово издвајање. Више о КСПатх језику можете прочитати, на пример, овде.

Важно је да након уноса формуле не притискате унети, и пречицу на тастатури Цтрл+смена+унети, тј. унесите га као формулу низа (витичасте заграде око њега ће се аутоматски додати). Ако имате најновију верзију Оффице 365 са подршком за динамичке низове у Екцел-у, онда једноставно унети, и не морате да изаберете празне ћелије унапред – сама функција ће узети онолико ћелија колико јој је потребно.

Да бисмо издвојили датуме, урадићемо исто – изабраћемо неколико празних ћелија у суседној колони и користити исту функцију, али са другачијим КСПатх упитом, да бисмо добили све вредности атрибута Датум из ознака Рецорд:

=ФИЛТЕР.КСМЛ(Б8;”//Запис/@Датум”)

Сада у будућности, када мењате датуме у оригиналним ћелијама Б2 и Б3 или бирате другу валуту у падајућој листи ћелије Б3, наш упит ће се аутоматски ажурирати, упућивањем на сервер Централне банке за нове податке. Да бисте ручно извршили ажурирање, можете додатно користити пречицу на тастатури Цтрл+други+F9.

• Увезите битцоин стопу у Екцел преко Повер Куери-а
• Увезите курсеве са Интернета у старије верзије Екцел-а