Building a Factor Risk Model: The Exposure Matrix

We have data: a security master and a financials table, both stored as parquet files in cloud storage. Now we need the exposure matrix $X$ , the known quantity in the cross-sectional regression from the last post. For each stock $i$ and factor $k$ , the entry $X_{i, k}$ tells us how exposed that stock is to that factor that week. Collecting these into a matrix gives us the right-hand side of the regression we’ll eventually run: $r_{i} = \sum_{k} X_{i, k} f_{k} + ϵ_{i}$ .

Building the exposure matrix is the bulk of the work in a fundamental factor model. Each stock gets a vector of standardized characteristics that describe its position in factor space. A large-cap, low-volatility, high-quality company will have a positive size exposure, a negative volatility exposure, and a positive quality exposure. Those numbers need to be comparable across factors and across time.

Our model uses 8 factors: size, value, quality, growth, volatility, beta, momentum, and reversal. Value and quality are cross-sectional composites, each one built by averaging multiple standardized financial ratios. Five more are time-series factors, computed from rolling windows per ticker and then standardized cross-sectionally. Size belongs to neither group: it is log market cap, read straight off the date with no window at all.

The distinction matters because composites require an extra step: we z-score the component signals, trim outliers, average them into one number, and then z-score the composite itself.

The Estimation Universe

Before any characteristic can be standardized, the model needs a list of stocks to standardize it against. Membership comes first: the pipeline’s opening step joins the financials to the security master with country = 'United States' in the join predicate, so only US-domiciled names enter the model at all, before any statistic is computed.

The data is US-listed, which is not the same thing. It includes a few thousand ADRs and foreign-domiciled listings, and this is a US risk model with a country factor that is supposed to mean “the US market,” not “whatever happens to trade here.” A ticker with no master row leaves at the same gate, and so does any ticker that never passes the investability screens below. A history that can never anchor a statistic and never reach the regression is dead weight.

Within that membership, not all stocks should influence the cross-sectional statistics. In the last post we defined two investability filters: market cap of at least 100M, and rolling 20-week median dollar volume above 1M. Those filters now define the estimation universe, the set of stocks whose characteristics anchor the cross-sectional distributions.

Statistics (means, standard deviations, medians, MADs) are computed over the investable universe only, and the resulting transformations are then applied to everyone. A 50M micro-cap still gets factor exposures, measured relative to the investable distribution. This split keeps illiquid, thinly traded names from distorting the center and spread of our factors while still letting us characterize every stock in the dataset.

Cross-Sectional Standardization

With membership settled, the characteristics themselves are still in different units. Earnings yield is a percentage, market cap is in millions, beta is dimensionless and hovers near 1. To make them comparable, we z-score each characteristic cross-sectionally: subtract the mean, divide by the standard deviation.

But which mean and which standard deviation? The convention in Barra-style models pairs a cap-weighted mean with an equal-weighted standard deviation:

z_{i, k} = \frac{x _{i, k} - μ _{k}^{cw}}{σ _{k}^{ew}}

Where $x_{i, k}$ is the raw value of characteristic $k$ for stock $i$ , $μ_{k}^{cw}$ is that characteristic’s capitalization-weighted cross-sectional mean, and $σ_{k}^{ew}$ is its equally weighted cross-sectional standard deviation.

The cap-weighted mean centers the distribution on large caps. This makes sense for risk modeling: large-cap stocks dominate portfolio risk, so “average” should reflect where the weight is. The equal-weighted standard deviation measures dispersion across all investable names equally, which keeps mega-caps from compressing the scale.

After z-scoring, we trim outliers. The mad_clamp macro from the last post reappears here. It clamps z-scores to $median \pm 3 \times 1.4826 \times MAD$ , where $1.4826$ is the scaling factor that makes MAD a consistent estimator of $σ$ under normality:

CREATE OR REPLACE MACRO mad_clamp(val, med, mad) AS
  CASE WHEN val IS NULL THEN NULL
       WHEN mad = 0 THEN val
       ELSE GREATEST(med - 3.0 * mad, LEAST(val, med + 3.0 * mad))
  END;

