O soluție pentru a importa și a actualiza cursul BNR într-un fișier Excel, fără VBA, folosind interogări Power Query și o formulă Excel
BNR oferă pentru programatori Cursurile pieței valutare în format XML. De aici vom folosi setul de înregistrări zilnice cu ultimele 10 cursuri valutare, aflate la adresa http://www.bnro.ro/nbrfxrates10days.xml
De ce avem nevoie de mai multe cursuri ale aceleași monede? Practic avem nevoie doar de ultimele două, vom stabili asta la construirea interogării. Se știe că BNR publică cursul valutar valabil în ziua următoare publicării (sau până luni inclusiv, dacă e vorba de cursul publicat vinerea). De aceea avem nevoie de ultimele două cursuri. Dacă ultimul curs publicat este de azi, vom folosi penultimul curs. Dacă ultimul curs publicat este anterior datei curente, îl vom folosi pe acela. BNR publică noul curs în jurul orei 13:00.
Fișierul XML pe care îl vom interoga arată astfel:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 | <DataSet xmlns="http://www.bnr.ro/xsd" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.bnr.ro/xsd nbrfxrates.xsd"> <Header> <Publisher>National Bank of Romania</Publisher> <PublishingDate>2024-12-11</PublishingDate> <MessageType>DR</MessageType> </Header> <Body> <Subject>Reference rates</Subject> <OrigCurrency>RON</OrigCurrency> <Cube date="2024-12-11"> <Rate currency="AED">1.2888</Rate> <Rate currency="AUD">3.0045</Rate> <Rate currency="BGN">2.5403</Rate> <Rate currency="BRL">0.7828</Rate> <Rate currency="CAD">3.3362</Rate> <Rate currency="CHF">5.3533</Rate> <Rate currency="CNY">0.6512</Rate> <Rate currency="CZK">0.1979</Rate> <Rate currency="DKK">0.6662</Rate> <Rate currency="EGP">0.0934</Rate> <Rate currency="EUR">4.9684</Rate> <Rate currency="GBP">6.0241</Rate> <Rate currency="HKD">0.6090</Rate> <Rate currency="HUF" multiplier="100">1.2100</Rate> <Rate currency="IDR" multiplier="100">0.0297</Rate> <Rate currency="ILS">1.3234</Rate> |
Pentru a începe construirea interogării, alegem Data – From Web
Completăm adresa fișierului
apoi alegem Body și Transform data
Intrăm astfel în editorul de interogări Power Query Editor. Deoarece porțiunea Body din fișierul XML este, de fapt, o zonă multi-dimensională, cu tabele imbricate, avem nevoie să aducem totul la un singur tabel cu două dimensiuni, rânduri și coloane. Pentru aceasta vom da click pe butonul din imagine
apoi, având toate coloanele selectate, pe OK
Din nou click pe butonul din imagine, ne asigurăm că sunt afișate și selectate toate coloanele și OK.
În acest moment avem vizibile toate datele, în format tabelar. Vom șterge coloanele de care nu avem nevoie, anume Subject, OrigCurrency și Cube.Rate.Attribute:multiplier. Aceasta din urmă nu e necesară, vom folosi multiplicatorul doar dacă e nevoie, în formula Excel de la sfârșit.
Pentru a șterge toate coloanele într-o operațiune, le selectăm cu mouse-ul ținând tasta Ctrl apăsat și click pe capul coloanelor respective. Apoi alegem butonul Remove Columns.
Ne-au rămas doar coloanele de care avem nevoie: Cube.Rate.Element:Text (cursul), Cube.Rate.Attribute:currency (moneda) și Cube.Attribute:date (data calendaristică). Teoretic, nici de coloana cu moneda nu avem nevoie, dar o păstrăm pentru considerente practice, pentru a vedea în Excel în orice moment cu ce monedă lucrăm.
Mai departe, modificăm numele ultimelor două coloane, pentru o mai ușoară citire a datelor, aici și în Excel. Dăm dublu-click pe numele coloanelor și modificăm în Moneda, respectiv Data.
Numele primei coloane nu are rost să-l modificăm, deoarece cursul (ca de altfel toate datele din tabel) este de tip text, noi interogând un fișier text. Vom obține mai târziu cursul în format numeric.
Observăm că data calendaristică este într-un format de afișare impropriu AAAA-LL-ZZ. În pasul următor o vom aduce la tipul dată calendaristică, formatată corect.
Având coloana selectată (click pe numele coloanei), mergem la Transform – Date – Parse
și obținem rezultatul
Poate vă întrebați de ce nu putem face același lucru și cu coloana cursului. Răspunsul este simplu: pentru că separatorul zecimal folosit în fișierul XML este punctul, nu virgula. Și pentru că suntem deștepți, am schimbat de mult sistemul din US în România, folosim ca separator zecimal virgula, ca separator mii punctul si datele calendaristice sunt de forma zz.ll.aaaa. Așa ați făcut, nu? 😀
Mai departe, filtrăm tabelul după moneda dorită (la fel cum facem în Excel).
Acum ne vom ocupa de coloana care conține cursul, să o aducem la tipul numeric, formatat corect.
Având coloana selectată, alegem Add Column – Custom Column și obținem următoarea fereastră.
Îi dăm coloanei numele Curs și după semnul „=” introducem formula
Value.Divide(Number.FromText([#"Cube.Rate.Element:Text"]),10000)Această formulă convertește textul în număr, rezultatul fiind un număr întreg (deoarece, cum spuneam, în fișierul XML se folosește punctul ca separator zecimal) și împarte rezultatul la 10000 pentru a obține valoarea corectă (toate valorile comunicate de BNR sunt cu 4 zecimale).
De coloana Cube.Rate.Element:Text nu mai avem nevoie mai departe, dar nu o ștergem, ci o ascundem. O selectăm și Choose Columns, apoi debifăm coloana din listă.
Mai departe, vom edita interogarea astfel încât să obținem doar primele două rânduri. Keep Rows – Keep Top Rows și completăm cu 2.
În acest moment avem toate datele necesare. Dacă dorim (dar nu este necesar), putem schimba ordinea coloanelor. Se trage cu mouse-ul de capul coloanei și se repoziționează în locul dorit.
Mai facem ceva și anume schimbăm numele interogării, ținând cont că acest nume va fi preluat apoi în Excel ca nume al tabelei create. Putem să folosim caracterul spațiu, acesta va fi înlocuit automat în numele tabelei cu _.
Interogarea este gata, alegem Close & Load To… pentru a ne întoarce în Excel și a obține rezultatul interogării.
Formula pe care o utilizăm pentru a extrage cursul corect azi este
=IF(DAY(INDEX(Curs_BNR[Data];1))=DAY(TODAY());INDEX(Curs_BNR[Curs];2);INDEX(Curs_BNR[Curs];1))
Care se traduce astfel: dacă ziua calendaristică din primul rând al tabelului este egală cu ziua calendaristică curentă
DAY(INDEX(Curs_BNR[Data];1))=DAY(TODAY())se ia cursul din rândul al doilea
INDEX(Curs_BNR[Curs];2)dacă nu, se ia cursul din primul rând.
INDEX(Curs_BNR[Curs];1)De aici deducem că tabelul nu trebuie sortat în nici un fel, trebuie păstrat așa cum rezultă din interogare.
Dacă doriți actualizarea automată a cursului la deschiderea fișierului, fiind poziționat oriunde în tabel, Data – Refresh All (aici dăm click pe jumătatea de jos a butonului, unde este vârful de săgeată, pentru a se deschide meniul) – Connection Properties și bifăm Refresh data when opening the file.
Ce facem dacă dorim să importăm mai multe monede? Avem două variante, una mai ușoară și alta mai complexă:
- Varianta ușoară este să copiem interogarea / tabelul, să edităm interogarea și la filtrarea monedei să alegem o altă monedă
- Varianta mai complexă este ca la filtrarea din interogare să alegem mai multe monede, rezultând astfel un tabel cu mai multe monede. În acest caz, formula Excel pentru extragerea cursului corect devine mult mai complexă, implicănd și căutări de monede cu INDEX() + MATCH().
Editarea, respectiv copierea (duplicarea) interogării le găsiți la Query Tools după ce v-ați poziționat undeva în tabel.
Update 17-12-2024:
Am publicat articolul
Cursul BNR în Excel fără VBA – mai multe valute
O soluție pentru a importa și a actualiza cursul BNR pentru mai multe valute, într-un fișier Excel, fără VBA, folosind interogări Power Query și o formulă Excel
Sursa: Freepik.com
