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.
- Aktualny kurs w obliczeniach
- Definiowanie nazw walut i używanie ich w formułach.
- Użycie kursu historycznego
- Użycie średniego kursu z danego okresu
- Pobieranie cen benzyny (np. do rozliczenia przejazdów służbowych)
- Przykład pobierania ceny złota
- Mały wykres waluty we własnym arkuszu i wyciągnięcie maksymalnych i minimalnych kursów.
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:

Do tego jest jeszcze dodatkowa informacja z datą wewnątrz znacznika.
Ż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.