Jak pobrać archiwalne kursy walut z nbp.pl do Excela lub Power BI?

Bardzo często na szkoleniach słyszymy pytanie jak przeliczyć kurs transakcji na inną walutę. A gdy na nie odpowiemy to często pytacie: „no tak, ale ja chce mieć kurs tak naprawdę na poprzedni dzień roboczy”. Gdy wytłumaczymy ideę to pada fundamentalne pytanie, na które czekamy 😊 „a jak to zrobić żeby robiło się automatycznie”.

Jeśli poszukujesz tylko aktualnego kursu waluty to sprawdź ten artykuł (https://imperiumszkoleniowe.pl/kursy-walut-z-nbp-pl-automatycznie-w-excelu/)

!!!!!! Zmienić! !!!!

Dzisiejszy artykuł będzie o podstawowej części, której potrzebujesz do zautomatyzowania przeliczenia transakcji na inną walutę po kursie na poprzedni dzień roboczy. Przyjrzymy się w jaki sposób pobrać automatycznie kursy walut ze strony nbp.pl z użyciem Power Query. Power Query znajdziesz wbudowany w Excela jak również w Power BI. Artykuł i zrzuty ekranu opieramy o Power BI, jednak jeśli chcesz korzystać z Excela – bez problemu sobie poradzisz. Pamiętaj jedynie gdzie znaleźć w Excelu odpowiednią opcję wywołującą Power Query:

Pierwsze pytanie nad którym musimy się pochylić to miejsce, skąd można pobrać oficjalne, aktualne kursy walut. Z pomocą przychodzi nam tutaj strona nbp.pl, a konkretnie link poniżej:

Na stronie Narodowego Banku Polskiego znajdziesz listę linków do plików przechowujących kursy walut za dany rok (dla każdego dnia, kiedy publikowane są notowania – w uproszczeniu dla dni roboczych).

Podczas pisania tego artykułu najbardziej aktualnym plikiem jest plik za rok 2021. W pierwszej kolejności należy pozyskać bezpośredni tekst hiperłącza. Najłatwiej to wykonać klikając prawym przyciskiem myszy na numer roku w sekcji „Format CSV” i wybierając polecenie Kopiuj adres linku.

Dla roku 2021 link odnośnika to:

https://www.nbp.pl/kursy/Archiwum/archiwum_tab_a_2021.csv

Kolejnym krokiem będzie przerzucenie odnośnika do narzędzia, gdzie chcemy mieć dostępne dane (Excel/Power BI). W Power BI należy w grupie Pobierz dane wskazać Internet. Kuszące może być wskazanie opcji Plik tekstowy lub CSV, jednak dla nas (a raczej dla Power BI 😊 ) ważniejsze jest to, że należy przemieścić się do sieci www i tam będzie w stanie rozpoznać do czego konkretnie się podłączamy (plik HTML czy np. csv)

W oknie adresu w Power BI następnie wklejamy adres linku (skopiowany wcześniej):

I potwierdzamy OK.

Power BI wyświetli okno podglądu źródła danych, które w tym przypadku… Cóż… Pozostawia trochę do życzenia. Naszym zadaniem zatem stanie się takie przekształcenie danych w Power Query, żeby można było z nimi swobodnie i komfortowo pracować w Power BI i wykorzystać np. w formułach DAX.

Klikamy zatem przycisk Przekształć dane.

Zostaniemy przeniesieni do okna Power Query z podglądem danych źródła:

Zanim zaczniemy pracę z danymi zidentyfikujmy problemy, które należy rozwiązać:

1.  Pod wierszem nagłówkowym istnieje jeden nadmiarowy wiersz, który nie jest nam potrzebny:

2. Na końcu tabeli (z prawej strony) znajdują się trzy niepotrzebne kolumny:

3. Na końcu tabeli (na dole) znajdują się cztery niepotrzebne dla naszej analizy wiersze:

4. Dane ułożone są tak, że każda waluta ma dla siebie oddzielną kolumnę, co nie jest najwygodniejszym sposobem przechowywania danych w Power BI czy też w Excelu. Zdecydowanie wygodniej byłoby pracować z taką    tabelą, gdyby miała układ zgodny z poniższym:

5. Czasem koszt waluty podany jest dla 1 jednostki waluty np. 1EUR a czasem dla paczki wielu sztuk – np. 100CHF. Dużo wygodniejsze byłoby przechowywanie zawsze jednostkowego kosztu waluty, tak aby nie trzeba było się głowić przy przeliczaniu kwot transakcji.

6. Dane liczbowe (kursy walut) traktowane są jako tekst – potrzebujemy natomiast ich w postaci liczbowej jeśli chcemy ich użyć później w Power BI do obliczeń z wykorzystaniem DAX czy też przekształceń w Power Query,

7. Data nie jest prawdziwą datą. Jeśli nie przekształcimy ją na datę w rozumienie stricte Power BI – nie będziemy w stanie analizować danych z wykorzystaniem chociażby funkcji typu Time Intelligence w DAX.

Jak widzimy, jest sporo problemów, które należy naprawić przed wczytaniem danych do Power BI. Pozytywne jest to, że wykorzystując Power Query (niezależnie czy w Power BI czy w Excelu) takie czynności (naprawy) wykonamy tylko 1 raz. Potem będziemy tylko odświeżać nasze zapytanie (gdy pojawią się bardziej aktualne kursy, a Power Query sam będzie szedł do strony nbp.pl i wykonywał wszystkie te działania. Zacznijmy zatem naprawy.

Usuwamy niepotrzebne wiersze (pkt 1 i 3).

Pierwszy wiersz usuwamy wybierając opcje Usuwanie pierwszych wierszy dostępną na karcie Narzędzia główne w sekcji Zmniejsz wiersze.

Po wybraniu tej opcji należy wskazać ile wierszy z widocznego na danym etapie (tym samym dostępnego) datasetu należy usunąć. W naszym konkretnym przypadku pobierania kursów walut z nbp.pl jest to jeden wiersz:

Analogicznie wybieramy opcję Usuwanie końcowych wierszy i wskazujemy, że interesuje nas usunięcie 4 ostatnich wierszy.

Pozbyliśmy się już niepotrzebnych wierszy, które utrudniałyby nam dalsze przekształcenia w Power Query.

Usuwamy niepotrzebne kolumny (pkt 2).

Usuwanie kolumn jest jedną z najprostszych czynności, które można wykonać w edytorze Power Query, choć trzeba przyznać – bywa osobliwe. Dlaczego? Otóż – możemy usunąć kolumny zaznaczone, lub usunąć kolumny spoza zaznaczenia. Każda z tych opcji zapisuje w inny sposób kod w języku M, który wpływa na późniejsze działanie zapytania gdyby pojawiły się nowe kolumny, to jednak temat na oddzielny artykuł 😊

Zaznaczmy 3 ostatnie kolumny i wybierzmy polecenie Usuń kolumny.

Jesteśmy coraz bliżej celu 😊 Na tym etapie mamy już tylko dane, których potrzebujemy choć niestety wciąż nie w takim formacie/układzie jaki byłby dla nas idealny.

Odpivotowujemy dane

Nie znaleźliśmy lepszego słowa na tłumaczenie tej opcji w Power Query. Technicznie – opcja, z której korzystamy nazywa się „Anuluj przestawienie kolumn”, jednak w naszej ocenie – nazwa nie sugeruje użycia, dlatego trzymamy się zapożyczenia „odpivotowywanie”.

W tym kroku interesuje nas sprowadzenie danych do postaci, w której jedna kolumna będzie zawierać datę, druga nazwę waluty (z nagłówka), a trzecia wartość.

Klikamy zatem na pierwszą kolumnę z Datą prawym przyciskiem myszy i wybieramy polecenie Anuluj przestawienie innych kolumn.

Nasze dane na obecnym etapie transformacji wyglądają już (prawie) idealnie:

Naprawiamy formaty danych (pkt 6)

Musimy zaopiekować się datami oraz liczbami. W pierwszej kolejności klikamy w ikonę wyróżnika typu w kolumnie z datą i wybieramy Data:

W kolejnym kroku klikamy ikonę typu w nagłówku Wartość i wybieramy Liczba dziesiętna.

Obecnie nasze dane wyglądają już dość dobrze 😊 i widzimy dodatkowo jak potężnym narzędziem jest Power Query.

!!!! zaktualizować !!!!!

Często na szkoleniach (np. tutaj) mówimy, że jeśli Power BI to Batman, to Power Query to jego Robin.

Ostatnim etapem, który może nam spędzać jeszcze sen z powiem jest sposób zapisu danych na nbp.pl. Kolumna Wartość odpowiada kosztowi określonej liczby jednostek waluty.

Na zrzucie powyżej możemy zauważyć, że np. za 1 USD należy zapłacić 3,6998 zł. Jeśli chcielibyśmy podać koszt jednostkowy jednego 1 HUF to musielibyśmy odnaleźć odpowiedni kurs – 1,2605 i podzielić go przez liczbę jednostek waluty w paczce, gdyż koszt HUF jest podawany za 100 szt.

Taki zapis niestety utrudnia nam dalszą pracę, ponieważ analizując transakcje w różnych walutach musielibyśmy zadbać o to, aby za każdym razem sprawdzać koszt jednostkowy. Zdecydowanie wygodniejszym rozwiązaniem będzie zadbanie o to już na etapie ładowania danych ze strony Narodowego Banku Polskiego.

Przeliczamy koszt jednostkowy waluty (pkt 5)

Proces ten możemy podzielić na 2 etapy:

1. Podział kolumny o obecnej nazwie „Atrybut” tak, aby w jednej kolumnie uzyskać liczbę jednostek, w drugiej zaś nazwę kodu waluty,
2. Podzielenie kursu (kolumna Wartość) przez liczbę jednostek waluty (która dopiero powstanie 😊).

Power Query oferuje nam wiele możliwości podziału tekstu na kolumny. To zadanie możemy też na kilka sposobów rozwiązać. Pokażemy tutaj na blogu rozwiązanie pół-klasyczne.

Aby podzielić tekst w kolumnie – należy kliknąć na nią prawym przyciskiem myszy i wybrać polecenie Podziel kolumny/Według liczby znaków…

Wiedząc, że kod waluty jest zawsze 3-znakowy (np. PLN, USD, EUR) możemy wskazać, że interesuje nas przecięcie kolumny przed ostatnimi 3 znakami w komórce. Wskaż opcje jak na poniższym zrzucie, czyli:

Liczba znaków: 3

Jedno, jak najdalej z prawej strony

Po zatwierdzeniu przyciskiem OK okna Dzielenie kolumny według liczby znaków otrzymasz następujący układ danych:

Ostatnim krokiem jest już tylko podzielenie wartości z kolumny Wartość przez kolumnę Atrybut.1…

… oraz posprzątanie 😊 – usuwamy więc, niepotrzebne kolumny, zmieniamy ich nazwy a także nazwę zapytania.

Tym samym udało nam się uzyskać kursy walut z całego roku pobierane bezpośrednio ze strony Narodowego Banku Polskiego.

Pamiętaj, że korzystając z Power Query możesz te dane pobrać zarówno do Power BI jak również do Excela i wykorzystywać do dalszych przeliczeń – zarówno z użyciem Power Query, języka DAX czy też zwykłych formuł po załadowaniu danych do Excela.

Chcesz dowiedzieć się więcej na temat wykorzystania Power Query i jego współpracy z Excelem i Power BI? Sprawdź nasze szkolenie e-learningowe: tutaj

A jeśli wolisz przyswajać wiedzę bardziej „klasycznie” to zerknij na opcje szkoleń stacjonarnych/zdalnych/zamkniętych: tutaj.