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 ```python 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 ```python 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 | Format | Size | Speed | Use Case | |--------|------|-------|----------| | CSV | Large | Slow | Human-readable, small datasets | | Parquet | Small (10×) | Fast | Production — columnar, compressed | | HDF5 | Medium | Very fast | ML pipelines — random access | | Feather | Small | Fastest | In-memory → disk → in-memory cycles | ```python # 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 ```python 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](../market-data-ingestion.md) - [Data Pipelines](../market-data-ingestion.md) - [Market Microstructure](../market-microstructure.md) - [Backtesting Sim](../backtesting-sim.md) --- --- # ── 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 | Property | Definition | Trading Use | |----------|-----------|-------------| | **Transition Matrix (P)** | P[i][j] = prob of moving from state i → j | Regime switching probabilities | | **Stationary Distribution (π)** | Long-run % time spent in each state | Expected time in trending vs ranging | | **Mixing Time** | Steps until process forgets initial state | Regime persistence measure | | **Ergodicity** | Every state reachable from every other | Ensures stationary dist. exists | ### 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 ```python 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 ```python # 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** ```python 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 | | Markov Chain | LLM (Claude/GPT) | |--|-------------|-----------------| | Memory | Memoryless (current state only) | Long-range context window | | Prediction | P(next | current) | P(next | all prior tokens) | | Complexity | O(states²) | O(sequence²) | | Interpretable | Yes | No | ### Trading Applications Summary | Application | How | |-------------|-----| | **Regime detection** | HMM on returns → hidden states | | **Regime persistence** | Transition matrix diagonal | | **Strategy switching** | Trigger on regime change | | **Monte Carlo backtests** | Simulate paths from historical returns | | **Position sizing** | Weight by stationary distribution | | **Risk modeling** | Tail state probabilities | ---