Jak używać aktualnych kursów walut, aktualnych cen benzyny i surowców.

Nie musisz ręcznie aktualizować kursów walut w swoich arkuszach. Co więcej, jak się trochę postarasz, to możesz pobierać też automatycznie ceny paliwa, ceny surowców. A także sięgać po dane historyczne kursów jeśli potrzebujesz przeliczyć coś z przeszłości. Oczywiście staramy się to osiągnąć bez pisania skryptów – jak najprostszymi narzędziami Google Sheets.

Wszystkie przykłady możecie zobaczyć w tym pliku google sheets. Jeśli chcecie go edytować, musicie zrobić własną kopię (Plik —> Utwórz kopię)

Aktualny kurs w obliczeniach

Z pomocą przychodzi nam formuła GOOGLEFINANCE. To genialna funkcja sięgająca po dane z giełd i bieżące dane o kursach na rynkach światowych. W przypadku walut kursy mają opóźnienie maksymalnie 3 minut.

Wnikliwych i bardzo ostrożnych zachęcam do poczytania dokumentacji źródeł danych do Googlefinance.

Najprostsze jej użycie to sięgnięcie po kurs waluty. Wówczas funkcja będzie miała składnię:

=googlefinance("CURRENCY:symbol1symbol2")

CURRENCY – mówi, że będziemy operować na kursach walut

symbol1 oraz symbol2 – to międzynarodowe symbole walut (PLN, USD, EUR, GBP, CHF, etc…) Jeśli nie jesteś pewien symbolu waluty, to skorzystaj z tego spisu na stronie IBAN.

Wynik ma postać liczby więc może być wpleciony w formułę, zagnieżdżany. Zatem jeśli chcemy szybko policzyć ile wyniesie kurs dolara jak wzrośnie o 10% to możemy wpisać:

Definiowanie nazw walut i używanie ich w formułach.

Jeśli kursów używamy w wielu miejscach w arkuszu, dobrze będzie jeśli zdefiniujemy sobie nazwę tej zmiennej. Tak aby lepiej się czytało formułę.

Możemy zrobić np. tak by komenda =10*dolar dawała nam informację ile złotych jest wart banknot 10 dolarowy.

W tym celu należy nazwać komórkę w której obliczamy kurs (Menu –> Dane —> Zakresy nazwane):

Od tej pory możesz używać słowa dolar w swoich obliczeniach w dowolnym miejscu pliku i Arkusze będą podstawiać tam aktualny kurs.

Użycie kursu historycznego

Załóżmy, że kupiliśmy laptopa za 800 € w lipcu 2016 i chcemy wiedzieć ile to wówczas było w złotówkach. Albo grzebiemy się w starych transakcjach firmy i musimy używać historycznych kursów. Funkcja GOOGLEFINANCE pozwala nam sięgać po kursy walut z przeszłości – z konkretnego dnia.

Tylko trzeba ją trochę uporządkować.

W komórce C5 umieszczam datę o którą mi chodzi, a następnie w komórce poniżej wpisuję funkcję: =googlefinance("CURRENCY:EURPLN";"price";C5)

