A Notowania giełdowe w czasie rzeczywistym w Excelu przy użyciu Pythona

Wprowadzenie

Posiadanie dostępu do aktualnych informacji jest jedną z najważniejszych rzeczy, których potrzebujesz podczas handlu na rynkach finansowych. Z Excel Stock Price Calculator Template możesz śledzić notowania akcji w czasie rzeczywistym, którymi handlujesz i monitorować wydajność swojego portfela.

Co jeśli chcesz pójść dalej?

Handlowcy używają notowań giełdowych w czasie rzeczywistym, aby wybrać odpowiedni czas na zakup i sprzedaż, a dane w czasie rzeczywistym mogą być dostępne bezpośrednio w programie Excel.

Zamiast klikać przycisk w celu odświeżenia cen akcji, ten blog pokaże Ci, jak za pomocą odrobiny kodu Pythona możesz przesyłać dane w czasie rzeczywistym bezpośrednio do Excela.

Python jest językiem programowania, który zyskał ogromną popularność w branży finansowej. Jeśli szukasz sposobu, aby Twoje własne decyzje handlowe były bardziej oparte na danych lub algorytmach, to warto zainwestować trochę czasu w naukę Pythona jako dodatku do Excela.

Python czy Excel? Używaj obu razem!

Wiele osób mówi o używaniu Pythona zamiast Excela. Python jest świetnym językiem programowania, ale nie zastąpi arkusza kalkulacyjnego! Excel jest niezwykle potężnym narzędziem w praktycznie każdej branży, i to nie bez powodu.

Dla niektórych zadań, szczególnie tych wymagających czegoś więcej niż prostych obliczeń, Python może być lepszym wyborem. Python posiada ogromny ekosystem pakietów do praktycznie każdego rodzaju obliczeń, o jakich można pomyśleć. Chociaż możliwe jest wykonanie wszystkiego w Excelu, po opanowaniu odrobiny Pythona przekonasz się, że niektóre rzeczy są po prostu łatwiejsze w Pythonie!

Na szczęście nie ma potrzeby wybierać między jednym a drugim. Funkcje Pythona mogą być wywoływane z Excela za pomocą PyXLL, dodatku Python do Excela. Połączenie Excela i Pythona poprzez wykorzystanie Excela jako interfejsu użytkownika, a Pythona jako narzędzia do wykonywania bardziej złożonych zadań jest doskonałym połączeniem.

Używanie Pythona do uzyskiwania notowań giełdowych w czasie rzeczywistym

Poniższy tekst zakłada pewną znajomość Pythona. Jeśli nie jesteś jeszcze zaznajomiony z Pythonem, nie przejmuj się, jeśli nie do końca rozumiesz to wszystko teraz. Nadal możesz śledzić, ale może chcesz spojrzeć na sekcji zasobów dla niektórych stron, które pomogą Ci zacząć z Pythonem, jak również.

Jako źródło danych o cenach akcji, użyjemy iextrading.com. IEX posiada Socket.IO API, którego będziemy używać do otrzymywania aktualizacji w czasie rzeczywistym. Socket.IO jest biblioteką dla aplikacji internetowych działających w czasie rzeczywistym, a my możemy uzyskać dostęp do Socket.IO API IEX używając Pythona.

Pierwszą rzeczą do zrobienia jest zainstalowanie pakietu Python Socket.IO, abyśmy mogli rozmawiać z IEX. Instalujemy go za pomocą menedżera pakietów Pythona, Pip, z wiersza poleceń:

pip install python-socketio

Teraz w pliku Pythona możemy zaimportować socketio i połączyć się z serwerem IEX.

Aby otrzymywać aktualizacje cen akcji, musimy dodać kilka funkcji wywołania zwrotnego, które klient będzie wywoływał w odpowiedzi na pewne zdarzenia.

import socketio

endpoint = "https://ws-api.iextrading.com/1.0/tops"
symbols = [
   "AAPL",
    " MSFT",
    "SNAP"
]

@client.on("connect", namespace=namespace)
async def on_connect():
    for symbol in symbols:
        await client.emit("subscribe", symbol, przestrzeń nazw=przestrzeń nazw)
        print(f "Subscribed to '{symbol}'")

Po pierwsze, gdy klient połączył się z serwerem, wysyłamy kilka komend, aby zasubskrybować niektóre symbole giełdowe (często nazywane tickerami).

Wywołanie zwrotne on_connect zostanie wywołane, gdy klient się połączy. Gdy każdy symbol zostanie zasubskrybowany, na wyjście Pythona zostanie wypisany komunikat.

Kolejne wywołanie zwrotne będzie musiało odebrać dane, gdy cena akcji zostanie zaktualizowana. Kiedy tylko jakieś dane są gotowe, API IEX socket.io wywołuje callback wiadomości z danymi zakodowanymi jako ciąg JSON. Używamy pakietu json Pythona, aby odczytać ten ciąg JSON do obiektu słownika Pythona i wydrukować go.

import json

@client.on("message", namespace=namespace)
def on_message(message):
    data = json.loads(message)
   print(dane)