The median and MAD are computed over the investable universe. The clamp is applied to all stocks. This two-pass pattern (compute the statistics on the clean subset, apply transformations everywhere) runs throughout the pipeline.

Composite Factors: Value and Quality

Value and quality get z-scored twice: once on each component signal, and once again on the average of them.

Value combines five valuation ratios: earnings yield, EBITDA-to-enterprise-value, cash-flow-to-price, book-to-market, and dividend yield. Quality combines four profitability and leverage ratios: ROE, ROA, gross margin, and debt ratio. The debt ratio is negated so that lower debt maps to higher quality, which keeps every component signal pointed in the same direction.

The pipeline for each composite is: z-score each component signal over the investable universe, MAD-trim the z-scores, then average the non-null trimmed z-scores into the composite. The SQL below condenses that chain down to two of the value signals.

-- Step 1: cap-weighted mean, equal-weighted std per date (investable only)
zstats AS (
  SELECT date,
    SUM(greenblatt_earnings_yield * market_cap)
      FILTER (WHERE greenblatt_earnings_yield IS NOT NULL)
      / NULLIF(SUM(market_cap)
        FILTER (WHERE greenblatt_earnings_yield IS NOT NULL), 0) AS gey_mu,
    STDDEV_POP(greenblatt_earnings_yield) AS gey_sd,
    SUM(cash_flow_to_price_ratio * market_cap)
      FILTER (WHERE cash_flow_to_price_ratio IS NOT NULL)
      / NULLIF(SUM(market_cap)
        FILTER (WHERE cash_flow_to_price_ratio IS NOT NULL), 0) AS cfp_mu,
    STDDEV_POP(cash_flow_to_price_ratio) AS cfp_sd,
    -- ... 7 more signals
  FROM staging
  WHERE investable_mcap AND investable_liq
  GROUP BY date
),

-- Step 2: z-score every stock
zscored AS (
  SELECT s.ticker, s.date,
    CASE WHEN z.gey_sd > 0
      THEN (s.greenblatt_earnings_yield - z.gey_mu) / z.gey_sd END AS z_gey,
    CASE WHEN z.cfp_sd > 0
      THEN (s.cash_flow_to_price_ratio - z.cfp_mu) / z.cfp_sd END AS z_cfp,
    -- ...
  FROM staging s JOIN zstats z USING (date)
),

-- Step 3: MAD-trim, then average into composite
clamped_z AS (
  SELECT ticker, date,
    mad_clamp(z_gey, gey_zmed, gey_zmad) AS tz_gey,
    mad_clamp(z_cfp, cfp_zmed, cfp_zmad) AS tz_cfp,
    -- ...
  FROM zscored JOIN trim_z_stats USING (date)
)
SELECT *,
  (COALESCE(tz_gey,0) + COALESCE(tz_cfp,0) + ...)
    / NULLIF((tz_gey IS NOT NULL)::INT + (tz_cfp IS NOT NULL)::INT + ..., 0)
    AS raw_value
FROM clamped_z;

The COALESCE/NULLIF pattern is what handles missing data. A stock with no dividend yield but all four of the other value signals gets a composite that averages those four. Nothing is thrown out and nothing is imputed to zero.

Time-Series Factors

The composites are computed across stocks on one date. The five time-series factors work the other way: computed per ticker using rolling windows, then standardized cross-sectionally in the final step. All five use DuckDB’s window functions, which let us define a WINDOW clause once and reuse it across columns.

Volatility and Beta

Volatility and beta both use 52-week rolling windows. Volatility is the sample standard deviation of returns.

Beta requires more care. The definition is $β_{i} = Cov (r_{i}, r_{m}) / Var (r_{m})$ , where $r_{m}$ is the cap-weighted market return over the investable universe. We can expand this into rolling averages that SQL handles directly:

β_{i} = \frac{r _{i} \cdot r _{m} - r ˉ _{i} \cdot r ˉ _{m}}{r _{m}^{2} - r ˉ _{m}^{2}}

In SQL, this becomes rolling AVGs over a 52-week window:

