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 (...). This recipe estimates EWMA vol in SQL, cross-checks it against pandas.ewm to the last bit, then uses it the way practitioners actually do, scaling exposure to hold a constant 10% annualized risk target, and compares raw vs vol-targeted equity curves on real data.

import numpy as np
import pandas as pd
import pyarrow as pa

import h5i_db
import cookbook_utils as cu

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

1. Real prices, and a portfolio return series in SQL#

Ten liquid names, daily, 2020–2026 (cached). Our traded asset is the equal-weight portfolio of the ten, a reasonable broad-equity proxy with a proper covid drawdown and the 2022 bear market in-sample. Per-symbol simple returns come from lag() per partition; the equal-weight portfolio is just 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.

daily = cu.fetch_daily(cu.SP500_EXAMPLES[:10], start="2020-01-01", end="2026-07-01")

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",
)

port = db.sql(
    """
    WITH r AS (
        SELECT ts, symbol,
               adj_close / lag(adj_close) OVER (PARTITION BY symbol ORDER BY ts) - 1 AS ret
        FROM prices
    )
    SELECT ts, avg(ret) AS ret
    FROM r
    WHERE ret IS NOT NULL
    GROUP BY ts
    HAVING count(*) = 10
    ORDER BY 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 (e.g. λ closer to 0.97 for weekly bars) rather than reuse 0.94 blindly.

ewma_sql = db.sql(
    """
    SELECT ts, ret,
           ewma(ret * ret, 0.06) OVER (ORDER BY ts) AS ewma_var
    FROM port_returns
    ORDER BY 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#

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 sizing. Leverage is capped at 2x (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% (versus ~24% raw), and the max drawdown shrinks by roughly two-thirds because the strategy de-levers into vol spikes (covid 2020, the 2022 bear). The Sharpe improves modestly: vol targeting is primarily a risk shaping tool that exploits the negative vol/return correlation in equities, so treat any Sharpe pickup as a bonus rather than the objective.

Takeaways#

db.close()