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.
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.
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.
Here’s the code that’s used to connect to the IEX websocket and stream to Excel:
After connecting to the websocket in lines 22-25, the
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.
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.
is added to the
tops grid on every update. The updated sections of code are shown below.