market-data-integrity

name	market-data-integrity
description	Market data integrity rules — gap filling, look-forward bias prevention, and stock split handling. Use when writing or reviewing code that loads prices, fills missing data, computes returns, backtests strategies, or makes trading decisions. Applies across Python, TypeScript, SQL, and TimescaleDB.

Gap Fill, Look-Forward Bias & Stock Split Handling

These are inviolable rules for handling missing market data, avoiding look-forward bias, and correctly managing stock splits. They apply everywhere: research experiments, production services, SQL queries, Python analysis, TypeScript API code.

---

The Gap Fill Rule

When loading a price (option or underlying) at a specific time and no value exists at that exact timestamp:

Before first available price of the day → LOOK FORWARD

If we want the price at 9:30 and it doesn't exist yet, look at 9:31, 9:32, ..., until we find the first available price.

Why: If we want to trade at 9:30 but the asset hasn't traded yet, we must wait until it first trades. The first available price (e.g., 10:35) is the earliest price we could actually execute at. Using an earlier day's close would be trading at a stale price we can't actually get.

Timeline:  9:30  9:31  ...  10:35  10:36  ...
Data:       ∅     ∅    ...  $4.20  $4.25  ...
                              ↑
                    Use this (first available)

After first available price of the day → LOOK BACKWARD

If we want the price at 15:30 and there isn't a value, look at 15:29, 15:28, ..., until we find it.

Why: After the first trade, we already have a known price. The last observed price is the price we can trade at. A price at 15:00 is the last known price and represents what the market will fill us at.

Timeline:  ...  15:00  15:01  ...  15:29  15:30
Data:      ...  $4.50   ∅     ...   ∅      ∅
                  ↑
         Use this (last known)

Summary

Situation	Direction	Method	Rationale
Before first trade of day	Forward (→)	Look at 9:31, 9:32, ...	Must wait for first tradeable price
After first trade of day	Backward (←)	Look at T-1, T-2, ...	Use last known tradeable price

Reference Implementation (Python)

The canonical implementation is apply_gap_fill() in flow_regime_productions_filter/01_generate_flow/create_flow.py. Identical copies exist in flow_regime_productions/01_generate_flow/ and flow_regime_pair/01_generate_flow/.

def apply_gap_fill(trades_df, bars_df):
    """
    Smart gap-fill using pandas merge_asof (O(n log n)).
    1. For trades at/after first bar of day: use most recent bar (look back)
    2. For trades before first bar of day: use first bar (look ahead)
    """
    # Step 1: Get first bar of each day
    first_bars = bars_df.groupby('bar_date').first().reset_index()

    # Step 2: merge_asof backward — find most recent bar <= trade time
    trades_df = pd.merge_asof(
        trades_df, bars_df[['time_naive', 'close']],
        left_on='sip_ts_naive', right_on='time_naive',
        direction='backward'
    )

    # Step 3: For trades BEFORE first bar of day, use first bar instead
    same_day_mask = trades_df['time_naive'].notna() & (
        trades_df['time_naive'] >= trades_df['first_time']
    )
    trades_df['underlying_close'] = trades_df['lookback_close'].where(
        same_day_mask,
        trades_df['first_close']  # look-ahead for pre-market
    )

Result: ZERO NULL underlying prices. Every trade gets a price.

Implementation Patterns

Python (pandas merge_asof)

# After first trade: look backward
pd.merge_asof(trades, bars, on='time', direction='backward')

# Before first trade: look forward
pd.merge_asof(trades, bars, on='time', direction='forward')

Python (pandas reindex for daily gaps)

full_range = pd.bdate_range(start, end)
df = df.set_index('date').reindex(full_range).ffill()  # LOCF

SQL (TimescaleDB)

SELECT
    time_bucket_gapfill('1 day'::interval, time, start, end) AS date,
    locf(last(close, time)) AS price
FROM minute_bars
WHERE symbol = $1 AND time >= $2 AND time <= $3
GROUP BY date

TimescaleDB rules:

