Tick Data Processing

Tick data processing — cleaning, aggregation, bar construction from raw market data.

When to Activate

• User is working with raw tick-level trade and quote data
• Cleaning tick data (outliers, splits, corporate actions, bad prints)
• Constructing bars from ticks (time, volume, dollar, tick, entropy bars)
• Handling bid-ask bounce and trade classification
• Designing data storage and retrieval for large tick datasets

Core Concepts

What Is Tick Data?

Tick data is the finest granularity of market data — every individual trade and quote update:

Trade ticks: timestamp, price, size, exchange, conditions (regular, odd lot, block, etc.) Quote ticks: timestamp, bid price, bid size, ask price, ask size, exchange

A liquid US equity generates 10,000-100,000 trade ticks and 500,000-5,000,000 quote updates per day. Storage and processing at scale is a significant engineering challenge.

Data Quality Issues

Raw tick data is messy. Common problems:

1. Erroneous prints: trades reported at clearly wrong prices (fat fingers, test trades)
2. Stale quotes: quotes that persist after the market has moved (delayed cancellations)
3. Stock splits: price drops by the split factor but shares outstanding change
4. Dividends: ex-date price drop equal to dividend amount
5. Corporate actions: mergers, spinoffs, ticker changes
6. Odd lots: trades below 100 shares (different handling by data vendors)
7. Consolidated vs direct: SIP consolidation introduces variable delays
8. Time zone issues: exchange timestamps vs UTC vs local time
9. Late prints: trades reported after the fact (sometimes hours later)
10. Crossed/locked markets: bid >= ask (usually transient, data artifact)

Bar Types

Beyond traditional time bars, alternative bar types proposed by Lopez de Prado (2018):

Methodology

Step 1: Data Cleaning

import pandas as pd
import numpy as np

def clean_trade_ticks(trades):
    """
    Clean raw trade tick data. Apply filters in order.
    """
    original_count = len(trades)

    # 1. Remove trades outside regular trading hours (if desired)
    trades = trades.between_time('09:30', '16:00')

    # 2. Remove trades with condition codes indicating non-regular
    # Condition codes vary by exchange; common exclusions:
    irregular_conditions = {'W', 'Z', 'T', 'U', 'L', 'G', 'P'}
    # W = average price, Z = sold out of sequence, T = form-T (after hours)
    if 'condition' in trades.columns:
        trades = trades[~trades['condition'].isin(irregular_conditions)]

    # 3. Remove zero-price and zero-volume trades
    trades = trades[(trades['price'] > 0) & (trades['size'] > 0)]

    # 4. Remove outlier prices (median filter)
    trades = remove_price_outliers(trades, window=50, threshold=5.0)

    # 5. Remove duplicate timestamps (keep first)
    trades = trades[~trades.index.duplicated(keep='first')]

    cleaned_count = len(trades)
    print(f"Removed {original_count - cleaned_count} ticks "
          f"({(original_count - cleaned_count)/original_count:.1%})")

    return trades

def remove_price_outliers(trades, window=50, threshold=5.0):
    """
    Remove ticks where price deviates more than `threshold` rolling MADs
    from the rolling median.
    """
    rolling_median = trades['price'].rolling(window, center=True).median()
    rolling_mad = trades['price'].rolling(window, center=True).apply(
        lambda x: np.median(np.abs(x - np.median(x)))
    )

    # MAD-based z-score
    deviation = np.abs(trades['price'] - rolling_median) / rolling_mad.clip(lower=1e-8)
    mask = deviation < threshold

    return trades[mask]

Step 2: Corporate Action Adjustments

def adjust_for_splits(trades, splits_df):
    """
    Adjust historical prices for stock splits.
    splits_df: DataFrame with columns ['date', 'ratio']
    Example: 2-for-1 split has ratio = 2.0
    """
    trades = trades.copy()
    adjustment_factor = 1.0

    # Apply splits in reverse chronological order
    for _, split in splits_df.sort_values('date', ascending=False).iterrows():
        mask = trades.index.date < split['date'].date()
        trades.loc[mask, 'price'] /= split['ratio']
        trades.loc[mask, 'size'] *= split['ratio']

    return trades

