Portfolio Performance & Risk Analytics

A SQL-only performance and risk engine, queried directly by Excel/Power BI.

What it calculates

Metric	Method
Daily portfolio value	SUM(quantity × close_price) per holding, per day
Daily / cumulative returns	LAG() window function + log-return compounding
Rolling 30-day annualized volatility	correlated subquery over a trailing window, STDDEV × √252
Sharpe ratio	(annualized return − risk-free rate) / annualized volatility
Maximum drawdown	running peak via MAX() OVER (... ROWS UNBOUNDED PRECEDING), then min of (value − peak)/peak
Brinson-Fachler sector attribution	allocation effect + selection effect, decomposing active return by sector

Dataset

• 262 trading days (2024 calendar, weekdays only)
• 10 securities across 5 sectors (Technology, Financials, Healthcare, Energy, Consumer)
• 2 portfolios: a "Model Portfolio" with a deliberate active tilt (overweight Tech/Healthcare, underweight Energy) and a "Benchmark Index" at roughly equal sector weights — so the attribution query has something real to explain.

Repo structure

sql/
  01_schema.sql              -- tables: portfolios, securities, prices, holdings, calendar
  02_seed_data.sql            -- synthetic calendar + random-walk prices + holdings, all in SQL
  03_returns_and_risk.sql     -- views: daily value, returns, rolling vol, Sharpe, drawdown
  04_attribution.sql          -- Brinson-Fachler sector attribution
sample_output/
  summary_stats.csv
  brinson_attribution.csv
  cumulative_index_model_portfolio.csv
  cumulative_index_benchmark.csv
  cumulative_performance_chart.png
build.sh                      -- rebuilds the SQLite DB and runs everything end-to-end

Running it

git clone <this-repo>
cd portfolio-risk-sql
./build.sh

This produces db/portfolio.db, which you can open directly in DB Browser for SQLite, or connect to from Excel (Power Query → ODBC) or Power BI for a live dashboard.

Note on reproducibility: prices are generated with SQLite's RANDOM(), which reseeds on every run, so re-running build.sh will produce a slightly different (but structurally identical) dataset than the numbers below. The sample_output/ files are a snapshot from one run, kept as a reference so the repo is browsable without executing anything.

Sample results

Summary stats (annualised):

Portfolio	Return	Volatility	Sharpe	Max Drawdown
Model Portfolio	14.7%	6.5%	1.96	-3.48%
Benchmark Index	7.8%	5.9%	0.99	-3.32%

Brinson attribution (Model vs. Benchmark, full period):

Sector	Portfolio Weight	Benchmark Weight	Allocation Effect	Selection Effect
Energy	4.4%	20.0%	+4.53%	0.00%
Technology	36.7%	20.0%	+3.58%	-0.59%
Healthcare	22.2%	20.0%	-0.25%	+1.49%
Consumer	20.0%	20.0%	0.00%	0.00%
Financials	16.7%	20.0%	-0.51%	0.00%

The effects sum to the portfolio's period excess return over the benchmark (small residual from rounding) — that reconciliation is the standard sanity check performance teams run on a live attribution report. Here, being massively underweight the worst-performing sector (Energy, -20.9%) was the single biggest driver of outperformance — a classic allocation-effect story.

Design notes

• The benchmark is modelled as a second portfolio (same holdings table, different weights) rather than a separate price series. This means every return/risk/drawdown query works identically for both — no duplicated logic, and relative performance is just a JOIN on portfolio_id.
• All time-series logic (returns, drawdown, rolling vol) uses window functions, not procedural loops.
• Views are used instead of one-shot queries so the calculation chain (v_daily_value → v_daily_returns → v_summary_stats) is auditable step by step, which mirrors how a real performance/risk data model is laid out.