Być może zauważyłeś, że powyższy kod używa słów kluczowych Pythona async i await. Python 3.5 zawiera te słowa i można je powszechnie używać do programowania asynchronicznego. W takim przypadku nie chcemy, aby cały program musiał czekać na zakończenie operacji, jak na przykład oczekiwanie na powrót żądania do zdalnego serwera internetowego. Zamiast tego Python używa pętli zdarzeń do planowania każdej funkcji, a gdy jedna z nich musi czekać, Python zawiesza ją i kontynuuje wszystkie inne, które są gotowe do kontynuowania.

Aby ukończyć ten kod, musimy uzyskać pętlę zdarzeń Pythona asyncio, zaplanować zadanie tworzenia klienta utworzonego wcześniej i uruchomić pętlę zdarzeń.

import asyncio

loop = asyncio.get_event_loop()
loop.create_task(task)
loop.run_forever()

Kiedy uruchomimy ten kod, wygenerowane dane wyjściowe wyglądają jak poniżej. Jest ono aktualizowane w miarę jak ceny akcji zmieniają się w czasie rzeczywistym.

Zapisany do "AAPL
Subskrybuje 'MSFT'
Subskrybuje ' SNAP'
{"symbol": "AAPL", "sektor": "technologyhardwareequipment", "securityType": "commonstock", "bidPrice": 181.1, 'bidSize': 100, 'askPrice': 182.03, 'askSize': 100, 'lastUpdated': 1552491513626, 'lastSalePrice': 182.04, 'lastSaleSize': 100, 'lastSaleTime': 1552491513046, 'volume': 355975, 'marketPercent': 0.02927, 'seq': 84605}.

Strumieniowe przesyłanie danych z Pythona do Excela

W poprzednim rozdziale dowiedzieliśmy się, jak uzyskać notowania giełdowe w czasie rzeczywistym z IEX przy użyciu Socket.IO API. W tej sekcji zobaczymy, jak wykorzystać to do stworzenia arkusza kalkulacyjnego, który aktualizuje się w czasie rzeczywistym.

Aby wywołać nasz kod Pythona z Excela, użyjemy dodatku PyXLL. Bezpłatną wersję próbną dodatku PyXLL można pobrać ze strony https://www.pyxll.com/download.html.

1. Po pobraniu dodatku PyXLL należy postępować zgodnie z instrukcjami, aby go zainstalować. W pliku konfiguracyjnym, pyxll.cfg, zobaczysz, że jest tam opcja mówiąca o ścieżce python i inna mówiąca o modułach. Ścieżka python jest listą folderów, gdzie Python będzie szukał podczas ładowania wymienionych modułów Pythona. Moduł Pythona to po prostu plik tekstowy zawierający kod Pythona o nazwie kończącej się na ".py".
2. Zacznij od utworzenia nowego modułu Pythona (plik tekstowy zakończony ".py") i zapisz go gdzieś (np. C:/Projects/ExcelStockPrices/iex.py). Do listy pythonpath w pliku pyxll.cfg dodaj katalog, w którym zapisałeś plik (np. C:/Projects/ExcelStockPrices) i dodaj nazwę modułu do listy modułów (np. iex). Zwróć uwagę, że nazwa modułu nie zawiera rozszerzenia pliku ".py".
3. Po uruchomieniu Excela lub ponownym załadowaniu dodatku PyXLL, nowy moduł "iex" zostanie załadowany.
4. Następnie napiszemy funkcję w Pythonie, którą będzie można wywołać z Exc ela. Zrobimy to przy użyciu dekoratora @xl_func z PyXLL. Ponieważ nasza funkcja będzie zwracała dane w czasie rzeczywistym, a nie tylko pojedynczą wartość, zwrócimy specjalny typ obiektu, zwany obiektem RTD. W Pythonie utworzymy nową klasę wywodzącą się z klasy PyXLL RTD.

from pyxll import xl_func, RTD


class IEXClient(RTD):
    def __init__(self, symbole):
       super().__init__(self, value=.None)
       self.symbols = symbols