• time_bucket_gapfill MUST be top-level (not nested in another function)
• Aggregates go INSIDE locf(): locf(last(close, time)) not last(locf(close), time)
• locf() = Last Observation Carried Forward (backward-looking)
• interpolate() = Linear interpolation between known points

TypeScript

When loading prices in the API or processor:

1. Find the first available bar for the day
2. For times before it → use that first bar's price
3. For times after it → use most recent bar's price

---

Stock Split Handling

Our minute_bars and daily_bars tables store RAW UNADJUSTED prices from Polygon.io SIP data. Any code that computes returns across dates MUST account for stock splits, or the results will be catastrophically wrong.

Why Splits Are Catastrophic

Example: NVDA 10:1 Split (2024-06-10)
Entry: 2024-06-07 at $1,191.88
Exit:  2024-06-10 at $122.49 (after 10:1 split)

WITHOUT ADJUSTMENT:
  Return = (122.49 / 1191.88) - 1 = -89.72%  ← FALSE CATASTROPHIC LOSS

WITH ADJUSTMENT:
  Return = (122.49 × 10.0 / 1191.88) - 1 = +2.77%  ← CORRECT

Reverse splits are equally dangerous — a 1:5 reverse split makes the price jump 5x overnight, appearing as a +400% gain.

Known High-Impact Splits in Our Data (2022-2025)

Symbol	Date	Ratio	Type	False Signal
AMZN	2022-06-06	20:1	Forward	-95% false loss
GOOGL/GOOG	2022-07-18	20:1	Forward	-95% false loss
TSLA	2022-08-25	3:1	Forward	-67% false loss
NVDA	2024-06-10	10:1	Forward	-90% false loss
TQQQ	2022-01-13, 2025-11-20	2:1	Forward	-50% false loss
SSO	2022-01-13, 2025-11-20	2:1	Forward	-50% false loss
SQQQ	2022-01-13, 2024-11-07, 2025-11-20	1:5 reverse	Reverse	+400% false gain
SDS	2022-01-13, 2025-11-20	1:5 reverse	Reverse	+400% false gain
SPXS	2025-09-29	1:10 reverse	Reverse	+884% false gain
QID	2024-04-10	1:5 reverse	Reverse	+400% false gain
QLD	2025-11-20	2:1	Forward	-50% false loss

Total: 5,217 splits across 3,999 symbols (2022-2025). Leveraged/inverse ETFs split frequently — multiple times per ETF in our date range.

The stock_splits Table

Splits are synced from Polygon.io API and stored in PostgreSQL:

-- Schema
CREATE TABLE stock_splits (
    symbol TEXT NOT NULL,
    split_date DATE NOT NULL,
    split_ratio FLOAT NOT NULL,  -- split_to / split_from
    source TEXT DEFAULT 'polygon',
    PRIMARY KEY (symbol, split_date)
);

-- split_ratio > 1 = forward split (price drops, e.g. 10:1 → ratio=10.0)
-- split_ratio < 1 = reverse split (price jumps, e.g. 1:5 → ratio=0.2)

-- Query splits for symbols in a date range
SELECT symbol, split_date, split_ratio
FROM stock_splits
WHERE symbol = ANY($1)
  AND split_date >= $2 AND split_date <= $3
ORDER BY symbol, split_date;

Synced via sync_splits_from_polygon.py scripts in research experiments.

Two Approaches to Handling Splits

We use both approaches in different parts of the codebase. Choose based on context.

Approach A: Adjust Returns (Preferred for Backtests)

Multiply the exit price by the cumulative split factor, then compute the return. This preserves exact price levels while fixing the return calculation.

Reference: credit-spread/v5/lib/splits.py

def get_cumulative_split_factor(symbol, from_date, to_date):
    """
    Factor to multiply exit price by for correct return.
    Only counts splits AFTER entry date (entry is at post-split price
    if split occurs on entry day).
    """
    splits = get_splits_in_range(symbol, from_date, to_date)
    splits = splits[splits['split_date'] > from_date]  # Exclude entry day
    if splits.empty:
        return 1.0
    return float(splits['split_ratio'].prod())

