Funkcja pobierająca kursy walut z NBP - Krystian Brożek what medications contain ivermectin can we use ivermectin daily does ivermectin kill red mites on chickens does ivermectin kill ticks on dogs ivermectina comprar ultrafarma ivermectin tablet use ivermectina gotas dosis niños 3 años

Tworząc zestawienia i analizując dane w Excelu czasem potrzebujemy aktualnych danych. Są to np. kursy walut, ceny akcji lub cenę z konkretnego dnia jakiegoś produktu. Ręczne kopiowanie jest męczące i łatwo się pomylić. Znacznie lepiej skorzystać z tego, co zostało udostępniane w sieci i automatycznie pobierać dane.

Tym razem chciałbym pokazać możliwość pobierania danych z sieci. Zrobimy to na przykładzie ściągania kursów walut z NBP. W tym celu utworzymy funkcję, która na podstawie waluty i daty zwróci nam ile złotówek musimy zapłacić za jedną jednostkę.

NBP udostępnia użytkownikom API, które umożliwia pobieranie kursów walut. W skrócie to zestaw funkcji dostępnych w sieci. Wymagają jednak dodatkowego kodu, aby móc z nich korzystać. Dokumentację API udostępnionego przez Narodowy Bank Polski znajdziesz tu: api.npb.pl. Wynikiem działania takich funkcji jest dokument XML lub JSON (w dużym skrócie, to uproszczony XML). Daje to ogromną elastyczność, ponieważ nie ważne z jakiego języka programowania będziemy chcieli wywołać takie funkcje, prawie każdy obsługuje XML i JSON. Można również potraktować je jako tekst i łopatologicznie wyciągać z tych dokumentów informacje.

My skorzystamy z dokumentów XML. Jeśli chcesz zobaczyć jak wygląda taki dokument wjedź pod poniższy adres

http://api.nbp.pl/api/exchangerates/rates/c/gbp/2018-07-02/?format=xml

Jest to właśnie url, który wywołuje funkcję API. Mamy tu dwa parametry, które nas interesują. Pierwszym z nich to waluta – w tym przypadku gbp. Drugim data kursu, czyli 2 lipca 2018. Musimy jednak zachować format daty zgodny z dokumentacją NBP, czyli rrrr-mm-dd.

Zatem pierwszym krokiem w stworzeniu naszej funkcji będzie stworzenie linka i przyjęcie dwóch wyżej wspomnianych parametrów. Oczywiście zachowamy odpowiedni format daty. Do tego celu wykorzystamy funkcję Format.

Function PobierzKurs(kurs As String, data As Date)
link = "http://api.nbp.pl/api/exchangerates/rates/c/" & kurs & "/" & Format(data, "yyyy-mm-dd") & "/?format=xml"
End Function

Kolejny krok to odpalenie linka i odebranie informacji. W tym celu musimy rozbudować naszą funkcję o poniższy kod.

Function PobierzKurs(kurs As String, data As Date)
Dim hReq As Object

link = "http://api.nbp.pl/api/exchangerates/rates/c/" & kurs & "/" & Format(data, "yyyy-mm-dd") & "/?format=xml"

Set hReq = CreateObject("MSXML2.XMLHTTP")
With hReq
.Open "GET", link, False
.Send
End With

odpowiedz = hReq.ResponseText
End Function

W zmiennej odpowiedz mamy XML z odpowiedzią typu string. Zostało nam już tylko odczytanie odpowiedniej wartości. Zaczniemy od zamienienia naszej odpowiedzi w dokument XML. Aby to uczynić trzeba dodać referencję, czyli powiedzieć VBA, aby skorzystało z kodu napisanego przez kogoś. Biblioteka zawierająca wszystko o XMLu została dostarczona razem z Excelem. Zatem wystarczy ją tylko dodać do naszego makra. Wybieramy Tools-> References… i na liście wyszukujemy Microsoft XML, v6.0. Zaznaczamy, dajemy ok i już 🙂

