h5i-db / Cookbook / Alpha research / EWMA volatility and vol-targeted position sizing

EWMA volatility and vol-targeted position sizing

RiskMetrics-style EWMA volatility is the workhorse conditional-vol estimate on every risk desk, and h5i-db ships it as a native SQL window function: ewma(x, alpha) OVER (...).

In this recipe we:

  1. build a portfolio return series in SQL,
  2. estimate EWMA vol in SQL and cross-check it against pandas.ewm bit for bit,
  3. use it the way practitioners do, scaling exposure to hold a constant 10% annualized risk target,
  4. compare raw and vol-targeted equity curves on real data.
import numpy as np
import pandas as pd
import pyarrow as pa

import h5i_db
from h5i_db import col, count_star, sql_expr
import cookbook_utils as cu

db = h5i_db.Database(cu.fresh_db("alpha_voltarget"), create=True)

1. The data#

Ten liquid names, daily, 2020 to 2026, from the cu.fetch_daily cache. One row per symbol per session.

column type meaning
ts timestamp[us, tz=UTC] session date
symbol string ticker
open, high, low, close float64 session prices
adj_close float64 close adjusted for splits and dividends
volume int64 shares traded 
daily = cu.fetch_daily(cu.SP500_EXAMPLES[:10], start="2020-01-01", end="2026-07-01")
print(f"{daily.num_rows:,} rows x {daily.num_columns} columns, "
      f"{len(set(daily['symbol'].to_pylist()))} symbols")
daily.to_pandas().head()
output
16,310 rows x 8 columns, 10 symbols
ts symbol open high low close adj_close volume
0 2020-01-02 20:00:00+00:00 AAPL 74.059998 75.150002 73.797501 75.087502 72.333862 135480400
1 2020-01-02 20:00:00+00:00 JPM 139.789993 141.100006 139.259995 141.089996 117.899200 10803700
2 2020-01-02 20:00:00+00:00 MSFT 158.779999 160.729996 158.330002 160.619995 151.829529 22622100
3 2020-01-02 20:00:00+00:00 BRK-B 227.509995 228.389999 226.710007 228.389999 228.389999 3764000
4 2020-01-02 20:00:00+00:00 META 206.750000 209.789993 206.270004 209.779999 207.953842 12077100

The traded asset is the equal-weight portfolio of those ten. It is a reasonable broad-equity proxy, and it puts a proper covid drawdown and the 2022 bear market in-sample.

Per-symbol simple returns come from lag() per partition, and the equal-weight portfolio is the cross-sectional average per day. We keep only days where all ten names traded, and store the result as its own port_returns table so the EWMA step is a clean single-table window query.

schema = pa.schema(
    [
        pa.field("ts", pa.timestamp("us", tz="UTC"), nullable=False),
        pa.field("symbol", pa.string()),
        pa.field("adj_close", pa.float64()),
    ]
)
db.create_table("prices", schema, time_column="ts", sort_key=["ts", "symbol"])
db.append(
    "prices",
    daily.select(["ts", "symbol", "adj_close"])
    .sort_by([("ts", "ascending"), ("symbol", "ascending")])
    .cast(schema),
    note="yfinance 10 names 2020-2026",
)

PREV = sql_expr("lag(adj_close)").over(partition_by="symbol", order_by="ts")

port = (
    db.table("prices")
    .with_columns(ret=col("adj_close") / PREV - 1)
    .filter(col("ret").is_not_null())
    .group_by("ts")
    .agg(ret=col("ret").mean(), names=count_star())
    .filter(col("names") == 10)  # the HAVING: full cross-section only
    .select("ts", "ret")
    .sort("ts")
    .to_arrow()
)

ret_schema = pa.schema(
    [
        pa.field("ts", pa.timestamp("us", tz="UTC"), nullable=False),
        pa.field("ret", pa.float64()),
    ]
)
db.create_table("port_returns", ret_schema, time_column="ts", sort_key=["ts"])
db.append("port_returns", port.cast(ret_schema), note="equal-weight 10-name portfolio")
print(f"{port.num_rows} daily portfolio returns")
output
1630 daily portfolio returns

2. EWMA variance in SQL, and the lambda/alpha dictionary#

RiskMetrics writes the recursion as $\sigma_t^2 = \lambda\,\sigma_{t-1}^2 + (1-\lambda)\,r_t^2$, with $\lambda = 0.94$ for daily data.

h5i-db's ewma(x, alpha) uses the smoothing weight on the new observation, so alpha = 1 − lambda = 0.06. Same recursion, opposite naming convention, and a perennial source of off-by-one-convention bugs.

