Tick Data Storage

Skill: Tick Data Storage | Domain: trading | Category: data | Level: advanced Tags: trading, data, tick, parquet, hdf5, storage

Tick Data Storage

MT5 Real-Time Tick Capture

import MetaTrader5 as mt5
import pandas as pd
import time
from pathlib import Path

def capture_ticks(symbol: str, duration_sec: int = 3600,
                  save_path: str = "data/ticks/") -> pd.DataFrame:
    """Capture live ticks from MT5 for specified duration."""
    mt5.initialize()
    Path(save_path).mkdir(parents=True, exist_ok=True)
    ticks = []
    start = time.time()
    last_tick = None

    while time.time() - start < duration_sec:
        tick = mt5.symbol_info_tick(symbol)
        if tick and tick != last_tick:
            ticks.append({
                "time": pd.Timestamp(tick.time, unit="s"),
                "bid": tick.bid, "ask": tick.ask,
                "last": tick.last, "volume": tick.volume,
                "spread": round((tick.ask - tick.bid) * 10000, 1),
            })
            last_tick = tick
        time.sleep(0.05)  # 20Hz polling

    mt5.shutdown()
    df = pd.DataFrame(ticks).set_index("time")
    # Save as Parquet (efficient columnar storage)
    fname = f"{save_path}{symbol}_{pd.Timestamp.now().strftime('%Y%m%d_%H%M')}.parquet"
    df.to_parquet(fname, compression="snappy")
    return df

Tick → OHLCV Aggregation

def ticks_to_ohlcv(ticks: pd.DataFrame, freq: str = "5min") -> pd.DataFrame:
    """Aggregate tick data into OHLCV bars at any frequency."""
    mid = (ticks["bid"] + ticks["ask"]) / 2
    ohlcv = mid.resample(freq).agg(
        open="first", high="max", low="min", close="last"
    )
    ohlcv["volume"] = ticks["volume"].resample(freq).sum()
    ohlcv["avg_spread"] = ticks["spread"].resample(freq).mean()
    return ohlcv.dropna()

Efficient Storage Formats

# Recommended: Parquet for persistence, Feather for ML pipelines
import pyarrow.parquet as pq

def load_ticks(symbol: str, date: str, path: str = "data/ticks/") -> pd.DataFrame:
    pattern = f"{path}{symbol}_{date}*.parquet"
    import glob
    files = glob.glob(pattern)
    return pd.concat([pd.read_parquet(f) for f in files]).sort_index()

Spread Analytics

def spread_analysis(ticks: pd.DataFrame) -> dict:
    """Analyze bid/ask spread patterns throughout the day."""
    ticks["hour"] = ticks.index.hour
    hourly = ticks.groupby("hour")["spread"].agg(["mean", "max", "min"])
    widest = hourly["mean"].idxmax()
    tightest = hourly["mean"].idxmin()
    return {
        "avg_spread_pips": round(ticks["spread"].mean(), 2),
        "widest_hour_utc": widest,
        "tightest_hour_utc": tightest,
        "scalp_recommended_hours": hourly[hourly["mean"] < 1.0].index.tolist(),
        "avoid_hours": hourly[hourly["mean"] > 2.0].index.tolist(),
    }

Related Skills

• Market Data Ingestion
• Data Pipelines
• Market Microstructure
• Backtesting Sim

── KNOWLEDGE INJECTION: Markov Chains (from Veritasium) ──

Source: "The Strange Math That Predicts (Almost) Anything" — Veritasium (2025) Added: 2026-03-17 · Relevant skills: statistics-timeseries, quant-ml-trading, ml-trading

Markov Chains — Complete Reference

Origin Story (1905 Russian Math Feud)

• Pavel Nekrasov ("Tsar of Probability"): claimed statistical independence proved free will
• Andrey Markov ("The Furious"): proved him wrong using Pushkin's Eugene Onegin
  • Counted vowel/consonant sequences → next letter depends on current letter
  • Dependent events STILL follow the Law of Large Numbers → predictable patterns emerge

Core Definition

"The next state depends ONLY on the current state — not the entire past."

Three components:

1. States — all possible conditions the system can be in
2. Transition probabilities — P(next state | current state)
3. Markov property — memoryless; past history is irrelevant

Key Mathematical Properties

Transition Matrix Example (Market Regimes)

           Trending  Ranging  Volatile
Trending  [  0.70     0.20     0.10  ]
Ranging   [  0.25     0.60     0.15  ]
Volatile  [  0.30     0.30     0.40  ]

→ Stationary distribution gives long-run % time in each regime

Python Implementation

import numpy as np

# Transition matrix
P = np.array([
    [0.70, 0.20, 0.10],  # from Trending
    [0.25, 0.60, 0.15],  # from Ranging
    [0.30, 0.30, 0.40],  # from Volatile
])

# Stationary distribution (solve π = πP)
eigenvalues, eigenvectors = np.linalg.eig(P.T)
stationary = eigenvectors[:, np.isclose(eigenvalues, 1)].real.flatten()
stationary /= stationary.sum()
print(stationary)  # [0.47, 0.35, 0.18] → 47% trending, 35% ranging, 18% volatile

# Simulate Markov chain
def simulate(P, start_state, n_steps):
    states = [start_state]
    for _ in range(n_steps):
        states.append(np.random.choice(len(P), p=P[states[-1]]))
    return states

Real-World Applications

1. Monte Carlo Method (Manhattan Project, 1940s)

• Ulam + von Neumann: simulated nuclear chain reactions using Markov processes
• Now used for: strategy stress testing, option pricing, risk simulation

# Monte Carlo equity curve simulation
def monte_carlo(returns, n_sims=1000, n_days=252):
    results = []
    for _ in range(n_sims):
        simulated = np.random.choice(returns, n_days, replace=True)
        results.append(np.cumprod(1 + simulated))
    return np.array(results)

2. Google PageRank

• Web pages = states, links = transitions
• Random surfer model → stationary distribution = page importance
• Trading equivalent: asset importance by capital flow network

3. Card Shuffling (Mixing Time)

• Bayer & Diaconis: exactly 7 riffle shuffles randomize a 52-card deck
• Trading equivalent: how many bars until regime signal is reliable

4. Hidden Markov Models (HMM) for Regime Detection

from hmmlearn import hmm

model = hmm.GaussianHMM(n_components=3, covariance_type="diag", n_iter=100)
returns = np.array(daily_returns).reshape(-1, 1)
model.fit(returns)
hidden_states = model.predict(returns)
# 0=low vol, 1=trending, 2=high vol/crisis

5. Markov Decision Processes (MDP) — Reinforcement Learning

• State: market conditions
• Action: buy/sell/hold
• Reward: P&L
• Policy: optimal action per state
• Used in: AlphaGo, self-driving cars, RL trading agents

Markov Chains vs LLMs

Trading Applications Summary