Giełda na pulpicie

Kryzys finansowy na świecie spowodował, że wiele osób odwróciło się od inwestowania na giełdzie. Akcje przestały cieszyć się tak dużym powodzeniem, jak wcześniej. Niespokojne czasy jednak minęły. Indeksy giełdowe odbudowują swoje wartości, duże prywatyzacje przyciągają nowych inwestorów. Papiery wartościowe mogą przynieść całkiem przyzwoity zysk, o ile posiada się odpowiednią wiedzę, rozsądek i ułatwiające zarządzanie portfelem narzędzia.

Najpopularniejszym programem, który może zostać użyty do podstawowej kontroli inwestycji giełdowych jest dowolny arkusz kalkulacyjny. Na przykładzie Excela pokażemy, jak na bieżąco kontrolować ich wartość, obliczać zysk i stratę. Zawartość portfela będzie na bieżąco aktualizowana na podstawie aktualnych kursów giełdowych. Dzięki temu dowiesz się, jak pobierać do arkusza dane zewnętrzne, łączyć go ze stronami internetowymi, jak przeszukiwać załadowane dane i formatować uzyskane wyniki.

Pobieranie danych giełdowych

Zadaniem przygotowywanego arkusza jest dynamiczna aktualizacja inwestycji. Aby Excel mógł takie obliczenia przeprowadzać należy mu dostarczyć odpowiednie wartości. Możesz do tego celu wykorzystać oferowaną przez program funkcję pobierania danych zewnętrznych. Źródłem aktualnych cen i zmian kursów może być zaś dowolny serwis internetowy, który publikuje takie informacje w trakcie trwania sesji. Najlepiej, jeżeli wyświetlana na wybranej stronie tabela kursów zawiera mało elementów formatowanych, zwłaszcza poziomych podziałów separujących bloki akcji, bądź dodatkowych nagłówków.

1. Swój arkusz giełdowy możesz oprzeć na serwisie www.parkiet.com Dokładny adres prowadzący do tabeli z notowaniami odczytasz po otworzeniu strony główniej, kliknięciu łącza Notowania oraz Ciągłe - GPW. Uruchom Excela i przejdź na kartę. Kliknij polecenie Z sieci Web w sekcji Dane zewnętrzne. W pole Adres wklej adres WWW tabeli. Kliknij przycisk Przejdź. Zawartość serwisu zostanie wyświetlona w oknie arkusza. Musisz teraz wybrać element strony, który będzie pobierany. Najlepiej byłoby kopiować jedynie informacje o kursach ale nie zawsze jest to możliwe. Tak jest w przypadku serwisu parkiet.com. Tabele możliwe do pobrania oznaczone są niewielką strzałką skierowaną w prawą stronę.

2. Po umieszczeniu kursora myszy nad strzałką pojawia się ramka zaznaczająca obszar, do którego się ona odnosi. Ułatwia to wybór odpowiedniego elementu. Kliknij strzałkę w lewym górnym rogu okna. Przenieś zdefiniowaną kwerendę do arkusza klikając przycisk Importuj u dołu okna. Importowane dane możesz umieścić w miejscu proponowanym domyślnie przez Excela. Używając przycisku Właściwości zmień natomiast sposób pobierania informacji. W polu Nazwa nadaj kwerendzie nazwę. Ułatwi to jej identyfikację w przyszłości. Przejdź do sekcji Sterowanie odświeżaniem i zaznacz pola: Włącz odświeżanie w tle, Odśwież co oraz Odśwież dane podczas otwierania pliku. Ustal częstotliwość odświeżania na 5 minut. W takich odstępach aktualizowana jest zawartość strony internetowej.

3. Wybierz opcję Wstaw całe wiersze dla nowych danych… w sekcji Jeżeli liczba wierszy w zakresie danych zmieni się podczas odświeżania. Zapisz zmiany przyciskiem OK. Jeszcze raz kliknij OK i poczekaj na pobranie informacji. Początkowe komórki arkusza zajmują nieistotne dane związane na przykład z elementami sterującymi witryny. Kursy widoczne są dopiero w okolicach 70 wiersza. Zawierają one wartości opóźnione w stosunku do giełdy o 15 minut (dane w czasie rzeczywistym są płatne) ale aktualizują się automatycznie w odstępach pięciominutowych.

Wyszukiwanie wartości w tabeli danych

Przygotowany w poprzedniej poradzie arkusz stanowi źródło danych dla naszego portfela. Będzie również odpowiadał za ich regularną aktualizację. Zastawienie akcji, zysków i strat umieścimy w kolejnym arkuszu.

