This post comes from Tancrede Collard, who has written an excellent tutorial that shows how to use Python to fetch cryptocurrency data from Coinbase, store it in QuestDB, and visualize the data using Grafana. Thanks for the submission, Tancrede!
When analyzing streaming data such as cryptocurrency or market metrics, the foundation of the data processing pipeline is efficient storage and queries. To use this data for insights and analytics, data visualization is a convenient way to plot and convey trends, create actionable reports, or even set up alerting.
Most cryptocurrency trading projects will focus on price charts and standard indicators like RSI or moving averages. Derivatives are often overlooked in many cryptocurrency analytics and visualization projects, and there's plenty to explore, such as the underlying pricing metrics such as volatility and funding rates.
A lot of common off-the-shelf tools can plot prices over time, but few are available for derivative features. Having control of the underlying database, creating custom metrics, and building dashboards based on these metrics allows us to build our own solutions with custom pricing inputs and models for derivatives.
In this tutorial, you'll learn how to fetch data from the Coinbase API using a Python script, load the data into QuestDB and run SQL queries via QuestDB for derivatives insights. We'll be visualizing data using Grafana so that we can build dashboards for reporting or alerts based on metrics you care about.
To follow with this tutorial, you'll need the following:
Before we can start storing data, we'll need to get QuestDB up and running. Aside from running QuestDB from binaries and via Docker, macOS users can get started using homebrew:
Additional settings can be changes in the
server configuration file
/opt/homebrew/var/questdb/conf/server.conf for the homebrew install.
The interface we'll be using for inserting data is ILP
(InfluxDB line protocol)
over TCP which runs on port 9009 by default. The server configuration for these
keys looks as follows:
To avoid unnecessary memory usage when using Grafana, it's recommended to
SELECT query cache with the following PostgreSQL property in
QuestDB's server config:
When we've set these server configuration properties, we can start up the database service with QuestDB's CLI:
Cryptocurrency data is readily available from a variety of public APIs. Coinbase offers a simple API endpoint to get an indicative bid and offer price which is all we need for our use case.
It requires an authorization token which you should obtain from your Coinbase account. For this tutorial, we'll use a simple Python script to periodically poll the endpoint for a given currency pair. The loop does 3 things:
- fetch SELL (bid) and BUY (ask) prices
- send the current bid & ask prices to QuestDB via ILP
- sleep 2 seconds before looping to step 1
The script uses
requests for the HTTP requests to the Coinbase API and
socket for the direct TCP communication to QuestDB. Replace
the API key from your Coinbase account:
When you start the script, prices should appear in the web console for QuestDB.
crypto (this is shorthand for
SELECT * FROM crypto) in the SQL
editor shows the prices flowing in QuestDB:
One important thing to note is that we didn't create a table before sending
data. QuestDB automatically creates tables using the appropriate columns
detected from the message. The currency identifiers are inserted as
symbol types, and the values are
inserted as doubles. It's also noteworthy that the record timestamp is set as
the server time when the row was inserted and tables created from ILP ingestion
have a default
day partitioning. The schema looks like this:
Grafana is an excellent tool for data visualization, and it comes in extremely handy If you're doing any algorithmic trading. The variety of integrations with other services enables you to quickly set up monitoring and alerts for conditions like irregular prices or flow and risk limits.
This tutorial is tailored for macOS users, so we'll use Homebrew, but there are other options covered in the QuestDB Grafana guide:
To configure Grafana, open the UI available
- Go to the
Configurationsection and click on
Add data source
- Choose the
PostgreSQLplugin and configure it with the following settings:
Let's craft a query in Grafana that will allow us to plot the metrics we're
interested in. Start off by creating a new dashboard and click on
The pencil icon allows you to provide a query to run against QuestDB for Grafana
Paste the following query to create a time series of the bid-offer and mid price:
To simplify queries which have dynamic elements such as date ranges, the query contains global variables. We're using two:
$__timeFilter(timestamp)- allows filtering results by sending a start-time and end-time to QuestDB; generates a
$__interval- calculates a dynamic interval based on the time range applied to the dashboard. By using this function, the sampling interval changes automatically as the user zooms in and out of the panel.
At this point, we have a dashboard with ticking prices. We can start experimenting with other parameters such as the colors or the sample frequency for each of the series. The Grafana UI can be used to change the chart range and resolution:
Although our data is very simplistic right now (indicative bid an offer from one platform on one pair), the nice thing about building this from scratch is that we can calculate derived metrics such as the spread over time:
There are many other things to do, such as pulling prices from various venues or fetching order book data rather than indicative top-of-book. The next steps we can take with this project that would be interesting to explore would be:
- Deriving forward curve and implied funding on cryptocurrency using futures prices
- Plotting crypto volatility smiles
If you like this content, we'd love to know your thoughts! Feel free to share your feedback or just come and say hello in the QuestDB Community Slack.