Post-trade markout analysis
Markout analysis measures how the market mid-price moves after a trade executes. It is the natural complement to slippage:
- Slippage tells you how much you paid at the moment of execution.
- Markout tells you what happened next — did the market move in your favor (reversion) or against you (adverse selection)?
A positive markout means the trade was profitable in hindsight: for buys, the mid-price rose; for sells, it fell. A negative markout means the market moved against you, which may indicate you were trading against informed flow.
By computing markouts at multiple time horizons (e.g., every second for 5 minutes), you build a markout curve — the standard tool for evaluating execution quality over time.
Problem
You want to evaluate whether your fills are subject to adverse selection. For each trade, you need to know how the mid-price evolved over the seconds and minutes following execution, broken down by venue, counterparty, and passive/aggressive.
Solution
Use HORIZON JOIN to compute the mid-price at multiple time offsets after each trade, then aggregate into a markout curve:
SELECT
t.symbol,
t.ecn,
t.counterparty,
t.passive,
h.offset / 1000000000 AS horizon_sec,
count() AS n,
avg(
CASE t.side
WHEN 'buy' THEN ((m.best_bid + m.best_ask) / 2 - t.price)
/ t.price * 10000
WHEN 'sell' THEN (t.price - (m.best_bid + m.best_ask) / 2)
/ t.price * 10000
END
) AS avg_markout_bps,
sum(
CASE t.side
WHEN 'buy' THEN ((m.best_bid + m.best_ask) / 2 - t.price)
* t.quantity
WHEN 'sell' THEN (t.price - (m.best_bid + m.best_ask) / 2)
* t.quantity
END
) AS total_pnl
FROM fx_trades t
HORIZON JOIN market_data m ON (symbol)
RANGE FROM 0s TO 30s STEP 5s AS h
WHERE t.timestamp IN '$now-1h..$now'
GROUP BY t.symbol, t.ecn, t.counterparty, t.passive, horizon_sec
ORDER BY t.symbol, t.ecn, t.counterparty, t.passive, horizon_sec;
How it works
HORIZON JOIN is the key construct. For each trade and each time offset in the range, it performs an ASOF match against market_data at trade_timestamp + offset. The RANGE FROM 0s TO 30s STEP 5s generates 7 offsets (0s, 5s, 10s, ... 30s), giving you a markout reading every 5 seconds for 30 seconds after each trade.
The two metrics:
avg_markout_bps— average price movement in basis points, normalized by fill price. Positive means the market moved in your favor. At offset 0, this is simply the negative of slippage-vs-mid.total_pnl— actual P&L in currency terms (price difference × quantity). This captures the dollar impact, not just the rate — 0.1 bps on 1M.
The markout formula flips the sign convention compared to slippage:
- For buys: positive if mid rose after the fill (profit)
- For sells: positive if mid fell after the fill (profit)
As the offset increases, you see how the market evolved after each trade.
Variations
Markout at specific horizons
Use LIST instead of RANGE for non-uniform time points — useful when you care about specific benchmarks (e.g., -30s, -5s, 0, 5s, 30s):
SELECT
t.ecn,
t.passive,
h.offset / 1000000000 AS horizon_sec,
count() AS n,
round(avg(
CASE t.side
WHEN 'buy' THEN ((m.best_bid + m.best_ask) / 2 - t.price)
/ t.price * 10000
WHEN 'sell' THEN (t.price - (m.best_bid + m.best_ask) / 2)
/ t.price * 10000
END
), 3) AS avg_markout_bps
FROM fx_trades t
HORIZON JOIN market_data m ON (symbol)
LIST (-30s, -5s, 0, 5s, 30s) AS h
WHERE t.timestamp IN '$now-1h..$now'
GROUP BY t.ecn, t.passive, horizon_sec
ORDER BY t.ecn, t.passive, horizon_sec;
Pre- and post-trade analysis
Use negative offsets to detect information leakage — whether the market was already moving before your trade:
SELECT
h.offset / 1000000000 AS horizon_sec,
count() AS n,
round(avg(
CASE t.side
WHEN 'buy' THEN ((m.best_bid + m.best_ask) / 2 - t.price)
/ t.price * 10000
WHEN 'sell' THEN (t.price - (m.best_bid + m.best_ask) / 2)
/ t.price * 10000
END
), 3) AS avg_markout_bps
FROM fx_trades t
HORIZON JOIN market_data m ON (symbol)
RANGE FROM -30s TO 30s STEP 1s AS h
WHERE t.timestamp IN '$now-1h..$now'
GROUP BY horizon_sec
ORDER BY horizon_sec;
If the markout is already trending before offset 0, it suggests the market was moving before your order — a sign of information leakage or that you are reacting to stale signals.
Markout by side
Add t.side to the grouping to detect asymmetry between buy and sell execution. A counterparty might look fine on average but show adverse selection on one side only:
SELECT
t.ecn,
t.side,
h.offset / 1000000000 AS horizon_sec,
count() AS n,
round(avg(
CASE t.side
WHEN 'buy' THEN ((m.best_bid + m.best_ask) / 2 - t.price)
/ t.price * 10000
WHEN 'sell' THEN (t.price - (m.best_bid + m.best_ask) / 2)
/ t.price * 10000
END
), 3) AS avg_markout_bps
FROM fx_trades t
HORIZON JOIN market_data m ON (symbol)
LIST (-30s, -5s, 0, 5s, 30s) AS h
WHERE t.timestamp IN '$now-1h..$now'
GROUP BY t.ecn, t.side, horizon_sec
ORDER BY t.ecn, t.side, horizon_sec;
If buy markouts diverge significantly from sell markouts at the same venue, it may indicate directional information leakage or asymmetric adverse selection.
Single-side markout
When analyzing one side at a time, you can drop the CASE entirely for a simpler formula:
SELECT
t.symbol,
h.offset / 1000000000 AS horizon_sec,
count() AS n,
avg(((m.best_bid + m.best_ask) / 2 - t.price) / t.price * 10000) AS avg_markout_bps,
sum(((m.best_bid + m.best_ask) / 2 - t.price) * t.quantity) AS total_pnl
FROM fx_trades t
HORIZON JOIN market_data m ON (symbol)
RANGE FROM 0s TO 10m STEP 10s AS h
WHERE t.side = 'buy'
AND t.timestamp IN '$now-1h..$now'
GROUP BY t.symbol, horizon_sec
ORDER BY t.symbol, horizon_sec;
SELECT
t.symbol,
h.offset / 1000000000 AS horizon_sec,
count() AS n,
avg((t.price - (m.best_bid + m.best_ask) / 2) / t.price * 10000) AS avg_markout_bps,
sum((t.price - (m.best_bid + m.best_ask) / 2) * t.quantity) AS total_pnl
FROM fx_trades t
HORIZON JOIN market_data m ON (symbol)
RANGE FROM 0s TO 10m STEP 10s AS h
WHERE t.side = 'sell'
AND t.timestamp IN '$now-1h..$now'
GROUP BY t.symbol, horizon_sec
ORDER BY t.symbol, horizon_sec;
This approach is useful when you want to run separate analyses per side, or when feeding results into dashboards that track buy and sell P&L independently.
Counterparty toxicity
Group by counterparty to identify which LPs are sending you toxic flow — trades that consistently move against you shortly after execution:
SELECT
t.symbol,
t.counterparty,
h.offset / 1000000000 AS horizon_sec,
count() AS n,
avg(((m.best_bid + m.best_ask) / 2 - t.price) / t.price * 10000) AS avg_markout_bps,
sum(t.quantity) AS total_volume
FROM fx_trades t
HORIZON JOIN market_data m ON (symbol)
LIST (0, 1s, 5s, 10s, 30s, 1m, 5m) AS h
WHERE t.side = 'buy'
AND t.timestamp IN '$now-1h..$now'
GROUP BY t.symbol, t.counterparty, horizon_sec
ORDER BY t.symbol, t.counterparty, horizon_sec;
A counterparty whose markout is persistently negative across horizons is likely trading on information you don't have. Compare total_volume alongside markout — a small counterparty with terrible markout may not matter, but a large one warrants flow management.
Passive vs aggressive with spread context
Compare markout between passive (limit) and aggressive (market) orders, with the half-spread as a baseline. Aggressive fills should cost roughly half the spread; if the markout is worse than that, execution quality needs attention:
SELECT
t.symbol,
t.ecn,
t.passive,
h.offset / 1000000000 AS horizon_sec,
count() AS n,
avg(((m.best_bid + m.best_ask) / 2 - t.price)
/ t.price * 10000) AS avg_markout_bps,
avg((m.best_ask - m.best_bid)
/ ((m.best_bid + m.best_ask) / 2) * 10000) / 2 AS avg_half_spread_bps
FROM fx_trades t
HORIZON JOIN market_data m ON (symbol)
RANGE FROM 0s TO 5m STEP 5s AS h
WHERE t.side = 'buy'
AND t.timestamp IN '$now-1h..$now'
GROUP BY t.symbol, t.ecn, t.passive, horizon_sec
ORDER BY t.symbol, t.ecn, t.passive, horizon_sec;
At offset 0, aggressive fills typically show avg_markout_bps close to negative avg_half_spread_bps (you crossed the spread). If markout recovers toward zero over subsequent offsets, execution is healthy — you paid the spread but the market didn't move further against you. If markout stays flat or worsens, it signals adverse selection beyond the spread cost.
Interpreting the markout curve
- Flat near zero: No significant post-trade price impact. Fills are neutral.
- Rising markout (positive trend): Price reverts in your favor after the fill. This is the ideal scenario — it suggests you are capturing spread or providing liquidity at good levels.
- Falling markout (negative trend): Adverse selection — the market moves against you after the fill. This may indicate you are being picked off by informed counterparties or reacting too slowly.
- Passive vs aggressive: Passive fills typically show better markouts because they provide liquidity. Aggressive fills often show initial negative markout equal to the spread cost, which may or may not revert.
- Counterparty differences: Persistent negative markout against specific counterparties is a strong signal of adverse selection and may warrant flow management.