def calculate_safe_return(entry_price, exit_price, symbol, entry_date, exit_date):
    factor = get_cumulative_split_factor(symbol, entry_date, exit_date)
    return (exit_price * factor / entry_price) - 1

Edge case: A split ON entry_date does NOT need adjustment — you entered at the post-split price.

Vectorized (batch) implementation from flow_regime/03k_static_optimization/utils.py:

# Merge prices with splits on symbol
merged = df.merge(
    relevant_splits[["symbol", "split_date", "split_ratio"]],
    on="symbol", how="left",
)
# Filter: entry_date < split_date <= exit_date
merged = merged[
    (merged["split_date"] > merged["_from_date"]) &
    (merged["split_date"] <= merged["_to_date"])
]
# Cumulative factor per trade (handles multiple splits)
if len(merged) > 0:
    split_factors = merged.groupby("_row_id")["split_ratio"].prod()
    df["split_factor"] = 1.0
    df.loc[split_factors.index, "split_factor"] = split_factors

# Adjusted return
df["next_day_return"] = ((df["next_price"] * df["split_factor"]) / df["price"] - 1) * 100

Approach B: Detect and Exclude (Simpler, Used in Productions)

Flag any overnight price change >50% as a potential split, exclude those dates from trading.

Reference: flow_regime_productions_filter/03_baseline/utils.py

MAX_DAILY_PRICE_CHANGE_PCT = 50.0
SPLIT_BUFFER_DAYS = 1

def detect_suspicious_price_gaps(prices_df, max_change_pct=50.0):
    """Flag day-to-day price changes > threshold as potential splits."""
    for symbol in prices_df["symbol"].unique():
        sym_df = prices_df[prices_df["symbol"] == symbol].sort_values("trade_date")
        sym_df["prev_price"] = sym_df["price"].shift(1)
        sym_df["change_pct"] = (sym_df["price"] - sym_df["prev_price"]) / sym_df["prev_price"] * 100
        gaps = sym_df[abs(sym_df["change_pct"]) > max_change_pct]

def get_split_dates_set(conn, symbols, start, end, buffer_days=1):
    """Build set of (symbol, date) to skip — split day +/- buffer."""
    splits = get_splits_for_symbols(conn, symbols, start, end)
    skip = set()
    for _, row in splits.iterrows():
        for offset in range(-buffer_days, buffer_days + 1):
            skip.add((row["symbol"], split_date + pd.Timedelta(days=offset)))
    return skip

# In load_prices(): flag remaining >50% returns as NaN
df.loc[abs(df["next_day_return"]) > MAX_DAILY_PRICE_CHANGE_PCT, "next_day_return"] = np.nan

Production service (services/flow-regime/app/services/price_fetcher.py):

def filter_stock_splits(signals, prev_prices, threshold=50.0):
    """Remove signals whose underlying price changed >50% overnight."""
    for signal in signals:
        pct_change = abs(today_price / prev_price - 1) * 100
        if pct_change > threshold:
            continue  # Skip this signal

Which Approach to Use

Context	Approach	Why
Backtest computing P&L per trade	A (Adjust)	Need correct return for each trade
Signal generation / breadth counting	B (Exclude)	Just need to skip bad dates
Single-symbol ETF/index trading	A if any symbol splits	Even SPY-like ETFs can split
Multi-symbol with 100s of symbols	B first, then A for remaining	Two-pass: filter obvious, adjust subtle
Leveraged/inverse ETFs	A (Adjust) MANDATORY	These split multiple times per year
Options data	Options prices don't split	But check underlying prices
Flow data (aggregate dollar flow)	No adjustment needed	Dollar flow is continuous through splits

SQL Patterns for Split-Aware Queries

