Blog post banner

Streaming real-time stock prices from IEX into Excel

March 12, 2018

Not so long ago getting a real-time feed of stock prices was an expensive proposition - something usually available only to the professional financial establishment.

Since IEX Trading (the heroes of Michael Lewis’s bestselling book Flash Boys) came on the scene, the landscape has changed. These guys have been on a mission to disrupt the financial markets and, democratise access to market data.

Gridarrow is a perfect fit for connecting to IEX data and streaming it to Excel.

What is Gridarrow?

Gridarrow is the missing server for Excel. It connects just about any data source to Excel with simple Python scripts.
Why is it awesome?
  • It's real-time, so as soon as the data is updated in the Gridarrow App it's updated in Excel, no need to poll or refresh.
  • It's multi-user so your colleagues can connect to the same data at the same time.
  • It simplifies your spreadsheets by moving data retrieval and pre-processing into Python.
Request Demo

Here’s how it works

IEX make several data feeds openly and freely available over Websockets, including TOPS for aggregated best quoted bid and offer position.

Below you can see the result of connecting to the TOPS websocket and streaming the data feed into Excel.

Excel dashboard showing real-time exchange market data

What am I looking at?

The current best bid and best offer are shown in the bidPrice and askPrice columns. The data bars in the bidSize and askSize columns show the number of shares available at the best price in the order book.

The conditional formatting in the marketPercent and volume columns give some idea of how much trading there is in a particular symbol, and how much has taken place on the IEX exchange itself.

Only 25 lines of code!

Here’s the code that’s used to connect to the IEX websocket and stream to Excel:

 1 2 3 4 5 6 7 8 910111213141516171819202122232425
import os
import ujson
from socketIO_client_nexus import SocketIO, SocketIONamespace
from gridarrow import Arrow

arrow = Arrow()

@arrow.on_start
def on_start(_):
    symbols = os.environ['SYMBOLS'].split(',')
    cols = ["symbol", "marketPercent", "volume", "bidPrice", "bidSize", 
            "askSize", "askPrice", "lastSalePrice", "lastSaleSize", 
            "lastSaleTime", "lastUpdated", "sector", "securityType"]
    
    tops = arrow.grid("Top 50 By Volume", rows=symbols, columns=cols)

    class FeedHandler(SocketIONamespace):
        def on_message(self, msg):
            data = ujson.loads(msg)
            tops[data['symbol'], tops.columns] = [data[c] for c in cols]

    socket = SocketIO('https://ws-api.iextrading.com', 443)
    namespace = socket.define(FeedHandler, "/1.0/tops")
    namespace.emit('subscribe', ','.join(symbols))
    socket.wait()

After connecting to the websocket in lines 22-25, the FeedHandler.on_message method is called for every data update from IEX.

We simply deserialise the message from JSON into a python dictionary, and set the values into the tops grid in line 20, which causes the data to be immediately sent to Excel.

Bonus material: Last 5 trades sparkline

We felt that getting this real-time data into Excel was a little too easy. We decided to jazz things up a bit with the last 5 trades buy/sell sparkline that you see in the screenshot above.

The sparkline shows a green bar for a trade above the mid-point (assumed to be a buy), and a red bar for a sell. It’s created by maintaining a Python deque of fixed length for each of the symbols, and appending 1 for a buy and -1 for a sell.

The whole deque is added to the tops grid on every update. The updated sections of code are shown below.

But before you jump into it, remember to:

 1 2 3 4 5 6 7 8 91011121314151617181920
NUM_TRADES = 5
trades = {symbol: deque([0] * NUM_TRADES, maxlen=NUM_TRADES) 
          for symbol in SYMBOLS}

... snip ...

        def on_message(self, msg):
            data = ujson.loads(msg)
            symbol = data['symbol']
    
            if data['lastSaleTime'] != 0 and \
                    tops[symbol, 'lastSaleTime'] != data['lastSaleTime']:
                mid = (data['bidPrice'] + data['askPrice']) / 2
                if data['lastSalePrice'] >= mid:
                    trades[symbol].append(1)
                else:
                    trades[symbol].append(-1)
    
            tops[symbol, tops.columns] = ([data[c] for c in cols] +
                                          list(trades[symbol]))