ContextQMD
Back to Questdb

Post-trade analysis overview

documentation/cookbook/sql/finance/post-trade-overview.md

latest5.5 KB
Original Source

Post-trade analysis — also called transaction cost analysis (TCA) — measures execution quality after the fact. Market makers use it to detect adverse selection on their resting orders. Buy-side desks use it to evaluate broker and venue performance. Compliance teams use it to demonstrate best execution.

QuestDB is well suited to this workload because TCA is fundamentally a time-series join problem: pair each trade with the state of the order book at the time of execution, then again at various points in the future. The key SQL features used across these recipes are:

  • ASOF JOIN — match each trade to the most recent order book snapshot. The foundation of all slippage calculations.
  • HORIZON JOIN — match each trade to the order book at multiple time offsets in a single pass. Powers markout curves, implementation shortfall decomposition, and multi-horizon venue scoring.
  • PIVOT — reshape horizon offsets from rows into columns for dashboard-style wide tables.
  • Window functions — cumulative sums and rolling averages for volume-bucketed metrics like VPIN.

Key concepts

Before diving into the recipes, here are the core TCA metrics in the order you'll encounter them:

  • Slippage — the difference between your execution price and a reference price (mid or top-of-book) at the time of the fill. The simplest measure of execution cost. Positive means you paid more than the reference.
  • Markout — how the market moves after your fill. The complement of slippage: slippage tells you what you paid, markout tells you what happened next. Negative markout means the market moved against you (adverse selection).
  • Implementation shortfall — total cost decomposed into why you paid it: spread cost (the bid-ask spread you crossed), permanent impact (the market moved because of your order), and temporary impact (cost that reverted).
  • Adverse selection — when counterparties or venues systematically trade against you just before the market moves in their favor. The central problem TCA tries to detect and quantify.
  • VPIN — Volume-synchronized Probability of Informed Trading. A volume-bucketed measure of order flow imbalance that detects informed trading activity without relying on post-trade price movement.

Recipes

The recipes build on each other. Slippage answers "how much did I pay?", markout answers "what happened after?", implementation shortfall answers "why did I pay?", venue scoring answers "where should I trade?", and VPIN answers "who is informed?"

1. Slippage — how much did I pay?

Compare each fill to the prevailing order book at the time of execution.

2. Markout — what happened after?

Track post-fill price movement at multiple time horizons.

  • Markout analysis — markout curves by side, counterparty, and passive vs aggressive
  • Last look detection — millisecond-granularity markout to identify asymmetric rejection patterns in FX

3. Implementation shortfall — why did I pay?

Decompose total cost into spread, permanent impact, and temporary impact.

  • IS decomposition by symbol — Perold framework separating effective spread, permanent, and temporary components
  • Order-level IS — per-order cost including execution drift, spread cost, and impact breakdown

4. Venue scoring — where should I trade?

Compare execution quality across venues and counterparties to inform routing.

  • ECN scorecard — fill quality, toxicity by hour, passive vs aggressive breakdown, composite toxicity score, and pivoted multi-horizon view

5. Flow toxicity — who is informed?

Detect informed trading using volume-synchronized metrics instead of price-based markout.

  • VPIN — Volume-synchronized Probability of Informed Trading, including per-ECN variant

Data schema

All recipes use the demo dataset. The two tables are joined by symbol and aligned by timestamp:

  • fx_trades — trade executions with symbol, ecn, side, passive, price, quantity, counterparty, order_id (nanosecond timestamps)
  • market_data — order book snapshots with symbol, bids[][], asks[][], best_bid, and best_ask (microsecond timestamps). The bids and asks arrays hold price and size at each level of the book - [1][1] is the best price, [1][-1] is the price at the deepest level. The best_bid and best_ask columns provide the top-of-book prices directly for convenience and efficiency, since most post-trade analytics queries need only the best price

The tables use different timestamp resolutions. QuestDB's time-series joins handle mixed-precision timestamps automatically — no explicit casting is needed.

:::info Related documentation