Real Time Crypto Prices in Excel
Learn how to stream live crypto prices into Microsoft Excel
Note from Towards Data Science’s editors: While we allow independent authors to publish articles in accordance with our rules and guidelines, we do not endorse each author’s contribution. You should not rely on an author’s works without seeking professional advice. See our Reader Terms for details.
Trading crypto currencies can be an extremely interesting and rewarding activity. There are many different platforms and exchanges for trading that will offer API access to their data which allows the savvy trader to build their own tools around their own strategy and trading needs.
Microsoft Excel is the go to choice for many traders because of the enormous potential it offers to create custom tools and dashboards to build market insights, test and experiment with data and ideas, and to monitor portfolio performance and keep track of positions.
More serious technically minded traders will also want to use Python for data analysis and back testing trading strategies, or even building systematic or automated trading strategies.
Most crypto exchanges offer a way to get data from their platforms programmatically via an API. This is what we would use to connect our Excel or Python based tools to the platform to fetch data and manage orders.
In this article we’ll use a BitMEX Python API to stream real time prices into Microsoft Excel.
To stream real time BitMEX prices into Excel you will need the following:
- A BitMEX account from www.bitmex.com.
- Python 3 installed on your PC from www.python.org or another Python distribution.
- PyXLL, the Python Excel Add-In, from www.pyxll.com. This is a commercial product but you can use the 30 day free trial.
To begin with we’ll write the Python code to fetch real time prices in Python and test that. Once that’s working we’ll call that Python code from Excel using PyXLL.
To talk to the BitMEX API over websockets we’ll need to Python “websockets” package. Open a Command Prompt and use the Python package manager “pip” to install the websockets package by running “pip install websockets”.
The following Python code creates a websocket connection to the BitMEX API, subscribes to updates to the “XBTUSD” instrument, and then waits for messages back and prints them as they arrive. With this code we are now streaming live prices from BitMEX using Python!
You can run this code by saving it to a file called “bitmex.py” and then running “python bitmex.py” from a command prompt. For developing Python code you will find it easier to use a Python IDE such as IDLE (which comes as standard when you install Python) or PyCharm.
The code above shows how to use the websockets package to connect to the BitMex API to receive price updates in Python. Before we can use this in Excel we’ll refine it slightly so that we can “subscribe” to individual update events.
Below we define a class “BitMex” with a “subscribe” method. Using this we can subscribe to individual updates. The BitMex class will handle routing the messages it receives from the websockets API to the relevant callbacks for each symbol and field that has been subscribed to.
In the “main” function above we create an instance of the “BitMex” class and subscribe to updates to the “lastPrice” field on the “XBTUSD” instrument. Whenever a new “lastPrice” event occurs the callback is called and the new price is printed.
Now we have this BitMex class and the ability to subscribe to specific fields and symbols we will next expose this to Excel using PyXLL. This is what will enable us to get real time streaming data from BitMEX into Excel!
To use the code we’ve written above in Python we need to install the PyXLL Excel Add-in. You can find the installation instructions for this and download the add-in from https://www.pyxll.com.
Once you’ve installed PyXLL you can add your own Python modules to it by editing the pyxll.cfg config file. We will write a new Python module that uses PyXLL’s Real Time Data feature, which you can read more about here.
We write an RTD function with PyXLL by creating a new class that derives from the “pyxll.RTD” class, and then write a Python function to return that. The Python function is decorated using PyXLL’s “@xl_func” decorator to expose it as an Excel function. The RTD class has a couple of methods, “connect” and “disconnect” that we use to subscribe and unsubscribe from the BitMex data as needed. When we receive new values we update the “value” property of the RTD object and that causes the value that we see in Excel to update.
This new Python module is added to our PyXLL add-in by adding it to the “modules” list in the pyxll.cfg config file. The folder containing this module, as well as the bitmex.py module we wrote earlier, also needs to be on the “pythonpath” so that Python can import them. The pythonpath can also be set in the pyxll.cfg config file.
When we start Excel or reload the PyXLL add-in this new module will be loaded and the “bitmex_rtd” function will be available to us to call from Excel. If there are any problems, check the PyXLL log file to check what’s gone wrong.
In Excel now, we can call the new “bitmex_rtd” function in exactly the same way as another other Excel worksheet function. Because it’s a real-time data function the value will continue to update every time an update to the requested field is received via the BitMEX websockets API.
=bitmex_rtd(“XBTUSD”, “lastPrice”)
Many crypto platforms and exchanges offer API access in a similar way to BitMEX. We can use the same techniques shown above to access those prices in Python. PyXLL provides an elegant bridge between the Python code used to call these APIs and Excel.
We’re not just limited to getting data as shown above. Most of the platform APIs will also allow us to place orders via their API too. We can use that to build and integrate bespoke trading applications written in Python, but using Excel as our interface to those tools.
The combination of Python and Excel gives us the best of both a modern and powerful programming language and keeping Excel as a flexible, intuitive front-end for us to work in.
