vectorbt with Parquet market data

vectorbt runs a backtest as array arithmetic: one wide matrix of closes, one matrix of signals, one portfolio. MarketParquet's per-day files are the opposite shape, one file per date with every symbol inside. This guide turns the files into the matrix and runs a portfolio on it.

Step 1: a long table of closes

Pull the symbols you want from every daily file in one DuckDB query. The COALESCE handles daily files from before 2026-03-27, which name the date column timestamp:

  import duckdb

  universe = ["SPY", "QQQ", "IWM", "TLT", "GLD"]
  quoted = ", ".join(f"'{s}'" for s in universe)

  long = duckdb.sql(f"""
      SELECT symbol,
             COALESCE(date, CAST(timestamp AS DATE)) AS date,
             close
      FROM read_parquet('by_date/etf_daily/*/*.parquet', union_by_name=true)
      WHERE symbol IN ({quoted})
      ORDER BY 2, 1
  """).df()

Step 2: pivot to the wide matrix

  import pandas as pd

  close = (long.pivot(index="date", columns="symbol", values="close")
               .sort_index())
  close.index = pd.to_datetime(close.index)
  close = close.dropna(how="all")
  print(close.shape)          # (trading days, symbols)

Missing cells are real: an ETF that did not exist yet, or a stock that skipped a session. Leave them as NaN; vectorbt treats NaN closes as "not tradable that day". Forward-filling would invent prices and fills.

Step 3: signals and portfolio

  import vectorbt as vbt

  fast = vbt.MA.run(close, 20)
  slow = vbt.MA.run(close, 50)
  entries = fast.ma_crossed_above(slow)
  exits = fast.ma_crossed_below(slow)

  pf = vbt.Portfolio.from_signals(
      close, entries, exits,
      init_cash=100_000,
      fees=0.0005,
      freq="1D",
  )
  print(pf.total_return())
  print(pf.stats())

Every column of close becomes an independent backtest; pf.total_return() is a Series indexed by symbol. For a single combined portfolio, pass group_by=True and cash_sharing=True to from_signals.

Step 4: scale to the whole market

The same query with no WHERE symbol IN clause returns every ticker, delisted names included. A 26-year daily matrix for all US stocks is roughly 6,800 rows by 23,000 columns of floats, about 1.2 GB in memory. Two habits keep it practical:

Filter the date range in SQL (WHERE date >= '2015-01-01') before pivoting; DuckDB prunes files by their date column statistics.
Drop columns with fewer than, say, 250 observations before running indicators. Names that traded for a month add columns but no signal.

  long = duckdb.sql("""
      SELECT symbol, COALESCE(date, CAST(timestamp AS DATE)) AS date, close
      FROM read_parquet('by_date/stock_daily/*/*.parquet', union_by_name=true)
      WHERE COALESCE(date, CAST(timestamp AS DATE)) >= DATE '2015-01-01'
  """).df()
  close = long.pivot(index="date", columns="symbol", values="close")
  close = close.loc[:, close.count() >= 250]

Intraday with vectorbt

For 5-minute or 1-minute bars, build the matrix from stock_5min files the same way, using timestamp as the index and freq="5T" in from_signals. Restrict to the regular session first (between_time("09:30", "15:55") on bar open times), or the overnight gaps distort volatility-based indicators.

Pitfalls

Splits: closes are split-adjusted, so a 4-for-1 split is not a 75% drop. Dividends are not embedded; add them if the strategy's edge is total return.
Survivorship: the matrix includes tickers that later delisted. That is the point; do not filter them out.
Look-ahead in universe selection: choose the columns from data available at the backtest start, not from today's index membership.

backtrader with Parquet data · DuckDB guide · SPY data page · delisted stock data · pricing