Jak pobierać kursy walut wprost z NBP?

W rachunkowości zdarza się używać oficjalnych kursów NBP (opisane tutaj) Od pewnego czasu Narodowy Bank Polski udostępnia aktualne kursy pod postacią API, czyli interfejsu pozwalającego łączyć różne środowiska.

Dzięki temu możemy oficjalne dane używać na żywo w naszym arkuszu. Alternatywą jest użycie wewnętrznej funkcji Arkuszy: GOOGLEFINANCE, którą omówiłem w osobnym wpisie.

Arkusz z przykładami

Ze wpisu dobrze korzystać przeglądając plik roboczy, gdzie są wszystkie przykłady “na żywo”.

Krótko o formule IMPORTXML, której tu będziemy używać

IMPORTXML służy do pobierania zawartości plików w formacie XML – to język w którym treść jest ustrukturyzowana za pomocą znaczników jak w HTML. Znajomość XML nie będzie potrzebna by zrozumieć ten wpis.

Składnia:

=IMPORTXML("link do pliku .xml";"zapytanie XPATH")

Zapytanie XPATH to sposób wydobywania informaji z plików XML. Jeśli ktoś chce zgłębić temat – tu są szczegóły. Na nasze potrzeby wystarczy wiedzieć, że jeśli wpiszemy w zapytanie “//Mid” to otrzymamy treść pliku znajdującą się pomiędzy znacznikami Mid. Na przykład jeśli w pliku będzie <Mid>cośtam</Mid>, to formuła importxml wyświetli nam cośtam.

Przykładowy plik generowany przez NBP:

Krótko o API Narodowego Banku Polskiego

Dokumentację tego interfejsu możecie znaleźć na stronie samego banku. Jest łatwa do zrozumienia. Nasze zapytanie to jest link, który można wpisać w okno przeglądarki tak jak każdy inny adres. A podane parametry ustalą co zostanie wyświetlone.

Jak pobrać bieżący kurs waluty?

Żeby pobrać kurs musimy “pójść” do banku i poprosić o niego przy pomocy API i potem formułą IMPORTXML wydłubać samą liczbę.

Wygląda to tak:

Jeśli chcemy mieć więcej walut, możemy samym arkuszem wpływać na kształt linku:

Jak pobrać kurs historyczny (na podstawie daty)?

Tutaj możemy skorzystać z właściwości API z banku. Do zapytania możemy po symbolu waluty dopisać jeszcze / i datę i otrzymamy co trzeba:

Swoją drogą 4,2 PLN za dolara w maju 2020 w środku pandemii to był niezły cios.

Uważny czytelnik zauważył, że pojawiła się tu dodatkowa formuła to_text(data) – jest ona konieczna, bo arkusz w swoim wnętrzu trzyma datę w formacie liczbowym i bez tego by wstawił do linku 43957 i nic by nie wyszło.

Czasem zdarza się, że API wypluwa błąd – nie znajduje danych dla danego dnia. Wynika to z faktu, że w dni wolne od pracy NBP nie aktualizuje kursu. Jeśli komuś to będzie przeszkadzać – proszę napisać w komentarzu, a pokażę jak obejść problem.

Jak zbudować wykres kursu z ostatniego miesiąca czy kwartału?

API pozwala na wybranie ilości ostatnich dni z których mają być zebrane kursy (np. 10 ostatnich notowań funta). Żeby to otrzymać powinniśmy wysłać do banku zapytanie:

http://api.nbp.pl/api/exchangerates/rates/a/GBP/last/10/?format=xml

Ale żeby móc wygodniej manewrować wyciągamy te zmienne do innych komórek i otrzymujemy całą kolumnę kursów:

Jeśli jeszcze opakujemy to w formułę SPARKLINE, to uzyskamy miniaturowy wykres kursu z ostatnich 10 dni.

Jak pobrać kursy z przedziału czasowego by policzyć średnią.

