2:45 AM monday.
A. went to bed around midnight after spending the evening fighting a client’s postgres migration that kept deadlocking under load. she was frustrated, said goodnight, gave me a look that meant don’t be up all night. I said I wouldn’t be.
two hours later I’m still at my desk pulling execution logs.
pulled Q1 fill exports after the close today. what started as routine sanity-checking turned into a rabbit hole that swallowed most of my night. found something I should have been measuring three years ago and wasn’t.
the gap I didn’t know I had #
every systematic fill event is logged. strategy, symbol, time, direction, fill price. I have all of it in TimescaleDB going back to early 2024.
what I didn’t have was the comparison. fill price vs. what? mid at submission? bid-ask at that moment? prior trade price?
without the comparison, fill price is noise. I’ve been counting P&L correctly. I’ve been attributing returns by strategy. I’ve been measuring theta, delta drift, IV regime performance. but execution quality — how well the actual fills matched what the models expected — that had a gaping hole in it.
the Q1 attribution work from earlier this month told me theta edge was real across the options book. what it couldn’t tell me was whether execution timing and routing were quietly eating part of that edge. running $1.2M across 40+ simultaneous positions, the math on that matters.
if average slippage is 5 bps across $1.2M of notional and positions turn ~4x per year, that’s roughly $24k in annual friction I can’t backtest because backtests use mid-price fills.
time to measure it.
what slippage actually means here #
it’s different for every instrument type, which is the whole problem.
options (60% of book): SPX and QQQ spreads, iron condors, diagonals. bid-ask spreads are $0.05-$0.50+ depending on strike and DTE. mid-price is the natural reference because that’s what models use. slippage = fill vs. mid at order submission, direction-adjusted. target: ≤5 bps average. anything above 8 is a routing or timing problem.
futures (10% of book): ES and NQ momentum strategies. futures are liquid with sub-tick spreads at volume. target: ≤1.5 bps. if it’s worse than this on ES during RTH something is broken at the order level.
crypto (30% of book): BTC and ETH via ccxt across Binance/Kraken/Coinbase. liquidity is solid on majors but varies by venue and time. target: ≤4 bps during active sessions, slightly looser overnight.
those thresholds are aspirational. I made them up based on gut feel. the point of this system is to replace gut feel with data.
the schema #
every fill event needs enough context to compute slippage retrospectively and slice it ten different ways.
CREATE TABLE execution_events (
id BIGSERIAL,
ts TIMESTAMPTZ NOT NULL,
strategy_id VARCHAR(64) NOT NULL,
asset_class VARCHAR(16) NOT NULL, -- 'options', 'futures', 'crypto'
symbol VARCHAR(32) NOT NULL,
direction CHAR(4) NOT NULL, -- 'buy' / 'sell'
quantity NUMERIC(18, 4) NOT NULL,
expected_px NUMERIC(18, 6) NOT NULL, -- mid-market at order submission
fill_px NUMERIC(18, 6) NOT NULL,
bid_at_sub NUMERIC(18, 6),
ask_at_sub NUMERIC(18, 6),
iv_rank_at_sub NUMERIC(5, 2),
venue VARCHAR(32) NOT NULL, -- 'IB', 'Tastyworks', 'Binance', etc.
order_type VARCHAR(16) NOT NULL, -- 'limit', 'market', 'midpoint'
fill_ms INT, -- milliseconds from submit to fill
PRIMARY KEY (ts, id)
);
SELECT create_hypertable(
'execution_events', 'ts',
chunk_time_interval => INTERVAL '7 days'
);
CREATE INDEX idx_exec_strategy ON execution_events (strategy_id, ts DESC);
CREATE INDEX idx_exec_asset ON execution_events (asset_class, ts DESC);
CREATE INDEX idx_exec_venue ON execution_events (venue, ts DESC);
-- main analysis view
CREATE VIEW execution_slippage AS
SELECT
ts,
strategy_id,
asset_class,
symbol,
direction,
quantity,
fill_px,
expected_px,
CASE direction
WHEN 'buy' THEN (fill_px - expected_px) / expected_px * 10000
WHEN 'sell' THEN (expected_px - fill_px) / expected_px * 10000
ELSE NULL
END AS slippage_bps,
CASE
WHEN bid_at_sub IS NOT NULL AND ask_at_sub IS NOT NULL
THEN (ask_at_sub - bid_at_sub) / expected_px * 10000
END AS spread_bps,
iv_rank_at_sub,
venue,
order_type,
fill_ms,
EXTRACT(HOUR FROM ts AT TIME ZONE 'America/New_York') AS hour_et
FROM execution_events;
the slippage_bps field is direction-aware. buying above mid is positive (bad). selling below mid is positive (also bad). negative slippage means a better-than-mid fill — this actually happens on limit orders with patient routing, probably 15-20% of my options fills.
the tracker #
the class that intercepts fill events and handles ingestion. designed for zero overhead on the execution path — non-blocking queue, async batch writes to TimescaleDB, parallel Redis update for real-time dashboards.
import asyncio
import asyncpg
import redis.asyncio as redis
from dataclasses import dataclass, field
from datetime import datetime, timezone
from decimal import Decimal
from typing import Literal, Optional
import logging
logger = logging.getLogger("exec_tracker")
AssetClass = Literal["options", "futures", "crypto"]
Direction = Literal["buy", "sell"]
@dataclass
class FillEvent:
strategy_id: str
asset_class: AssetClass
symbol: str
direction: Direction
quantity: Decimal
expected_px: Decimal
fill_px: Decimal
bid_at_sub: Optional[Decimal] = None
ask_at_sub: Optional[Decimal] = None
iv_rank_at_sub: Optional[float] = None
venue: str = "IB"
order_type: str = "limit"
fill_ms: Optional[int] = None
ts: datetime = field(
default_factory=lambda: datetime.now(timezone.utc)
)
@property
def slippage_bps(self) -> float:
if self.expected_px == 0:
return 0.0
delta = self.fill_px - self.expected_px
if self.direction == "sell":
delta = -delta
return float(delta / self.expected_px * 10_000)
@property
def spread_bps(self) -> Optional[float]:
if None in (self.bid_at_sub, self.ask_at_sub) or self.expected_px == 0:
return None
return float(
(self.ask_at_sub - self.bid_at_sub) / self.expected_px * 10_000
)
class ExecutionQualityTracker:
"""
Non-blocking fill event ingestion.
DB writes are batched async; Redis keeps rolling 100-fill averages
per asset class for real-time Grafana panels.
"""
REDIS_WINDOW = 100
def __init__(self, pg_dsn: str, redis_url: str) -> None:
self._pg_dsn = pg_dsn
self._redis_url = redis_url
self._pg: Optional[asyncpg.Pool] = None
self._redis: Optional[redis.Redis] = None
self._queue: asyncio.Queue[FillEvent] = asyncio.Queue(maxsize=2_000)
async def connect(self) -> None:
self._pg = await asyncpg.create_pool(
self._pg_dsn,
min_size=2, max_size=6,
command_timeout=15,
)
self._redis = await redis.from_url(
self._redis_url, decode_responses=True
)
logger.info("ExecutionQualityTracker online")
async def record(self, event: FillEvent) -> None:
"""Non-blocking. Call from fill handlers on the hot path."""
try:
self._queue.put_nowait(event)
except asyncio.QueueFull:
logger.warning("exec queue full — dropping fill for %s", event.symbol)
async def _process_loop(self) -> None:
while True:
batch: list[FillEvent] = []
try:
e = await asyncio.wait_for(self._queue.get(), timeout=0.5)
batch.append(e)
while not self._queue.empty() and len(batch) < 100:
batch.append(self._queue.get_nowait())
except asyncio.TimeoutError:
continue
await asyncio.gather(
self._bulk_insert(batch),
self._update_redis(batch),
return_exceptions=True,
)
async def _bulk_insert(self, events: list[FillEvent]) -> None:
if not self._pg:
return
rows = [
(
e.ts, e.strategy_id, e.asset_class, e.symbol,
e.direction, float(e.quantity),
float(e.expected_px), float(e.fill_px),
float(e.bid_at_sub) if e.bid_at_sub is not None else None,
float(e.ask_at_sub) if e.ask_at_sub is not None else None,
e.iv_rank_at_sub, e.venue, e.order_type, e.fill_ms,
)
for e in events
]
async with self._pg.acquire() as conn:
await conn.executemany(
"""
INSERT INTO execution_events (
ts, strategy_id, asset_class, symbol, direction,
quantity, expected_px, fill_px, bid_at_sub, ask_at_sub,
iv_rank_at_sub, venue, order_type, fill_ms
) VALUES (
$1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13,$14
)
""",
rows,
)
async def _update_redis(self, events: list[FillEvent]) -> None:
if not self._redis:
return
pipe = self._redis.pipeline()
for e in events:
key = f"exec:slippage:{e.asset_class}"
pipe.lpush(key, round(e.slippage_bps, 4))
pipe.ltrim(key, 0, self.REDIS_WINDOW - 1)
await pipe.execute()
async def get_realtime_averages(self) -> dict[str, float]:
if not self._redis:
return {}
out = {}
for ac in ("options", "futures", "crypto"):
raw = await self._redis.lrange(f"exec:slippage:{ac}", 0, -1)
if raw:
out[ac] = round(sum(float(x) for x in raw) / len(raw), 3)
return out
async def start(self) -> None:
asyncio.create_task(self._process_loop())
async def close(self) -> None:
if self._pg:
await self._pg.close()
if self._redis:
await self._redis.aclose()
integration with the IB fill handler is a single call. when ib_insync fires orderStatusEvent, I pull bid/ask from the Redis market data cache, construct the FillEvent, and call tracker.record(event).
async def on_ib_fill(order, fill, trade):
bid, ask = get_market_snapshot(fill.contract.conId) # from Redis cache
iv_rank = get_iv_rank(fill.contract.conId)
is_future = fill.contract.secType == "FUT"
event = FillEvent(
strategy_id = order.orderRef,
asset_class = "futures" if is_future else "options",
symbol = fill.contract.localSymbol,
direction = "buy" if fill.execution.side == "BOT" else "sell",
quantity = Decimal(str(fill.execution.shares)),
expected_px = Decimal(str((bid + ask) / 2)),
fill_px = Decimal(str(fill.execution.avgPrice)),
bid_at_sub = Decimal(str(bid)),
ask_at_sub = Decimal(str(ask)),
iv_rank_at_sub = iv_rank,
venue = "IB",
order_type = order.orderType.lower(),
fill_ms = int(
(datetime.now(timezone.utc) - order._submit_time).total_seconds() * 1000
),
)
await tracker.record(event)
sub-millisecond overhead on the hot path. the queue handles the async write.
what the data showed #
847 fill events. Q1 2026 plus first 25 days of April. enough to start seeing real patterns.
Figure 1: Slippage distribution across 847 fill events. Positive = worse-than-mid fill. Negative = better-than-mid (happens on patient limit orders). Target thresholds marked in amber.
key numbers:
- options median: 3.4 bps (target ≤5 bps ✓, but the right tail is ugly)
- futures median: 0.8 bps (target ≤1.5 bps ✓, basically textbook)
- crypto median: 2.3 bps (target ≤4 bps ✓, BTC/ETH fine, altcoin fills pulling the tail)
options have by far the widest variance. the outliers above 8 bps are what I care about. tagged every fill above 8 bps. 80% of them happened in the first 30 minutes of RTH or in the last 20 minutes before close. not surprising. but I was not routing around those windows consistently. just hoping limit orders would behave.
time-of-day matters more than I thought #
second chart. options fills only, averaged by hour ET.
Figure 2: Options fill slippage by time of day. Green = acceptable (≤3.5 bps), yellow = marginal (3.5-5 bps), red = bad (>5 bps). Amber dashed line = 5 bps target. Fill count (purple) shows volume distribution.
9:00-9:30 AM ET is a disaster. 9.4 and 7.1 bps average. those fills should not be happening. the midday window — 10:30 AM to 2:30 PM ET — averages 2.1-2.9 bps, which is clean. the 3:30-4:00 PM window degrades but not as badly as the open.
the action item from this chart is obvious: add a time-of-day gate to the execution engine. any entry or roll order triggered between 9:00 and 10:15 AM ET gets deferred to 10:15 unless IV conditions are forcing an immediate response (gap risk management, emergency delta hedge). the estimated impact of the open-session fills in Q1 alone is $8k-$12k in unnecessary friction. that’s not variance, that’s a fixable routing bug.
redis real-time layer #
the Grafana dashboard now has a live execution quality panel. the tracker maintains a rolling 100-fill window per asset class in Redis, exposed via a simple JSON endpoint the dashboard polls every 30 seconds:
@app.get("/exec-quality")
async def exec_quality():
avgs = await tracker.get_realtime_averages()
return {
"options_bps": avgs.get("options", 0.0),
"futures_bps": avgs.get("futures", 0.0),
"crypto_bps": avgs.get("crypto", 0.0),
"thresholds": {"options": 5.0, "futures": 1.5, "crypto": 4.0},
"alerts": {
ac: avgs.get(ac, 0) > threshold
for ac, threshold in [("options", 7.0), ("futures", 2.5), ("crypto", 6.0)]
},
}
if options slippage in the last 100 fills crosses 7 bps, an alert fires and a human-review flag gets set on the routing layer. at that point market conditions have probably shifted enough that the midpoint limit strategy needs adjusting or I’m hitting unusual liquidity.
next steps #
this week:
- deploy the time-of-day gate to the IB routing wrapper — should cut average options slippage by 0.8-1.2 bps immediately
- backfill
execution_eventswith 2024 fills from old CSV exports — they have enough columns to reconstruct slippage - add venue-level breakdowns — Tastyworks vs IB differ meaningfully on options execution and I want to see the actual numbers, not gut feel
- start building the altcoin slippage filter — certain ccxt venues have consistently bad fills on the lower-liquidity alts
early hypothesis on venue comparison: Tastyworks fills are slightly worse on SPX spreads than IB despite being the options specialist. could be routing differences, could be PFOF. have a feeling about this. need data to confirm.
there’s a thread on NexusFi — Attack of the Robots algo journal — where someone has been building and documenting systematic strategy improvements for years. the discipline of logging every change, every result, every decision. that’s what I’ve been trying to build toward since I found that thread back in 2023. this is my version of it. better late than never.
my dad was an engineer. obsessed with measurement. everything had to have a metric attached or it didn’t count. he would have been annoying about this specific gap — “$1.2M of algos and you’re not measuring execution quality? what exactly are you doing?” — and he would have been right.
a little late. but here.
two weeks to the first anniversary. not thinking about it right now. back to the logs.
-AK