Excel dla (makro)ekonomisty –

Jak wydobywać dane z sieci Web?Jakub Rybacki, jakub.rybacki@gmail.com

Warszawa, 2014

SPIS TREŚCI

Słowem wstępu......................................................................................................................................................3

Do dzieła.................................................................................................................................................................4

Wstępny rekonesans..........................................................................................................................................4

Ściągamy dane....................................................................................................................................................5

Przekształcenie danych do serii (dodatek QAvg)................................................................................................6

Dane dlaHandlu vs GUS......................................................................................................................................8

Odświeżanie informacji......................................................................................................................................9

Uwagi...............................................................................................................................................................10

Zadania.................................................................................................................................................................11

Zadanie 1 – Ceny Alkoholi i Tytoniu – portal Alleceny.pl..................................................................................11

Zadanie 2 – Przejrzyj wykres sezonowości (dodatek Macro Charts).................................................................12

Zadanie 3 – Sporządź Arkusz (i prognozy).........................................................................................................12

SŁOWEM WSTĘPU

Utrzymanie arkuszy kalkulacyjnych w stanie możliwie najbardziej aktualnym to podstawowa część życia i

zarazem jeden z najbardziej naprzykrzających się obowiązków w świetle każdego analityka. Dobrze więc, aby

takie czynności wykonywały się automatycznie.

Dzisiejszy trening ma na celu praktyczne wskazanie możliwości Excela do importu danych koszyka cenowego

witryny dlahandlu.pl (docelowo wykorzystywanego przeze mnie jako jedno z głównych źródeł danych w

temacie inflacji CPI). Poradnik pokaże:

W jaki sposób pobierać dane bezpośrednio z witryny internetowej

W jaki sposób przekształcić dane z tabel dlahandlu.pl do budowy pojedynczej serii (wykorzystany

zostanie tu dodatek QAvg z działu Excel Add-in)

W jaki sposób jednym kliknięciem myszy odświeżyć zebrane dane

W części zadania czytelnik zostanie pokierowany jak można pożenić tak zebrane dane z seriami publikowanymi

przez GUS, a w efekcie wykonać już wstępną (aczkolwiek już w miarę precyzyjną) prognozę bieżącą cen

podstawowych kategorii żywnościowych.

DO DZIEŁA

WSTĘPNY REKONESANS

Zbieranie danych zaczniemy od wizyty na stronie najbardziej zmiennego produktu w koszyku CPI czyli

ziemniaka1. Strona produktu w witrynie dla handlu składa się z dwóch części:

1) Aktualnych cen produktu w ostatnim dostępnym badaniu (zdjęcie poniżej) – teoretycznie

przedstawiające, gdzie najtaniej można kupić dany produkt. Ciekawa wiadomość dla zwykłego

konsumenta, równocześnie raczej nie będzie miała przełożenia na prognozy makroekonomiczne.

Warto jednak wrócić do tej tabeli w momencie, kiedy zmiana cen wydaje się Wam zbyt duża

(zwłaszcza jeśli obserwujecie ceny w środku miesiąca) – portal dlaHandlu prowadzi badanie falami,

tym samym nagły wzrost może być wywołany faktem, że przebadane zostały jedynie droższe dyskonty,

a w próbie brakuje supermarketów (dlatego też portal jest najbardziej informatywny pod koniec

miesiąca).

2) Archiwum cenowego – zawierającego dane na temat cen średnich w danym miesiącu (na których

podstawie będziemy budować prognozę). Warto zauważyć, że dane reprezentują okres od 1 dnia

danego miesiąca do ostatniego, natomiast GUS zbiera notowania cen w okresie od 5 do 22 dnia

miesiąca2 - dlatego też ewentualne promocje z końca miesiąca mogą zupełnie nie znaleźć odbicia w

odczycie GUS (niestety dobre przewidywanie CPI jest prognozą gdzie trzeba być precyzyjnym niczym

szwajcarski zegarek). Dla dopełnienia formalności zdjęcie witryny:

1 Dobra znajomość tak wydawałoby się abstrakcyjnych produktów jest często podstawowym czynnikiem sukcesu w prognozach CPI. Jako ciekawy przykład można tu wskazać lipiec 2013, kiedy na skutek zimnej wiosny (niższych zbiorów ziemniaka) oraz zmiany metodologicznej w notowaniach młodych ziemniaków wskaźnik CPI zaskoczył o ok. 0,5% (1,1% r/r w stosunku do prognoz konsensusu rzędu 0,5%-0,6% r/r ).2 Szczegółowe dane na temat metodologii zbierania danych do CPI dostępne są na stronie IMF

Czas przenieść informacje z witryny do naszego arkusza kalkulacyjnego.

ŚCIĄGAMY DANE

Ściągnięcie danych rozpoczynamy w Excelu, przechodząc na wstążce do zakładki dane i wybierając opcję z sieci