Zdarzało mi się wykorzystywać średni kurs z jakiegoś okresu na potrzeby statystyk sprzedaży. Nie potrzebowałem szczegółowego kursu każdej transakcji, ale warto uwzględnić czy w danym okresie dolar był w okolicach 3,40 czy 4,20… To tu jest moment by upewnić się, że nie ma problemu z separatorem dziesiętnym. Problem zależy od ustawień regionalnych. Ponieważ on wystąpił – formuła ma trochę więcej segmentów.

=average( ArrayFormula( value( substitute( importxml("http://api.nbp.pl/api/exchangerates/rates/a/"&B22&"/"&to_text(C22)&"/"&to_text(D22)&"/?format=xml";"//Mid") ;".";",") )))

Jak pobrać kurs z poprzedniego dnia?

Skoro da się pociągnąć ostatnich 10 kursów to żeby znać kurs z poprzedniego dnia wystarczy pociągnąć ostatnie 2 kursy i wybrać wcześniejszy. Można to zrobić nie podając żadnych dat.

Najpierw ciągniemy ostatnie 2 kursy:

Drugi etap to wybranie pierwszego wyniku z powstałej dwuelementowej tabeli. Używamy do tego formuły INDEX.

Z tego co słyszałem ( a nie zajmuję się księgowością) do księgowania przelewów używa się kursu z wczoraj. Dlatego szybkie pozyskanie tego kursu wydało mi się ciekawe.

Jak pobrać całą tabelę walut z NBP?

Tym razem będziemy tworzyć kilka kolumn danych, więc musimy zrobić kilka formuł IMPORTXML.

Znów sięgamy po API narodowego banku, tylko zmieniamy składnię zapytania. Tym razem w miejsce /rates/ pojawia się /tables/ . Musimy również wyciągnąć nie jeden, a trzy znaczniki, żeby mieć nazwę waluty, jej symbol i akt. kurs. Te znaczniki to odpowiednio Currency, Code i Mid.

W przypadku kursu musiałem zamienić wszędzie kropki na przecinki. I tak to się prezentuje.

Bajerek: Spróbujmy dodać flagi do kursów!

Wiem, że flagi mogę łatwo wstawić do arkusza korzystając ze strony countryflags.io

Jest tam dostęp do wszystkich flag w plikach graficznych uporządkowanych według 2 cyfrowych kodów państw:

Adres:

https://www.countryflags.io/br/flat/64.png

zawiera flagę BRazylii wielkości 64 x 64 pikseli. Flat oznacza zwykłą wersję. Jest jeszcze opcja shiny – połyskliwa.

Przy okazji warto wiedzieć, że 3- literowy kod waluty w większości wypadków zawiera w sobie 2-literowy kod kraju. Np. USD to US dollar. Więc wystarczy, że podstawimy pierwsze 2 litery kodu waluty do linku tworzącego flagi i powinno się udać.

Łączymy wszystko razem. Do wyświetlania grafiki służy formuła IMAGE(url zdjęcia)(opisana w tym wpisie). Do tego musimy skrócić kod waluty przy pomocy funcji LEFT(treść do przycięcia;ile znaków zostawić)

Efekt wygląda tak:

Jeśli nie chcemy kopiować formuły IMAGE w dół, możemy opakować ją w formułę tablicową, która “sama się skopiuje”. Arrayformula jest opisana w odrębnym wpisie.

Błędy i pułapki

Kropki zamiast przecinków

Jeśli podstawiamy dane z importu NBP, musimy upewnić się, że są dobrze zinterpretowane. Wartości w wygenerowanym pliku z NBP mają kropki zamiast przecinków. O ile nie przeszkadza to w czytaniu, to arkusz dane często zaczyna interpretować jako daty i gubi się niesamowicie. Dlatego jeśli używamy polskich ustawień w google sheets, powinniśmy przed wykonaniem operacji na tych danych, zamienić im kropki na przecinki.

Robimy to obudowując każdą funkcję formułą zamieniającą SUBSTITUTE i ustalającą format na liczbowy VALUE:

= value(substitute(nasza formuła;".";","))

W przypadku formuł generujących całe tablice i wykresy, musimy dodać jeszcze Arrayformula, żeby formatowanie zostało wykonane na wszystkich wartościach tablicy.

= Arrayformula(value(substitute(nasza formuła;”.”;”,”)))