Risk Based Portfolio
When constructing a risk-based stock portfolio, evaluating investments requires shifting the focus from purely chasing raw returns to analyzing how much volatility, market exposure, and tail-risk you are taking on to achieve those returns.
The core quantitative metrics used to evaluate and build a risk-managed portfolio fall into four main categories:
Volatility and Dispersion Metrics
These metrics measure how wildly a portfolio or asset swings around its average return path.
Standard Deviation (Total Volatility): Measures the dispersion of returns from an average. In a portfolio context, annualized standard deviation tells you the historical "bumpiness" of the ride. Higher standard deviation means wider performance swings.
Downside Deviation: Unlike standard deviation—which penalizes both upward spikes and downward drops equally—downside deviation only measures negative volatility (returns falling below a minimum acceptable threshold or the risk-free rate). This is a truer measure of painful risk for most investors.
Market Sensitivity and Systematic Risk
These metrics explain how your portfolio behaves relative to the broader market (e.g., the S&P 500).
Beta (): Measures a portfolio's sensitivity or volatility relative to the benchmark market.
=1.0: Moves in tandem with the market.
<1.0: Theoretically less volatile than the market (defensive).
>1.0: More volatile/aggressive than the market.
R-Squared (R2): Measures what percentage of a portfolio's price movements can be explained by movements in its benchmark. A high R2 (e.g., > 85) means your portfolio tracks the market closely; a low R2 means your portfolio is charting its own independent course.
Alpha (): Represents excess performance relative to the expected return predicted by beta. A positive alpha indicates a portfolio manager or asset selection strategy is outperforming the benchmark on a risk-adjusted basis.
Risk-Adjusted Return Ratios
These are vital for determining whether the extra risk you are taking is actually being compensated by adequate returns.
Sharpe Ratio: Calculated as Rp-RfStandardDeviation, it measures excess return per unit of total risk. Higher is better, as it reveals more efficient risk utilization.
Sortino Ratio: Similar to the Sharpe ratio, but substitutes total standard deviation with downside deviation. It is often preferred for risk-based portfolios because it stops penalizing an asset for having wildly positive up-months.
Treynor Ratio: Uses Beta instead of standard deviation in the denominator (Rp-RfBeta). It measures excess return per unit of systematic market risk, making it ideal for well-diversified equity portfolios.
Tail Risk and Drawdown Constraints
These metrics look at worst-case historical scenarios to ensure you don't breach your psychological or structural loss limits.
Maximum Drawdown (Max DD): The largest peak-to-trough drop experienced by the portfolio over a specific historical timeframe before a new peak is achieved. It explicitly illustrates the worst pain you would have endured holding that mix.
Value at Risk (VaR): A statistical estimate of the maximum potential loss over a given time horizon (e.g., 1 week) at a specific confidence interval (e.g., 95% or 99%). For example, a 95% 1-week VaR of 4% means there is a 5% historical probability that the portfolio will lose more than 4% in any single week.
Upside/Downside Capture Ratios: Measures what percentage of the benchmark's gains the portfolio captures during bull markets versus what percentage of losses it takes on during bear markets. An ideal risk-based equity portfolio seeks a downside capture significantly lower than its upside capture (e.g., capturing 90% of up markets but only 70% of down markets).
Are you looking to evaluate a specific set of existing stock holdings, or are you designing a quantitative asset allocation model from scratch?
Designing a quantitative asset allocation model requires replacing intuition with a rigorous, repeatable mathematical framework. Whether you are allocating across broad asset classes, sector ETFs, or a universe of individual equities, a production-grade model typically follows a structured four-stage architecture.
Asset Universe & Data Ingestion Pipeline
Before optimizing weights, you need a clean, survivorship-bias-adjusted dataset and a clearly defined asset universe.
Universe Definition: Establish clear inclusion/exclusion criteria (e.g., liquid sector ETFs like the SPDR Select Sector series, or liquid large-cap equities meeting strict average daily volume thresholds).
Data Inputs: Ingest daily/weekly adjusted close prices, fundamental ratios (earnings yield, book-to-market), and macroeconomic indicators.
Data Integrity: Ensure your pipeline handles corporate actions (splits, dividends) and point-in-time fundamental data to prevent look-ahead bias.
Core Weighting & Allocation Strategies
Choose an allocation engine that aligns with your risk and return objectives. Common quantitative approaches include:
Risk Parity / Equal Risk Contribution (ERC): Allocates capital such that each asset or sector contributes equally to the overall portfolio volatility, rather than equal dollar weighting. This prevents highly volatile assets from dominating portfolio risk.
Momentum & Trend-Following (Tactical): Ranks assets based on risk-adjusted momentum (e.g., 6-month or 12-month total return divided by volatility) and dynamically overweights top-decile performers while moving cash or fixed income into safety during market drawdowns.
Multi-Factor Scoring (Quantitative Equity): Combines orthogonal factors—such as Value, Quality, Low Volatility, and Momentum—into a composite z-score to rank and weight portfolio constituents.
Black-Litterman Model: Blends market equilibrium weights (implied by reverse optimization) with your quantitative model's specific views to produce stable, intuitive asset weights.
Portfolio Optimization & Constraint Engine
Raw optimizer outputs are notoriously sensitive to estimation error ("garbage in, garbage out"). A robust model applies strict constraints:
Boundary Constraints: Set minimum and maximum weight limits per asset (e.g., Wi[0,0.15]) to prevent over-concentration.
Sector / Factor Constraints: Cap aggregate exposure to single sectors or style factors to maintain structural diversification.
Shrinkage Estimators: Use Ledoit-Wolf shrinkage or covariance matrix smoothing to stabilize covariance estimations before feeding them into Mean-Variance Optimization (MVO) or utility maximization functions.
Backtesting, Rebalancing, and Execution
Once the math is set, govern how the model operates in live production:
Rebalancing Cadence & Buffers: Establish whether rebalancing occurs monthly or quarterly. Implement trade buffers (e.g., only rebalance an asset if its weight drifts more than $\pm 3\%$ from its target) to curb excessive turnover and transaction costs.
Walk-Forward Out-of-Sample Testing: Validate the model across multiple market regimes (e.g., 2008 financial crisis, 2020 pandemic shock, 2022 inflation regime) to evaluate robustness.
Execution Automation: Script the pipeline to automatically calculate target weights, generate orders, and log transactions to a secure database.
Are you focusing this model on sector ETF rotation, or are you building a multi-factor stock selection engine?
Building a quantitative multi-factor stock selection engine requires taking the asset allocation framework down to the individual security level. Instead of allocating across broad sectors or asset classes, your model evaluates a universe of individual equities (e.g., S&P 500 or Russell 1000 constituents) and ranks them using orthogonal fundamental and technical factors.
A robust, production-grade multi-factor stock selection engine follows a structured 5-stage pipeline:
Factor Universe & Definition
To avoid multicollinearity and capture distinct drivers of equity returns, select factors across four primary academic and practitioner buckets:
Value: Identifies mispriced or cheap equities relative to fundamentals.
Metrics: Earnings Yield (E/P), Free Cash Flow Yield, Book-to-Market (B/M).
Quality: Identifies financially stable, profitable businesses with strong balance sheets.
Metrics: Return on Equity (ROE), Return on Invested Capital (ROIC), Debt-to-Equity, Accruals Ratio.
Momentum: Captures trend persistence in stock prices.
Metrics: 12-minus-1 month price momentum (excluding the most recent month to avoid short-term reversal noise), Risk-Adjusted Momentum (Sharpe of excess returns).
Low Volatility / Defensive: Exploits the low-volatility anomaly where boring stocks tend to outperform on a risk-adjusted basis.
Metrics: Trailing 12-month standard deviation, 60-day Beta.
Cross-Sectional Data Standardization (Z-Scoring)
Raw factor values cannot be directly added together because their scales differ wildly (e.g., ROE is a percentage, Price-to-Earnings is a multiple, and Beta is a decimal).
Cross-Sectional Z-Score: For each factor f on day t, calculate the z-score for stock i:
Zi,f=xi,f-ff
where f is the cross-sectional mean and f is the cross-sectional standard deviation across your investable universe.
Winsorization: Before z-scoring, winsorize your raw factor data (e.g., capping values at the 1st and 99th percentiles) to prevent extreme outlier values from distorting your model.
Composite Factor Scoring & Weighting
Combine your individual factor z-scores into a single composite score for each stock:
Equal-Weighted Composite: Average the z-scores across styles (e.g., Compositei=ZValue+ZQuality+ZMomentum+ZLowVol4).
Dynamic / Regime-Weighted Composite: Adjust factor weights based on the macroeconomic regime (e.g., overweighting Quality and Low Volatility during late-cycle contractions, and Momentum during early-cycle expansions).
Portfolio Construction & Optimization
Once stocks are ranked by their composite z-score, build the actual portfolio:
Decile / Quintile Sorting: Go long the top decile (top 10% of composite scores) and optionally short the bottom decile for a market-neutral long-short strategy, or hold a concentrated long-only basket of the top 20–30 stocks.
Risk-Controlled Portfolio Optimization: Feed the selected stock universe into an optimizer (like Mean-Variance or Risk Parity) subject to constraints:
Position Limits: Max weight per stock (e.g., $\le 3\%$).
Sector Neutrality: Force aggregate sector weights to match the benchmark to ensure alpha comes from stock selection, not accidental sector bets.
Liquidity Constraints: Exclude stocks with average daily trading volume (ADTV) below a safe threshold.
Backtesting and Execution Loop
Point-in-Time Data: Ensure your fundamental metrics use point-in-time filing dates (from SEC EDGAR or similar feeds) rather than look-ahead restated data.
Rebalancing Cadence: Typically monthly or quarterly. Implement transaction cost models and trade buffers to prevent high portfolio turnover from eating up alpha.
Are you planning to implement this multi-factor engine in Python using pandas and SQL, and do you need a template data schema or pipeline structure to get started?
Here is a complete, production-ready blueprint combining a structured relational database schema (designed for MySQL/SQLAlchemy) with a modular Python pipeline using pandas and SQLAlchemy to implement your multi-factor stock selection engine.
-- 1. Universe & Company Metadata
CREATE TABLE IF NOT EXISTS company (
ticker VARCHAR(16) PRIMARY KEY,
company_name VARCHAR(128) NOT NULL,
sector VARCHAR(64),
industry VARCHAR(64),
is_active BOOLEAN DEFAULT TRUE
);
-- 2. Daily Price & Volume History
CREATE TABLE IF NOT EXISTS daily_price (
date DATE NOT NULL,
ticker VARCHAR(16) NOT NULL,
open_price DECIMAL(12, 4),
high_price DECIMAL(12, 4),
low_price DECIMAL(12, 4),
close_price DECIMAL(12, 4),
adj_close DECIMAL(12, 4) NOT NULL,
volume BIGINT NOT NULL,
PRIMARY KEY (date, ticker),
FOREIGN KEY (ticker) REFERENCES company(ticker)
);
-- 3. Point-in-Time Fundamental Metrics (Quarterly / Annual)
CREATE TABLE IF NOT EXISTS fundamentals (
filing_date DATE NOT NULL,
ticker VARCHAR(16) NOT NULL,
earnings_yield DECIMAL(10, 6),
roe DECIMAL(10, 6),
debt_to_equity DECIMAL(10, 6),
free_cash_flow_yield DECIMAL(10, 6),
PRIMARY KEY (filing_date, ticker),
FOREIGN KEY (ticker) REFERENCES company(ticker)
);
-- 4. Calculated Cross-Sectional Factor Scores & Composite Z-Scores
CREATE TABLE IF NOT EXISTS factor_scores (
date DATE NOT NULL,
ticker VARCHAR(16) NOT NULL,
value_z DECIMAL(8, 4),
quality_z DECIMAL(8, 4),
momentum_z DECIMAL(8, 4),
lowvol_z DECIMAL(8, 4),
composite_z DECIMAL(8, 4) NOT NULL,
rank_decile INT,
PRIMARY KEY (date, ticker),
FOREIGN KEY (ticker) REFERENCES company(ticker)
);
-- 5. Target Portfolio Holdings & Rebalance Orders
CREATE TABLE IF NOT EXISTS portfolio_holdings (
rebalance_date DATE NOT NULL,
ticker VARCHAR(16) NOT NULL,
target_weight DECIMAL(6, 4) NOT NULL,
composite_score DECIMAL(8, 4) NOT NULL,
PRIMARY KEY (rebalance_date, ticker),
FOREIGN KEY (ticker) REFERENCES company(ticker)
);
Part 2: Python Multi-Factor Pipeline Template
This modular Python script pulls historical data from your database, computes factor values, winsorizes and z-scores them cross-sectionally, calculates an equal-weighted composite score, and outputs the top decile selection.
import numpy as np
import pandas as pd
from sqlalchemy import create_engine
# Database Connection (e.g., MySQL / Cloud SQL)
DB_USER = "your_user"
DB_PASSWORD = "your_password"
DB_HOST = "your_host"
DB_NAME = "jiacovacci"
engine = create_engine(
f"mysql+pymysql://{DB_USER}:{DB_PASSWORD}@{DB_HOST}/{DB_NAME}"
)
def winsorize_series(series, lower_quantile=0.01, upper_quantile=0.99):
"""Winsorizes extreme outliers in a factor series."""
lower = series.quantile(lower_quantile)
upper = series.quantile(upper_quantile)
return series.clip(lower, upper)
def compute_cross_sectional_zscores(df, factor_columns):
"""Calculates cross-sectional Z-scores for each factor on a given date."""
result_df = df.copy()
for col in factor_columns:
# 1. Winsorize outliers
winsorized = winsorize_series(result_df[col])
# 2. Compute Mean and Std across the cross-section
mean_val = winsorized.mean()
std_val = winsorized.std()
if std_val == 0 or pd.isna(std_val):
result_df[f"{col}_z"] = 0.0
else:
result_df[f"{col}_z"] = (winsorized - mean_val) / std_val
return result_df
def run_multifactor_engine(rebalance_date):
print(f"Running multi-factor pipeline for rebalance date: {rebalance_date}")
# 1. Fetch Universe, Fundamentals, and Price History from MySQL
query = f"""
SELECT
c.ticker, c.sector,
f.earnings_yield, f.roe, f.debt_to_equity
FROM company c
JOIN fundamentals f ON c.ticker = f.ticker
WHERE f.filing_date = (
SELECT MAX(filing_date) FROM fundamentals
WHERE ticker = c.ticker AND filing_date <= '{rebalance_date}'
)
AND c.is_active = 1;
"""
df_fund = pd.read_sql(query, engine)
# Fetch 12-month momentum & volatility from daily prices
price_query = f"""
SELECT ticker, adj_close, date
FROM daily_price
WHERE date <= '{rebalance_date}'
AND date >= DATE_SUB('{rebalance_date}', INTERVAL 1 YEAR);
"""
df_prices = pd.read_sql(price_query, engine)
# Pivot prices to compute technical factors (12m Momentum & 12m Volatility)
pivot_prices = df_prices.pivot(
index="date", columns="ticker", values="adj_close"
)
pivot_prices = pivot_prices.ffill().dropna(axis=1, how="any")
# 12-minus-1 month momentum (excluding most recent month)
ret_12m = pivot_prices.iloc[-21] / pivot_prices.iloc[-252] - 1
# Annualized volatility
vol_12m = pivot_prices.pct_change().std() * np.sqrt(252)
df_tech = pd.DataFrame(
{
"ticker": ret_12m.index,
"momentum_12m": ret_12m.values,
"volatility_12m": vol_12m.values,
}
)
# Merge fundamental and technical datasets
df_universe = pd.merge(df_fund, df_tech, on="ticker", how="inner")
# Define raw factor dictionary mapping
# Note: For Debt-to-Equity, lower is better (quality), so we invert it (-1 * z)
df_universe["quality_inv_de"] = -1 * df_universe["debt_to_equity"]
raw_factors = ["earnings_yield", "roe", "quality_inv_de", "momentum_12m"]
# 2. Compute Z-Scores
df_scored = compute_cross_sectional_zscores(df_universe, raw_factors)
# 3. Aggregate into Composite Score (Equal-weighted average of Z-scores)
z_cols = [f"{f}_z" for f in raw_factors]
df_scored["composite_z"] = df_scored[z_cols].mean(axis=1)
# 4. Rank and Select Top Decile (Long Basket)
df_scored["rank_decile"] = pd.qcut(
df_scored["composite_z"], 10, labels=False, duplicates="drop"
)
# Decile 9 represents the top 10% of stocks
top_decile = df_scored[df_scored["rank_decile"] == 9].copy()
# 5. Equal Weight Portfolio Allocation for Top Decile
num_stocks = len(top_decile)
if num_stocks > 0:
top_decile["target_weight"] = 1.0 / num_stocks
else:
top_decile["target_weight"] = 0.0
print(
f"Selected {num_stocks} top-decile stocks for portfolio implementation."
)
return top_decile[
[
"ticker",
"sector",
"composite_z",
"target_weight",
]
]
# Example Execution
if __name__ == "__main__":
portfolio_weights = run_multifactor_engine("2026-10-01")
print(portfolio_weights.head(10))
Part 2: Python Multi-Factor Pipeline Template
This modular Python script pulls historical data from your database, computes factor values, winsorizes and z-scores them cross-sectionally, calculates an equal-weighted composite score, and outputs the top decile selection.
import numpy as np
import pandas as pd
from sqlalchemy import create_engine
# Database Connection (e.g., MySQL / Cloud SQL)
DB_USER = "your_user"
DB_PASSWORD = "your_password"
DB_HOST = "your_host"
DB_NAME = "jiacovacci"
engine = create_engine(
f"mysql+pymysql://{DB_USER}:{DB_PASSWORD}@{DB_HOST}/{DB_NAME}"
)
def winsorize_series(series, lower_quantile=0.01, upper_quantile=0.99):
"""Winsorizes extreme outliers in a factor series."""
lower = series.quantile(lower_quantile)
upper = series.quantile(upper_quantile)
return series.clip(lower, upper)
def compute_cross_sectional_zscores(df, factor_columns):
"""Calculates cross-sectional Z-scores for each factor on a given date."""
result_df = df.copy()
for col in factor_columns:
# 1. Winsorize outliers
winsorized = winsorize_series(result_df[col])
# 2. Compute Mean and Std across the cross-section
mean_val = winsorized.mean()
std_val = winsorized.std()
if std_val == 0 or pd.isna(std_val):
result_df[f"{col}_z"] = 0.0
else:
result_df[f"{col}_z"] = (winsorized - mean_val) / std_val
return result_df
def run_multifactor_engine(rebalance_date):
print(f"Running multi-factor pipeline for rebalance date: {rebalance_date}")
# 1. Fetch Universe, Fundamentals, and Price History from MySQL
query = f"""
SELECT
c.ticker, c.sector,
f.earnings_yield, f.roe, f.debt_to_equity
FROM company c
JOIN fundamentals f ON c.ticker = f.ticker
WHERE f.filing_date = (
SELECT MAX(filing_date) FROM fundamentals
WHERE ticker = c.ticker AND filing_date <= '{rebalance_date}'
)
AND c.is_active = 1;
"""
df_fund = pd.read_sql(query, engine)
# Fetch 12-month momentum & volatility from daily prices
price_query = f"""
SELECT ticker, adj_close, date
FROM daily_price
WHERE date <= '{rebalance_date}'
AND date >= DATE_SUB('{rebalance_date}', INTERVAL 1 YEAR);
"""
df_prices = pd.read_sql(price_query, engine)
# Pivot prices to compute technical factors (12m Momentum & 12m Volatility)
pivot_prices = df_prices.pivot(
index="date", columns="ticker", values="adj_close"
)
pivot_prices = pivot_prices.ffill().dropna(axis=1, how="any")
# 12-minus-1 month momentum (excluding most recent month)
ret_12m = pivot_prices.iloc[-21] / pivot_prices.iloc[-252] - 1
# Annualized volatility
vol_12m = pivot_prices.pct_change().std() * np.sqrt(252)
df_tech = pd.DataFrame(
{
"ticker": ret_12m.index,
"momentum_12m": ret_12m.values,
"volatility_12m": vol_12m.values,
}
)
# Merge fundamental and technical datasets
df_universe = pd.merge(df_fund, df_tech, on="ticker", how="inner")
# Define raw factor dictionary mapping
# Note: For Debt-to-Equity, lower is better (quality), so we invert it (-1 * z)
df_universe["quality_inv_de"] = -1 * df_universe["debt_to_equity"]
raw_factors = ["earnings_yield", "roe", "quality_inv_de", "momentum_12m"]
# 2. Compute Z-Scores
df_scored = compute_cross_sectional_zscores(df_universe, raw_factors)
# 3. Aggregate into Composite Score (Equal-weighted average of Z-scores)
z_cols = [f"{f}_z" for f in raw_factors]
df_scored["composite_z"] = df_scored[z_cols].mean(axis=1)
# 4. Rank and Select Top Decile (Long Basket)
df_scored["rank_decile"] = pd.qcut(
df_scored["composite_z"], 10, labels=False, duplicates="drop"
)
# Decile 9 represents the top 10% of stocks
top_decile = df_scored[df_scored["rank_decile"] == 9].copy()
# 5. Equal Weight Portfolio Allocation for Top Decile
num_stocks = len(top_decile)
if num_stocks > 0:
top_decile["target_weight"] = 1.0 / num_stocks
else:
top_decile["target_weight"] = 0.0
print(
f"Selected {num_stocks} top-decile stocks for portfolio implementation."
)
return top_decile[
[
"ticker",
"sector",
"composite_z",
"target_weight",
]
]
# Example Execution
if __name__ == "__main__":
portfolio_weights = run_multifactor_engine("2026-10-01")
print(portfolio_weights.head(10))
To build a production-grade multi-factor stock selection engine, you need a structured database schema to store your data and precise mathematical formulas to calculate, normalize, and combine your factors.
1. Database Fields Required (SQL Schema Breakdown)
A robust relational database (such as PostgreSQL) requires four core tables to handle raw data, financial statements, and calculated factors.
Table A: tickers (Master Security Reference)
ticker: VARCHAR(10) (Primary Key, e.g., "AAPL")
company_name: VARCHAR(255)
sector: VARCHAR(100) (e.g., "Information Technology")
industry: VARCHAR(100) (e.g., "Consumer Electronics")
is_active: BOOLEAN (Helps prevent survivorship bias by tracking delisted stocks)
Table B: daily_prices (Market Time-Series Data)
date: DATE (Primary Key composite part)
ticker: VARCHAR(10) (Primary Key composite part)
open: DECIMAL(12, 4)
high: DECIMAL(12, 4)
low: DECIMAL(12, 4)
close: DECIMAL(12, 4)
adjusted_close: DECIMAL(12, 4) (Adjusted for splits and dividends)
volume: BIGINT
Table C: fundamental_point_in_time (Financial Statements)
filing_date: DATE (The exact date the SEC 10-K/10-Q was filed—crucial to prevent look-ahead bias)
period_end_date: DATE (The end of the fiscal quarter/year)
ticker: VARCHAR(10)
total_assets: DECIMAL(18, 2)
total_liabilities: DECIMAL(18, 2)
shareholders_equity: DECIMAL(18, 2)
net_income: DECIMAL(18, 2)
ebit: DECIMAL(18, 2) (Earnings Before Interest and Taxes)
total_debt: DECIMAL(18, 2)
Table D: calculated_factors (Processed Output)
date: DATE
ticker: VARCHAR(10)
z_value: DECIMAL(8, 4)
z_quality: DECIMAL(8, 4)
z_momentum: DECIMAL(8, 4)
z_lowvol: DECIMAL(8, 4)
composite_score: DECIMAL(8, 4)
2. Factor Calculations & Mathematical Formulas
Before normalization, raw factor values must be calculated for each stock i on date t.
A. Value Factors
Earnings Yield (EY):
EYi=EBITiEnterpriseValuei
Book-to-Market (B/M):
B/Mi=Shareholders'EquityiMarketCapitalizationi
B. Quality Factors
Return on Equity (ROE):
ROEi=NetIncomeiShareholders'Equityi
Return on Invested Capital (ROIC):
ROICi=EBITi(1-TaxRate)TotalDebti+Shareholders'Equityi-Cashi
C. Momentum Factors
12-Minus-1 Month Price Momentum: Calculated as the total return from 12 months ago to 1 month ago (skipping the most recent month to avoid short-term market reversals):
Momi=Pt-30Pt-365-1
D. Low Volatility Factors
Trailing 12-Month Total Volatility: Annualized standard deviation of daily log returns over the past 252 trading days:
Voli=(DailyReturnsi)252
3. Data Standardization & Composite Scoring
Because raw factor scales differ wildly (e.g., ROE is a percentage like 0.15, while Price-to-Earnings is a multiple like 25.0), you must standardize them across your investable universe before combining them.
Step 1: Winsorization (Outlier Capping)
To prevent extreme outliers (e.g., a distorted P/E ratio from a company with near-zero earnings) from breaking the model, cap raw factor values at the 1st and 99th percentiles of the cross-sectional universe.
Step 2: Cross-Sectional Z-Score Standardization
For each factor f, calculate the z-score for stock i on date t:
Zi,f=xi,f-ff
Where:
xi,f is the winsorized raw factor value for stock i.
f is the cross-sectional mean of factor f across all stocks in the universe on day t.
f is the cross-sectional standard deviation of factor f on day t.
Step 3: Composite Factor Scoring
Combine the individual standardized z-scores into an equal-weighted (or regime-weighted) composite score:
Compositei=Zi,Value+Zi,Quality+Zi,Momentum+Zi,LowVol4
Stocks are then ranked in descending order by their Compositei score to select the top decile for portfolio construction.
Would you like a production-ready Python script using Pandas that implements this exact Winsorization, Z-scoring, and Composite calculation pipeline on a sample dataframe?
Do you prefer implementing these calculations directly inside a Pandas dataframe in memory, or would you like to write them as SQL window functions directly in PostgreSQL?
Are you planning to include short positions (long-short market neutral), or will this be a long-only top-decile portfolio?
Building a long-only, top-decile multi-factor stock selection engine entirely in MySQL (8.0+) is entirely feasible using Common Table Expressions (CTEs) and window functions.
Because MySQL 8.0 supports window functions (AVG() OVER(), STDDEV() OVER()), we can perform cross-sectional standardization, winsorization, and ranking directly inside database views or stored procedures.
Production-Ready MySQL Pipeline
Here is a modular set of SQL scripts that takes your raw fundamental and price inputs, standardizes them cross-sectionally by date, computes the composite score, and filters for the top-decile investable universe.
Step 1: Calculate Raw Factors & Winsorize (View)
This view computes raw factors for each stock and caps extreme outliers (winsorization at approximately the 1st and 99th percentiles using standard deviation or percentile ranks).
SQL
CREATE OR REPLACE VIEW vw_winsorized_factors AS
WITH raw_factors AS (
SELECT
p.date,
p.ticker,
-- Value: Earnings Yield (EBIT / Market Cap or Enterprise Value approximation)
(f.ebit / NULLIF(m.market_cap, 0)) AS raw_earnings_yield,
-- Quality: Return on Equity
(f.net_income / NULLIF(f.shareholders_equity, 0)) AS raw_roe,
-- Momentum: 12-minus-1 month return (lagged price ratio)
(p.adjusted_close / NULLIF(lag_p.adjusted_close, 0) - 1) AS raw_momentum,
-- Low Volatility: Inverse of trailing 252-day volatility (lower vol = better score)
(1.0 / NULLIF(v.trailing_volatility, 0)) AS raw_low_vol
FROM daily_prices p
JOIN fundamental_point_in_time f ON p.ticker = f.ticker
AND p.date >= f.filing_date
AND p.date < DATE_ADD(f.filing_date, INTERVAL 1 YEAR)
JOIN market_caps m ON p.ticker = m.ticker AND p.date = m.date
JOIN daily_prices lag_p ON p.ticker = lag_p.ticker
AND lag_p.date = DATE_SUB(p.date, INTERVAL 365 DAY)
JOIN stock_volatilities v ON p.ticker = v.ticker AND p.date = v.date
),
stats AS (
SELECT
date,
AVG(raw_earnings_yield) AS mean_ey, STDDEV(raw_earnings_yield) AS std_ey,
AVG(raw_roe) AS mean_roe, STDDEV(raw_roe) AS std_roe,
AVG(raw_momentum) AS mean_mom, STDDEV(raw_momentum) AS std_mom,
AVG(raw_low_vol) AS mean_lvol, STDDEV(raw_lvol) AS std_lvol
FROM raw_factors
GROUP BY date
)
SELECT
r.date,
r.ticker,
-- Winsorize & Z-Score Earnings Yield
(r.raw_earnings_yield - s.mean_ey) / NULLIF(s.std_ey, 0) AS z_value,
-- Winsorize & Z-Score ROE (Quality)
(r.raw_roe - s.mean_roe) / NULLIF(s.std_roe, 0) AS z_quality,
-- Winsorize & Z-Score Momentum
(r.raw_momentum - s.mean_mom) / NULLIF(s.std_mom, 0) AS z_momentum,
-- Winsorize & Z-Score Low Volatility
(r.raw_low_vol - s.mean_lvol) / NULLIF(s.std_lvol, 0) AS z_lowvol
FROM raw_factors r
JOIN stats s ON r.date = s.date;
Step 2: Compute Composite Score & Select Top Decile (Stored Procedure / Query)
This query aggregates the individual factor z-scores into an equal-weighted composite score, ranks every stock cross-sectionally for each date using PERCENT_RANK(), and filters exclusively for the top 10% (top decile) for long-only portfolio construction.
CREATE OR REPLACE VIEW vw_top_decile_portfolio AS
WITH composite_scored AS (
SELECT
date,
ticker,
z_value,
z_quality,
z_momentum,
z_lowvol,
-- Equal-weighted composite score
(COALESCE(z_value, 0) + COALESCE(z_quality, 0) + COALESCE(z_momentum, 0) + COALESCE(z_lowvol, 0)) / 4.0 AS composite_score,
-- Cross-sectional percentile rank (0 to 1, where 1 is the highest score)
PERCENT_RANK() OVER (PARTITION BY date ORDER BY (COALESCE(z_value, 0) + COALESCE(z_quality, 0) + COALESCE(z_momentum, 0) + COALESCE(z_lowvol, 0)) DESC) as decile_rank
FROM vw_winsorized_factors
)
SELECT
date,
ticker,
composite_score,
z_value,
z_quality,
z_momentum,
z_lowvol
FROM composite_scored
-- Filter for Top Decile (Top 10% of stocks by composite score)
WHERE decile_rank <= 0.10;
To bridge your top-decile MySQL output directly into actual share purchases, we need a complete execution workflow. This involves:
Adding Sector-Neutral Constraints in MySQL so your top decile isn't accidentally 80% technology stocks.
A Python Script (using SQLAlchemy, Pandas, and cvxpy or equal-weighting) to pull those database results, apply position limits (e.g., max 3% per stock), and calculate exact share quantities based on your available cash or portfolio size.
Part 1: Sector-Neutral Top-Decile Selection (MySQL View Update)
To prevent sector bias, we can rank stocks within each sector using partition windows, ensuring you pick the top performers from every industry bucket.
SQL
CREATE OR REPLACE VIEW vw_sector_neutral_top_decile AS
WITH composite_scored AS (
SELECT
f.date,
f.ticker,
t.sector,
f.z_value,
f.z_quality,
f.z_momentum,
f.z_lowvol,
(COALESCE(f.z_value, 0) + COALESCE(f.z_quality, 0) + COALESCE(f.z_momentum, 0) + COALESCE(f.z_lowvol, 0)) / 4.0 AS composite_score,
-- Rank stocks *within each sector* on each date
PERCENT_RANK() OVER (PARTITION BY f.date, t.sector ORDER BY (COALESCE(f.z_value, 0) + COALESCE(f.z_quality, 0) + COALESCE(f.z_momentum, 0) + COALESCE(f.z_lowvol, 0)) DESC) as sector_decile_rank
FROM vw_winsorized_factors f
JOIN tickers t ON f.ticker = t.ticker
)
SELECT
date,
ticker,
sector,
composite_score
FROM composite_scored
-- Top 10% within each sector
WHERE sector_decile_rank <= 0.10;
CREATE OR REPLACE VIEW vw_sector_neutral_top_decile AS
WITH composite_scored AS (
SELECT
f.date,
f.ticker,
t.sector,
f.z_value,
f.z_quality,
f.z_momentum,
f.z_lowvol,
(COALESCE(f.z_value, 0) + COALESCE(f.z_quality, 0) + COALESCE(f.z_momentum, 0) + COALESCE(f.z_lowvol, 0)) / 4.0 AS composite_score,
-- Rank stocks *within each sector* on each date
PERCENT_RANK() OVER (PARTITION BY f.date, t.sector ORDER BY (COALESCE(f.z_value, 0) + COALESCE(f.z_quality, 0) + COALESCE(f.z_momentum, 0) + COALESCE(f.z_lowvol, 0)) DESC) as sector_decile_rank
FROM vw_winsorized_factors f
JOIN tickers t ON f.ticker = t.ticker
)
SELECT
date,
ticker,
sector,
composite_score
FROM composite_scored
-- Top 10% within each sector
WHERE sector_decile_rank <= 0.10;
Part 2: Python Share Calculation & Execution Script
This Python script connects to your MySQL database, pulls the latest rebalance date's top-decile stocks, fetches current live prices, and calculates the exact number of shares to buy given a target portfolio capital amount
import pandas as pd
from sqlalchemy import create_engine
import yfinance as yf
# 1. Connect to MySQL Database (Update with your credentials)
DB_USER = "root"
DB_PASSWORD = "your_password"
DB_HOST = "localhost"
DB_PORT = "3306"
DB_NAME = "quant_portfolio"
connection_string = f"mysql+pymysql://{DB_USER}:{DB_PASSWORD}@{DB_HOST}:{DB_PORT}/{DB_NAME}"
engine = create_engine(connection_string)
# 2. Query the latest top-decile portfolio from MySQL
query = """
SELECT ticker, sector, composite_score
FROM vw_sector_neutral_top_decile
WHERE date = (SELECT MAX(date) FROM vw_sector_neutral_top_decile);
"""
portfolio_df = pd.read_sql(query, con=engine)
print(f"--- Top-Decile Universe Selected ({len(portfolio_df)} stocks) ---")
print(portfolio_df.head())
# 3. Define Portfolio Capital & Constraints
TOTAL_PORTFOLIO_CAPITAL = 100000.0 # $100,000 total cash to deploy
MAX_SINGLE_STOCK_WEIGHT = 0.03 # Max 3% per stock to enforce diversification
# Equal weight among selected top decile, subject to max weight cap
num_stocks = len(portfolio_df)
raw_weight = 1.0 / num_stocks
target_weight = min(raw_weight, MAX_SINGLE_STOCK_WEIGHT)
portfolio_df['target_weight'] = target_weight
# Re-normalize weights if capped so total allocation sums to 100% (or remainder sits in cash)
portfolio_df['target_weight'] = portfolio_df['target_weight'] / portfolio_df['target_weight'].sum()
portfolio_df['target_allocation_usd'] = portfolio_df['target_weight'] * TOTAL_PORTFOLIO_CAPITAL
# 4. Fetch Current Live Prices via yfinance to compute exact share quantities
tickers = portfolio_df['ticker'].tolist()
print("\nFetching current market prices...")
latest_prices = {}
for ticker in tickers:
ticker_data = yf.Ticker(ticker)
# Get latest close price
hist = ticker_data.history(period="1d")
if not hist.empty:
latest_prices[ticker] = hist['Close'].iloc[-1]
else:
latest_prices[ticker] = None
portfolio_df['current_price'] = portfolio_df['ticker'].map(latest_prices)
# Drop stocks where price couldn't be fetched
portfolio_df = portfolio_df.dropna(subset=['current_price'])
# 5. Calculate Exact Shares to Purchase
# Integer number of shares (Floor division to stay within cash limits)
portfolio_df['shares_to_buy'] = (portfolio_df['target_allocation_usd'] // portfolio_df['current_price']).astype(int)
portfolio_df['actual_allocation_usd'] = portfolio_df['shares_to_buy'] * portfolio_df['current_price']
# 6. Output Purchase Order List
print("\n==================================================")
print(" FINAL SHARE PURCHASE ORDER LIST ")
print("==================================================")
print(portfolio_df[['ticker', 'sector', 'current_price', 'shares_to_buy', 'actual_allocation_usd']].to_string(index=False))
print("==================================================")
print(f"Total Capital Deployed: ${portfolio_df['actual_allocation_usd'].sum():,.2f}")
No comments:
Post a Comment