DEV Community

EmilyL
EmilyL

Posted on

Dev Perspective: Overcoming Dirty K-Line Data in Stock Backtesting Pipelines

#data #dataengineering #python #tutorial

Hey devs who venture into quant finance 👋. If you’re used to building APIs and microservices, stock market data might seem like “just another JSON”. I thought so too, until I built my first backtesting engine for US equities. Turns out, historical K-line (OHLCV) data is a swamp of edge cases. Let me walk you through how I tamed it.

The Wake-Up Call: A “Perfect” Backtest That Couldn’t Trade

I coded a simple breakout strategy in Python. Backtest result: +35% annually, drawdown 4%. I deployed a paper-trading version using the same data pipeline. Live results: -12% in three weeks. After days of debugging the algorithm, I found the issue: the minute bars I downloaded had timestamps in UTC but I had assumed they were US Eastern. The “breakouts” my strategy captured happened outside market hours — pure noise that couldn’t be traded. My entire evaluation was based on unexecutable signals.

Define Your Data Needs by Strategy Type

In software terms, choose the right data granularity for your use case:

  • Daily bars (1d): like a daily batch job. Sufficient for trend-following and end-of-day signals.
  • Minute bars (1m, 5m, etc.): analogous to real-time event streams. Critical for intraday logic.
  • Tick data: the raw event firehose. Requires Kafka-like throughput and careful state management.

Most quant developers will spend 80% of their time in minute bars. That’s also where data quality bites hardest.

Data Pain Points: A Quick Diagnostic Table

I keep this table in my project README:

Data Type Frequent Bug My Fix
Daily Unadjusted for splits/dividends Explicitly request adjusted data; prefer forward-adjusted for short-term tests
Minute Timezone misalignment Normalize all timestamps to EST; filter with official market calendar
Tick Enormous storage size Batch ingest, convert to Parquet, partition by symbol/date

Dividend and split adjustments are a common trap. A 2-for-1 split showing a price drop of 50% will trigger any momentum filter incorrectly, generating false short signals.

Building a Robust Data Pipeline

I now use a dedicated market data API to fetch clean data. A provider like AllTick returns structured OHLCV arrays. Example: pulling SPY daily klines.

import requests
import pandas as pd

# Fetch SPY daily kline from AllTick API
url = "https://api.alltick.co/stock/history/kline"
params = {
    "symbol": "SPY",
    "interval": "1d",
    "start_date": "2024-01-01",
    "end_date": "2024-12-01"
}

resp = requests.get(url, params=params)
data = resp.json()

df = pd.DataFrame(data['kline'])
df['time'] = pd.to_datetime(df['time'])
df.set_index('time', inplace=True)
print(df.head())
Enter fullscreen mode Exit fullscreen mode

To avoid repeated API calls and network latency, I persist data locally using DuckDB — an embeddable OLAP database perfect for this job:

# Local persistent storage via DuckDB
import duckdb

conn = duckdb.connect('market_data.db')
conn.execute("""
    CREATE TABLE IF NOT EXISTS spy_daily (
        time DATE,
        open FLOAT,
        high FLOAT,
        low FLOAT,
        close FLOAT,
        volume BIGINT
    )
""")

# Incrementally append new data
conn.execute("INSERT INTO spy_daily SELECT * FROM new_data")
Enter fullscreen mode Exit fullscreen mode

Four Engineering Best Practices

These are my non-negotiable rules for any backtesting data pipeline:

  1. Time standardisation: Convert everything to America/New_York timezone and trim to 09:30–16:00. Use pandas_market_calendars to generate valid trading sessions.
  2. Adjustment metadata: Store adjustment type in the schema (adj_type column). Let the backtest engine branch logic based on it. Never rely on “auto” adjustment.
  3. Missing data handling: Forward-fill missing values and add an is_filled Boolean. In analysis, compute metrics both including and excluding these points.
  4. Automated validation: Write a test that randomly picks several days and compares your OHLCV with a trusted reference (e.g., exchange bulk data). Run this weekly as a CI job.

Conclusion: Data First, Algorithms Second

As developers, we love elegant algorithms. But in quantitative finance, a robust data pipeline is the true differentiator. Clean K-line data makes simple strategies work; dirty data makes complex strategies fail. Invest the time to validate your historical bars, and your backtest will actually mean something when you go live.

Top comments (0)