Data Format¶
qex reads data as a Python dict of pandas objects: one entry per field, each a table of dates by stocks. This page is the contract every other part of qex relies on. The pages after it cover labels, universes, event data, large datasets, adjusted prices, and halts and delistings.
Basic Structure¶
Data is a dict of fields:
Each DataFrame field has:
- index: a
DatetimeIndexof trading dates, ascending; - columns: one per stock (ticker symbols, or any stable identifier);
- values: numbers, with
NaNfor missing (labels are strings: see Labels and Groups).
A field with a single value per day can instead be a date-indexed pd.Series
(see Date-only fields).
Required Fields¶
For price-based operations:
Optional, for the operators that read them:
data = {
'close': ...,
'open': ..., # For open()
'high': ..., # For high()
'low': ..., # For low()
'volume': ..., # For volume(), adv()
}
Custom Fields¶
Access any field with field('name'):
data['earnings_surprise'] = earnings_df
data['analyst_rating'] = rating_df
qex("field('earnings_surprise') * field('analyst_rating')")
Date-only fields and the market¶
A field with one value per day, such as an index level or a rate, can be stored as a
pd.Series indexed by date. field('name') broadcasts it to every ticker, on the dates and
tickers of close (dates the series lacks are NaN).
market() reads the field 'market'. Point it at whatever market you hedge against:
data['market'] = spy_total_return_close # pd.Series, date-indexed
qex("returns(1, 'market')") # market daily return
qex("ts_beta(returns(1), returns(1, 'market'), 252)") # each name's beta
qex("ts_corr(returns(1), returns(1, 'market'), 252)") # each name's market correlation
Use a total-return series (dividends reinvested), so that ex-dividend days are not market moves. Market Beta covers choosing the market and estimating beta.
Example: Complete Dataset¶
import pandas as pd
import numpy as np
# Setup
dates = pd.date_range('2020-01-01', '2024-01-01', freq='B')
tickers = ['AAPL', 'MSFT', 'GOOG', 'AMZN', 'META', 'NVDA', 'JPM', 'BAC']
# Price data
np.random.seed(42)
returns = np.random.randn(len(dates), len(tickers)) * 0.02
prices = 100 * np.exp(returns.cumsum(axis=0))
data = {
'close': pd.DataFrame(prices, index=dates, columns=tickers),
'volume': pd.DataFrame(
np.random.randint(1_000_000, 10_000_000, (len(dates), len(tickers))),
index=dates, columns=tickers
),
}
# Sector membership
sectors = ['Tech', 'Tech', 'Tech', 'Tech', 'Tech', 'Tech', 'Finance', 'Finance']
data['sector'] = pd.DataFrame(
[sectors] * len(dates),
index=dates, columns=tickers
)
# Sparse earnings (quarterly)
earnings = pd.DataFrame(np.nan, index=dates, columns=tickers)
for ticker in tickers:
# Random quarterly dates
quarter_dates = np.random.choice(len(dates), size=16, replace=False)
earnings.iloc[quarter_dates, tickers.index(ticker)] = np.random.randn(16)
data['earnings'] = earnings
# Use it
signal = qex("group_demean(returns(20), 'sector')")
result = signal.evaluate(data)
Data Alignment¶
All DataFrame fields should share the same index and columns (a date-indexed Series is
aligned to close automatically):
# Check alignment
assert data['close'].index.equals(data['volume'].index)
assert data['close'].columns.equals(data['volume'].columns)
If your data isn't aligned, align it first:
# Align to common index and columns
common_dates = data['close'].index.intersection(data['volume'].index)
common_tickers = data['close'].columns.intersection(data['volume'].columns)
data = {
'close': data['close'].loc[common_dates, common_tickers],
'volume': data['volume'].loc[common_dates, common_tickers],
}