Valūtu kursu iegūšana Excel vai Power BI

valutu-kursi-Excel-PowerBI

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ā.

1-valutas-kursa-adrese.png

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.

2-valutas-kursi-error.png

Open file as izvēlnē norāda Csv Document.

3-open-file-as.png

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.

4-atdalitajs.png

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.

5-pirmie-rezultati.png

Home cilnē no Manage Parameters jāizvēlas New.

6-manage-parameters.png

Aizpilda parametram nepieciešamos laukus tā, kā redzams zemāk attēlā.

7-manage-parameters-izveide.png

Kreisajā pusē sadaļā Queries tagad var redzēt šādu ainiņu.

8-vaicajumi.png

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.

9-source-step.png

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.

10-solis-ar-parametru.png

Lai būtu ērtāk, parametra datus var ievadīt arī atsevišķā kolonnā. Add Column -> Custom Column.

11-kolonna-ar-parametru.png

Gala rezultātā, samainot arī kolonnu secību, redzama šāda ainiņa. Darbu var pabeigt ar Home -> Colse & Load.

12-dienas-valutas-kurss-Excel.png

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.

14-Excel-Queries.png

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.

15-parametrs-ar-Excel-datumu-ddmmyyyy.png

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”.

16-Excel-jauns-vaicajums.png

Iepriekš veidotajā datuma kolonnā tagad var veikt atsauci uz parametru “datums”.

17-Power-Query-jauna-kolonna.png

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.

18-advanced-editor.png

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ā.

19-advanced-editor.png

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ā.

20-Excel-properties

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ā.

21.png

Š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šā.

22-papildus-parametri.png

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.

23-power-query-liste.png

Vaicājumam piešķir nosaukumu “ecb_no_lidz” un rezultāts, atkarībā no izvēlētajiem datumiem, izskatās šādi.

24-genereti-datumi.png

Sarakstu pārveido par tabulu ar attēlā redzamo rīku un kolonnai piešķir atbilstošu nosaukumu un formātu.

25-to-table.png26-papildus-kolonna.png

Blakus jāizveido vēl viena kolonna, kur šie ģenerētie datumi tiek pārveidoti datu pieprasīšanas linkam nepieciešamajā formā.

27-kolonna-ar-datumiem.png

Būtisks ir arī kolonnas nosaukums. Rezultāts izskatās šādi.

28-datumu-kolonnas-rezultats.png

Pirmo vaicājumu “ecb ar datumu” pārveido par funkciju “Page” Advanced Editor logā.

29-power-query-funkcija.png

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.

30-invoke-function.png

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ā.

31-invoke-custom-function.png

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:

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.

 

 

 

Komentēt