Web:

Naszym oczom ukaże się dość prymitywna przeglądarka Excela, równocześnie na tyle funkcjonalna, że pozwoli

na gładkie przerzucenie tabeli. W polu adres (1) wpisujemy stronę archiwum, powinien pojawić się następujący

obraz:

1

2

3

Zaznaczenie właściwej tabeli (żółty przycisk - oznaczony jako 2), oraz kliknięcie importuj spowoduje, że dane

trafią do Excela. W momencie kiedy przyciski są niewidoczne warto dwukrotnie kliknąć przycisk 3 (ukryj/pokaż

ikony). Efekt wykonanej pracy prezentuje się następująco (tutaj akurat będzie na przykładzie musztardy):

W wypadku kiedy przecinek jest ustawiony jako docelowy punkt dziesiętny dla liczb, Excel powinien dokładnie

rozpoznać wartości liczbowe, u mnie jednak punktem dziesiętnym jest kropka, dlatego konieczne będzie

wykorzystanie funkcji zamień (CTRL+H – należy pamiętać, że w Excelu znajomość skrótów klawiszowych to

podstawa podstaw dla kończenia pracy w określonych umową godzinach )

That’s all folks. Koszyk dlaHandlu zawiera 50 produktów, możemy więc wykonać 50 powtórzeń, aby nowa

technika weszła w krew.

PRZEKSZTAŁCENIE DANYCH DO SERII (DODATEK QAVG)

Posiadanie danych w Excelu to jednak jedynie półkrok do sukcesu, konieczne jest teraz ułożenie ich w serię tak,

aby pożenić je z danymi GUS. Dużym wsparciem jest tutaj dodatek QAvg dostępny w repozytorium

RybackimOkiem.

Po jego instalacji wciskamy CTRL+SHIFT+F6, pokazuje się nam ekran menu agregacji zmiennych do niższej

częstotliwości. W polu Input wybieramy wszystkie zakres począwszy od wiersza z rokiem (1182 na obrazku

powyżej) do ostatniej cyfry (wiersz 1205) dla wszystkich kolumn (od B do G). W polu Output zaznaczamy

dowolne wolne miejsce, najlepiej poniżej albo z boku pola z danymi.

W polu Periods wybieramy 2 (dane liczbowe znajdują się w co 2 polu), a w opcjach kopiuj link (Copy link).

Obrazek przedstawiający poprawne oznaczenie znajduje się na następnej stronie.

A efekt wykonania Aggregate prezentuje się następująco:

Teraz wystarczy ułożyć te 6 kolumn w 1 spójny ciąg – do tego zadania przydatne będą następujące skróty

klawiszowe (jego wykonanie pozostawiam jednak użytkownikom):

CTRL + X – wytnij

CTRL + V – wklej

CTRL + Strzałka - przejdź do następnego pola zawierającego wartość w kierunku wskazanym przez

strzałkę

CTRL + SHIFT + Strzałka – działanie podobne jak poprzednia kombinacja, równocześnie dodatkowo

zaznacza wszystkie pola.

Ponownie 50 szeregów pomoże dokładnie wyćwiczyć pracę z dodatkiem wprowadzając nas na wyższy poziom

mistrzostwa Excela.

DANE DLAHANDLU VS GUS

Finalnym efektem naszej pracy powinno być przygotowanie prognozy. Aby do tego doszło wystarczy policzyć

dynamikę zmian cen (personalnie proponuje przygotowywać prognozy zmian m/m), oraz ułożyć obok serię

zmian wskaźnika CPI publikowanego przez GUS. Początkujący analitycy mogą zapytać które dane wykorzystać,

poniżej prezentuje krótkie kompendium, równocześnie bez fotografii z moich arkuszy (rybak nie daje wędki,

uczy łowić )

Komercyjni analitycy wykorzystują dane z Biuletynu Statystycznego GUS (dostępny w dziale inne

opracowania zbiorcze). Publikacja ukazuje się pod koniec miesiąca wraz z konferencją prasową GUS i

publikacją danych sprzedaży detalicznej i bezrobocia (bądź raz na kwartał danych PKB).

Szersze rozbicie można znaleźć w comiesięcznym raporcie Ceny w Gospodarce Narodowej,

równocześnie timing publikacji jest nieco mniej fortunny – wychodzi ona w ostatnie dni miesiąca, gdzie

zwykle warto mieć już w miarę dobrze opracowaną figurę CPI, a swój czas poświęcać na pozostałe

prognozy, zwłaszcza tam gdzie brakuje alternatywnych danych dla np. wskaźników Komisji

Europejskiej (które publikowane są w podobnych warunkach. Należy pamiętać, że dla komercyjnego

analityka koniec miesiąca to ciężki okres - większość konsensusów wymaga podania wszystkich

prognoz w ostatni dzień miesiąca)