-- Load prices with split factor in one query
-- NOTE: Uses AT TIME ZONE for DST-aware 15:30 ET filtering
WITH prices AS (
    SELECT symbol, time::date as trade_date, close as price,
           LEAD(close) OVER (PARTITION BY symbol ORDER BY time::date) as next_price,
           LEAD(time::date) OVER (PARTITION BY symbol ORDER BY time::date) as next_date
    FROM minute_bars
    WHERE symbol = ANY($1)
      AND ((time AT TIME ZONE 'UTC') AT TIME ZONE 'America/New_York')::time = '15:30:00'::time
),
split_factors AS (
    SELECT p.symbol, p.trade_date,
           COALESCE(EXP(SUM(LN(s.split_ratio))), 1.0) as factor
    FROM prices p
    LEFT JOIN stock_splits s
        ON s.symbol = p.symbol
        AND s.split_date > p.trade_date
        AND s.split_date <= p.next_date
    GROUP BY p.symbol, p.trade_date
)
SELECT p.*, sf.factor,
       ((p.next_price * sf.factor) / p.price - 1) * 100 as adjusted_return
FROM prices p
JOIN split_factors sf USING (symbol, trade_date);

TypeScript Pattern

async function getSplitFactor(
  symbol: string, entryDate: string, exitDate: string
): Promise<number> {
  const splits = await db.query(`
    SELECT split_ratio FROM stock_splits
    WHERE symbol = $1 AND split_date > $2::date AND split_date <= $3::date
  `, [symbol, entryDate, exitDate]);

  return splits.rows.reduce((acc, r) => acc * r.split_ratio, 1.0);
}

// Usage
const factor = await getSplitFactor('NVDA', '2024-06-07', '2024-06-10');
const adjustedReturn = (exitPrice * factor / entryPrice) - 1;

TimescaleDB + Splits Warning

When using time_bucket_gapfill with LOCF across split dates, the carried-forward price will be the pre-split price — this is wrong if you then compare it to a post-split price. Either:

1. Exclude split dates from the gapfill range, or
2. Apply split adjustment after the gapfill

Common Split Pitfalls

Pitfall	What Happens	Fix
Ignoring splits entirely	-90% false loss or +400% false gain	Always check stock_splits table
Only checking >50% threshold	Misses 2:1 splits (exactly 50%)	Use >= or check table directly
Applying split factor on entry day	Double-counting — entry is already post-split	Factor only for splits AFTER entry date
LOCF across split boundaries	Carried-forward price is from wrong era	Exclude split dates from gapfill
Assuming ETFs don't split	Leveraged/inverse ETFs split 2-3x per year	ALWAYS check TQQQ, SQQQ, SPXL, SPXS, SSO, SDS, QLD, QID
Assuming only forward splits exist	Reverse splits (1:5) cause +400% false gains	Handle both directions (ratio > 1 AND ratio < 1)
Using adjusted data without knowing it	Mixing adjusted and unadjusted sources	Our minute_bars are UNADJUSTED — be explicit
Not handling multiple splits in range	Some symbols split twice (SQQQ: 3 times in 2022-2025)	Use cumulative product of ALL split ratios
Using shift(-1) for next-day return without split check	The shift crosses a split boundary silently	Always join against stock_splits for the (entry, exit) window

---

Timezone & DST Handling

Our minute_bars table stores timestamps as timestamp without time zone in UTC. All time-of-day queries MUST account for EST/EDT (Eastern Daylight Time transitions).

The Problem

US equity markets trade 09:30-16:00 Eastern Time (ET). Eastern Time switches between EST (UTC-5) and EDT (UTC-4):

Period	Offset	15:30 ET in UTC	09:30 ET in UTC
EST (Nov-Mar)	UTC-5	20:30 UTC	14:30 UTC
EDT (Mar-Nov)	UTC-4	19:30 UTC	13:30 UTC

A hardcoded UTC time like '15:30' or '20:30' is wrong for ~7 months of the year:

• '15:30' UTC = 10:30 AM ET (EST) or 11:30 AM ET (EDT) -- morning, not close!
• '20:30' UTC = 15:30 ET (EST) but 16:30 ET (EDT) -- after market close!

The Rule

NEVER hardcode a UTC time for market-hour queries. Always convert from Eastern Time.

Python Implementation

Use _et_to_utc_time() from services/flow-regime/app/services/price_fetcher.py:

from app.services.price_fetcher import _et_to_utc_time