The 0.94/0.06 pair implies a center of mass of λ/(1−λ) ≈ 16 trading days. At other bar frequencies you would rescale, for instance λ closer to 0.97 for weekly bars, rather than reuse 0.94 blindly.

ewma_sql = (
    db.table("port_returns")
    .select("ts", "ret", ewma_var=(col("ret") * col("ret")).ewma(0.06, order_by="ts"))
    .sort("ts")
    .to_pandas()
)
ewma_sql["ewma_vol_ann"] = np.sqrt(ewma_sql["ewma_var"] * 252)

# Cross-check against pandas (adjust=False = the plain recursion).
pandas_var = (ewma_sql["ret"] ** 2).ewm(alpha=0.06, adjust=False).mean()
assert np.allclose(ewma_sql["ewma_var"], pandas_var)
print("SQL ewma() == pandas ewm(alpha=0.06, adjust=False): exact match")
ewma_sql.set_index("ts").tail(3).round(6)
output
SQL ewma() == pandas ewm(alpha=0.06, adjust=False): exact match
ret ewma_var ewma_vol_ann
ts
2026-06-26 20:00:00+00:00 0.010634 0.000098 0.156986
2026-06-29 20:00:00+00:00 0.010465 0.000098 0.157549
2026-06-30 20:00:00+00:00 0.008129 0.000097 0.155986

3. Vol targeting#

We target 10% annualized. Each day the position is scaled by leverage_t = target_vol / ewma_vol_{t-1}, so yesterday's vol estimate sizes today's exposure. Using today's would leak the day's own squared return into its own sizing.

Leverage is capped at 2x, which is financing and mandate reality, and we charge 5 bps on each day's change in gross exposure so the strategy pays for its own rebalancing.

TARGET_VOL, LEV_CAP, COST_BPS = 0.10, 2.0, 5

df = ewma_sql.set_index("ts")
lev = (TARGET_VOL / df["ewma_vol_ann"].shift(1)).clip(upper=LEV_CAP)
raw = df["ret"]
targeted = (lev * raw - COST_BPS / 1e4 * lev.diff().abs()).dropna()


def perf_stats(r: pd.Series) -> dict:
    eq = (1 + r).cumprod()
    dd = eq / eq.cummax() - 1
    return {
        "ann_ret": r.mean() * 252,
        "ann_vol": r.std() * np.sqrt(252),
        "sharpe": r.mean() / r.std() * np.sqrt(252),
        "max_dd": dd.min(),
    }


stats = pd.DataFrame(
    {"raw (1x)": perf_stats(raw.loc[targeted.index]), "vol-targeted 10%": perf_stats(targeted)}
).T
print(f"leverage: mean {lev.mean():.2f}x, range [{lev.min():.2f}, {lev.max():.2f}]x")
stats.round(3)
output
leverage: mean 0.56x, range [0.11, 1.19]x
ann_ret ann_vol sharpe max_dd
raw (1x) 0.258 0.242 1.067 -0.324
vol-targeted 10% 0.121 0.105 1.155 -0.121 
import matplotlib.pyplot as plt

fig, axes = plt.subplots(3, 1, figsize=(10, 8), sharex=True)
axes[0].plot(df.index, df["ewma_vol_ann"] * 100, lw=0.9, color="tab:blue")
axes[0].axhline(TARGET_VOL * 100, color="tab:red", lw=0.8, ls="--", label="10% target")
axes[0].set_ylabel("EWMA vol (ann, %)")
axes[0].set_title("EWMA(0.94) volatility, leverage, and equity curves")
axes[0].legend()

idx = targeted.index
axes[1].plot((1 + raw.loc[idx]).cumprod(), lw=1.1, label="raw (1x)")
axes[1].plot((1 + targeted).cumprod(), lw=1.1, label="vol-targeted")
axes[1].set_ylabel("growth of $1")
axes[1].legend()

for r, lab in [(raw.loc[idx], "raw (1x)"), (targeted, "vol-targeted")]:
    eq = (1 + r).cumprod()
    axes[2].plot(eq / eq.cummax() - 1, lw=0.9, label=lab)
axes[2].set_ylabel("drawdown")
axes[2].set_xlabel("date")
axes[2].legend()
fig.tight_layout()
output
output figure

The mechanics show up exactly where theory says they should. Realized vol of the targeted book lands near 10%, against roughly 24% raw, and the max drawdown shrinks by about two-thirds because the strategy de-levers into vol spikes: covid in 2020, the 2022 bear market.

The Sharpe improves only modestly. Vol targeting is primarily a risk shaping tool that exploits the negative vol/return correlation in equities. Treat any Sharpe pickup as a bonus rather than the objective.

Takeaways#

db.close()