Data Analysis
Real-Time-Stock-Quotes-in-Excel-using-Python

A Real Time Stock Quotes in Excel using Python

Introduction

Having access to up to date information is one of the most important things you need when trading financial markets. With Someka’s Excel Stock Price Calculator Template you can keep track of the real time stock quotes you’ve traded and monitor the performance of your portfolio.

What if you want to go further?

Traders use real-time stock quotes to choose the right time to buy and sell, and that real-time data can be access directly in Excel.

Trading-Journal-Template-Banner

Rather than have to click a button to refresh stock prices, this blog will show you how with a little bit of Python code you can stream real-time data directly into Excel.

Python is a programming language that has gained a huge following in the financial industry. If you are looking for a way to make your own trading decisions more data driven or algorithmic then it will be worth investing some of your time learning a little bit of Python in addition to Excel.

Real-Time-Stock-Quotes-in-Excel-using-Python-03Python or Excel? Use both together!

Lots of people talk about using Python instead of Excel. Python is a great programming language, but it’s not a replacement for a spreadsheet! Excel is an extremely powerful tool in virtually every industry, and for good reason.

For some tasks, particularly those requiring anything more than simple calculations, Python can be a better choice. Python has an immense ecosystem of packages for virtually every type of computation you can think of. While it might be possible to do everything in Excel, once you’ve learned a bit of Python you’ll find out that some things are just easier in Python!

Fortunately, there’s no need to choose between one or the other. Python functions can be called from Excel using PyXLL, the Python Excel Add-In. Combining Excel and Python by using Excel as the front end (or user interface) with Python powering the more complex tasks is a perfect match.

Using Python to Get Real Time Stock Quotes

The following assumes some knowledge of Python. If you’re not already familiar with Python, don’t worry if you don’t quite get all of it right now. You can still follow along, but you might want to take a look at the resources section for some pages to help you start with Python as well.

For our source of stock price data, we are going to use iextrading.com. IEX have a Socket.IO API that we will use to get real-time updates. Socket.IO is a library for real time web applications, and we can access IEX’s Socket.IO API using Python.

code

The first thing to do is to install the Python Socket.IO package so that we can talk to IEX. We install it using the Python package manager, Pip, from a command prompt:

pip install python-socketio

Now in a Python file we can import socketio and connect to the IEX server.

In order to receive the stock price updates, we need to add some callback functions that the client will call in response to certain events.

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, namespace=namespace)
        print(f"Subscribed to '{symbol}'")

Firstly, when the client has connected to the server we send back some commands to subscribe to some stock symbols (often called tickers).

The on_connect callback will be called once the client has connected. As each symbol is subscribed, a message is printed to Python’s output.

Another callback will need receive the data when a stock price updates. Whenever some data is ready, the IEX socket.io API calls a message callback with the data encoded as a JSON string. We use the json Python package to read that JSON string into a Python dictionary object and print it.

import json

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

You may have noticed the code above is using Python’s async and await keywords. Python 3.5 contains these and you can commonly use them for asynchronous programming. This is where we don’t want the whole program to have to wait for an operation to complete, like waiting for a request to a remote web server to return. Instead, Python uses an event loop to schedule each function, and when one function has to wait, Python suspends that function and continues with anything else that’s ready to continue.

To complete this code we must get the Python asyncio event loop, schedule the task to create the client created earlier, and start the event loop.

import asyncio

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

When we run this code, the output produced looks like below. It keeps updating as the stock prices change in real-time.