# Automatically handles EST/EDT
utc_time = _et_to_utc_time("2025-06-03", "15:30")  # -> "19:30" (EDT)
utc_time = _et_to_utc_time("2025-11-03", "15:30")  # -> "20:30" (EST)

# fetch_prices_from_db() accepts ET time and converts internally
prices = fetch_prices_from_db(symbols, trade_date, target_time="15:30")  # ET!

SQL Multi-Date Queries

For queries spanning multiple dates (which may cross DST boundaries), use AT TIME ZONE:

-- Convert stored-as-UTC timestamp to ET for filtering
WHERE ((time AT TIME ZONE 'UTC') AT TIME ZONE 'America/New_York')::time
      >= '09:30:00'::time
  AND ((time AT TIME ZONE 'UTC') AT TIME ZONE 'America/New_York')::time
      <= '15:30:00'::time

For single-date queries, pre-compute the UTC bounds in application code (faster than SQL timezone conversion).

TypeScript / Bridge Callers

When calling the Python bridge for prices, pass ET time -- Python handles conversion:

// CORRECT: pass ET time -- Python converts to UTC per trade date
const result = await bridge.getStockPrices({
    symbols, trade_date: tradeDate, target_time: '15:30'  // ET!
});

// WRONG: hardcoded UTC time -- breaks during EDT
const result = await bridge.getStockPrices({
    symbols, trade_date: tradeDate, target_time: '20:30'  // after-hours in EDT!
});

Key Files

File	Role
services/flow-regime/app/services/price_fetcher.py	_et_to_utc_time() -- canonical ET-to-UTC converter
services/flow-regime/app/services/price_fetcher.py	fetch_prices_from_db() -- accepts ET, converts internally
services/flow-regime/app/services/price_fetcher.py	fetch_ema_price_history() -- uses SQL AT TIME ZONE

DST Transition Dates (US)

DST transitions happen at 2:00 AM local time on:

• Spring forward: Second Sunday of March (clocks skip 2 AM to 3 AM)
• Fall back: First Sunday of November (clocks repeat 1 AM to 2 AM)

Year	Spring Forward	Fall Back
2024	March 10	November 3
2025	March 9	November 2
2026	March 8	November 1

Checklist

• Is the time-of-day hardcoded in UTC? If yes, it's WRONG for ~7 months. Convert from ET.
• Does the query span multiple dates? Use SQL AT TIME ZONE for correct per-date conversion.
• Is target_time passed through the bridge? It should be in ET, not UTC.
• Does the query filter by time::time <= 'XX:XX'? This is a fixed UTC filter -- wrong across DST. Use _et_to_utc_time() or AT TIME ZONE.

---

Look-Forward Bias

Definition: Using information from the future to make a decision in the present. The most common and most dangerous error in backtesting.

Real Examples from This Codebase

Bug found and fixed (options/laplace/040-bb-hyperparameter/run_v3.py):

Previous versions had look-forward bias:
- Signal generated at close[T] using close[T] data
- Assumed entry at close[T] — IMPOSSIBLE for 0DTE!
- You can't see the close price and then trade at that close

Fix: Signal at close[T] → Enter at open[T+1] → Exit at close[T+1]

Legitimate pattern (flow regime breadth signal cooldowns):

• Cooldowns use only realized past P&L (did the last trade win or lose?)
• The cooldown state is known at decision time
• They encode backward-looking momentum — NOT look-forward bias

Legitimate pattern (flow regime signal timing):

• Day T signal uses T-1 flow data (available at decision time)
• Trade SPY at T close → exit T+1 close
• The signal is fully determined before the trade

Common Violations

Violation	Why it's wrong	Fix
Using today's close to decide today's trade	You don't know the close until EOD	Use yesterday's close or today's open
Entry at close[T] for signal generated at close[T]	Can't see the close and also trade at it	Enter at open[T+1]
Fitting parameters on test data	You've "seen" the future	Train/validate/test split with temporal ordering
Sorting by future returns to select trades	Future returns aren't known at decision time	Use only backward-looking metrics
Using full-dataset mean/std for z-scores	Includes future observations	Use expanding or rolling window from past only
Gap-filling backward before first trade	Implies you knew a price before it existed	Look forward to first available price
Survivorship bias in symbol selection	Selecting only symbols that "survived"	Use point-in-time membership lists

