Frissített árfolyam az Excelben

Többször elemeztem az adatok Excelbe történő importálásának módjait az internetről az ezt követő automatikus frissítéssel. Különösen:

• Az Excel 2007–2013 régebbi verzióiban ez közvetlen webes kéréssel is megtehető.
• 2010-től ez nagyon kényelmesen megtehető a Power Query bővítménnyel.
  

A Microsoft Excel legújabb verzióiban ezekhez a módszerekhez most hozzáadhat egy másikat – adatok importálását az internetről XML formátumban a beépített funkciók segítségével.

Az XML (eXtensible Markup Language = Extensible Markup Language) egy univerzális nyelv, amelyet bármilyen típusú adat leírására terveztek. Valójában ez egyszerű szöveg, de speciális címkékkel, amelyek az adatszerkezetet jelölik. Sok webhely ingyenes adatfolyamot biztosít XML formátumban, hogy bárki letölthesse. A Hazánk Központi Bankjának honlapján (www.cbr.ru) különösen hasonló technológia segítségével adnak meg adatokat a különböző devizák árfolyamairól. A Moszkvai Tőzsde weboldaláról (www.moex.com) ugyanígy tölthet le részvényekre, kötvényekre vonatkozó jegyzéseket és sok más hasznos információt.

A 2013-as verzió óta az Excel két funkcióval rendelkezik az XML-adatok közvetlen betöltésére az internetről a munkalap celláiba: WEBES SZOLGÁLTATÁS (WEBES SZOLGÁLTATÁS) и SZŰRŐ.XML (FILTERXML). Párban dolgoznak – először a funkció WEBES SZOLGÁLTATÁS végrehajt egy kérést a kívánt helyre, és válaszát XML formátumban adja vissza, majd a függvény használatával SZŰRŐ.XML ezt a választ komponensekre „elemezzük”, kinyerjük belőle a szükséges adatokat.

Nézzük meg ezeknek a függvényeknek a működését egy klasszikus példán keresztül – importálva az Országunk Központi Bankjának honlapjáról egy adott dátumintervallumra bármely, számunkra szükséges deviza árfolyamát. Üresként a következő konstrukciót használjuk:

Itt:

• A sárga cellák a számunkra érdekes időszak kezdő és záró dátumát tartalmazzák.
• A kékben a pénznemek legördülő listája található a parancs használatával Adatok – Érvényesítés – Lista (Adatok – Érvényesítés – Lista).
• A zöld cellákban függvényeink segítségével lekérdezési karakterláncot hozunk létre, és megkapjuk a szerver válaszát.
• A jobb oldali táblázat a pénznemkódokra utal (kicsit később lesz rá szükségünk).
  

Menjünk!

1. lépés: Lekérdezési karakterlánc létrehozása

Ahhoz, hogy a szükséges információkat megkapja az oldalról, helyesen kell rákérdeznie. Megyünk a www.cbr.ru oldalra, és megnyitjuk a linket a főoldal láblécében. Műszaki erőforrások- Adatok lekérése XML használatával (http://cbr.ru/development/SXML/). Egy kicsit lejjebb görgessünk, és a második példában (2. példa) ott lesz, amire szükségünk van – egy adott dátumintervallum árfolyamainak lekérése:

Amint a példából látható, a lekérdezési karakterláncnak tartalmaznia kell a kezdő dátumokat (date_req1) és a befejezések (date_req2) a számunkra érdekes időszak és a pénznem kódja (VAL_NM_RQ), amelynek az arányát szeretnénk elérni. A főbb pénznemkódokat az alábbi táblázatban találja:

A valutakódok teljes útmutatója szintén elérhető a Központi Bank honlapján – lásd: http://cbr.ru/scripts/XML_val.asp?d=0

Most létrehozunk egy lekérdezési karakterláncot egy munkalap cellájában a következővel:

• a szövegösszefűzési operátor (&) az összeállításhoz;
  
• Jellemzők VPR (VLOOKUP)hogy megtaláljuk a címtárban a számunkra szükséges pénznem kódját;
  
• Jellemzők TEXT (SZÖVEG), amely a dátumot a megadott minta szerint nap-hónap-év egy perjellel alakítja át.

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

2. lépés: Hajtsa végre a kérést

Most a függvényt használjuk WEBES SZOLGÁLTATÁS (WEBES SZOLGÁLTATÁS) a generált lekérdezési karakterlánc az egyetlen argumentum. A válasz egy hosszú sor XML-kód lesz (jobb, ha bekapcsolod a tördelést és növeled a cella méretét, ha teljes egészében szeretnéd látni):

3. lépés: A válasz elemzése

A válaszadatok szerkezetének könnyebb megértése érdekében érdemesebb valamelyik online XML-elemzőt használni (például http://xpather.com/ vagy https://jsonformatter.org/xml-parser), amely vizuálisan formázza az XML-kódot, behúzásokat ad hozzá, és színnel kiemeli a szintaxist. Akkor minden sokkal világosabb lesz:

Most már tisztán láthatja, hogy a kurzusértékeket címkéink keretezik ..., és a dátumok attribútumok találka címkékben .

Kibontásukhoz jelöljön ki egy tíz (vagy több – ha margós) üres cellát tartalmazó oszlopot a lapon (mivel 10 napos dátumintervallumot állított be), és írja be a függvényt a képletsorba. SZŰRŐ.XML (SZŰRŐXML):

Itt az első argumentum egy kiszolgálóválaszt tartalmazó cellára mutató hivatkozás (B8), a második pedig egy lekérdezési karakterlánc az XPath-ban, egy speciális nyelvben, amellyel a szükséges XML-kódrészletek elérhetők és kivonhatók. Az XPath nyelvről például itt olvashat bővebben.

Fontos, hogy a képlet beírása után ne nyomja meg belépés a billentyűparancsot Ctrl+műszak+belép, azaz írja be tömbképletként (a körülötte lévő göndör kapcsos zárójelek automatikusan hozzáadódnak). Ha az Office 365 legújabb verziójával rendelkezik, amely támogatja a dinamikus tömböket az Excelben, akkor egyszerű belép, és nem kell előre kijelölni az üres cellákat – maga a függvény annyi cellát vesz fel, amennyire szüksége van.

A dátumok kinyeréséhez ugyanezt tesszük – kijelölünk több üres cellát a szomszédos oszlopban, és ugyanazt a függvényt használjuk, de más XPath-lekérdezéssel, hogy megkapjuk a Dátum attribútumok összes értékét a Record címkékből:

=SZŰRŐ.XML(B8;”//Record/@Date”)

Mostantól, amikor az eredeti B2 és B3 cellában módosítja a dátumot, vagy a B3 cella legördülő listájában más pénznemet választ, a lekérdezésünk automatikusan frissül, az új adatokért a Központi Bank szerverére hivatkozva. A frissítés kézi kényszerítéséhez használhatja a billentyűparancsot is Ctrl+más+F9.

• Importáljon bitcoin árfolyamot Excelbe a Power Query segítségével
• Importáljon árfolyamokat az internetről az Excel régebbi verzióiban