Module 4 — Market Data Engineering

Part II · Research Infrastructure · Priority 🎯 Core · Status: Draft v0.1 Prerequisites: Module 1, Module 3

Overview

Every statistic you will ever compute about a strategy sits on top of a data pipeline. If the pipeline is wrong — silently missing bars, shifted timestamps, survivorship-filtered symbols — every result above it is wrong in ways no amount of modeling skill can repair. Data engineering is unglamorous, cheap to do right, and catastrophically expensive to do wrong.

This module builds your foundation layer: clean, point-in-time, honestly-versioned market data across the granularity ladder from ticks to daily bars, with special attention to the low-timeframe demands of Module 8.

The deliverable mindset: data is not something you have, it’s something you manufacture — with quality control at every step.

The Granularity Ladder

Figure: the data ladder. Each rung multiplies storage and changes which questions are even answerable; choose the lowest rung that answers your actual question.

flowchart TD
    ticks[Ticks<br/>every quote change]
    m1[M1 bars<br/>288 per FX day]
    h1[H1 bars]
    d1[D1 bars]

    ticks -->|"aggregate"| m1
    m1 -->|"aggregate"| h1
    h1 -->|"aggregate"| d1

    ticks --> q1{Question needs<br/>intrabar paths?}
    keep[Stay at ticks<br/>~10M rows/symbol-year]
    up[Climb the ladder<br/>100x smaller per rung]

    q1 -->|"yes: scalping,<br/>stop fills"| keep
    q1 -->|"no"| up

    classDef data fill:#e8f0fe,stroke:#4a86e8
    classDef process fill:#f3f3f3,stroke:#888
    classDef decision fill:#fff4d6,stroke:#d6a300

    class ticks,m1,h1,d1 data
    class keep,up process
    class q1 decision

How to read this:

• Aggregation is lossy and one-way: you can always climb up, never down. Archive raw ticks once; derive everything else.
• A year of EURUSD ticks is tens of millions of rows — trivial for columnar storage, painful for spreadsheets, impossible for naive CSV loops.
• Your strategy horizon sets the floor: D1 systems need D1 bars; M5 Meta models need M5 bars and occasional tick forensics for fill questions (Module 8).

The MT5 Data Reality

Your primary source is the terminal itself. The Python API exposes two functions whose exact behavior you must internalize:

copy_rates_range returns bars with these fields:

copy_ticks_range returns ticks with: time, time_msc (millisecond precision), bid, ask, last, volume, and flags (distinguishing quote changes from actual trades).

Three API behaviors bite everyone eventually:

from datetime import datetime, timedelta, timezone
import polars as pl
import MetaTrader5 as mt5

def fetch_rates(symbol: str, days: int, timeframe=mt5.TIMEFRAME_M5):
    """UTC-aware fetcher that fails loudly instead of returning None."""
    mt5.initialize()
    utc_to   = datetime.now(timezone.utc)
    utc_from = utc_to - timedelta(days=days)

    # Pitfall 1: naive datetimes -> API may return None silently.
    # Pitfall 2: history depth limits also surface as None, not errors.
    raw = mt5.copy_rates_range(symbol, timeframe, utc_from, utc_to)
    assert raw is not None and len(raw) > 0, (
        f"{symbol}: empty window — check symbol enabled, history depth, "
        f"and timezone-aware datetimes"
    )
    frame = pl.from_numpy(raw)
    # `time` is epoch seconds; keep it UTC end-to-end.
    return frame.with_columns(
        (pl.col("time") * 1_000_000).cast(pl.Datetime("us")).alias("time")
    ).sort("time")

mt5.shutdown()

1. Naive datetimes return None, not errors. Always construct datetime(..., tzinfo=timezone.utc) and assert on emptiness with a message that names the likely cause.
2. Returned timestamps are epoch ints — convert once, immediately, to a typed UTC datetime column, and let nothing downstream treat them as strings.
3. The values encode broker-clock offsets (the GMT+2/+3 rule from Module 1). Two brokers’ “2026-08-19 00:00” bars cover different wall-clock minutes. Record the rule next to the data, forever.

⚠️ Pitfall: Requesting months of ticks in one call exhausts memory or trips broker-side retrieval limits — again surfacing as None. Chunk requests into day-sized windows and concatenate.

Beyond the Broker: Cross-Source Validation

A single-source pipeline cannot detect its own feed’s defects. The professional habit is maintaining a second independent source for the same instrument and reconciling periodically:

The reconciliation ritual (automate it): align two sources on UTC, compare close prices within tolerance, count mismatched bars, and inspect worst offenders. Systematic divergence usually means a timestamp-offset bug, not a market anomaly. Divergence in spreads is expected and informative — it’s the broker’s markup fingerprint (Module 1).

Survivorship Bias: The Invisible Filter

A dataset containing only symbols that still exist has already answered your research question with survivorship. Classic equities case: backtesting today’s S&P members on 2005 data silently deletes every company that was deleted along the way — inflating returns by a wide margin.

On MT5 the equivalent is subtler but real: brokers delist illiquid exotics and renamed crypto pairs; symbol lists drift over the years.

Defenses, in order of practicality:

1. Freeze your universe snapshots — persist the symbol list (with specs!) alongside every experiment’s data window.
2. Point-in-time membership for anything index-like: reconstruct “what existed then” from archives before backtesting selection strategies.
3. Deliberate inclusion of failed instruments when testing robustness claims (“does the edge survive adding illiquid crosses?” is a different question than “did my five survivors do well?”).

For your current scope (XAUUSD, BTCUSD — neither delisted anytime soon), the exposure is low; the habit still matters the day you widen the universe (Module 16).

Spread Is Data, Not Noise