The Walk-Forward Pattern

For any parameter selection or model fitting:

|-------- Train --------|--- Validate ---|---- Test ----|
     Fit parameters        Confirm          Final eval
     here only             generalization   NEVER touch
                                            until ready

• Parameters chosen on train data
• Validated on validation data (can iterate)
• Test data touched ONCE for final evaluation
• In production: use expanding or rolling window that only looks back

---

Master Checklist

When writing or reviewing code that touches market data:

Gap Fill

• Before first trade → forward. After first trade → backward.
• Using merge_asof with correct direction parameter?
• TimescaleDB locf() used properly (not across split boundaries)?

Look-Forward Bias

• Prices: Am I using a price that was actually available at decision time?
• Entry timing: Signal at T → can only enter at T+1 open (or T close if signal uses T-1 data)
• Parameters: Were they fitted only on past data relative to each decision point?
• Sorting: Am I sorting by anything that uses future information?
• Aggregations: Are my rolling/expanding windows strictly backward-looking?
• Selection: Am I selecting symbols/strategies using only information available at the time?

Stock Splits

• Does this code compute returns across dates? If yes, splits MUST be handled.
• What symbols are involved? ETFs, leveraged ETFs, and individual stocks ALL split.
• Is the stock_splits table consulted? Either adjust returns or exclude split dates.
• Entry-day edge case: Split on entry day → no adjustment (entered post-split).
• Multiple splits: Using cumulative product, not just single split factor?
• Reverse splits: Handled? (ratio < 1, price jumps UP)
• Leveraged/inverse ETFs: TQQQ, SQQQ, SPXL, SPXS, SSO, SDS, QLD, QID split frequently.
• LOCF gapfill: Not carrying forward pre-split prices into post-split territory?
• shift(-1) for returns: Does the shift cross a split boundary? Join stock_splits to check.

---

All Implementations

File	What it does
Gap Fill
flow_regime_productions_filter/01_generate_flow/create_flow.py	apply_gap_fill() — canonical implementation
flow_regime_productions/01_generate_flow/create_flow.py	Identical copy
flow_regime_pair/01_generate_flow/create_flow.py	Same logic, slight variation
services/flow-regime/app/services/flow_computation.py	Production apply_gap_fill()
research/experiments/credit-spread/v2/lib/gapfill.py	SQL + pandas gap fill library
research/experiments/credit-spread/v5/lib/prices.py	gapfill_intraday() — smart pre/post market
options/laplace/040-bb-hyperparameter/run_v3.py	T+1 entry fix for look-forward bias
options/extrinsic/039-etf-dte-visualization/audit_lookahead.py	Look-ahead bias audit
credit-spread/v5/00_data_integrity/gaps/validate_gaps.py	Data gap validation & reporting
Split Adjustment (Approach A)
credit-spread/v5/lib/splits.py	Core split adjustment library
credit-spread/v5/00_data_integrity/proofs/proof_splits.py	6 mathematical proofs of correctness
flow_regime/03k_static_optimization/utils.py	Vectorized split factor computation
flow_regime/03_optimize/utils.py	Split-adjusted load_prices()
Split Detection/Exclusion (Approach B)
flow_regime_productions_filter/03_baseline/utils.py	detect_suspicious_price_gaps(), get_split_dates_set()
flow_regime_productions_filter/03_baseline/validate_data.py	Pre-optimization data quality checks
services/flow-regime/app/services/price_fetcher.py	Production filter_stock_splits()
services/flow-regime/tests/test_price_fetcher.py	Unit tests for split filter
Split Sync
flow_regime_productions_filter/03_baseline/sync_splits_from_polygon.py	Batch sync from Polygon API
flow_regime_pair/03a_optimize_ema/sync_splits_from_polygon.py	Same (copies in multiple experiments)