Cookbook
Executed, end-to-end notebooks: from your first database to point-in-time factor research and production risk workflows. Every recipe runs top to bottom against real or deterministic synthetic market data.
Fundamentals#
01Quickstart: your first h5i-db market databaseh5i-db is an embedded, versioned time-series database built for quant workloads: every write is an atomic commit producing an immutable version, and the SQL layer (Apache DataFusion) ships native time-series operators …02Designing market data schemasIn h5i-db a table is an Arrow schema plus a time column, persisted as immutable, time-sorted Parquet segments under versioned manifests. That makes schema design the one decision you cannot cheaply revisit: the types …03Ingestion patterns: five sources, one tableReal desks never get data from one place: the tick feed hands you Arrow batches, research notebooks live in pandas or polars, vendors drop Parquet, and the odd legacy process still emails CSV. h5i-db's ingestion surface …04A SQL tour for quantsh5i-db's query layer is Apache DataFusion (full SQL with joins, CTEs and window functions) extended with finance-native operators (timebucket, rollingavg, ewma, vwap, ASOF joins) that exploit time-sorted storage. This …05Time travel and versioning: which version did my backtest see?Every write to an h5i-db table (append, write, delete, restore) is an atomic commit that produces a new immutable version. Old versions are never rewritten; reading one is an O(1) manifest lookup, not a log replay. For …06Previewable mutations: fix bad ticks without fearing the delete keyDeleting or rewriting rows in a shared tick store is the scariest routine operation on a quant desk: a fat-fingered DELETE WHERE has ruined more research datasets than any hardware failure. h5i-db's answer is the …07Streaming appends and tail(): a live feed on a versioned storeAn h5i-db table with an append-only history doubles as a message log: every append is one commit, commits are strictly ordered, and a reader that remembers the last version it processed can fetch exactly the rows added …08Maintenance: verify, compact, and vacuum a versioned storeA versioned database makes an unusual bargain: it never overwrites data, so it accumulates manifests, segments, history. That is the feature. But it means three maintenance questions need honest answers:
Market data engineering#
01OHLCV bars from tick dataBar construction is the first transformation every tick dataset goes through, and it is where subtle bugs are born: wrong bucket boundaries, last picked by file order instead of event time, sessions split across UTC …02VWAP, TWAP and execution benchmarksExecution desks live and die by benchmark arithmetic: interval VWAP, TWAP, arrival price, slippage in basis points. All of it is bucketed, weighted aggregation over a trade tape plus one point-in-time lookup against the …03Regular grids for irregular markets: gapfill and resampleIlliquid names don't trade every minute, but almost everything downstream (covariance matrices, joins against liquid benchmarks, risk marks) wants a regular time grid. h5i-db's gapfill() (alias resample()) turns a …04ASOF joins: trades vs quotes, signing and spreadsAttaching the prevailing quote to every trade is the microstructure join: it powers trade signing, effective/realized spread measurement, TCA and toxicity analytics. In h5i-db asofjoin is a native SQL table function …05Corporate actions: split adjustment without losing the tapeA stock split rewrites history: every price before the effective date must be scaled, or every return computed across it is garbage. That forces an engineering choice: do you restate the stored series (and lose the raw …06Point-in-time fundamentals: killing lookahead bias with ASOF joinsFundamentals have two timestamps: the fiscal date the numbers describe (periodend) and the date the market actually learned them (the report date, typically 25-55 days later). Index your database by the wrong one and …07Tick data cleaning: find it, preview the fix, keep the audit trailRaw vendor tick files arrive dirty: fat-finger prints 10x off, zero prices from feed glitches, duplicated blocks from replayed packets, after-hours junk. The dangerous part is the fix, not the detection: a script that …08NBBO consolidation: best bid/offer across fragmented venuesUS equities trade on a dozen-plus venues, each publishing its own top of book. The consolidated best bid and offer (NBBO), the number your execution quality is measured against, has to be derived: at any instant, take …
Alpha research#
01Cross-sectional momentum: an honest monthly backtestThe classic 12-1 momentum factor, end to end on real prices: signal computation as a SQL window query, monthly rebalance dates from timebucket('1mo', ...), and, the part most backtest stacks get wrong, a versioned …02Pairs trading with a version-pinned data spineA cointegration pair strategy on real prices: scan candidate pairs with an Engle-Granger test, build a rolling hedge ratio, compute the spread z-score in SQL with h5i-db's window functions, and backtest with lagged …03EWMA volatility and vol-targeted position sizingRiskMetrics-style EWMA volatility is the workhorse conditional-vol estimate on every risk desk, and h5i-db ships it as a native SQL window function: ewma(x, alpha) OVER (...). This recipe estimates EWMA vol in SQL …04Realized volatility from ticks: signature plots, jumps, overnight riskRealized variance, the sum of squared intraday returns, is the standard nonparametric vol estimate, and computing it well is mostly a sampling problem: sample too fast and bid-ask bounce inflates the estimate, too slow …05Building a point-in-time factor libraryEquity factors die by lookahead: a B/P ratio computed with a book value the market had not seen yet will backtest beautifully and trade terribly. This recipe builds three classic factors, value (B/P), momentum (12-1) …06Event studies: CARs with ASOF-aligned announcement datesThe classic event-study pipeline, market-model abnormal returns and cumulative abnormal returns (CAR) around announcements, has one perennially fiddly step: announcements do not land on trading days. Earnings drop on …07Order flow imbalance: does signed volume predict returns?Order flow imbalance (OFI), the excess of buyer-initiated over seller-initiated volume, is the workhorse microstructure signal: strongly correlated with contemporaneous price moves, and (in real markets, at short …08Intraday seasonality: volume U-shape, volatility smile, spread decayAlmost every execution and alpha model conditions on time of day: volume concentrates at the open and close, volatility peaks in the first hour, spreads narrow through the morning. Measuring these curves correctly is a …09Lead-lag discovery: cross-correlations on irregular ticksWho moves first? Lead-lag analysis between related instruments (index vs futures, ADR vs home listing, correlated FX crosses) is plagued by one mechanical problem: ticks arrive irregularly and asynchronously, and every …10Portfolio rebalancing with versioned holdingsA portfolio book is the canonical versioned dataset: every rebalance is a commit, every commit has a note, and questions like "what did we hold after the March rebalance?" or "how much did we trade in January?" are …
Risk & production#
01VaR and Expected Shortfall with an auditable risk tableA risk number nobody can reproduce is a liability. This recipe computes historical and parametric VaR / Expected Shortfall for a fixed equity book on real daily data, backtests the VaR with Kupiec's POF test, and then …02Reproducible backtests: pin the data, not just the codeEvery quant team has lived this incident: a backtest from March cannot be reproduced in July. The code is in git, the params are in the run log, but the data moved underneath it. The vendor restated history (split …03EOD snapshots and the audit trail regulators actually ask forThe question that arrives eighteen months later is never "what is the price now". It is "what did your systems know at the close of business on 2026-06-02?". If your answer involves restoring backup tapes, you have …04Data-quality gates: staging, policy, and previewable remediationThe worst place to discover a broken vendor file is in the P&L meeting. The pattern that prevents it is old ETL wisdom with a versioned twist: land every delivery in a staging table, run gates, and only promote to …05A crash-safe paper-trading loop with full order attributionThe hard part of a live loop is answering, a week later, "why did we send that order?", not the strategy itself. This recipe builds a sequential, deterministic paper-trading loop where every stage is a versioned commit …06Multi-writer coordination: optimistic locking, conflicts, and retriesAn h5i-db database is a directory, and nothing stops two processes (a feed handler and a corrections job, or two teammates' notebooks) from opening it at the same time. h5i-db's answer to concurrent writers is …07Options: implied-vol surfaces as versioned marksA vol desk's surface is a sequence of marks rather than one object: EOD snapshots, intraday re-marks, corrections. Storing each chain snapshot as an h5i-db commit gives you the whole sequence with O(1) access to any of …08FX and crypto: 24/7 data without an exchange sessionEquity tooling leans on the session: the exchange defines "the day", the open, the close. FX and crypto have none of that: EURUSD trades around the clock with a weekend halt, BTC never stops, and "daily close" is a desk …09Fixed income: versioned curve marks, restatements, carry & rolldownA rates desk's core dataset is small but unforgiving: one par curve per mark date, and every number on it feeds risk, P&L and client marks. The natural h5i-db layout is long format, (ts, tenoryears, yieldpct), with one …10Performance tuning: pruning, projection, commit granularity, cachesh5i-db stores each table as immutable, time-sorted Parquet segments under a versioned manifest. That layout dictates what makes queries fast, and what quietly makes them slow:11leakage-check: which of last night's backtests actually held up?A research agent (or a parameter sweep, or a junior with a for-loop) runs forty backtests overnight. In the morning there are forty Sharpes. Reviewing them properly means re-deriving each one against the data as it …