1. Kliknij kartę Arkusz2 arkusza, wyświetlaną u dołu ekranu. Przygotuj nagłówki tabeli zestawienia. Mogą to być przykładowo: Spółka, Symbol, Cena zakupu, Cena aktualna, Zmiana dzienna (%), Liczba akcji, Wartość początkowa, Wartość aktualna, Zysk/strata, Zysk/strata (%). Do wyboru są również inne pozycje dostępne w arkuszu źródłowym jak na przykład wysokość dziennych obrotów, wolumen czy ceny maksymalne i minimalne oraz wszystkie wartości, które można na ich podstawie wyliczyć. Wpisz również spółki wchodzące w skład portfela. Pomiędzy kolumną z nazwą spółki, a jej symbolem pozostaw dodatkową kolumnę. Zostanie ona wykorzystana w celach kontrolnych. Jeżeli jej nie przewidziałeś kliknij prawym przyciskiem myszy oznaczenie kolumny symbolu i z menu kontekstowego wybierz Wstaw.

2. Aby zmniejszyć prawdopodobieństwo popełnienia błędu podczas konstruowania formuł nadaj kartom arkusza stosowne nazwy - na przykład Dane i Portfel. Zrobisz to klikając prawym przyciskiem myszy na oznaczenia kart u dołu i wybierając z menu polecenie Zmień nazwę. Pora przystąpić do wyszukiwania i pobierania informacji z zestawienia źródłowego. Użyj do tego celu funkcji WYSZUKAJ.PIONOWO. Zaznacz komórkę z prawej strony pierwszej spółki i wpisz formułę:

=WYSZUKAJ.PIONOWO(A5;Dane!A75:LZ485;1)

Działa ona w ten sposób, że przeszukuje w pionie tabelę danych sprawdzając, czy nie ma w niej wzorca określonego adresem A5, czyli nazwy spółki wpisanej do portfela (symbol oznacza odwołanie względne i ułatwi ci konstruowanie kolejnych formuł). Jeżeli znajdzie, zatrzymuje wyszukiwanie i pobiera zawartość komórek z tego samego wiersza. Tabela z danymi określona jest na podstawie adresów jej lewego, górnego i prawego, dolnego rogu. Określając zakres danych pomiń wszystkie komórki zawierające informacje dodatkowe. Zmniejszy to prawdopodobieństwo błędu i przyspieszy działanie. Dane! To odwołanie do karty arkusza. Liczba 1 oznacza numer kolumny tabeli, z której należy pobrać wartość (dla odnalezionego wiersza). W tym przypadku jest to również nazwa.

3. Formuła do wyszukiwania symbolu ma bardzo podobną postać:

=WYSZUKAJ.PIONOWO(A5;Dane!A75:LZ485;2)

Jak poprzednio brany jest pod uwagę ten sam zakres danych źródłowych. Wyszukiwanie jest również przeprowadzane na podstawie tego samego wzoru - nazwy spółki wpisanej do arkusza z portfelem. Pobierana jest jedynie inna informacja po znalezieniu w tabeli pasującego elementu. W tym przypadku odczytywana jest zawartość z kolumny numer 2 czyli tej, która zawiera symbole.

4. W kolumnie z ceną zakupu umieścisz cenę akcji, po jakiej je nabyłeś. Cenę aktualną przepisz z arkusza danych, tak jak nazwę czy symbol. W tabeli danych umieszczona jest ona w kolumnie 4 (z oznaczeniem Ost.), więc formuła będzie wyglądała tak:

=WYSZUKAJ.PIONOWO(A5;Dane!A75:LZ485;4)

Zmiana dzienna to kolumna numer 6. Stosowna formuła przyjmuje więc postać:

=WYSZUKAJ.PIONOWO(A5;Dane!A75:LZ485;6)/100

W tym przypadku warto sformatować kolumnę tak, by wyświetlała wartości procentowe. Zabieg dzielenia przez 100 ma zapewnić prawidłowe wyświetlanie wartości. Zaznacz teraz poszczególne komórki z wprowadzonymi przed chwilą formułami i korzystając z kursora myszy oraz metody przeciągania skopiuj ich zawartość w dół kolumn portfela, do pozostałych komórek. Jeżeli nazwa waloru z kolumny Spółka nie zgadza się z wartością w kolumnie kontrolnej zmień zakresy tabeli źródłowej dla wszystkich formuł w danym wierszu. Upewnij się jednak najpierw, że użyłeś prawidłowej nazwy we wzorcu.

