{
"cells": [
{
"cell_type": "markdown",
"id": "3264daf4",
"metadata": {},
"source": [
"# Quantitative Investment Analytics ETL Pipeline\n",
"\n",
"Four Canadian equity portfolios, auditable SQL analytics, and purged volatility prediction. This notebook reads the last completed pipeline run. Charts are embedded as PNG outputs for notebook viewers. See the source modules for the executable implementation."
]
},
{
"cell_type": "code",
"execution_count": 1,
"id": "58165ccf",
"metadata": {
"execution": {
"iopub.execute_input": "2026-09-09T05:55:21.303663Z",
"iopub.status.busy": "2026-09-09T05:55:21.303531Z",
"iopub.status.idle": "2026-09-09T05:55:22.143343Z",
"shell.execute_reply": "2026-09-09T05:55:22.142880Z"
}
},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"Yahoo Finance via yfinance\n",
"{'start': '2018-01-01', 'end': '2026-09-01', 'benchmark': 'XIC.TO', 'initial_capital': 100000, 'risk_free_rate': 0.03, 'transaction_cost_bps': 10, 'warmup_days': 252, 'forecast_days': 20, 'test_fraction': 0.2, 'seed': 42}\n"
]
},
{
"data": {
"text/html": [
"\n",
"\n",
"
\n",
" \n",
" \n",
" cumulative_return \n",
" annualized_return \n",
" volatility \n",
" sharpe \n",
" max_drawdown \n",
" portfolio_id \n",
" total_cost \n",
" turnover \n",
" excess_annualized_return \n",
" \n",
" \n",
" \n",
" \n",
" 0 \n",
" 2.798174 \n",
" 0.191215 \n",
" 0.162911 \n",
" 0.974572 \n",
" -0.287301 \n",
" Balanced \n",
" 776.605214 \n",
" 3.625783 \n",
" 0.028992 \n",
" \n",
" \n",
" 1 \n",
" 2.147465 \n",
" 0.162223 \n",
" 0.163749 \n",
" 0.820370 \n",
" -0.372117 \n",
" Benchmark \n",
" 0.000000 \n",
" 0.000000 \n",
" 0.000000 \n",
" \n",
" \n",
" 2 \n",
" 5.492029 \n",
" 0.277950 \n",
" 0.286007 \n",
" 0.897374 \n",
" -0.487163 \n",
" Growth \n",
" 1856.877502 \n",
" 5.196306 \n",
" 0.115728 \n",
" \n",
" \n",
" 3 \n",
" 2.129369 \n",
" 0.161344 \n",
" 0.160645 \n",
" 0.827860 \n",
" -0.330561 \n",
" Income \n",
" 395.514165 \n",
" 2.350359 \n",
" -0.000878 \n",
" \n",
" \n",
" 4 \n",
" 2.435912 \n",
" 0.175662 \n",
" 0.152889 \n",
" 0.942033 \n",
" -0.297091 \n",
" Low volatility \n",
" 573.676954 \n",
" 2.992934 \n",
" 0.013439 \n",
" \n",
" \n",
"
\n",
"
"
]
},
"metadata": {},
"output_type": "display_data"
}
],
"source": [
"daily = pd.read_sql('SELECT * FROM portfolio_daily_summary ORDER BY date', conn, parse_dates=['date'])\n",
"fig, axes = plt.subplots(2, 1, figsize=(12, 8), sharex=True)\n",
"for name, group in daily.groupby('portfolio_id'):\n",
" axes[0].plot(group.date, group.nav, label=name)\n",
" axes[1].plot(group.date, group.drawdown * 100, label=name)\n",
"axes[0].set(title='Portfolio value after transaction costs', ylabel='CAD')\n",
"axes[1].set(title='Drawdown', ylabel='% below peak')\n",
"axes[0].legend(ncol=3)\n",
"for ax in axes: ax.grid(alpha=.2)\n",
"plt.tight_layout()\n",
"plt.show()"
]
},