def adjust_for_dividends(trades, dividends_df, method='proportional'):
    """
    Adjust for cash dividends.
    method: 'proportional' (most common) or 'subtractive'
    """
    trades = trades.copy()

    for _, div in dividends_df.sort_values('ex_date', ascending=False).iterrows():
        mask = trades.index.date < div['ex_date'].date()
        if method == 'proportional':
            # Adjust by (1 - div/close_before_ex)
            factor = 1 - div['amount'] / div['close_before_ex']
            trades.loc[mask, 'price'] *= factor
        elif method == 'subtractive':
            trades.loc[mask, 'price'] -= div['amount']

    return trades

Step 3: Bar Construction

def time_bars(trades, interval='5min'):
    """Standard OHLCV time bars."""
    ohlcv = trades.resample(interval).agg({
        'price': ['first', 'max', 'min', 'last'],
        'size': 'sum',
    })
    ohlcv.columns = ['open', 'high', 'low', 'close', 'volume']
    ohlcv = ohlcv.dropna()
    return ohlcv

def volume_bars(trades, bar_volume=10000):
    """
    Each bar contains exactly `bar_volume` shares traded.
    More bars during active periods, fewer during quiet periods.
    """
    trades = trades.copy()
    trades['cum_volume'] = trades['size'].cumsum()
    trades['bar_id'] = (trades['cum_volume'] / bar_volume).astype(int)

    bars = trades.groupby('bar_id').agg({
        'price': ['first', 'max', 'min', 'last'],
        'size': 'sum',
    })
    bars.columns = ['open', 'high', 'low', 'close', 'volume']
    bars.index = trades.groupby('bar_id')['price'].apply(lambda x: x.index[0])

    return bars

def dollar_bars(trades, bar_dollars=1_000_000):
    """
    Each bar contains `bar_dollars` worth of trading activity.
    Robust to price level changes (a $500 stock needs fewer shares than a $50 stock).
    """
    trades = trades.copy()
    trades['dollar_volume'] = trades['price'] * trades['size']
    trades['cum_dollars'] = trades['dollar_volume'].cumsum()
    trades['bar_id'] = (trades['cum_dollars'] / bar_dollars).astype(int)

    bars = trades.groupby('bar_id').agg({
        'price': ['first', 'max', 'min', 'last'],
        'size': 'sum',
        'dollar_volume': 'sum',
    })
    bars.columns = ['open', 'high', 'low', 'close', 'volume', 'dollar_volume']
    bars.index = trades.groupby('bar_id')['price'].apply(lambda x: x.index[0])

    return bars

def tick_imbalance_bars(trades, expected_imbalance=None):
    """
    Lopez de Prado (2018) tick imbalance bars.
    New bar forms when cumulative signed tick imbalance exceeds a threshold.
    Detects shifts in buying/selling pressure.
    """
    trades = trades.copy()
    trades['tick_direction'] = np.sign(trades['price'].diff()).replace(0, np.nan).ffill().fillna(1)

    if expected_imbalance is None:
        # Use exponentially weighted estimate of expected imbalance
        expected_imbalance = abs(trades['tick_direction'].ewm(span=1000).mean()) * 1000

    cum_imbalance = 0
    bar_indices = [0]

    for i in range(1, len(trades)):
        cum_imbalance += trades['tick_direction'].iloc[i]

        # Dynamic threshold based on expected bar size
        threshold = expected_imbalance if isinstance(expected_imbalance, float) \
                    else expected_imbalance.iloc[i]

        if abs(cum_imbalance) >= threshold:
            bar_indices.append(i)
            cum_imbalance = 0

    # Build bars from bar_indices
    bars = []
    for j in range(len(bar_indices) - 1):
        start, end = bar_indices[j], bar_indices[j + 1]
        segment = trades.iloc[start:end + 1]
        bars.append({
            'timestamp': segment.index[0],
            'open': segment['price'].iloc[0],
            'high': segment['price'].max(),
            'low': segment['price'].min(),
            'close': segment['price'].iloc[-1],
            'volume': segment['size'].sum(),
            'n_ticks': len(segment),
        })

    return pd.DataFrame(bars).set_index('timestamp')

Step 4: Trade Classification (Lee-Ready)