Tworzenie formuł obliczeniowych

Przeniesione do arkusza wartości posłużą, do przeprowadzania różnych obliczeń. Nie można ich robić bezpośrednio na arkuszu danych, bo formuły uległyby skasowaniu w trakcie operacji pobierania ze strony internetowej.

1. Dwie wartości arkusza podsumowującego wprowadzane są zawsze ręcznie. To cena zakupu akcji oraz ich liczba. Uzupełnij te pola dla każdego waloru. Wartość początkowa to oczywiście iloczyn ceny zakupu i liczby akcji, wartość aktualna wyliczana jest z iloczynu liczby akcji oraz ceny aktualnej. Odpowiednie formuły dla pierwszego wiersza przykładowego portfela przyjmą więc następującą postać:

=I6*M6

=J6*M6

Po uzupełnieniu formuł skopiuj je do komórek poniżej.

2. Kolej na wyliczenie zysków i strat z inwestycji. Odpowiadające za to wzory są bardzo proste. Wynik w postaci kwoty uzyskasz odejmując zawartość pola wartość początkowa od zawartości pola wartość aktualna. Aby rezultat wyświetlany był w postaci procentowej wystarczy wynik tej formuły podzielić przez wartość początkową i odpowiednio sformatować komórki. Dla pierwszego rekordu tabeli odpowiednie zapisy wyglądają tak:

=O6-N6

=(O6-N6)/O6

3. Przydałoby się jeszcze podsumowanie. Zaznacz komórkę powyżej listy wartości początkowych (umieszczenie podsumowania poniżej listy utrudni możliwość jej rozbudowywania w przyszłości). Jeżeli brakuje wolnych pól dodaj kilka wierszy klikając oznaczenie pierwszego prawym przyciskiem myszy i wybierając Wstaw. Podsumuj zainwestowane kwoty. Nie korzystaj jednak z funkcji SUMA. Przeszkodzi to później w rozbudowie arkusza. Użyj prostego dodawania:

=(N6+ N7+ N8+ N9+ N10+ N11+ N12+ N13+ N14+N15)

Podobny zapis umieść nad kolumną z wartościami aktualnymi:

=(O6+ O7+ O8+ O9+ O10+ O11+ O12+ O13+ O14+O15)

Niestety będziesz musiał pamiętać o dodawaniu kolejnych pozycji gdy powiększysz portfel o następne walory. Zyski obliczysz za pomocą wzorów:

=O2-N2

=(O2-N2)/N2

Jeżeli wszystkie dane wyświetlane są prawidłowo ukryj kolumnę kontrolną z nazwami spółek. Kliknij jej nagłówek prawym przyciskiem myszy i wybierz polecenie Ukryj. Nie kasuj jej.

Czas na formatowanie

Wszystkie istotne informacje zawarte są w wyświetlanych przez arkusz wartościach. Istotną cechą arkusza jest jednak możliwość odpowiedniego sformatowania komórek w taki sposób, by wyróżnić pożądane cechy. Najważniejszą rolę odgrywa funkcja formatowania warunkowego.

1. Spadki na giełdzie oznacza się zwykle kolorem czerwonym, wzrosty zielonym, to, co pozostanie bez zmian możesz oznaczyć na żółto. Ułatwi to odbiór wyświetlanych informacji. Zacznij od kolumny z dzienną zmianą wartości. Zaznacz zakres danych i kliknij przycisk Formatowanie warunkowe w sekcji Style, karty Narzędzia główne. Wybierz opcję Reguły wyróżniania komórek i Większe niż. W pole z lewej strony wpisz 0, a na liście po prawej stronie ustaw Format niestandardowy. Jeżeli chcesz zmienić jedynie wygląd czcionki to na karcie Czcionka, w polu Kolor wskaż kolor zielony, jeżeli chcesz zmienić tło, odpowiednie parametry znajdziesz na karcie Wypełnienie. Kliknij OK i ponownie OK.

2. Pozostaw zaznaczone komórki, ponownie rozwiń menu formatowania warunkowego ale tym razem użyj polecenia Mniejsze niż. Sformatuj czcionkę w kolorze czerwonym. Jeszcze raz włącz formatowanie, kliknij Równe… i wybierz żółty kolor fontu. W identyczny sposób użyj formatowania warunkowego w kolumnach zysk/strata oraz zysk/strata (%). Takie samo formatowanie zdefiniuj także dla pojedynczych komórek podsumowujących zyski bądź straty. Jeżeli chcesz zmienić parametry reguły, zaznacz zawierające ją komórki, rozwiń menu formatowania warunkowego i kliknij opcję Zarządzaj regułami. Wskaż regułę na liście i użyj przycisku Edytuj regułę.

