Tick Data Processing

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):

| Bar Type | Trigger | Property | Use Case | |----------|---------|----------|----------| | Time | Fixed time interval | Equal time spacing | Standard analysis | | Tick | Fixed # of trades | Equal information events | Removes time seasonality | | Volume | Fixed volume traded | Equal activity | Normalizes for liquidity | | Dollar | Fixed dollar value | Equal capital flow | Handles price changes | | Imbalance (tick) | Signed tick imbalance | Detects order flow shifts | ML features | | Imbalance (volume) | Signed volume imbalance | Detects large traders | ML features |

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:
            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

Source & license

This open-source skill is cataloged on AgentStack and links to its original source — we do not rehost the code.

• Author: brainbytes-dev
• Source: brainbytes-dev/everything-claude-trading
• License: MIT

Install and usage instructions live in the source repository linked above.