def lee_ready_classification(trades, quotes, delay_sec=5):
    """
    Lee-Ready (1991) algorithm for classifying trades as buyer/seller initiated.

    1. Compare trade price to quote midpoint (with 5-second delay for quote staleness)
    2. If above midpoint → buyer-initiated
    3. If below midpoint → seller-initiated
    4. If at midpoint → use tick test (compare to previous different price)
    """
    # Merge trades with quotes (lagged by delay_sec)
    quotes_delayed = quotes.copy()
    quotes_delayed.index = quotes_delayed.index + pd.Timedelta(seconds=delay_sec)

    merged = pd.merge_asof(
        trades.reset_index(), quotes_delayed.reset_index(),
        on='timestamp', direction='backward'
    ).set_index('timestamp')

    merged['midpoint'] = (merged['bid'] + merged['ask']) / 2

    # Quote rule
    merged['direction'] = np.where(
        merged['price'] > merged['midpoint'], 1,
        np.where(merged['price'] < merged['midpoint'], -1, 0)
    )

    # Tick rule for trades at midpoint
    at_mid = merged['direction'] == 0
    tick_dir = np.sign(merged['price'].diff()).replace(0, np.nan).ffill().fillna(1)
    merged.loc[at_mid, 'direction'] = tick_dir[at_mid]

    merged['buy_volume'] = merged['size'] * (merged['direction'] == 1)
    merged['sell_volume'] = merged['size'] * (merged['direction'] == -1)

    return merged

Step 5: Data Storage

def storage_architecture():
    """
    Tick data storage considerations for a trading operation.
    """
    storage_options = {
        'parquet': {
            'description': 'Columnar, compressed, fast reads',
            'compression': '5-10x over CSV',
            'query': 'Excellent with pandas, Spark, DuckDB',
            'best_for': 'Historical analysis, backtesting',
            'partition_by': 'date/symbol for efficient queries',
        },
        'arctic': {
            'description': 'Built for financial time series (on MongoDB)',
            'features': 'Versioning, chunked storage, tick store',
            'query': 'Python-native, good for tick data',
            'best_for': 'Research teams, rapid iteration',
        },
        'kdb+/q': {
            'description': 'Column-oriented database for time series',
            'performance': 'Fastest for time series queries',
            'query': 'q language (steep learning curve)',
            'best_for': 'Production tick databases, banks',
            'cost': '$50-100K+ per core license',
        },
        'timescaledb': {
            'description': 'PostgreSQL extension for time series',
            'features': 'SQL interface, automatic partitioning',
            'best_for': 'Teams with SQL skills, moderate scale',
        },
        'duckdb': {
            'description': 'Embedded analytical database',
            'performance': 'Excellent for Parquet files',
            'best_for': 'Single-machine analysis, replacing pandas',
        },
    }

    # Data volume estimates (single US equity):
    # Trade ticks: ~50K/day × 252 days × 10 bytes = ~125 MB/year
    # Quote ticks: ~2M/day × 252 days × 20 bytes = ~10 GB/year
    # Full US equity universe (~8000 stocks): ~80 TB/year for quotes

    return storage_options

Examples

Processing a Day of AAPL Tick Data

# Load and clean
trades = pd.read_parquet('aapl_trades_20240115.parquet')
trades = clean_trade_ticks(trades)

# Build different bar types
time_5m = time_bars(trades, '5min')       # ~78 bars (6.5 hours)
vol_bars = volume_bars(trades, 50000)      # variable count
dollar_b = dollar_bars(trades, 5_000_000)  # variable count

# Compare bar properties:
# Time bars: uneven information content (busy open vs quiet midday)
# Volume bars: more uniform information, better statistical properties
# Dollar bars: normalize for price level changes, best for multi-year analysis

Bid-Ask Bounce Effect

# Bid-ask bounce: trades alternate between bid and ask, creating
# artificial negative autocorrelation in trade prices at high frequency
# This inflates realized volatility from tick data

# Solution: use midpoint returns, not trade price returns
quotes['midpoint'] = (quotes['bid'] + quotes['ask']) / 2
mid_returns = quotes['midpoint'].resample('1min').last().pct_change()
# Cleaner than trade price returns for volatility estimation

Quality Gate

• Trade condition codes filtered appropriately (exclude irregular prints)
• Price outliers detected and removed using robust methods (MAD, not standard deviation)
• Corporate actions (splits, dividends, mergers) adjusted correctly with point-in-time data
• Time zones handled consistently (UTC preferred for storage, local for display)
• Bar type chosen with rationale (volume/dollar bars for ML features, time bars for reporting)
• Lee-Ready or equivalent trade classification applied for buy/sell volume
• Bid-ask bounce effect accounted for in volatility and return calculations
• Data storage format supports efficient queries by date and symbol
• Late prints and out-of-sequence trades handled (exclude or mark)
• Survivorship-free universe maintained — include delisted tickers through their active period