“price” – czyli cena to jeden z atrybutów o które można poprosić funkcję (zawsze musi wystąpić jako drugi – po symbolu giełdowym

C5 – to miejsce w którym jest podana data. Mogłem ją również wpisać w samą formułę. Wówczas należałoby umieścić ją w cudzysłowiu: “2016-07-22”.

Widać, że funkcja wyświetliła trochę dużo informacji – całą tabelkę z nagłówkami. Mamy info, że chodzi o kurs końcowy z dnia. No, ale ciężko to użyć do obliczeń. Chyba, że będziemy się odwoływać do komórki D7 w której jest potrzebna nam liczba.

Ale możemy zadziałać bardziej elegancko i postarać się by wyświetliło nam tylko tą wartość 4,3627… z drugiej kolumny i drugiego wiersza wygenerowanej tabelki.

W tym celu używamy funkcji INDEX i zagnieżdżamy ją.

Jak się pewnie domyśleliście, funkcja INDEX ma składnię:

=INDEX(zakres;numer wiersza;numer kolumny)

Tym razem zakresem będzie to co wygeneruje nam funkcja googlefinance a nie adres z całego arkusza.

Pamiętaj, że symbol € możesz uzyskać wciskając prawy Alt + U

Użycie średniego kursu z danego okresu

Może zdarzyć się też, że nie chcemy kursów z poszczególnych dni czy wszystkich transakcji, tylko żeby mieć ogólne pojęcie jaki kurs był w danym okresie w przeszłości. Załóżmy, że chodzi o euro i lipiec 2016 roku, kiedy kupiliśmy laptopa (który właśnie padł 😉 )

=googlefinance(“CURRENCY:EURPLN”;”price”;”2016-07-01″;”2016-08-01″)

Teraz mamy już do czynienia z całą składnią googlefinance:

=googlefinanace(symbol giełdowy; atrybut; data początkowa; data końcowa; interwał)

• symbol to rynek walut i relacja euro do złotówki
• atrybut to cena
• data początkowa – pierwszy lipca 2016
• data końcowa – pierwszy sierpnia 2016 (bez tej daty)
• interwał – jeden dzień (to wartość domyślna, możemy też wpisać “weekly” by dostać wartości co tydzień)

Nie potrzebujemy takiej wielkiej tabeli – wystarczy nam średnia z wartości z drugiej kolumny – jedna liczba. Żeby ją uzyskać bez zajmowania cennego miejsca w arkuszu i trzymania bałaganu, możemy zagnieździć funkcje. W tym celu użyjemy funkcji INDEX (składnia opisana we wcześniejszym przykładzie) by wziąć tylko drugą kolumnę i funkcji AVERAGE by uzyskać średnią z tej kolumny. Wszystko w jednej komendzie.

Pobieranie cen benzyny (np. do rozliczenia przejazdów służbowych)

W przypadku rozliczania wyjazdów służbowych zdarza się, że nie korzysta się z konkretnych faktur tylko z przybliżonych cen. Dlatego dobrze mieć narzędzie do ich importowania. Załóżmy, że rozliczamy się z handlowcem, który jeździ swoim autem po Polsce, tankuje w różnych miejscach i musimy przyjmować jakieś wartości do rozliczeń.

Na stronie autocentrum.pl znalazłem ładną i aktualną tabelę.

Do ściągnięcia danych używam funkcji IMPORTHTML:

=IMPORTHTML(url; zapytanie; indeks)

• url to oczywiście adres strony z której będziemy chcieli pobrać dane. Adres musimy umieścić w cudzysłowie, ewentualnie wskazać w tym miejscu, w której komórce funkcja ma szukać adresu.
• zapytanie – oznacza rodzaj obiektu jaki ma być znaleziony. Tabela lub lista. A więc w to miejsce wpisujemy „table” lub „list”.
• indeks – określamy którą tabelę lub listę ma dla nas pociągnąć funkcja. Pierwszą, drugą czy inną napotkaną licząc od góry.

Na stronie powyżej widać tylko jedną tabelę więc śmiało wpisuję 1 jako indeks. I udaje się! Dane mam już w Arkuszu!

Ponieważ interesuje mnie tylko cena uśredniona z Polski, to mogę ograniczyć funkcję by wyświetliła tylko drugą kolumnę i jej ostatni wiersz. Wierszy jest 18 i mogę śmiało założyć, że przy kolejnych notowaniach podsumowanie też będzie w wierszu 18, bo liczba województw się nie zmieni. Zatem używam funkcji INDEX opisanej już powyżej.

Gdybyśmy obawiali się, że wysokość tabeli będzie się zmieniać (np. nie będzie notowań z niektórych województw), to w takiej sytuacji bezpieczniej jest użyć funkcji vlookup (opisanej we wcześniejszym wpisie).

Przykład pobierania ceny złota

Załóżmy, że interesuje Cię cena złota i potrzebujesz jej by aktualizować sobie informacje o wartości sztabek trzymanych w piwnicy w bloku.

Niestety GOOGLEFINANCE nie obsługuje rynku surowców i musimy posiłkować się innymi funkcjami oraz danymi dostępnymi w internecie. Odnośnie złota posłużymy się dzisiejszą ceną ustaloną przez NBP. Wyszperałem ją na stronie banku:

Jak widzicie – bank wyświetla informację w tabelce i można próbować sięgnąć po nią przy użyciu funkcji IMPORTHTML opisanej we wcześniejszym wpisie, ale ta strona okazała się najeżona tabelkami i bez pisania dodatkowych skryptów odkrycie która to jest tabela w kolejności zajęłoby dużo czasu. Dlatego dziś skupimy się na krótkiej notce, że dane o cenie złota bank udostępnia w tajemniczym formacie XML. Mówiąc najkrócej to taki format, gdzie starannie definiuje się strukturę danych i nazywa co jest czym. Ważne, że jak klikniemy na link na stronie NBP to pojawi się króciutka podstrona:

Żeby wydobyć dane ze struktury XML (język HTML jest odmianą takiej struktury) używamy formuły IMPORTXML.

składnia:

=IMPORTXML (adres; ścieżka XPATH)

adres to oczywiście strona z której pobierzemy dane

ścieżka xpath to informacja jakiej struktuty szukamy – zapisana jako wyrażenie xpath (więcej o nim przeczytacie np. na stronach wikipedii)

Brzmi to wszystko groźnie, ale plik jest bardzo krótki i prosty. Naszym zadaniem jest wyciągnięcie wszystkich danych ograniczonych znacznikiem “cena”.

W tym celu wydajemy polecenie:

=importxml("https://www.nbp.pl/aspx/Cena_zlota.aspx";"//cena")

Oznacza to, że w arkuszu ma być wyświetlone wszystko (wyrażenie //) co jest pomiędzy jakimikolwiek znacznikami <cena> </cena>

I włala! (jak to mówią Francuzi). Znalazła się pojedyncza liczba o którą chodziło!

Mały wykres waluty we własnym arkuszu i wyciągnięcie maksymalnych i minimalnych kursów.

To tak naprawdę malutki rozdział o funkcji SPARKLINE, która umie generować wykresy wewnątrz komórek. Takie malutkie i z prostym formatowaniem, których zadaniem jest zwykle pokazanie trendu, albo pobieżna ilustracja danych.

Spróbujmy więc nakreślić w komórce wykres kursu dolara z ostatniego miesiąca, tak żebyśmy mieli ogólne pojęcie czy dolar rośnie czy spada. Żeby to było użyteczne, dodajmy jeszcze wyświetlenie minimalnego i maksymalnego kursu w ciągu miesiąca (średni i ostatni już jest opisany).

Zaczniemy od wygenerowania tabeli z ostatniego miesiąca. Tabela powinna się codziennie aktualizować, więc musi umieć czytać dzisiejszą datę i też na jej podstawie określać datę 30 dni wstecz. Dlatego w funkcji GOOGLEFINANACE pojawia się formuła today()-30 i today() jako daty początkowe i końcowe dla tabeli.

Po zagnieżdżeniu z funkcją SPARKLINE, znika cała tabela i pojawia się wykres w jednej komórce:

Na koniec jeśli chcemy żeby wykres był ciut większy, możemy rozciągnąć komórkę, ewentualnie scalić kilka komórek:

Pozostaje zebrać wartości minimalne i maksymalne, żeby mieć pojęcie jak duże były wachnięcia waluty w ostatnim miesiącu. W tym celu jeszcze raz wygenerujemy tabelę przy pomocy funkcji GOOGLEFINANCE, przytniemy ją funkcją INDEX, a na koniec znajdziemy interesujące nas wartości przy pomocy funkcji MAX i MIN.

Jeśli jesteście zainteresowanie pozyskaniem z sieci innych danych – dajcie znać w komentarzach.