Returns
Daily return = today's price divided by yesterday's price, minus 1
Technical notationr_t = P_t / P_(t-1) - 1Turns price history into comparable percentage movements.
Research-to-production portfolio analytics
Quantitative Portfolio Research Pipeline
A reproducible ETF research workflow built around SQL market data, Python and pandas analytics, constrained portfolio optimization, MATLAB model comparison, walk-forward testing, and documented financial calculations.
Historical research only / no live trading
Annual return12.2%geometric
Annual volatility11.7%daily observations
Sharpe ratio0.793% risk-free rate
Maximum drawdown-18.3%peak to trough
Rebalances20walk-forward
Research question
The project asks whether a constrained ETF portfolio can improve risk-adjusted performance without using future data.
The optimizer did not beat SPY on raw return.
That is a credible outcome, not a weakness. The objective was risk-adjusted return under constraints, not manufacturing an unrealistic backtest.
It produced a smoother risk profile.
In the published sample, the optimized portfolio had lower volatility, lower drawdown, lower daily VaR, and a higher Sharpe ratio than the benchmark.
System architecture
The report is generated from a repeatable pipeline rather than one-off spreadsheet analysis.
Input
Historical ETF data
Frozen adjusted prices are loaded and checked before calculations begin.
Storage
SQLite and SQL
Queries use grouping, window functions, and validation checks around ticker/date records.
Research
Python analytics
Pandas and NumPy calculate returns, covariance, volatility, drawdown, Sharpe, and VaR.
Output
Optimizer and report
Constrained weights feed a walk-forward backtest, cost model, charts, and documented results.
Walk-forward methodology
At each rebalance, the optimizer only sees the previous 36 months. The chosen weights are then frozen for the next quarter.
Jan 2024 rebalance
Jan 2021 to Dec 2023
Estimate expected returns and covariance using past data.
Jan 2024 to Mar 2024
Hold the calculated weights out of sample.
Apr 2024 rebalance
Apr 2021 to Mar 2024
Roll the estimation window forward by one quarter.
Apr 2024 to Jun 2024
Evaluate the next period without exposing future observations.
Out-of-sample performance
Weights are estimated from the previous 36 months, then held until the next quarterly rebalance. Costs are charged when allocations change.
Risk and allocation
The strategy caps every asset at 30% and remains fully invested without leverage or short positions.
Latest optimized weights
AGG30.0%
GLD30.0%
SPY30.0%
EFA6.8%
EEM3.2%
EWC0.0%
Allocation through time
Selected rebalance snapshots show whether the optimizer is stable, dynamic, or repeatedly hitting the 30% asset cap.
2021 Q1
2022 Q1
2023 Q1
2024 Q1
Latest
SPYEWCEFAAGGGLDEEM
Cross-asset structure
Daily return correlations are estimated from the frozen research dataset.
Strategy comparison
| Strategy | Return | Volatility | Sharpe | Max drawdown | Daily VaR |
|---|---|---|---|---|---|
| Optimized portfolio | 12.2% | 11.7% | 0.79 | -18.3% | 1.1% |
| Benchmark (SPY) | 14.9% | 17.1% | 0.69 | -24.5% | 1.7% |
Methodology and equations
The main page keeps the math compact. The notebook contains the full implementation and outputs.
Portfolio risk
Portfolio risk depends on weights, asset volatility, and how assets move together
Technical notationportfolio variance = weights transposed x covariance matrix x weightsCaptures diversification effects that are not visible from single assets alone.
Sharpe ratio
Sharpe ratio = return above the risk-free rate divided by portfolio volatility
Technical notationSharpe = (portfolio return - risk-free rate) / portfolio riskCompares return against the amount of risk required to earn it.
Optimization objective
Find the best constrained asset mix for the selected objective
Technical notationmaximize Sharpe or minimize variance, with weights adding to 100% and each asset capped at 30%Keeps the allocation fully invested, long-only, and bounded.
Additional methodology details
- Annualized return converts the compounded period return into a yearly rate.
- Annualized volatility scales daily volatility by the square root of 252 trading days.
- Drawdown measures the loss from a previous portfolio high.
- 95% VaR estimates a daily loss threshold exceeded about 5% of the time.
- Transaction cost uses gross traded notional multiplied by the cost rate.
- Correlation is used to identify whether assets are likely to diversify each other.
Engineering and validation
The project emphasizes reviewable calculations, not just attractive charts.
pytest checks
Return calculations, weight normalization, asset caps, drawdowns, transaction costs, and backtest chronology are tested.
MATLAB implementation
The minimum-variance optimization is reproduced in MATLAB and compared against Python within numerical tolerance.
Repeatable assumptions
Lookback period, rebalance frequency, maximum asset weight, risk-free rate, transaction cost, and benchmark are controlled from one research configuration.