Real Time Data in Excel Using Sockets

Last week I wrote about getting real time data into Excel using PyXLL and Python code.

I described a way to do this using HTTP connections. HTTP is the type of connection your browser uses to read web pages.

In this article I'm looking at using sockets to get real time data into Excel.

Sockets

A socket is connection made between a client device (e.g. your PC) and a server (e.g. a website, a server, a sensor, etc) that allows the client and server to send each other messages.

This socket connection, once established, stays open and requires little overhead to send messages whilst open.

Think of it like this. You call your best friend on the phone to have a chat, but when you are finished talking, you keep the call connected in case you think of something else you want to talk about in a minute. By not hanging up, you don't have to dial the number again and start another conversation when you think of something new to discuss.

Compare this with a HTTP connection. If your PC (the client) wants to read a web page from our blog you have to click a link. By clicking this link you are telling your browser to request the contents of the blog post from our website (the server) and display it on your screen.

Before any part of the blog post can be sent to you, the browser has to send a request to the server asking it for the blog post. The server then responds informing your browser if it is able to send the blog, before it actually starts to send any part of the page.

Every time you click a link to load another blog post (or any web page) your browser and our website have to go through this whole routine again.

Compared to sockets, the HTTP connection has more overhead in its communications. Every time a page is requested, the browser and server have to talk to each other to set up the connection before any data can be sent.

Once a socket is established it remains open and the server can just send a message down it to the client. The client doesn't have to keep asking the server for the next piece of information.

For real time systems this is beneficial as the client can just sit there and wait for the next piece of data to arrive, like a change in a stock price, or the latest reading from a sensor.

Implementing Sockets in Excel With PyXLL and Python

I'm again going to use the PyXll Python add-in. You'll need to install both PyXLL and Python if you want to give this a go.

As explained in the last post, my Python code will use a modification of Excel's RTD class to pass data into the worksheet. Python works away in the background and notifies Excel when new data has been received which is then displayed on the sheet.

For this exercise I'm going to use the websockets API provided by GDAX to monitor, amongst other things, the price of Bitcoin in USD.

To set up the socket I'll need to install the websocket-client package in Python.

The Code

Once you've imported the socket library into Python you create the socket like this

To get updates from GDAX on the Bitcoin-USD price, subscribe to the ticker channel, specifying the currency pair BTC-USD.

You can subscribe to multiple currency pairs here in the same request if you wish.

All the heavy lifting is done by the GDAXRTD class

and it's in __thread_func that we listen for updates from GDAX and then process the messages we receive.

Any message we receive is in the form of a JSON object and is stored in a variable called result.

The JSON data looks like this

{"type":"ticker","sequence":5981458471,"product_id":"BTC-USD", "price":"7361.05000000", "open_24h":"7493.86000000", "volume_24h":"5764.09338556", "low_24h":"7272.71000000", "high_24h":"7538.14000000", "volume_30d":"273076.23540644", "best_bid":"7361.04", "best_ask":"7361.05", "side":"buy","time":"2018-05-31T03:20:03.484000Z", "trade_id":44233953, "last_size":"0.13707180"}

Processing the Data Updates

PyXLL can only return a single piece of data to Excel. If we are trying to get the current price, bid and ask prices, how to do this?

By converting the data object sent to us by GDAX into a string, we can return that string to Excel and then use string functions like MID and FIND to extract the values we want.

Once we've extracted these numerical values, we can use the VALUE function to convert the number stored as a string, into a number.

In my sample workbook all of these formulae are included for you to examine.

Here's what the sheet looks like with the real time data updates coming in.

Charting This Data

As we are only able to send one value to Excel at any one time, the numbers we are getting are points in time which we can't produce a meaningful chart from. In order to create useful visualizations of the data, we need to store the data as it changes over time.

We could do this by writing some VBA to add every updated value to a dynamic named range, and chart that.

We could write the data to a file and chart that but we'd lose the ability to see the data in real time.

There are other solutions we could use involving Python too, but for now I'll just leave it up to you to figure this out and maybe I'll write another post dealing with this.

Visual Studio Code

I had to reinstall Anaconda for this post and in doing so also installed Microsoft's Visual Studio Code source code editor and so far it has been great. All the images of code above were taken from it.

As you can see from the screen shots above, it obviously has color coding, and also provides debugging and intellisense amongst other things. If you are looking for a code editor, give it a go, it's free.

Download the Workbook and Python Code