CASE WHEN n52 >= 52 AND (mean_mkt2 - mean_mkt * mean_mkt) > 0
  THEN (mean_r_mkt - mean_r * mean_mkt) / (mean_mkt2 - mean_mkt * mean_mkt)
END AS raw_beta

The guard on the denominator prevents division by zero when the market return has no variance over the window (which shouldn’t happen with 52 weeks of data, but defensive SQL is good SQL).

Momentum and Reversal

Unlike volatility and beta, which read every week in their window, momentum deliberately leaves part of its own out. Momentum is the cumulative log return from week $t - 51$ to $t - 4$ : roughly 12 months of returns with the most recent month skipped.

That gap exists because recent returns contain a short-term reversal signal that contaminates the momentum effect. Stocks that went up a lot this month tend to mean-revert next month, which is the opposite of what momentum is trying to capture.

Reversal is that short-term signal: the cumulative log return over the last 4 weeks, negated. Recent underperformers get positive reversal exposure. By splitting the return history into these two non-overlapping windows, we separate the medium-term trend from the short-term bounce.

Growth

Growth uses a 3-year (156-week) OLS trend slope of sales and EPS, normalized by level. That definition raises a problem the four factors above never did—namely that a regression coefficient is not a rolling aggregate. DuckDB will fit one directly, with regr_slope over a window, but that refits from scratch at every row, 156 observations at a time, down the whole panel.

That fit is avoidable. The regressor here is a position index rather than data, so every part of the slope except two rolling sums is known before the query touches the panel. For a window of $n$ consecutive observations with indices $i$ :

\hat{β} = \frac{n \sum i \cdot y _{i} - \sum i \cdot \sum y _{i}}{n \sum i ^{2} - ( \sum i ) ^{2}}

Where $y_{i}$ is the sales or EPS figure at position $i$ in the window, and $n$ is the number of observations in it.

The denominator $n \sum i^{2} - (\sum i)^{2}$ is the same for any consecutive sequence of $n$ integers. It simplifies to $n^{2} (n^{2} - 1) /12$ . For $n = 156$ , that’s $49, 351, 380$ , a constant we can hardcode. The numerator decomposes into SUM(idx * y) and SUM(y), both of which DuckDB computes as rolling window aggregates.

The query never fits a regression. Two rolling sums slide forward one week at a time, and everything they are combined with is arithmetic on the row index.

Dividing the slope by the window’s mean level (SUM(y) over $n$ ) turns an absolute slope into a growth rate. In the code that appears as one extra factor of 156 in the numerator, cancelling the $1/ n$ inside the mean.

The level goes into that denominator as an absolute value, and the reason is loss-makers. A company three years into the red has a negative EPS sum, and dividing by it as-is would flip the sign of the trend, scoring shrinking losses as the worst growth in the cross-section. Sales can’t go negative, so only EPS needs the guard.

Sales growth and EPS growth are averaged into one composite, using the same COALESCE/NULLIF pattern as the value and quality composites.

Bringing It Together

At this point, all raw factors exist in separate tables: composites from the cross-sectional step, time-series factors from the rolling window step. The final standardization step joins them, adds size (log market cap), MAD-trims the five time-series factors, and z-scores all 8 factors together over the investable universe.

The final step also drops the first 156 weeks of the dataset, the warm-up period for the growth factor’s 3-year rolling window. Before that point, growth exposures are null, and we’d rather have a clean, complete exposure matrix than one with missing columns in the early dates.

The output is the exposure matrix: 8 z-scored factors per stock per week. Within the investable universe, each factor has a cap-weighted mean of zero and $σ = 1$ by construction. The two moments are not measured the same way, though: the mean that gets removed is cap-weighted and the standard deviation that divides is equal-weighted. That mismatch is deliberate, since a cap-weighted market portfolio should come out factor-neutral.

One consequence of that mismatch matters for the explorer further down. The plain, equal-weighted average of a factor is not pinned to zero. Stocks outside the investable universe get exposures measured relative to that same distribution, so their z-scores aren’t constrained to unit variance either.

Three More Columns

The z-scored factors aren’t the only thing this step emits. It adds three more columns, and all three exist for the regression in the next post.