Raport ten zawierać będzie jednak dokładne wagi dla poszczególnych kategorii pozwalające na

połączenie danych w jedną spójną całość. Za pomocą wag możemy połączyć poszczególne produkty z

dlaHandlu w jeden indeks (np. masło, margaryna, olej w indeks Tłuszcze, mleko, jajka, śmietana, jogurt

w indeks Nabiał), a następnie porównać z kategorią CPI.

Podstawowym źródłem prognozy może być porównanie serii na 2 skalach wykresu, bądź budowa prostego

modelu o równaniu:

CP I kategoriamom=α+β1∗dlaHandl ukategoriamom+β2∗dlaHandlukategoriamom+β3∗MA (12)

Oczywiście ten element wymaga sporej pracy – cóż nie ma nic za darmo. Adeptom ścieżki prawdziwego

ekonomisty (nie mylić ze studentem ekonomii) pozostaje mi życzyć tylko miłego grzebania w archiwach GUS,

oraz udanych prognoz na przyszłość, oraz pokazać w jaki sposób utrzymywać tak zbudowany arkusz w stanie

pełnej aktualności.

ODŚWIEŻANIE INFORMACJI

To co jest najpiękniejsze z danymi pobieranymi z Internetu, to fakt, że dla ich odświeżenia wystarczy jedno

kliknięcie w pole na poniższym obrazku.

Po kliknięciu Excel rozpoczyna automatyczne odświeżanie wszystkich tablic, a my spokojnie idziemy na kawę. W

momencie kiedy pojawiałyby się jakieś przestoje, próbę ponownego połączenia rozpoczynamy wciskając

ESCAPE. Jeżeli z jakiegoś powodu, któreś z pól nie odświeży się samo wystarczy na nie kliknąć PPM i z opcji

wybrać odśwież:

UWAGI

W momencie kiedy tablica ściągana z Internetu zmienia rozmiar, może okazać się konieczne ponowne

oznaczenie danych do ściągnięcia, tak jak robiliśmy to w punkcie ściągamy dane (dla danych z dlahandlu, dzieje

się to wraz z nowym rokiem).

Aby edytować dane klikamy PPM na polu i z opcji na rysunku u górze wybieramy edytuj kwerendę.

ZADANIA

ZADANIE 1 – CENY ALKOHOLI I TYTONIU – PORTAL ALLECENY.PL

Portal dlaHandlu.pl to wprawdzie świetne narzędzie, równocześnie nie perfekcyjne. Jednym z jego

mankamentów jest brak danych na temat cen alkoholi i tytoniu wchodzących w skład inflacji bazowej. Nic

straconego, alternatywą jest portal alleceny.pl.

Za jego pośrednictwem możemy śledzić bieżące ceny brakujących produktów. Aby tak się stało pobierz strony

portalu do Excela, ćwiczenia ponów za miesiąc i porównaj wyniki.

UWAGA1:

Portal może nie oferować możliwości wyboru tabelki – w takim wypadku konieczne będzie skopiowanie całej

strony. Aby nie naciąć się przy aktualizacji zalecałbym wprowadzenie każdej kategorii w oddzielnej karcie.

UWAGA2:

Brak podanej historii wcale nie oznacza, że nie można do niej dotrzeć. Poradnik jak niebawem.

ZADANIE 2 – PRZEJRZYJ WYKRES SEZONOWOŚCI (DODATEK MACRO CHARTS)

Ceny warzyw i owoców to produkty bardzo sezonowe. Bardzo dużą wiedzę na temat ich przebiegu może

dostarczyć wykonanie wykresu sezonowości i przyrównanie dotychczasowych osiągów z historycznymi

wynikami.

Dla takiego wyniku należy zainstalować dodatek Macro Charts. Następnie wciskamy CTRL + SHIFT + F4. Szybkie

wskazanie interesujących nas danych i początkowego roku pozwala na utworzenie takich oto pięknych grafik:

Oczywiście interpretację wykresu pozostawiam do własnoręcznego przemyślenia. Dodam też, że mnie

personalnie takie wykresy trochę pomagają, równocześnie nie jest to moje główne źródło kształtowania

prognoz.

Wynik za kwiecień 2014 jest własnym przemyśleniem i nie ma nic wspólnego z prognozą ekspercką NBP, ani

projekcją modelu NECMOD.

ZADANIE 3 – SPORZĄDŹ ARKUSZ (I PROGNOZY)

Nic tak nie uczy ekonomii jak praktyka (przynajmniej skromnym zdaniem). Przygotowując prognozy należy

pamiętać że:

Nie myli się ten kto nic nie robi.

Wstyd to najlepsza motywacja do poprawy swoich efektów.

Ceny żywności to kategoria czasami znacznie mniej przewidywalna niż krótkookresowe wahania

kursów walutowych (m.in. przez wpływ pogody, embarg politycznych etc.).