Parquet storage and timezones

CSV is a fine interchange format and a poor working format. It is slow to parse, loses types (dates come back as strings), and a year of minute bars is hundreds of megabytes. Parquet, a columnar binary format, fixes all three. And whatever the file format, the timestamps inside it must be handled deliberately, because timezone bugs are silent: the code runs, the numbers are plausible, and the strategy is quietly trading on tomorrow's bar.

Parquet in two lines

import pandas as pd
from src.data import synthetic_ohlcv

bars = synthetic_ohlcv(2000, seed=5)
bars.index = bars.index.tz_localize("UTC")          # make the index timezone-aware

bars.to_parquet("data/clean/SYNTH_1d.parquet")
back = pd.read_parquet("data/clean/SYNTH_1d.parquet")

print(back.index.dtype)                              # datetime64[ns, UTC]
print(back.equals(bars))                             # True

The round trip preserves the index dtype, the timezone, and every float exactly. Compare that to CSV, where you would need parse_dates, index_col and a dtype cast on the way back, and where the last decimal digit of a float may change.

Size and speed on a laptop, for two million rows of minute bars: roughly 120 MB as CSV and 3 seconds to read, versus about 25 MB as parquet and under half a second. That gap matters once you are running hundreds of parameter combinations.

A small store

Wrap the paths so nothing else in the project builds filenames by hand:

# src/store.py
from pathlib import Path
import pandas as pd

CLEAN_DIR = Path("data/clean")


def bar_path(symbol: str, timeframe: str) -> Path:
    return CLEAN_DIR / f"{symbol}_{timeframe}.parquet"


def save_bars(df: pd.DataFrame, symbol: str, timeframe: str) -> Path:
    if df.index.tz is None:
        raise ValueError("refusing to save a naive datetime index; localize to UTC first")
    path = bar_path(symbol, timeframe)
    path.parent.mkdir(parents=True, exist_ok=True)
    df.to_parquet(path)
    return path


def load_bars(symbol: str, timeframe: str,
              start: str | None = None, end: str | None = None) -> pd.DataFrame:
    df = pd.read_parquet(bar_path(symbol, timeframe))
    if start or end:
        df = df.loc[start:end]
    return df

save_bars refuses a naive index. That single check prevents the most common timezone bug from ever reaching disk.

Appending new bars later, without duplicating the overlap:

def append_bars(new: pd.DataFrame, symbol: str, timeframe: str) -> pd.DataFrame:
    path = bar_path(symbol, timeframe)
    if path.exists():
        old = pd.read_parquet(path)
        combined = pd.concat([old, new])
        combined = combined[~combined.index.duplicated(keep="last")].sort_index()
    else:
        combined = new.sort_index()
    save_bars(combined, symbol, timeframe)
    return combined

Timezones: the rules

Rule 1: store in UTC. UTC has no daylight saving, so no hour is repeated or skipped. Every source you saw in lesson 2 can give you UTC or be converted to it.

Rule 2: naive means unknown, and unknown is a bug. A naive timestamp 2024-03-10 09:30 could be New York, London or UTC. pandas will happily compare naive and aware timestamps in some operations and raise in others. Localize at the boundary (tz_localize) and convert everywhere else (tz_convert).

naive = pd.Timestamp("2024-03-10 09:30")
ny = naive.tz_localize("America/New_York")     # says: this clock time is New York time
utc = ny.tz_convert("UTC")                     # same instant, different clock
print(ny, "->", utc)                           # 2024-03-10 09:30:00-04:00 -> 13:30:00+00:00

10 March 2024 was the US daylight-saving switch. A naive series that spans it has either a missing 02:00 hour or, in autumn, a repeated 01:00 hour; tz_localize will raise on the ambiguous one unless you tell it what to do. That error is a gift: it is the moment you discover your data was local time.

Rule 3: convert to local time only for display and for session logic. "Was this bar in regular trading hours?" is a New York question for US stocks. Answer it by converting a copy, not by storing local time.

idx = pd.date_range("2024-03-08 13:00", "2024-03-12 21:00", freq="30min", tz="UTC")
local = idx.tz_convert("America/New_York")
in_rth = (local.time >= pd.Timestamp("09:30").time()) & (local.time < pd.Timestamp("16:00").time())
print(pd.Series(in_rth, index=idx).groupby(idx.date).sum())

That prints 13 half-hour bars per weekday inside regular hours, across the daylight-saving change, with no special-casing, because UTC storage plus a proper conversion handles it.

Rule 4: know what the bar's timestamp means. Some vendors stamp a bar with its open time, some with its close time. A daily bar stamped 2024-03-08 00:00 UTC that actually contains the session that closed at 21:00 UTC on 8 March is fine for daily strategies, but if you merge it with intraday data you will line the day's close up with the wrong minute bars. Read the vendor's documentation and write the convention as a comment at the top of the adapter.

Key idea: A timezone bug never crashes; it moves information across bars. Store everything in UTC, refuse naive indexes at the storage boundary, and convert to local time only when a question is genuinely about local clock time.

Crypto and forex

Crypto trades continuously, so a "daily" bar is a convention, almost always 00:00 UTC to 00:00 UTC. Forex daily bars vary by vendor: some use 17:00 New York (the industry rollover), some use 00:00 UTC, and the two produce different daily highs, lows and closes. If you compare a forex strategy across two vendors and get different results, this is the first thing to check.

Try it: Take the synthetic bars, localize the index to America/New_York, save them (the store should refuse), then convert to UTC and save again. Load them back, convert to Asia/Tokyo, and confirm that the calendar date of the last bar changed while the underlying instant did not ((back.index == tokyo_index).all()).

Recap

• Parquet preserves dtypes and timezones, is many times smaller than CSV, and reads far faster.
• Wrap storage in save_bars/load_bars/append_bars so filenames and de-duplication live in one place.
• Store in UTC; refuse naive indexes at the boundary.
• Localize once at the source, convert to local time only for display and session questions.
• Learn each vendor's timestamp convention (open time vs close time, forex day boundary) and write it down.