3. Zaznacz dane w kolumnie ze zmianą dzienną i skopiuj je do schowka. Wskaż wolną komórkę z prawej strony symbolu, w pierwszym rekordzie bazy i wklej zawartość zapisanych danych. W ten sposób powielisz informacje o procentowej zmianie wartości. Zaznacz nowy obszar i rozwiń menu formatowania. Wybierz funkcję Zestawy ikon. Kliknij polecenie Więcej reguł u dołu. Na liście Styl ikony ustaw zestaw trzech kolorowych strzałek. Zaznacz opcję Pokaż tylko ikonę. Na liście obok zielonej strzałki i napisu gdy wartość jest równa ustaw znak większości, a obok wpisz liczbę 0. Poniżej użyj symbolu >= i również wpisz wartość zero. Pola Typ zdefiniuj jako Liczba. Zaakceptuj zmiany przyciskiem OK

4. W podobny sposób używa się pasków danych. Mogą one posłużyć to zilustrowania wielkości zysku bądź straty. Ułatwią porównywanie tych wartości. Zaznacz komórki zawierające dane o zysku oraz stracie, włącz formatowanie warunkowe i rozwiń opcję Paski danych. Skorzystaj z zielonego paska w części Wypełnienie gradientowe. Jeżeli formatowanie graficzne utrudnia odczyt wartości liczbowych możesz zmodyfikować sposób wyświetlania bądź skorzystać jak poprzednio z triku polegającego na powieleniu kolumny z wartościami. Na tym etapie arkusz potrafi przechowywać informacje o zakupionych akcjach, obliczać zyski i straty oraz prezentować je w sposób czytelny na ekranie.

Ustawiamy alarmy

Excel może służyć nie tylko jako notatnik. Za pomocą funkcji arkusza skontrolujesz na bieżąco stan inwestycji. Skonfigurujesz go w ten sposób, by informował cię o różnych zmianach, czy przekroczeniu pewnych wartości.

1. Dodaj trzy wiersze poniżej pierwszego rekordu portfela. Kliknij w tym celu prawym przyciskiem myszy nagłówek drugiego wiersza i wybierz Wstaw. Zaznacz nowe wiersze, przejdź na kartę Dane i kliknij polecenie Grupuj w sekcji Konspekt. Kliknij niewielką strzałkę w prawym dolnym rogu sekcji i w oknie dialogowym usuń zaznaczenie z opcji Wiersze podsumowań poniżej szczegółów. Zapisz zmiany klikając OK. Skopiuj symbol spółki do pól pod głównym rekordem. Dzięki temu będziesz wiedział jakiego waloru dotyczą dodatkowe wiersze. Zastosuj po prostu znak równości i adres głównej komórki.

2. Wstaw dwie dodatkowe kolumny przed informacją o cenie zakupu. Pierwsza posłuży do celów informacyjnych, w drugiej umieścisz dodatkowe parametry. W komórki informacyjne wpisz kolejno, z góry na dół: Minimum, Maksimum, Strata. Pierwszy komentarz będzie oznaczał minimalną wartość akcji, poniżej której należy bezwzględnie sprzedać akcje mimo strat, drugi to minimalny, satysfakcjonujący cię zysk. Możesz sprzedać akcje, o ile osiągną tę wartość. Strata to procentowy spadek od lokalnego maksimum ceny akcji. Jeżeli przekroczy założony pułap możesz sprzedać akcje, by chronić wypracowany wcześniej zysk.

3. Skopiuj w dół informacje o cenie zakupu. Będą one stanowiły punkt odniesienia. Powiel również formuły z komórek podsumowujących czyli zawierających liczbę akcji, wartości początkowe i aktualne oraz informacje dotyczące zysku. Pola poniżej aktualnej ceny wykorzystaj do określenia limitów. Po wpisaniu minimalnej i maksymalnej wartości będziesz mógł ocenić, jak kształtowałaby się twoja inwestycja, gdybyś sprzedał akcje po danej cenie.

