Latvijas Banka katru dienu pārpublicē Eiropas Centrālās bankas interneta vietnē esošos eiro atsauces kursus un piedāvā tos iegūt dažādos Excel vai Power BI lietotājam parocīgos veidos. Šeit var aplūkot visus iespējamos datu avotus, kā iegūstami valūtu kursi, savukārt rakstā ir risinājumi saistībā ar CSV formātu Excel 2019. Līdzīgi tas būs arī Power BI, Excel 2016, bet vecākām Excel versijām nepieciešams Power Query Add-in.
Turpinājumā dažāda veida risinājumi tiek aprakstīti pa soļiem, kas ļauj labāk saprast un veikt sev nepieciešamās korekcijas. Zemāk ievietoti linki, kur var lejupielādēt jau gatavus risinājumus.
Datu pieprasīšana notiek izmantojot noteiktu adresi. Latvijas Bankas piemērā rakstīts, ka aktuālos kursus var iegūt izmantojot šādu adresi http://www.bank.lv/vk/ecb.csv, bet noteiktas dienas valūtas kursi iegūstami ievietojot papildus datuma norādi formātā yyyymmdd, piemēram, http://www.bank.lv/vk/ecb.csv?date=20050323.
1. Valūtu kursi noteiktam datumam
Izveidojot nepieciešamā datuma adresi, jādodas uz Data -> From Text/CSV (Power Bi gadījumā Get Data –> From Web) un jāiekopē paredzētajā vietā.
Excel gadījumā var būt, ka ir paziņojums par to, ka jāprecizē kā atvērt failu. Zemāk redzamajā paziņojumā jāizvēlas Edit.
Open file as izvēlnē norāda Csv Document.
Jānorāda atbilstošs kolonnu atdalītājs un loga apakšējā labajā stūrī jāizvēlas darbība Edit, jo vajadzēs mainīt kolonnu nosaukumus un izveidot parametru, kuru mainot varēs iegūt attiecīgās dienas kursus.
Zemāk attēlā redzamas kolonnas ar mainītiem nosaukumiem. Kolonnā “Rate” ar Power Query rīku Replace Values izmainīju decimālzīmju atdalītāju uz sev nepieciešamo, lai kolonnai varētu piešķirt formātu Decimal Number.
Home cilnē no Manage Parameters jāizvēlas New.
Aizpilda parametram nepieciešamos laukus tā, kā redzams zemāk attēlā.
Kreisajā pusē sadaļā Queries tagad var redzēt šādu ainiņu.
Jāizvēlas iepriekš lietotais vaicājums “ecb” un jāpievieno APPLIED STEPS solī “Source” iepriekš veidotais parametrs.
Pirms parametra pievienošanas izskatās šādi.
Pēc parametra pievienošanas izskatās šādi. Tātad, atkarībā no tā, kas tiks ievadīts parametrā, tiks veiktas izmaiņas linkā, ar kuru pieprasa datus.
Lai būtu ērtāk, parametra datus var ievadīt arī atsevišķā kolonnā. Add Column -> Custom Column.
Gala rezultātā, samainot arī kolonnu secību, redzama šāda ainiņa. Darbu var pabeigt ar Home -> Colse & Load.
Citas dienas valūtu kursu iegūšanai būs jāveic dubultklikšķis uz vaicājuma “yyyymmdd”, jāievada datums nepieciešamajā formā un jāveic “ecb” vaicājuma atjaunošana ar zemāk attēlā redzamo refresh ikonu.
Ja ir vēlme datumu ievadīt sev ērtākā, ierastākā formā, tad jāievieš vēl papildus parametra pārveidojumi, lai datumu pārveidotu tā, kā ir nepieciešams datu pieprasīšanai paredzētajā linkā.
Ar dublutklikšķi nepieciešams atvērt jebkuru vaicājumu, lai nokļūtu Power Query, kur jāizveido jauns parametrs ar nepieciešamo datuma formātu. Manā gadījumā dd.mm.yyyy.
Pie viena var dublēt “ecb” vaicājumu, pārsaukt saprotamā veidā, piemēram, “ecb ar datumu”. Tur būs nepieciešams ieviest pārveidojumus APPLIED STEPS solī “Source”.
Iepriekš veidotajā datuma kolonnā tagad var veikt atsauci uz parametru “datums”.
Vaicājumam “ecb ar datumu” jāatver Advanced Editor, kur var veikt papildus korekcijas kodā.
Kombinējot Power Query teksta un datumu funkcijas jāizveido mainīgais “date”, kuru pēc tam jāievieto linkā. Piemēram, Text.PadStart nodrošina to, ka mēneša vai dienu komponentēs vienmēr būs 2 cipari.
|
1 2 3 |
date = Text.From(Date.Year(datums)) & Text.PadStart(Text.From(Date.Month(datums)),2,"0") & Text.PadStart(Text.From(Date.Day(datums)),2,"0"), |
Failu ar rezultātu var lejupielādēt šeit.
2. Valūtu kursi aktuālajai dienai
Lai gan tam ir paredzēts speciāls links, tomēr, izmantojot M valodas funkcijas, to var iestrādāt arī līdz šim izmantotajā linkā.
Aktuālās dienas datums ar tiek iegūts šādi =Date.From(DateTime.LocalNow())
Šādi izskatās vaicājuma M valodas kods Advanced Editor logā.
Līdz ar to atsevišķs parametrs nav nepieciešams. Savukārt, lai panāktu to, ka dati tiek atjaunoti atverot Excel failu automātiski, jāveic labais klikšķis uz attiecīgā vaicājuma, jāizvēlas “properties” un jāveic attiecīgā atzīme, kā zemāk attēlā.
Failu ar rezultātu var lejupielādēt šeit.
3. Valūtu kursi noteiktam periodam
Var gadīties, ka nepieciešams iegūt apkopojuma tabulu ar valūtu kursiem dažādos datumos. Dotais gadījums tiek risināts izmantojot pirmo piemēru, kur tika sagatavots vaicājums ar datuma norādi datuma formātā.
Šoreiz jāizveido divi datumu parametri, kuri apzīmē perioda sākumu un beigas. Balstoties uz šiem datumiem, tiks veidota datumu virkne, pēc kuras tiks pieprasīti dati un apkopoti vienā tabulā.
Tātad vispirms jāizveidot divus datumu parametrus un visātrāk to var izdarīt veidojot kā dublikātus no jau esošā.
Pēc tam Excel logā, ejot uz Data -> Get Data -> From Other Sources -> Blank Query, no šiem diviem parametriem ar sarakstu veidošanas funkcijas palīdzību ģenerē datumu virkni.
|
1 |
= List.Dates(no, Duration.Days(lidz - no)+1, #duration(1, 0, 0, 0)) |
Vaicājumam piešķir nosaukumu “ecb_no_lidz” un rezultāts, atkarībā no izvēlētajiem datumiem, izskatās šādi.
Sarakstu pārveido par tabulu ar attēlā redzamo rīku un kolonnai piešķir atbilstošu nosaukumu un formātu.
Blakus jāizveido vēl viena kolonna, kur šie ģenerētie datumi tiek pārveidoti datu pieprasīšanas linkam nepieciešamajā formā.
Būtisks ir arī kolonnas nosaukums. Rezultāts izskatās šādi.
Pirmo vaicājumu “ecb ar datumu” pārveido par funkciju “Page” Advanced Editor logā.
Savukārt esot “ecb_no_lidz” vaicājumā Add Column sadaļā izvēlas Invoke Custom Function, kurā norāda, ka funkcijā “Page” tiks ievadīts kolonnas “yyyymmdd” saturs.
Tā rezultātā parādās vēl viena kolonna, kuru atverot var ielādēt valūtas kursus par katru datumu un apvienot vienā tabulā.
Liekās kolonnas var noņemt, pielāgot kolonnu formātus un rezultāts izskatās šādi.
Failu ar rezultātu var lejupielādēt šeit.
4. Dinamisku perioda datu iegūšana
Piemēram, valūtas kursu dati par aktuālo un iepriekšējām 7 dienām. Ja ir šāda veida vajadzība, tad datumus nav jāievada, bet virknējums jāveido no aktuālās dienas datuma uz atpakaļ.
Tātad iepriekšējā piemērā ģenerētais saraksts tiks balstīts uz aktuālās dienas datumu, kas izskatās šādi:
|
1 |
= List.Dates(Date.From(DateTime.LocalNow()), 8, #duration(-1, 0, 0, 0)) |
Līdzīgi kā iepriekšējā piemērā, kur dati tika iegūti par aktuālās dienas datumu, var iestatīt, ka vaicājums izpildās līdz ar Excel faila atvēršanu.
Failu ar rezultātu var lejupielādēt šeit.
Tev varētu noderēt arī raksts par Centrālās statistikas pārvaldes datu iegūšanu.