Subscribed to 'AAPL'
Subscribed to 'MSFT'
Subscribed to 'SNAP'
{'symbol': 'AAPL', 'sector': '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}

Streaming Data from Python into Excel

In the previous section we learned how to get real-time stock quotes from IEX using the Socket.IO API. In this section we will see how to use that to make a spreadsheet that updates in real-time.

To call our Python code from Excel we use the PyXLL add-in. A free trial of the PyXLL add-in can be downloaded from https://www.pyxll.com/download.html.

  1. Once you have downloaded the PyXLL add-in, follow the instructions to install it. In the configuration file, pyxll.cfg, you will see there is an option says pythonpath and another says modules. The pythonpath is a list of folders where Python will look when loading the listed Python modules. A Python module is just a text file containing Python code with a filename ending in “.py”.
  2. Start by creating a new Python module (a text file ending in “.py”) and save it somewhere (e.g. C:/Projects/ExcelStockPrices/iex.py). Add the folder where you saved the file to the pythonpath list in the pyxll.cfg file (e.g. C:/Projects/ExcelStockPrices) and add the module name to the list of modules (e.g. iex). Note that the module name does not include the “.py” file extension.
  3. When Excel is started, or the PyXLL add-in is reloaded, that new module “iex” will be loaded.
  4. Next we will write a function in Python that you can call from Excel. This is done using the @xl_func decorator from PyXLL. Because our function will return real-time data, and not just a single value, we return a special type of object called an RTD object. In Python, we will create a new class that derives from the PyXLL RTD class.
from pyxll import xl_func, RTD


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


@xl_func("str[] symbols: rtd<object>")
def iex(symbols):
    return IEXClient(symbols)

The iex function takes a list of strings, and returns an RTD object. The string passed to @xl_func above is the function signature. PyXLL uses this to determine how to convert the values passed from Excel to Python, as well as how to convert the Python return value to Excel. This signature is saying that symbols is a list of strings, and the function will return an RTD object whose value is a Python object. The value of the RTD object is what will show up in Excel.

When the iex function is called with a list of symbols, the result in Excel is a handle to the Python object “None”. If we had specified another type for the function return type (e.g. “rtd”) then we could return other types back to Excel instead of a handle to a Python object.

Real-Time-Stock-Quotes-in-Excel-using-Python-06

RTD objects have two methods, connect and disconnect. These are called when Excel is ready to start receiving values and when Excel no longer needs them respectively. For example, connect would be called when a user enters the formula, and disconnect would be called if the user cleared the formula.

To send a new value to Excel from the RTD object all you have to do is to set the value property on the RTD object.

We can take the code written in the previous section and add it the the IEXClient class. We’ll add it to the connect method so that we connect to the IEX server using a Socket.IO client when Excel connects to our RTD object.

Instead of simply printing the data, we’ll store it in a Python dictionary so we can retrieve it later.

class IEXClient(RTD):

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

    def __init__(self, symbols):
        super().__init__(value={})
        self.symbols = symbols
        self.client = socketio.AsyncClient()
        self.data = {}

    async def connect(self):
        """Connect AsyncClient and subscribe to updates."""
        @self.client.on("connect", namespace=self.namespace)
        async def on_connect():
            for symbol in 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)

            # Notify Excel that there's been an update
            self.value = self.data

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

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

Note that the connect and disconnect methods are marked async. PyXLL understands async methods and will schedule them on the Python event loop for us.

Now when we reload the PyXLL add-in and the function is called again the result is a handle to a Python dictionary. As prices tick in, the dictionary is updated and the value shown in Excel updates.

Real-Time-Stock-Quotes-in-Excel-using-Python-07

On it’s own this isn’t too much use! We need a way to get the values out of the Python dictionary into our sheet. This is as simple as writing another function that takes the dictionary, symbol and key and returns the value. The signature for this function says the first parameter is a Python object, followed by the symbol and key as strings, and returns a value of a variable type (i.e. it could be a string, number, or another type).

@xl_func("object data, str symbols, str key: var")
def iex_unpack(data, symbol, key):
    # get the values for the symbol
    values = data.get(symbol, None)
    if not values:
        return "#NoData"

    # return the value for the specified key
    return values.get(key, "#NoData")

This function iex_unpack  is called with the dictionary handle as the first argument, and the symbol and key and returns the value from the dictionary. As new values tick in and the dictionary updates, these values update.

Real-Time-Stock-Quotes-in-Excel-using-Python-08

Portfolio Calculations with Real Time Stock Quotes

Now we have everything we need to add real time pricing to the Someka Portfolio Calculator! You may want to create your own sheet using the template. If you just update the sheet as I have below, when you press the “Calculate” button the changed cells will be over-written.

Real-Time-Stock-Quotes-in-Excel-using-Python-09

You can fetch other data not included in the real time stock quote from IEX’s REST API. The code below shows how to fetch a single value using the Python package aiohttp.

from pyxll import xl_func
import aiohttp

endpoint = "https://api.iextrading.com/1.0/"

@xl_func
async def iex_fetch(symbol, key):
    """returns a value for a symbol from iextrading.com"""
    url = endpoint + f"stock/{symbol}/batch?types=quote"
    async with aiohttp.ClientSession() as session:
        async with session.get(url) as response:
            assert response.status == 200
            data = await response.read()

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

The function above is an asynchronous function. This means that you can call it multiple times and each request to the IEX REST endpoint can run concurrently, greatly improving the responsiveness of the worksheet.

To get the open price for a stock, you would call the above function in Excel as follows.

=iex_fetch("AAPL", "open")

More information about the IEX API can be found on their website https://iextrading.com/developer/docs/.

Related Posts

Search