4. Określenie procentowego spadku wartości akcji od lokalnego maksimum, przy którym trzeba sprzedać akcje nie jest już takie łatwe. Uzależnienie ceny sprzedaży od ceny aktualnej spowoduje, że będzie ona malała wraz ze spadkiem wartości. Powinna natomiast rosnąć gdy akcje drożeją i pozostać na stałym poziomie gdy tanieją. To tak zwany ruchomy stop podążający za zmianą ceny. Uproszczony efekt tego typu możesz uzyskać za pomocą nieskomplikowanego makra. Jego kod wygląda tak:

Sub Makro()

Range("j9").Select

i = 0

For a = 0 To 9

If IsNumeric(ActiveCell.Offset([i] - 3, [0])) Then

aktualnystop = ActiveCell.Offset([i], [0])

procent = ActiveCell.Offset([i], [-2])

nowystop = ActiveCell.Offset([i] - 3, [0]) - (ActiveCell.Offset([i] - 3, [0]) * procent)

If nowystop < aktualnystop Then

ActiveCell.Offset([i], [0]) = aktualnystop

Else

ActiveCell.Offset([i], [0]) = nowystop

End If

i = i + 4

Else

i = i + 4

End If

Next a

Range("a2").Select

End Sub

W pierwszej kolejności zaznaczana jest komórka, w której wyświetlony zostanie wynik obliczenia. To trzecia dodatkowa komórka poniżej pierwszego, głównego rekordu. Następnie zerowany jest licznik pomocniczy i definiowana pętla o liczbie przebiegów równiej liczbie walorów w portfelu pomniejszonej o 1. Dalsze działanie makra uzależnione jest od tego, czy w komórce umieszczonej dwie pozycje powyżej zaznaczonej znajduje się liczba (mówi to na przykład o tym, czy akcja jest danego dnia notowana). Jeżeli tak z zaznaczonej komórki tworzony jest aktualny stop czyli wartość poniżej której należy sprzedać akcje. Z komórki o trzy pozycje w lewo czytana jest maksymalna procentowa wartość spadku definiowana ręcznie. Na podstawie dwóch pierwszych zmiennych wyliczana jest nowa wartość graniczna sprzedaży.

Kolejne warunki określają, jakie dane mają przyjąć komórki arkusza. Gdy stara wartość jest niższa od nowej, zaznaczona komórka przyjmuje nową wartość, jeżeli odwrotnie, w komórce pozostaje stara wartość. Pierwszy przebieg pętli jest kończony, a licznik powiększany o liczbę wierszy arkusza związanych z pojedynczym rekordem czyli 4. Po zakończeniu działania całej pętli, zaznaczana jest komórka o adresie A2.

5. Zanim dodasz do arkusza makro uzupełnij pozostałe rekordy bazy o dodatkowe wiersze i brakujące informacje. Aby umieścić makro w arkuszu kliknij kartę Widok, rozwiń polecenie Makra i wybierz Wyświetl makra. W polu Nazwa makra nadaj makropoleceniu nazwę i zapisz je przyciskiem Utwórz. Uruchomi się edytor makr. Pomiędzy znacznikami początku i końca (Sub i End Sub) umieść zamieszczony kod. Zapisz go. Zwróć uwagę, że Excel 2010 wymaga do tego celu odpowiedniego typu pliku.

6. W komórkę obok pola Strata wpisz maksymalny akceptowany przez ciebie spadek wartości akcji wyrażony w procentach. Następnie uruchom makro - kliknij przycisk Makra, zaznacz swoje makro i użyj przycisku Uruchom.

7. Wyliczone wartości można powiązać jeszcze z formatowaniem warunkowym. Aby nie trzeba było samodzielnie porównywać ich wielkości wystarczy podświetlić odpowiednim kolorem komórkę z aktualną ceną, jeżeli osiągnie ona założony poziom. Zaznacz pole z aktualną ceną, rozwiń menu formatowania warunkowego i wybierz Reguły wyróżniania oraz Większe niż. W polu warunku umieść odwołanie do komórki zawierającej cenę maksymalną - wystarczy kliknąć w odpowiednim miejscu arkusza. Ustaw zielony deseń i zapisz zmiany. W tej samej komórce ustaw również regułę Mniejsze niż, wskaż komórkę z wartością minimum i sformatuj ją w kolorze czerwonym. Trzecia reguła podświetli komórkę na przykład na niebiesko, gdy jej wartość spadnie poniżej obliczonej za pomocą makra straty. Na tym kończymy rozbudowę arkusza, ale można go jeszcze wyposażyć na przykład w mechanizmy kontroli błędów, zautomatyzować proces uruchamiania makra, czy dodać kolejne przełączniki sprawdzające stan inwestycji i reagujące na zmiany.