This post was written by Dean Markwick, who has put together an excellent example using QuestDB as a time series database for high-frequency trading. This post shows how to use QuestDB to calculate the limit order book, price impact, trade sign distribution, and other concepts via the Julia programming language.

*Originally published at
Dean's personal blog.*

## #

Connecting to QuestDB from Julia langIn my first post, I showed how to set up a producer/consumer model to build a
`BTCUSD`

trades database using the CoinbasePro WebSocket feed. Now I'll show you
how you can connect to the same database to pull out the data, use some specific
timeseries database queries and hopefully show where this type of database is
helpful by improving some of my old calculations.

I ingested just over 24 hours worth of data over the 24th and 25th of July, 2021, but I completely missed the massive rally, which is just my luck. That would have been interesting to look at, but never mind! I'm going to repeat some of the calculations from older blog posts on high-frequency finance using more data this time.

Julia can connect to the database using the LibPQ.jl package and execute queries using all their functions. This is very handy as we don't have to worry about database drivers or connection methods; we can just connect and go.

The following is the default connection credentials for the database used to connect to QuestDB:

It's very easy; Julia thinks that it is a regular Postgres database. We can quickly move on to working with the data. I started with getting all trades out of the database:

It takes about 5 seconds to pull 210,000 rows into the notebook:

Now we can plot the results and export as a `.png`

:

You can see that `Plots.jl`

struggles a little with formatting the x-axis
correctly with a time series plot above. The timestamps are pretty long labels
which could be better if the text was angled 45 degrees. It's a shame that I
missed the sudden rally on Sunday 25th; that was pretty unlucky, but let's move
on to updating previous graphs with the new dataset.

## #

Order sign correlationThe correlation between buys and sells follows a power law. Last time, I only had 1000 trades to work on after pulling them using the REST API. Now I've got 200x more, which should improve the uncertainty around the previous values.

In the log-log plot, we can see a nice straight line which we fit a linear model on.

This time we've got a πΎ value of 0.7 with more certainty.

This lines up nicely with the data and is better than the previous attempt with just 1000 trades. πΎ is less than one, which means it is a 'long memory' process, so trades in the past affect trades in the future for a long time. This is usually explained as the effect of people breaking up large trades into slices and executing them bit by bit.

## #

Size distributionAgain, the size of each trade follows a power-law distribution too. We use a slightly different method to estimate the exponent, and last time, with just 1000 trades, we struggled to get a stable value. Now, with so much more data, we can have another crack.

Let's use the same Hill estimator as last time:

It's still hard to judge whether it is converging to a value or not. It always appears to be decreasing no matter the sample size. Maybe I still need more data, or perhaps I need a better understanding of the Hill estimator!

## #

Market impactI've not been using QuestDB to its full potential, and repeating all my previous graphs hasn't fully exploited the available features. One of those features is the ability to group by the timestamp across a bucket size (1 second, 5 minutes, etc.) and aggregate the data. We will use that to try and come up with a better model of a market impact than I had in my previous post.

We aggregate the trades into 1-minute buckets and calculate the total volume traded, the total signed volume (sell trades count as negative), the last price and also the number of trades in each bucket.

This took less than a second and is a really easy line of code to write:

timestamp | TotalVolume | SignedVolume | Close | NTrades |
---|---|---|---|---|

DateTimβ¦ | Float64 | Float64 | Float64 | Int64 |

2021-07-24T08:50:34.365 | 1.75836 | -0.331599 | 33649.0 | 52 |

2021-07-24T08:51:34.365 | 4.18169 | -3.01704 | 33625.2 | 67 |

2021-07-24T08:52:34.365 | 0.572115 | -0.325788 | 33620.1 | 46 |

Now for the market impact calculation, we calculated the return bucket to bucket and normalise the signed volume by the total volume traded to give a value of between -1 and 1. This is taken from https://arxiv.org/pdf/1206.0682.pdf and equation 26.

Here we can see that there is a positive coefficient, ΞΈ in the paper, as expected, and we can interpret this at how much the price moves after buying or selling. Specifically, in these minute buckets, those that contained only buy trades moved the market up by 4.5bps and the same for sells in the opposite direction.

You can see how the model lines of with the data, and there is a very slight trend that is picked. So overall, a better, if still a very simple model of market impact.

## #

Trades with top of bookI've saved down the best bid and offer using the same process as Part 1 of this series. Over the same period, the best bid and offer data has 17 million rows; quite a bit more. I use this best bid-offer data to do an ASOF join. ASOF joins take two tables and joins them such that the timestamps align or the previous observation is used. In our case, we can take the trades, join it with the best bid and best offer table to get where the mid-price was at the time of the trade.

This took 11 seconds but was all done in the database, so no issue with blowing
out the memory after pulling it into your Julia session. Doing a regular join in
Julia would only match timestamps exactly, whereas we want the last observed
bid/offer price at least making the `ASOF`

function very useful.

We now calculate a mid-price, how far the traded price was from the mid-price, and add an indicator for what quantile the trade size landed. We then group by this quantile indicator and calculate the average trade size and the average distance from the mid-price.

Unsurprisingly, we can see that larger trades are further away from the mid-price when they execute. This is because they are eating through the posted liquidity.

This is very similar to my https://cryptoliquiditymetrics.com/ sweep the book
graph, which is *estimating* the cost of eating liquidity. The graph above shows
the *actual cost* of eating liquidity for real trades that have happened on
Coinbase.

We can fit a model to this plot, and it is commonly referred to as the square root law of market impact. We ignore the smaller trade sizes, as they aren't following the nice linear log-log plot.

The πΎ value of 0.25 is pretty low compared to other assets, which we would expect to be around 0.5. But we haven't included the usual volatility calculation, which is in front of the volume component.

Apart from the small trades, the model lines up well with the increasing trade size.

Using this model, you can start to estimate how much a strategy might cost to implement. The outcome of your strategy is unknown, but your trading costs are known. If it costs you 1bp to enter and exit a trade (round trip), but you only think the price will change by 0.5bps, then you're at a loss even if you were 100% right on the price direction!

## #

SummaryQuestDB makes working with this data incredibly easy. Both aggregating the data
using `SAMPLE BY`

and joining two datasets using `AS OF`

. Connecting to the
database is a doddle using `LibPQ.jl`

, so you can get up and running without any
issues. These things rarely happen straight out of the box.

Using this data, I've ramped up the sample sizes, and all my plots and models look better. Again, all free data and with hopefully, very minimal technical difficulty. As someone that usually finds themselves drowning in CSVs, QuestDB has shown how much more efficient things can be when you use a database.

If you have have feedback or questions about this article, feel free ask in our Slack Community or browse the project on GitHub where we welcome contributions of all kinds.