
{
"cell_type": "markdown",
"id": "ab584ec8",
"metadata": {},
"source": [
"## SQL data mart\n",
"The view joins position facts to the security dimension. Monthly returns use a CTE and LAG over month-end NAV."
]
},
{
"cell_type": "code",
"execution_count": 3,
"id": "8e45daa1",
"metadata": {
"execution": {
"iopub.execute_input": "2026-09-09T05:55:22.503246Z",
"iopub.status.busy": "2026-09-09T05:55:22.503030Z",
"iopub.status.idle": "2026-09-09T05:55:22.669243Z",
"shell.execute_reply": "2026-09-09T05:55:22.668981Z"
}
},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"-- Latest allocations, with sector names obtained from the security dimension.\n",
"CREATE VIEW latest_sector_exposure AS\n",
"WITH latest AS (SELECT MAX(date) AS date FROM portfolio_positions)\n",
"SELECT p.portfolio_id, s.sector, SUM(p.market_value) AS market_value,\n",
" SUM(p.weight) AS weight\n",
"FROM portfolio_positions p\n",
"JOIN securities s ON s.ticker = p.ticker\n",
"JOIN latest l ON l.date = p.date\n",
"GROUP BY p.portfolio_id, s.sector;\n",
"\n",
"-- Compound monthly returns from month-end NAV using a window function.\n",
"CREATE VIEW monthly_portfolio_returns AS\n",
"WITH ranked AS (\n",
" SELECT *, SUBSTR(date, 1, 7) AS month,\n",
" ROW_NUMBER() OVER (PARTITION BY portfolio_id, SUBSTR(date, 1, 7) ORDER BY date DESC) AS rn\n",
" FROM portfolio_daily_summary\n",
"), endpoints AS (\n",
" SELECT portfolio_id, month, nav FROM ranked WHERE rn = 1\n",
")\n",
"SELECT portfolio_id, month, nav,\n",
" nav / LAG(nav) OVER (PARTITION BY portfolio_id ORDER BY month) - 1 AS monthly_return\n",
"FROM endpoints;\n",
"\n",
"-- Daily cross-sectional ranking by trailing annualized volatility.\n",
"CREATE VIEW volatility_ranking AS\n",
"SELECT date, ticker, rolling_volatility_20,\n",
" DENSE_RANK() OVER (PARTITION BY date ORDER BY rolling_volatility_20) AS volatility_rank\n",
"FROM security_daily_analytics WHERE rolling_volatility_20 IS NOT NULL;\n",
"\n",
"CREATE VIEW rolling_portfolio_returns AS\n",
"SELECT date, portfolio_id,\n",
" nav / LAG(nav, 20) OVER (PARTITION BY portfolio_id ORDER BY date) - 1 AS return_20_sessions\n",
"FROM portfolio_daily_summary;\n",
"\n"
]
},
{
"data": {
"text/html": [
"\n",
"\n",
"
\n",
" \n",
" \n",
" portfolio_id \n",
" sector \n",
" market_value \n",
" weight \n",
" \n",
" \n",
" \n",
" \n",
" 0 \n",
" Balanced \n",
" Energy \n",
" 54729.056875 \n",
" 0.144093 \n",
" \n",
" \n",
" 1 \n",
" Balanced \n",
" Financials \n",
" 94183.667480 \n",
" 0.247971 \n",
" \n",
" \n",
" 2 \n",
" Balanced \n",
" Industrials \n",
" 55795.837249 \n",
" 0.146902 \n",
" \n",
" \n",
" 3 \n",
" Balanced \n",
" Technology \n",
" 82793.440689 \n",
" 0.217982 \n",
" \n",
" \n",
" 4 \n",
" Balanced \n",
" Utilities \n",
" 92315.391825 \n",
" 0.243052 \n",
" \n",
" \n",
" 5 \n",
" Benchmark \n",
" Benchmark \n",
" 314746.547784 \n",
" 1.000000 \n",
" \n",
" \n",
" 6 \n",
" Growth \n",
" Financials \n",
" 90212.881038 \n",
" 0.138959 \n",
" \n",
" \n",
" 7 \n",
" Growth \n",
" Industrials \n",
" 120383.907598 \n",
" 0.185433 \n",
" \n",
" \n",
" 8 \n",
" Growth \n",
" Technology \n",
" 438606.100043 \n",
" 0.675607 \n",
" \n",
" \n",
" 9 \n",
" Income \n",
" Energy \n",
" 61403.248725 \n",
" 0.196216 \n",
" \n",
" \n",
" 10 \n",
" Income \n",
" Financials \n",
" 142866.983701 \n",
" 0.456536 \n",
" \n",
" \n",
" 11 \n",
" Income \n",
" Utilities \n",
" 108666.677989 \n",
" 0.347248 \n",
" \n",
" \n",
" 12 \n",
" Low volatility \n",
" Energy \n",
" 29400.047297 \n",
" 0.085567 \n",
" \n",
" \n",
" 13 \n",
" Low volatility \n",
" Financials \n",
" 132849.082620 \n",
" 0.386649 \n",
" \n",
" \n",
" 14 \n",
" Low volatility \n",
" Industrials \n",
" 37455.714306 \n",
" 0.109012 \n",
" \n",
" \n",
" 15 \n",
" Low volatility \n",
" Technology \n",
" 43578.712575 \n",
" 0.126833 \n",
" \n",
" \n",
" 16 \n",
" Low volatility \n",
" Utilities \n",
" 100307.672655 \n",
" 0.291939 \n",
" \n",
" \n",
"
\n",
"
"
]
},
"metadata": {},
"output_type": "display_data"
}
],
"source": [
"print((ROOT / 'sql/analysis_queries.sql').read_text())\n",
"exposure = pd.read_sql('SELECT * FROM latest_sector_exposure', conn)\n",
"display(exposure)\n",
"exposure[exposure.portfolio_id != 'Benchmark'].pivot(index='portfolio_id', columns='sector', values='weight').plot.bar(stacked=True, figsize=(10, 5))\n",
"plt.title('Latest sector exposure')\n",
"plt.ylabel('Portfolio weight')\n",
"plt.xticks(rotation=0)\n",
"plt.tight_layout()\n",
"plt.show()"
]
},