Blog post banner

Visualizing the BTC/USD orderbook on the GDAX exchange

March 14, 2018

It’s useful to visualize the shape of the order book by plotting the cumulative sum of liquidity on each side. In this example you’ll see how Gridarrow can stream exchange data into Excel where such visualisations are easy to create.

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

Why would you need this?

The “best” price for any instrument traded in a limit order book doesn’t tell the full story about the price at which you can actually trade.

Say, for example, that you want to buy 100 lots but there is only 1 lot for sale at the best offer. In this case you won’t be able to simply buy 100 lots at the lowest price available. If you want to trade immediately, you’ll have to “trade through the order book” buying at each price level along the way until you’ve satisfied your demand.

That’s where such visualisation comes in handy.

What goes for traditional exchange traded securities is also true for cryptocurrencies. Here’s an example where we’ve connected to the BTC/USD level2 orderbook feed from the GDAX exchange, and used Gridarrow to stream the first 60 price levels to Excel.

Excel chart showing real-time bitcoin orderbook data from GDAX exchange

Fewer than 100 lines of code!

The code to maintain the order book and stream the data to Excel is a bit more involved than some of the other examples we’ve blogged about, but fewer than 100 lines still.

But before you jump into it, remember to:

 1 2 3 4 5 6 7 8 910111213141516171819202122232425262728293031323334353637383940414243444546474849505152535455565758596061626364656667686970717273747576777879808182838485868788899091929394959697
import bisect
import json
import logging
from lomond import WebSocket
from lomond.persist import persist
import ujson
from gridarrow import Arrow

arrow = Arrow()

DEPTH = 60

def on_start(_):
    orderbook = arrow.grid('orderbook',
                           columns=['bidPrice', 'bidSize', 'askSize', 'askPrice'],
    bids = []
    asks = []

    websocket = WebSocket('wss://')
    for msg in persist.persist(websocket):
        if == 'ready':
            websocket.send_text(json.dumps({'type': 'subscribe',
                                            'channels': [{"name": "level2",
                                                          "product_ids": [PRODUCT]}]}))
        elif == 'text':
            event = ujson.loads(msg.text)
            if event['type'] == 'snapshot':
                process_snapshot(asks, bids, event, orderbook)
            elif event['type'] == 'l2update':
                process_l2update(asks, bids, event, orderbook)

def process_snapshot(asks, bids, snapshot, orderbook):
    for bid in snapshot['bids']:
        bisect.insort(bids, Bid(bid[0], bid[1]))
    for ask in snapshot['asks']:
        bisect.insort(asks, Ask(ask[0], ask[1]))
    orderbook[:, ['bidPrice', 'bidSize']] = [[b.price, b.size] for b in reversed(bids[-DEPTH:])]
    orderbook[:, ['askSize', 'askPrice']] = [[a.size, a.price] for a in reversed(asks[-DEPTH:])]

def process_l2update(asks, bids, update, orderbook):
    for change in update['changes']:
        side, price, size = change
        if side == 'buy':
            level = Bid(price, size)
            change, index = update_levels(bids, level)
            update_orderbook(orderbook, ['bidPrice', 'bidSize'], bids, change, index)

        elif side == 'sell':
            level = Ask(price, size)
            change, index = update_levels(asks, level)
            update_orderbook(orderbook, ['askPrice', 'askSize'], asks, change, index)

def update_orderbook(orderbook, cols, ob_side, change, index):
    if index > len(ob_side) - DEPTH:
        if change == 'REPLACE':
            orderbook[len(ob_side) - index, cols] = [ob_side[index].price, ob_side[index].size]
            orderbook[len(ob_side) - index:, cols] = [[b.price, b.size] for b in

class Level:
    def __init__(self, price, size):
        self.price = float(price)
        self.size = float(size)

class Bid(Level):
    def __lt__(self, other):
        return self.price < other.price

class Ask(Level):
    def __lt__(self, other):
        return self.price > other.price

def update_levels(levels, level):
    i = bisect.bisect_left(levels, level)
    if i == len(levels):
        # insert new best level
        return 'INSERT', i + 1

    if levels[i].price == level.price:
        # if there is an existing size at this level, we can just update this level
        if level.size > 0:
            levels[i] = level
            return 'REPLACE', i
        # there should always be an existing level where size=0,
        # which we have to remove
            return 'REMOVE', i
        # insert new non-best level
        levels.insert(i, level)
        return 'INSERT', i