Calibrating VWAP executions with QuestDB and Grafana

VWAP, or Volume Weighted Average Price, is a trading benchmark used in financial markets. It represents the average price that a security has traded at throughout the day, based on both volume and price. It can help analysts assess the quality of a single trade or look at the overall trend of the market.

In this post, we'll use QuestDB and Grafana to understand how a VWAP works and how it can be calibrated.

What is a VWAP?​

When executing a large order, it's generally better to slice it into smaller chunks over time. Why? It helps avoid trading too much at once and getting a bad price because of the lack of liquidity.

One such slicing algorithm is the VWAP which aims to match the average price traded over the execution period, weighted by volume traded. It is widely considered as one of the most important execution benchmarks.

Unlike the Time Weighted Average Price (TWAP) which slices an order evenly over time, the VWAP will slice based on the expected and observed volume at different times of the day. Since a VWAP algorithm needs to decide how much to trade, and at what time, it needs to be calibrated against a view of what the expected volume should be throughout the day.

For example,

• If the asset traded in a uniform manner throughout the day (e.g., every hour, exactly 2,000 BTC trade on exchange ABC), then the VWAP would slice the order evenly across time, and be equivalent to a TWAP.
• If the asset trades 10% of its volume in the morning, and 90% in the afternoon, then a VWAP algorithm would slice the order with greater weight in the afternoon.

Mathematically, the VWAP over a period is defined as follows:

It's important to differentiate two flavours of VWAP:

• Ex-post VWAP: calculates VWAP after the fact based on historical data. For example, look at the trading volume of BTC on exchange XYZ over the last hour, and calculate the observed VWAP based on trade data.
• Ex-ante VWAP: slices a large order into smaller chunks with the goal to minimise the difference between the average execution price and the ex-post VWAP.

While it's easy to calculate it ex-post, it's more difficult to track it without knowing in advance what volumes are going to trade in the future. This requires some calibration to produce an initial slicing profile. This profile can then be adjusted on the fly based on the observed trading volumes during the execution window.

Let's learn how to do that.

All the examples below point to the QuestDB Live Demo, with real time ingestion of crypto trades. Give it a go!

Understanding volume distribution​

To determine how to algorithmically slice an order, we first need to have an idea of the volume distribution over the day. One way to achieve this is to look at historical volume distributions over a variety of intervals and lookback periods. As a simple example, we could go with the assumption that today's volume 'should' be distributed similarly to yesterday's.

We can derive yesterday's volume as follows:

WITH x AS
  (
  SELECT timestamp, sum(amount) qty
  FROM trades
  WHERE symbol = 'BTC-USD' AND timestamp > dateadd('d',-1,now())
  SAMPLE BY 1h
  ALIGN TO CALENDAR
  )

SELECT hour(timestamp),  qty yesterday
FROM x ORDER BY hour ASC

We can of then expand on this and compare it to other dates or intervals. For example, we can compare with the average over the last week or month by adding an avg() function and changing the lookback period:

WITH x AS
  (
  SELECT timestamp, sum(amount) qty
  FROM trades
  WHERE symbol = 'BTC-USD' AND timestamp > dateadd('d',-30,now())
  SAMPLE BY 1h
  ALIGN TO CALENDAR
  )

SELECT hour(timestamp),  avg(qty) last_month
FROM x
ORDER BY hour ASC

Synthesizing volume predictions​

There is a lot of science in making a volume prediction. But a simple approach could be to use different lookback intervals with different weights assigned to different periods.

For example, one approach would be to favour stability and assign more weight to the longer lookback, and less weight to the more recent lookback. This approach means we account for recent changes in liquidity, but don't overly shift our distribution if there was an abrupt change recently which we believe is transient.

Using the example above, we could combine queries to synthesize a volume distribution with the following weights:

• 50% monthly lookback
• 30% weekly lookback
• 20% yesterday

WITH
  yest as (
  WITH x AS (SELECT timestamp, sum(amount) qty
  FROM trades where symbol = 'BTC-USD' and timestamp > dateadd('d',-1,now())
  SAMPLE BY 1h ALIGN TO CALENDAR)
  select hour(timestamp) h,  qty daily from x ORDER BY h asc),

  week as (
  WITH x AS (SELECT timestamp, sum(amount) qty FROM trades where symbol = 'BTC-USD' and timestamp > dateadd('d',-7,now())
  SAMPLE BY 1h ALIGN TO CALENDAR)
  select hour(timestamp) h,  avg(qty) weekly from x ORDER BY h asc),

  mth as (
  WITH x AS (SELECT timestamp, sum(amount) qty
  FROM trades where symbol = 'BTC-USD' and timestamp > dateadd('d',-30,now())
  SAMPLE BY 1h ALIGN TO CALENDAR)
  select hour(timestamp) h,  avg(qty) monthly from x ORDER BY h asc)

SELECT mth.h, 0.5*monthly + 0.3*weekly  + 0.2*daily
FROM
yest join week on yest.h = week.h
          join mth on mth.h = yest.h
ORDER BY h ASC

The output can then be used as a slicing profile for VWAP orders by slicing the order over the planned execution period. For example, an order executed throughout the full day would have fewer executions in the morning, and ramp up the pace in the afternoon.

Demonstration of slicing​

Let's say we want to buy 100 BTC between 10am and 3PM and want to achieve a price close to the VWAP over that period. The historical curve we just derived shows the following volume distribution:

Based on this, our algo could slice our 100BTC order such that it would execute 12.3BTC between 10 and 11, 11BTC between 11am and noon, and so on. While this example is simple, we may want to take a few elements into consideration before sending an order out.

Other important considerations​

The example above comes up with a strategy to execute X BTC in a set of one-hour windows. However, we still need to consider how to execute within each hour.

If we execute each one-hour chunk in one go we risk distorting the market and getting a bad execution price. In addition, our execution will be skewed towards the prevailing price at the moment of execution. If the price moves significantly, the resulting VWAPs from executing at the beginning, middle, and end of the hour could be vastly different.

If we were to slice the hourly chunks, we could use a TWAP within the hour with some level of price and time randomization. Or, we could increase the resolution of our VWAP liquidity curve. For example, we can use some interpolation function between the hourly percentages, or derive a curve with greater resolution such as every 10 minutes.

Another important consideration is the relative size of the order. If your order is large compared to the ordinary volume, then a significant part of the trading volume over the target VWAP period will be yours.

Intuitively, this means that you are more likely to get an average exec price close to the actual VWAP. However, it could also mean you will hit poor liquidity if your order outsizes what the market can take.

So while the final execution price would be close to the VWAP, it may also incur large trading costs compared to executing the VWAP over a larger time window. Depending on the liquidity, trading a certain quantity via VWAP over a certain period may not be viable.

QuestDB financial functions​

To show the full details, this article laid out the queries in greater detail. But QuestDB has a more concise, native vwap() function. It's one of a growing set of financial functions available out-of-the-box.

SELECT vwap(x, x)
FROM (SELECT x FROM long_sequence(100));

Read more about available finance functions in the documentation.

Conclusion​

VWAP is one of the most important financial execution algorithms. Calibrating and fine-tuning them is a complex piece of work which balances historical data, and realtime on-the-fly adjustments. While this article is not meant to be exhaustive, it should help you build intuition for how a VWAP algo works behind the scenes.

Come talk to us on Slack or Discourse!