At low timeframes the spread is simultaneously your largest cost and a legitimate feature (liquidity conditions). This changes storage requirements: bid/ask must survive into your archive, not just mid-prices.

# Derive an hourly spread curve once; reuse everywhere (Modules 7, 8, 22).
def spread_curve(ticks: pl.DataFrame) -> pl.DataFrame:
    hourly = ticks.with_columns(
        spread_pts=(pl.col("ask") - pl.col("bid"))
    ).with_columns(
        hour=pl.col("time").dt.hour()
    )
    return hourly.group_by("hour").agg(
        median_spread_pts=pl.col("spread_pts").median(),
        p90_spread_pts=pl.col("spread_pts").quantile(0.9),
        n_obs=pl.len(),
    ).sort("hour")

Meaning: after this function runs once per symbol, “what does this trade really cost at 21:30 UTC?” becomes a lookup instead of a guess. The p90 column matters more than the median for stop-placement sanity — tail spreads, not typical spreads, are what eat stops during fast markets.

Storage & Layout

The modern local stack is boring and excellent: Arrow in memory, Parquet on disk, DuckDB for ad-hoc SQL — with Polars reading and writing Parquet natively.

Layout principles that pay off:

1. Partition by symbol and year (data/xauusd/ticks/year=2025.parquet) — queries touching one period read one file group.
2. One immutable canonical schema, documented once: time (Datetime us, UTC), open/high/low/close (f64), tick_volume (u64), spread_pts (i64). Schema drift is the silent killer; version the schema name when it changes (your production repo’s polars_v15 naming is exactly this pattern).
3. Fingerprint every artifact so experiments can pin their inputs:

import hashlib
from pathlib import Path

def file_fingerprint(path: str) -> str:
    """First 12 hex chars of sha256 — stable dataset identity."""
    digest = hashlib.sha256()
    with Path(path).open("rb") as fh:
        for chunk in iter(lambda: fh.read(1 << 20), b""):
            digest.update(chunk)
    return digest.hexdigest()[:12]

print(file_fingerprint("data/xauusd/m5/year=2025.parquet"))  # e.g. '9f31ac2b7d04'

Ad-hoc analytics without loading anything into memory:

import duckdb

con = duckdb.connect()
row = con.execute("""
    SELECT avg(close) AS avg_close, count(*) AS n
    FROM read_parquet('data/xauusd/m5/*.parquet')
    WHERE hour(time) BETWEEN 12 AND 16      -- NY overlap only
""").fetchone()

Testing It Honestly

Data quality gates to run on every new ingestion batch — automate all six:

Gap detection deserves its own guard, because sessions make “missing bars” legal most of the time:

def suspicious_gaps(times_ms: list[int], max_legal_gap_min: float = 120):
    """Flag gaps beyond weekends + declared maintenance windows."""
    diffs_min = [(b - a) / 60_000 for a, b in zip(times_ms, times_ms[1:])]
    return [
        (a, g) for a, g in zip(times_ms, diffs_min)
        if g > max_legal_gap_min and not is_weekend_gap(a, g)
    ]

Hands-On Project

Deliverable: src/data/foundation.py + a dual-source quality report for your top two symbols.

Tasks:

1. Implement the canonical fetchers: UTC-safe fetch_rates and chunked fetch_ticks (day-sized windows, deduped, monotonic-checked), both returning typed Polars frames under the canonical schema.
2. Pull ≥ 12 months of M5 bars and 3 separate weeks of ticks for XAUUSD and BTCUSD from your broker; archive as partitioned Parquet.
3. Download the same windows from a second source (Dukascopy archive or HistData M1) and produce a reconciliation table: matched bars %, mean absolute close delta, max delta, and the five worst-matching timestamps each direction.
4. Compute the hourly spread curve (median + p90) from your tick weeks; save alongside the spec sheet from Module 1.
5. Run all six quality gates; write their results into the report regardless of pass/fail.

Acceptance criteria:

• Fetchers raise loud, named errors on None/empty results (never silent).
• Reconciliation table explains its worst deltas or flags them as open issues.
• Every stored artifact has a recorded fingerprint and schema version.
• Spread curves exist for both symbols and show the expected overlap trough.
• Report states the broker timezone rule in writing, verified against observed rollover timing.

Key Takeaways

• Choose the lowest granularity rung that answers your question, archive raw ticks once, and derive everything above — aggregation is one-way.
• The MT5 API fails silently: naive datetimes and exceeded history depths both surface as None. Loud asserts with named causes are mandatory hygiene.
• Returned epoch timestamps encode your broker’s clock rule; convert to typed UTC once at the boundary and record the offset rule beside the data.
• One source cannot audit itself — maintain a second feed for reconciliation; expect price agreement and informative spread disagreement.
• Store spread as data (hourly median + p90 curves); at low timeframes it is both your dominant cost and a model feature.
• Partitioned Parquet + DuckDB + content fingerprints make datasets addressable, queryable, and reproducible — the storage trifecta behind every later module.
• Six automated quality gates (completeness, uniqueness, monotonicity, range, gaps, cross-source) turn data trust from a feeling into a report.

References

• MetaQuotes — MetaTrader5 Python: copy_rates_range and copy_ticks_range (field definitions, boundary semantics)
• Apache Arrow / DuckDB communities — tickstack (Arrow-in-memory / Parquet-on-disk / DuckDB-query tick stack reference)
• Dukascopy public tick archive; HistData.com — free cross-validation sources for majors and metals
• Robert Carver — Systematic Trading, appendix on data handling (canonical-schema thinking)
• Hudson & Thames — MlFinLab data-structure notes (point-in-time discipline for features)
• Next in sequence: Module 5 — Backtesting Methodology