@xl_func("str[] symbole: rtd)
def iex(symbole):
    return IEXClient(symbole).

Funkcja iex pobiera listę łańcuchów i zwraca obiekt RTD. Łańcuch przekazany do @xl_func powyżej jest sygnaturą funkcji. PyXLL używa jej do określenia, jak przekonwertować wartości przekazane z Excela do Pythona, a także jak przekonwertować wartość zwracaną przez Pythona do Excela. Podpis ten mówi, że symbole są listą łańcuchów, a funkcja zwróci obiekt RTD, którego wartością jest obiekt Pythona. Wartość obiektu RTD jest tym, co pojawi się w Excelu.

Kiedy funkcja iex jest wywoływana z listą symboli, wynikiem w Excelu jest uchwyt do obiektu Pythona "None". Gdybyśmy podali inny typ zwracanego typu funkcji (np. "rtd"), moglibyśmy zwrócić do Excela inne typy zamiast uchwytu do obiektu Pythona.

Obiekty RTD mają dwie metody, connect i disconnect. Są one wywoływane, gdy Excel jest gotowy do rozpoczęcia odbierania wartości oraz gdy Excel ich już nie potrzebuje. Na przykład, connect zostanie wywołana, gdy użytkownik wprowadzi formułę, a disconnect zostanie wywołana, gdy użytkownik wyczyści formułę.

Aby wysłać nową wartość do Excela z obiektu RTD, wystarczy ustawić właściwość value na obiekcie RTD.

Możemy wykorzystać kod napisany w poprzednim rozdziale i dodać go do klasy IEXClient. Dodamy go do metody connect, dzięki czemu podczas łączenia się Excela z naszym obiektem RTD będziemy łączyć się z serwerem IEX za pomocą klienta Socket.IO.

Zamiast po prostu drukować dane, będziemy je przechowywać w słowniku Pythona, aby móc je później odzyskać.

class IEXClient(RTD):

    endpoint =. "https://ws-api.iextrading.com/1.0/tops"
    namespace = "/1.0/tops"

    def __init__(self, symbole):
        super().__init__(wartość={})
       self.symbols = symbols
       self.client = socketio.AsyncClient()
       self.data = {}

    async def connect(self):
       """Połącz AsyncClient i subskrybuj aktualizacje.""
       @self.client.on("connect", namespace=self.namespace)
        async def on_connect():
            dla symbol w self.symbols:
                await self.client.emit("subscribe",
                                       symbol,
                                      namespace=self.namespace)

       @self.client.on("message", namespace=self.namespace)
        def on_message(message):
            latest = json.loads(message)
            symbol = latest["symbol"]

            previous = self.data.setdefault(symbol, {})
            previous.update(latest)

            # Powiadamia Excel, że nastąpiła aktualizacja
            self.value = self.data

        await self.client.connect(self.endpoint,
                                 namespaces=[self. namespace])

    async def disconnect(self):
        await self.client.disconnect()

Zauważ, że metody connect i disconnect są oznaczone jako async. PyXLL rozumie metody async i umieści je za nas w pętli zdarzeń Pythona.

Teraz, gdy ponownie załadujemy dodatek PyXLL i wywołamy tę funkcję, wynikiem będzie uchwyt do słownika Pythona. W miarę jak ceny się zmieniają, słownik jest aktualizowany, a wartość pokazywana w Excelu jest aktualizowana.

Samo w sobie nie jest to zbyt użyteczne! Potrzebujemy sposobu, aby przenieść wartości ze słownika Pythona do naszego arkusza. Jest to tak proste, jak napisanie kolejnej funkcji, która pobiera słownik, symbol i klucz i zwraca wartość. Podpis dla tej funkcji mówi, że pierwszym parametrem jest obiekt Pythona, a następnie symbol i klucz jako ciągi znaków, i zwraca wartość typu zmiennej (tj. może to być ciąg znaków, liczba lub inny typ).

@xl_func("obiekt dane, str symbole, str klucz: var")
def iex_unpack(dane, symbol, klucz):
    # pobierz wartości dla symbolu
    values = data.get(symbol, None)
    if not values:
        return "#NoData"

    # zwróć wartość dla podanego klucza
   return values.get(key, "#NoData")

Ta funkcja iex_unpack jest wywoływana z uchwytem słownika jako pierwszym argumentem, oraz symbolem i kluczem i zwraca wartość ze słownika. W miarę jak nowe wartości są wprowadzane i słownik się aktualizuje, wartości te są aktualizowane.

Kalkulacje portfelowe z notowaniami giełdowymi w czasie rzeczywistym

Teraz mamy wszystko, czego potrzebujemy, aby dodać notowania w czasie rzeczywistym do Someka Portfolio Calculator! Możesz stworzyć swój własny arkusz używając szablonu. Jeśli po prostu zaktualizujesz arkusz tak jak ja to zrobiłem poniżej, po naciśnięciu przycisku "Oblicz" zmienione komórki zostaną nadpisane.

Możesz pobrać inne dane, które nie są zawarte w notowaniach giełdowych w czasie rzeczywistym z REST API IEX. Poniższy kod pokazuje jak pobrać pojedynczą wartość przy użyciu pakietu Pythona aiohttp.

from pyxll import xl_func
import aiohttp

punkt końcowy = "https://api.iextrading.com/1.0/"

@xl_func
async def iex_fetch(symbol, klucz):
   """zwraca wartość dla symbolu z iextrading.com"".
   url = endpoint + f "stock/{symbol}/batch?types=quote"
    async z aiohttp.ClientSession() as session:
        async z session.get(url) jak response:
            assert response.status == 200
            dane = await odpowiedź.read()

    dane = json.loads(dane)["cytat"]
    return data.get(key, "#NoData")

Powyższa funkcja jest funkcją asynchroniczną. Oznacza to, że możesz wywołać ją wiele razy, a każde żądanie do punktu końcowego IEX REST może działać równolegle, znacznie poprawiając responsywność arkusza.

Aby uzyskać otwartą cenę dla akcji, wywołałbyś powyższą funkcję w Excelu w następujący sposób.

=iex_fetch("AAPL", "open")