
{
"cell_type": "markdown",
"id": "d022748e",
"metadata": {},
"source": [
"## Machine learning validation\n",
"Predict next-20-session volatility from past returns, volatility and market volume. All scaling is inside cross-validation. Training labels end before validation begins. Compare held-out RMSE with persistence; ML may lose."
]
},
{
"cell_type": "code",
"execution_count": 4,
"id": "7dcc0204",
"metadata": {
"execution": {
"iopub.execute_input": "2026-09-09T05:55:22.670485Z",
"iopub.status.busy": "2026-09-09T05:55:22.670368Z",
"iopub.status.idle": "2026-09-09T05:55:22.753059Z",
"shell.execute_reply": "2026-09-09T05:55:22.752632Z"
}
},
"outputs": [
{
"data": {
"text/html": [
"\n",
"\n",
"
\n",
" \n",
" \n",
" portfolio_id \n",
" model \n",
" rmse \n",
" r2 \n",
" selected_by_cv \n",
" \n",
" \n",
" \n",
" \n",
" 0 \n",
" Balanced \n",
" Linear regression \n",
" 0.043156 \n",
" -0.087686 \n",
" 0 \n",
" \n",
" \n",
" 1 \n",
" Balanced \n",
" Ridge \n",
" 0.041783 \n",
" -0.019568 \n",
" 1 \n",
" \n",
" \n",
" 2 \n",
" Balanced \n",
" Lasso \n",
" 0.041799 \n",
" -0.020322 \n",
" 0 \n",
" \n",
" \n",
" 3 \n",
" Balanced \n",
" ElasticNet \n",
" 0.042053 \n",
" -0.032806 \n",
" 0 \n",
" \n",
" \n",
" 4 \n",
" Balanced \n",
" Persistence baseline \n",
" 0.049813 \n",
" -0.449087 \n",
" 0 \n",
" \n",
" \n",
" 5 \n",
" Growth \n",
" Linear regression \n",
" 0.099861 \n",
" -0.057570 \n",
" 0 \n",
" \n",
" \n",
" 6 \n",
" Growth \n",
" Ridge \n",
" 0.099264 \n",
" -0.044973 \n",
" 1 \n",
" \n",
" \n",
" 7 \n",
" Growth \n",
" Lasso \n",
" 0.099706 \n",
" -0.054298 \n",
" 0 \n",
" \n",
" \n",
" 8 \n",
" Growth \n",
" ElasticNet \n",
" 0.099741 \n",
" -0.055036 \n",
" 0 \n",
" \n",
" \n",
" 9 \n",
" Growth \n",
" Persistence baseline \n",
" 0.131365 \n",
" -0.830095 \n",
" 0 \n",
" \n",
" \n",
" 10 \n",
" Income \n",
" Linear regression \n",
" 0.035779 \n",
" -0.382895 \n",
" 0 \n",
" \n",
" \n",
" 11 \n",
" Income \n",
" Ridge \n",
" 0.035118 \n",
" -0.332244 \n",
" 1 \n",
" \n",
" \n",
" 12 \n",
" Income \n",
" Lasso \n",
" 0.035205 \n",
" -0.338864 \n",
" 0 \n",
" \n",
" \n",
" 13 \n",
" Income \n",
" ElasticNet \n",
" 0.035291 \n",
" -0.345389 \n",
" 0 \n",
" \n",
" \n",
" 14 \n",
" Income \n",
" Persistence baseline \n",
" 0.031161 \n",
" -0.048943 \n",
" 0 \n",
" \n",
" \n",
" 15 \n",
" Low volatility \n",
" Linear regression \n",
" 0.037045 \n",
" -0.210236 \n",
" 0 \n",
" \n",
" \n",
" 16 \n",
" Low volatility \n",
" Ridge \n",
" 0.036026 \n",
" -0.144576 \n",
" 1 \n",
" \n",
" \n",
" 17 \n",
" Low volatility \n",
" Lasso \n",
" 0.036005 \n",
" -0.143252 \n",
" 0 \n",
" \n",
" \n",
" 18 \n",
" Low volatility \n",
" ElasticNet \n",
" 0.036156 \n",
" -0.152897 \n",
" 0 \n",
" \n",
" \n",
" 19 \n",
" Low volatility \n",
" Persistence baseline \n",
" 0.039793 \n",
" -0.396456 \n",
" 0 \n",
" \n",
" \n",
"
\n",
"
"
]
},
"metadata": {},
"output_type": "display_data"
},
{
"data": {
"text/html": [
"\n",
"\n",
"
\n",
" \n",
" \n",
" portfolio_id \n",
" fold \n",
" train_start \n",
" last_train_label \n",
" validation_start \n",
" validation_end \n",
" \n",
" \n",
" \n",
" \n",
" 0 \n",
" Balanced \n",
" 0 \n",
" 2019-01-30 00:00:00 \n",
" 2020-04-07 00:00:00 \n",
" 2020-04-08 00:00:00 \n",
" 2021-06-14 00:00:00 \n",
" \n",
" \n",
" 1 \n",
" Balanced \n",
" 1 \n",
" 2019-01-30 00:00:00 \n",
" 2021-06-14 00:00:00 \n",
" 2021-06-15 00:00:00 \n",
" 2022-08-19 00:00:00 \n",
" \n",
" \n",
" 2 \n",
" Balanced \n",
" 2 \n",
" 2019-01-30 00:00:00 \n",
" 2022-08-19 00:00:00 \n",
" 2022-08-22 00:00:00 \n",
" 2023-10-26 00:00:00 \n",
" \n",
" \n",
" 3 \n",
" Balanced \n",
" 3 \n",
" 2019-01-30 00:00:00 \n",
" 2023-10-26 00:00:00 \n",
" 2023-10-27 00:00:00 \n",
" 2025-01-02 00:00:00 \n",
" \n",
" \n",
" 4 \n",
" Growth \n",
" 0 \n",
" 2019-01-30 00:00:00 \n",
" 2020-04-07 00:00:00 \n",
" 2020-04-08 00:00:00 \n",
" 2021-06-14 00:00:00 \n",
" \n",
" \n",
" 5 \n",
" Growth \n",
" 1 \n",
" 2019-01-30 00:00:00 \n",
" 2021-06-14 00:00:00 \n",
" 2021-06-15 00:00:00 \n",
" 2022-08-19 00:00:00 \n",
" \n",
" \n",
" 6 \n",
" Growth \n",
" 2 \n",
" 2019-01-30 00:00:00 \n",
" 2022-08-19 00:00:00 \n",
" 2022-08-22 00:00:00 \n",
" 2023-10-26 00:00:00 \n",
" \n",
" \n",
" 7 \n",
" Growth \n",
" 3 \n",
" 2019-01-30 00:00:00 \n",
" 2023-10-26 00:00:00 \n",
" 2023-10-27 00:00:00 \n",
" 2025-01-02 00:00:00 \n",
" \n",
" \n",
" 8 \n",
" Income \n",
" 0 \n",
" 2019-01-30 00:00:00 \n",
" 2020-04-07 00:00:00 \n",
" 2020-04-08 00:00:00 \n",
" 2021-06-14 00:00:00 \n",
" \n",
" \n",
" 9 \n",
" Income \n",
" 1 \n",
" 2019-01-30 00:00:00 \n",
" 2021-06-14 00:00:00 \n",
" 2021-06-15 00:00:00 \n",
" 2022-08-19 00:00:00 \n",
" \n",
" \n",
" 10 \n",
" Income \n",
" 2 \n",
" 2019-01-30 00:00:00 \n",
" 2022-08-19 00:00:00 \n",
" 2022-08-22 00:00:00 \n",
" 2023-10-26 00:00:00 \n",
" \n",
" \n",
" 11 \n",
" Income \n",
" 3 \n",
" 2019-01-30 00:00:00 \n",
" 2023-10-26 00:00:00 \n",
" 2023-10-27 00:00:00 \n",
" 2025-01-02 00:00:00 \n",
" \n",
" \n",
" 12 \n",
" Low volatility \n",
" 0 \n",
" 2019-01-30 00:00:00 \n",
" 2020-04-07 00:00:00 \n",
" 2020-04-08 00:00:00 \n",
" 2021-06-14 00:00:00 \n",
" \n",
" \n",
" 13 \n",
" Low volatility \n",
" 1 \n",
" 2019-01-30 00:00:00 \n",
" 2021-06-14 00:00:00 \n",
" 2021-06-15 00:00:00 \n",
" 2022-08-19 00:00:00 \n",
" \n",
" \n",
" 14 \n",
" Low volatility \n",
" 2 \n",
" 2019-01-30 00:00:00 \n",
" 2022-08-19 00:00:00 \n",
" 2022-08-22 00:00:00 \n",
" 2023-10-26 00:00:00 \n",
" \n",
" \n",
" 15 \n",
" Low volatility \n",
" 3 \n",
" 2019-01-30 00:00:00 \n",
" 2023-10-26 00:00:00 \n",
" 2023-10-27 00:00:00 \n",
" 2025-01-02 00:00:00 \n",
" \n",
" \n",
"
\n",
"