Sector. A second join to the security master supplies it. In a model with sector dummies, sector is itself a column of the exposure matrix. It happens to be categorical rather than z-scored, so it gets expanded into dummy columns at regression time rather than stored that way. The join is inner and repeats the same country = 'United States' predicate as the one that defined the universe at the top of the pipeline (not because it can drop anything new, but so this step cannot silently widen what that one narrowed).

Market cap. The raw value is kept alongside its log, which is already in the matrix as f_size. The regression needs it twice, once for the $market cap$ regression weights and once for the cap-weighted constraint that makes the sector dummies estimable at all.

The forward return. The regression’s dependent variable is stored here too. Exposures are known as of the close on date, and the return they explain is the one realized over the following week:

r_{i, t \to t + 1} = k \sum X_{i, k, t} \cdot f_{k, t} + ϵ_{i, t}

That’s a one-period LEAD, and getting it backwards produces a model that looks spectacular and is entirely lookahead. The lead is stored next to fwd_date, the date on which the return is realized.

Storing that second column looks redundant. Why not assume date + 7 days? Because LEAD follows row order, not the calendar: a ticker with a gap in its history gets whatever observation comes next in its own record, which might be three weeks out.

Without fwd_date that gap is invisible, and a three-week return enters a one-week regression. With it, the bad rows are one date_diff away.

The pipeline below runs all of this in the browser using DuckDB WASM. Each step’s SQL is expandable, down to every CTE, every window function, and every mad_clamp call. Clicking “Run Pipeline” sets DuckDB chewing through the parquet files.

Factor Exposure Pipeline Loading Loading DuckDB tables...

Once the pipeline completes, the explorer below shows the resulting factor distributions for the investable universe at each year-end date. The distributions come out reasonably well behaved, with standard deviations near 1 and tails clipped by the MAD trimming.

Some factors do not center at zero, and size is the extreme case. The explorer plots the plain equal-weighted average where the pipeline removed the cap-weighted mean, so what it shows is the gap between the two. For size that gap is large. Measured on the real panel, the equal-weighted mean of the size factor is between -1.8 and -2.3 across the weekly dates. The largest names score far above the typical name on their own factor, and centering on them pushes everyone else negative.

Run the pipeline above to explore factor exposures.

Where this step cuts corners

MAD trimming clips real tails. The pipeline clamps every z-score at $\pm 3$ scaled MADs, which stops one bad data point from dominating a cross-section but also flattens a real six-standard-deviation move down to three. In a risk model that’s an understatement of tail exposure baked in before the covariance matrix ever sees the data.
Composites don’t require minimum coverage. The COALESCE/NULLIF averaging hands a stock with one of the five value signals an exposure that looks every bit as authoritative as one built from all five, and nothing downstream can tell the two apart. A company with nothing but a dividend yield still gets a value score. A production model would set a minimum signal count, or shrink thin composites toward zero.
Nulls survive the warm-up filter. Dropping the first 156 weeks fixes growth’s rolling window at the start of the sample, but it does nothing for a company that listed in year eight. That company still turns up mid-panel with null beta and volatility until it has 52 weeks behind it. The next post has to decide what to do with those rows.
Three years are discarded for the sake of one factor. The 156-week cutoff is set entirely by growth’s trend window. Reversal needs four weeks of returns, volatility and beta need 52, and all three were computable for years before the panel is allowed to start.

Those four are a sample rather than an inventory. This same step also uses full market cap where a production model would use free float, and weights every observation in its rolling windows equally instead of decaying older ones toward a half-life. It leaves missing descriptors null rather than filling them from industry peers, and omits style factors that most commercial models include, non-linear size and liquidity among them. The list runs on for as long as there is patience to keep writing it. Few of the items are individually hard, and that is the point: each one costs a paragraph to explain and buys a reader learning the mechanics almost nothing.

What’s Next

We have the exposure matrix $X$ . The next step is estimating factor returns $f$ : for each week, regress returns across all stocks against their exposures. That’s a weighted least squares problem, repeated hundreds of times across dates, where SQL stops being the right tool and we bring in a small dedicated solver.