Dim objxml As Object
Set objxml = New MSXML2.DOMDocument60
objxml.LoadXML (odpowiedz)

Zostaje nam już tylko odczytanie z XMLa interesującej nas wartości. Dla uproszczenia zrobiłem to trochę łopatologicznie. Zatem odczytuję po kolei element o indeksie 3 (czyli czwarty, bo numerujemy od 0), następnie wybieramy pierwszy podelement i z niego Bid, czyli ten o indeksie 3. Na koniec z niego odczytuję wartość.

Poniżej ścieżka jaką przeszliśmy:

  1. ExchangeRatesSeries (DocumentElement)
  2. Rates (wśród ChildNodes wybieram Item(3))
  3. Rate (wśród ChildNodes wybieram Item(0))
  4. Ask (wśród ChildNodes wybieram Item(3))
  5. Szukana wartość (nodeTypedValue)
Function PobierzKurs(kurs As String, data As Date)
Dim hReq As Object
Dim objxml As Object

link = "http://api.nbp.pl/api/exchangerates/rates/c/" & kurs & "/" & Format(data, "yyyy-mm-dd") & "/?format=xml"

Set hReq = CreateObject("MSXML2.XMLHTTP")
With hReq
.Open "GET", link, False
.Send
End With

odpowiedz = hReq.ResponseText

Set objxml = New MSXML2.DOMDocument60
objxml.LoadXML (odpowiedz)
PobierzKurs = objxml.DocumentElement.ChildNodes.Item(3).ChildNodes.Item(0).ChildNodes.Item(3).nodeTypedValue
End Function

Istnieje jeszcze jeden problem. W niektóre dni nie było podanego kursu walut (np. w weekendy). Funkcja z NBP zwróci informację o braku danych. Dodamy zatem warunek sprawdzający, czy na końcu odpowiedzi z serwera jest napis „Brak danych”

Function PobierzKurs(kurs As String, data As Date)
Dim hReq As Object
Dim objxml As Object

link = "http://api.nbp.pl/api/exchangerates/rates/c/" & kurs & "/" & Format(data, "yyyy-mm-dd") & "/?format=xml"

Set hReq = CreateObject("MSXML2.XMLHTTP")
With hReq
.Open "GET", link, False
.Send
End With

odpowiedz = hReq.ResponseText

If odpowiedz Like "*Brak danych" Then
PobierzKurs = "Brak danych"
Else
Set objxml = New MSXML2.DOMDocument60
objxml.LoadXML (odpowiedz)
PobierzKurs = objxml.DocumentElement.ChildNodes.Item(3).ChildNodes.Item(0).ChildNodes.Item(3).nodeTypedValue
End If
End Function

Sporo pytań pojawiło się o odczyt danych z tabeli A, dlatego poniżej prezentuję działający kod ze zmianami:

Function PobierzKurs(kurs As String, data As Date)
Dim hReq As Object
Dim objxml As Object

link = "http://api.nbp.pl/api/exchangerates/rates/a/" & kurs & "/" & Format(data, "yyyy-mm-dd") & "/?format=xml"

Set hReq = CreateObject("MSXML2.XMLHTTP")
With hReq
.Open "GET", link, False
.Send
End With

odpowiedz = hReq.ResponseText

If odpowiedz Like "*Brak danych" Then
PobierzKurs = "Brak danych"
Else
Set objxml = New MSXML2.DOMDocument60
objxml.LoadXML (odpowiedz)
PobierzKurs = objxml.DocumentElement.ChildNodes.Item(3).ChildNodes.Item(0).ChildNodes.Item(2).nodeTypedValue
End If
End Function

No i już. Wszystko powinno działać. W ten sam sposób można korzystać z innych udostępnionych API. Trzeba znaleźć dokumentację lub samemu wywnioskować co dane API zwraca. Jeśli wykorzystujesz API w swoich projektach pochwal się w komentarzu.

W pliku dołączonym do wpisu znajdziesz gotową funkcję z przykładem działania.