from pathlib import Path
import json, sqlite3
import pandas as pd
import matplotlib.pyplot as plt
from IPython.display import display
%matplotlib inline
ROOT = Path.cwd()
if not (ROOT / 'output').exists():
    ROOT = ROOT.parent
manifest = json.loads((ROOT / 'output/run_manifest.json').read_text())
print(manifest['source']['source'])
print(manifest['config'])
conn = sqlite3.connect(ROOT / 'output/investment_analytics.db')
summary = pd.read_sql('SELECT * FROM portfolio_summary', conn)
display(summary)

Yahoo Finance via yfinance
{'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, 'simulation_years': 5, 'simulation_paths': 5000, 'bootstrap_block_days': 20, 'trading_days': 252, 'seed': 42}

daily = pd.read_sql('SELECT * FROM portfolio_daily_summary ORDER BY date', conn, parse_dates=['date'])
fig, axes = plt.subplots(2, 1, figsize=(12, 8), sharex=True)
for name, group in daily.groupby('portfolio_id'):
    axes[0].plot(group.date, group.nav, label=name)
    axes[1].plot(group.date, group.drawdown * 100, label=name)
axes[0].set(title='Portfolio value after transaction costs', ylabel='CAD')
axes[1].set(title='Drawdown', ylabel='% below peak')
axes[0].legend(ncol=3)
for ax in axes: ax.grid(alpha=.2)
plt.tight_layout()
plt.show()

from IPython.display import Image
for filename in ['forward_indexed.png', 'scenario_downside.png', 'scenario_drawdowns.png', 'forward_ranges.png']:
    display(Image(filename=str(ROOT / 'output' / filename)))
for table in ['forward_projection_summary', 'scenario_downside', 'scenario_paired', 'scenario_sensitivity']:
    display(pd.read_sql(f'SELECT * FROM {table}', conn))
display(Image(filename=str(ROOT / 'output' / 'forward_scenarios.png')))

print((ROOT / 'sql/analysis_queries.sql').read_text())
exposure = pd.read_sql('SELECT * FROM latest_sector_exposure', conn)
display(exposure)
exposure[exposure.portfolio_id != 'Benchmark'].pivot(index='portfolio_id', columns='sector', values='weight').plot.bar(stacked=True, figsize=(10, 5))
plt.title('Latest sector exposure')
plt.ylabel('Portfolio weight')
plt.xticks(rotation=0)
plt.tight_layout()
plt.show()

-- Latest allocations, with sector names obtained from the security dimension.
CREATE VIEW latest_sector_exposure AS
WITH latest AS (SELECT MAX(date) AS date FROM portfolio_positions)
SELECT p.portfolio_id, s.sector, SUM(p.market_value) AS market_value,
       SUM(p.weight) AS weight
FROM portfolio_positions p
JOIN securities s ON s.ticker = p.ticker
JOIN latest l ON l.date = p.date
GROUP BY p.portfolio_id, s.sector;

-- Compound monthly returns from month-end NAV using a window function.
CREATE VIEW monthly_portfolio_returns AS
WITH ranked AS (
 SELECT *, SUBSTR(date, 1, 7) AS month,
 ROW_NUMBER() OVER (PARTITION BY portfolio_id, SUBSTR(date, 1, 7) ORDER BY date DESC) AS rn
 FROM portfolio_daily_summary
), endpoints AS (
 SELECT portfolio_id, month, nav FROM ranked WHERE rn = 1
)
SELECT portfolio_id, month, nav,
 nav / LAG(nav) OVER (PARTITION BY portfolio_id ORDER BY month) - 1 AS monthly_return
FROM endpoints;

-- Daily cross-sectional ranking by trailing annualized volatility.
CREATE VIEW volatility_ranking AS
SELECT date, ticker, rolling_volatility_20,
 DENSE_RANK() OVER (PARTITION BY date ORDER BY rolling_volatility_20) AS volatility_rank
FROM security_daily_analytics WHERE rolling_volatility_20 IS NOT NULL;

CREATE VIEW rolling_portfolio_returns AS
SELECT date, portfolio_id,
 nav / LAG(nav, 20) OVER (PARTITION BY portfolio_id ORDER BY date) - 1 AS return_20_sessions
FROM portfolio_daily_summary;

scores = pd.read_sql('SELECT * FROM model_scores', conn)
display(scores[['portfolio_id', 'model', 'rmse', 'r2', 'selected_by_cv']])
scores.pivot(index='portfolio_id', columns='model', values='rmse').plot.bar(figsize=(12, 5))
plt.title('Held-out volatility prediction RMSE: lower is better')
plt.xticks(rotation=0)
plt.tight_layout()
plt.show()
display(pd.read_sql('SELECT * FROM validation_splits', conn))

import hashlib
import json
from datetime import datetime, timezone

import numpy as np
import pandas as pd


def extract(root, config, source):
    securities = pd.read_csv(root / "data/securities.csv")
    holdings = pd.read_csv(root / "data/holdings.csv")
    path = root / "data" / ("synthetic_prices.csv" if source == "synthetic" else "cached_prices.csv")
    metadata_path = path.with_suffix(".json")
    if source == "download":
        import yfinance as yf
        frames = []
        for ticker in securities.ticker:
            data = yf.download(ticker, start=config["start"], end=config["end"],
                               auto_adjust=False, progress=False, threads=False)
            if data.empty:
                raise ValueError(f"No market data returned for {ticker}; use --source synthetic explicitly for a demo.")
            if isinstance(data.columns, pd.MultiIndex):
                data.columns = data.columns.get_level_values(0)
            frames.append(pd.DataFrame({"date": data.index.strftime("%Y-%m-%d"),
                                        "ticker": ticker, "adjusted_price": data["Adj Close"].to_numpy(),
                                        "volume": data.Volume.to_numpy()}))
        prices = pd.concat(frames, ignore_index=True)
    elif source == "synthetic":
        rng = np.random.default_rng(config["seed"])
        dates = pd.bdate_range(config["start"], pd.Timestamp(config["end"]) - pd.Timedelta(days=1))
        market = rng.normal(0.00025, 0.01, len(dates))
        frames = []
        for row in securities.itertuples():
            scale = {"Technology": 0.018, "Utilities": 0.006}.get(row.sector, 0.009)
            r = market * (0.65 if row.sector == "Utilities" else 1) + rng.normal(0, scale, len(dates))
            frames.append(pd.DataFrame({"date": dates, "ticker": row.ticker,
                                        "adjusted_price": 100 * np.cumprod(1 + r),
                                        "volume": rng.integers(100000, 3000000, len(dates))}))
        prices = pd.concat(frames, ignore_index=True)
    else:
        prices = pd.read_csv(path)
        metadata = json.loads(metadata_path.read_text())
        if metadata["sha256"] != hashlib.sha256(path.read_bytes()).hexdigest():
            raise ValueError("Cached price checksum does not match provenance metadata")
        return prices, holdings, securities, metadata
    prices.to_csv(path, index=False)
    metadata = {"source": "Yahoo Finance via yfinance" if source == "download" else "SYNTHETIC DEMONSTRATION",
                "retrieved_at": datetime.now(timezone.utc).isoformat(),
                "sha256": hashlib.sha256(path.read_bytes()).hexdigest(),
                "start": config["start"], "end_exclusive": config["end"],
                "price_basis": "Dividend/split adjusted close; CAD"}
    metadata_path.write_text(json.dumps(metadata, indent=2))
    return prices, holdings, securities, metadata

import numpy as np
import pandas as pd


def clean_inputs(prices, holdings, securities):
    prices = prices.copy()
    prices["date"] = pd.to_datetime(prices.date, errors="raise")
    for col in ("adjusted_price", "volume"):
        prices[col] = pd.to_numeric(prices[col], errors="raise")
    if prices.isna().any().any() or not np.isfinite(prices[["adjusted_price", "volume"]]).all().all():
        raise ValueError("Missing or non-finite price fields")
    if not prices.adjusted_price.gt(0).all() or not prices.volume.ge(0).all():
        raise ValueError("Prices must be positive and volume nonnegative")
    before = len(prices)
    prices = prices.drop_duplicates()
    if prices.duplicated(["date", "ticker"]).any():
        raise ValueError("Conflicting duplicate ticker-date records")
    if securities.ticker.duplicated().any() or securities.isna().any().any():
        raise ValueError("Invalid security master")
    if set(prices.ticker) != set(securities.ticker):
        raise ValueError("Price universe does not match security master")
    if holdings.isna().any().any() or holdings.duplicated(["portfolio_id", "ticker"]).any():
        raise ValueError("Invalid or duplicate holdings")
    if not set(holdings.ticker) <= set(securities.ticker):
        raise ValueError("Unknown security in holdings")
    if not np.isfinite(holdings.target_weight).all() or not holdings.target_weight.ge(0).all():
        raise ValueError("Holdings must have finite nonnegative weights")
    if not np.allclose(holdings.groupby("portfolio_id").target_weight.sum(), 1):
        raise ValueError("Portfolio weights must sum to one")
    if set(securities.currency) != {"CAD"}:
        raise ValueError("This pipeline requires a single CAD currency basis")
    prices = prices.sort_values(["ticker", "date"]).reset_index(drop=True)
    wide = prices.pivot(index="date", columns="ticker", values="adjusted_price")
    if wide.isna().any().any():
        raise ValueError("Missing ticker-date prices; repair the source instead of silently filling")
    return prices, {"exact_duplicates_removed": before - len(prices), "missing_prices": 0,
                    "price_records": len(prices), "securities": len(securities), "dates": len(wide)}


def validate_outputs(daily, positions):
    if not np.isfinite(daily[["net_return", "nav"]]).all().all() or not daily.nav.gt(0).all():
        raise ValueError("Invalid portfolio results")
    if not np.allclose(positions.groupby(["date", "portfolio_id"]).weight.sum(), 1):
        raise ValueError("Position weights do not reconcile")
    totals = positions.groupby(["date", "portfolio_id"]).market_value.sum()
    expected = daily.set_index(["date", "portfolio_id"]).nav.reindex(totals.index)
    if not np.allclose(totals, expected):
        raise ValueError("Position values do not reconcile to NAV")

import numpy as np
import pandas as pd


def metrics(returns, risk_free_rate=0.03):
    returns = pd.Series(returns).dropna()
    wealth = (1 + returns).cumprod()
    drawdown = wealth / wealth.cummax().clip(lower=1) - 1
    std = returns.std(ddof=1)
    rf_daily = (1 + risk_free_rate) ** (1 / 252) - 1
    return {"cumulative_return": wealth.iloc[-1] - 1,
            "annualized_return": wealth.iloc[-1] ** (252 / len(returns)) - 1,
            "volatility": std * np.sqrt(252),
            "sharpe": (returns.mean() - rf_daily) / std * np.sqrt(252) if std > 0 else np.nan,
            "max_drawdown": drawdown.min()}


def security_analytics(prices):
    groups = []
    for _, group in prices.groupby("ticker", sort=True):
        group = group.sort_values("date").copy()
        group["daily_return"] = group.adjusted_price.pct_change(fill_method=None)
        group["cumulative_return"] = group.adjusted_price / group.adjusted_price.iloc[0] - 1
        group["moving_average_20"] = group.adjusted_price.rolling(20).mean()
        group["rolling_volatility_20"] = group.daily_return.rolling(20).std() * np.sqrt(252)
        group["drawdown"] = group.adjusted_price / group.adjusted_price.cummax() - 1
        group["max_drawdown_to_date"] = group.drawdown.cummin()
        group["average_volume_20"] = group.volume.rolling(20).mean()
        groups.append(group)
    return pd.concat(groups, ignore_index=True)


def portfolio_analytics(prices, holdings, securities, config):
    wide = prices.pivot(index="date", columns="ticker", values="adjusted_price").sort_index()
    returns = wide.pct_change(fill_method=None)
    warmup = config["warmup_days"]
    if len(wide) < warmup + 300:
        raise ValueError("At least 300 evaluation days plus the warmup period are required")
    assets = sorted(set(wide.columns) - {config["benchmark"]})
    targets = {name: group.set_index("ticker").target_weight.reindex(wide.columns, fill_value=0)
               for name, group in holdings.groupby("portfolio_id")}
    # Freeze inverse-volatility weights using only pre-investment observations.
    inv = 1 / returns.iloc[1:warmup + 1][assets].std()
    if not np.isfinite(inv).all():
        raise ValueError("Low-volatility calibration requires nonzero finite volatility")
    targets["Low volatility"] = (inv / inv.sum()).reindex(wide.columns, fill_value=0)
    targets["Benchmark"] = pd.Series({config["benchmark"]: 1.0}).reindex(wide.columns, fill_value=0)
    rows, position_rows, trades = [], [], []
    for name, target in targets.items():
        nav, weights = config["initial_capital"], target.copy()
        initial_date = wide.index[warmup]
        # Initial positions are assumed funded at the calibration close; entry cost excluded for all strategies.
        rows.append({"date": initial_date, "portfolio_id": name, "nav": nav,
                     "gross_return": 0., "net_return": 0., "turnover": 0., "cost": 0.})
        for date in wide.index[warmup + 1:]:
            r = returns.loc[date]
            gross = float(weights @ r)
            before_cost = nav * (1 + gross)
            drifted = weights * (1 + r) / (1 + gross)
            index = wide.index.get_loc(date)
            rebalance = date.month != wide.index[index - 1].month and name != "Benchmark"
            turnover = float((target - drifted).abs().sum()) if rebalance else 0.
            cost = before_cost * turnover * config["transaction_cost_bps"] / 10000
            new_nav = before_cost - cost
            rows.append({"date": date, "portfolio_id": name, "nav": new_nav,
                         "gross_return": gross, "net_return": new_nav / nav - 1,
                         "turnover": turnover, "cost": cost})
            if rebalance:
                trades.append({"date": date, "portfolio_id": name, "turnover": turnover, "cost": cost})
            weights = target.copy() if rebalance else drifted
            nav = new_nav
            for ticker in wide.columns:
                if weights[ticker] > 0:
                    position_rows.append({"date": date, "portfolio_id": name, "ticker": ticker,
                                          "adjusted_units": nav * weights[ticker] / wide.loc[date, ticker],
                                          "market_value": nav * weights[ticker], "weight": weights[ticker]})
    daily = pd.DataFrame(rows)
    daily["cumulative_return"] = daily.nav / config["initial_capital"] - 1
    daily["drawdown"] = daily.nav / daily.groupby("portfolio_id").nav.cummax() - 1
    positions = pd.DataFrame(position_rows).merge(securities, on="ticker", validate="many_to_one")
    sectors = positions.groupby(["date", "portfolio_id", "sector"], as_index=False).agg(
        market_value=("market_value", "sum"), weight=("weight", "sum"))
    summary = []
    for name, group in daily.groupby("portfolio_id"):
        row = metrics(group.net_return.iloc[1:], config["risk_free_rate"])
        row.update(portfolio_id=name, total_cost=group.cost.sum(), turnover=group.turnover.sum())
        summary.append(row)
    summary = pd.DataFrame(summary)
    benchmark_return = summary.loc[summary.portfolio_id == "Benchmark", "annualized_return"].iloc[0]
    summary["excess_annualized_return"] = summary.annualized_return - benchmark_return
    target_table = pd.concat([v.rename("weight").rename_axis("ticker").reset_index().assign(portfolio_id=k)
                              for k, v in targets.items()], ignore_index=True)
    return daily, positions, sectors, summary, pd.DataFrame(trades), target_table

"""Forward-looking portfolio scenarios built from completed historical returns."""

import numpy as np
import pandas as pd


def scenario_paths(daily, config, circular=False):
    """Create deterministic block-bootstrap projections without using future data.

    The simulation starts immediately after the final backtest date. Consecutive
    blocks of realized net returns preserve short-run market behaviour while the
    percentile bands communicate uncertainty rather than a point prediction.
    """
    paths = int(config["simulation_paths"])
    days = int(config["simulation_years"] * config["trading_days"])
    block = int(config["bootstrap_block_days"])
    if paths < 100 or days < 1 or block < 1:
        raise ValueError("Invalid forward-scenario configuration")

    actual = daily.sort_values(["date", "portfolio_id"]).copy()
    returns = actual.pivot(index="date", columns="portfolio_id", values="net_return").iloc[1:]
    initial_capital = float(config["initial_capital"])
    if not np.isfinite(initial_capital) or initial_capital <= 0:
        raise ValueError("Initial scenario capital must be positive and finite")
    starts = pd.Series(initial_capital, index=returns.columns)
    if len(returns) < block:
        raise ValueError("Not enough completed return history for the selected bootstrap block")

    # Map 252 modeled sessions per year to an exact calendar horizon, not an exchange calendar.
    dates = pd.date_range(returns.index.max(), returns.index.max() + pd.DateOffset(years=int(config["simulation_years"])), periods=days + 1)
    rng = np.random.default_rng(config["seed"])
    blocks_needed = int(np.ceil(days / block))
    block_starts = rng.integers(0, len(returns) if circular else len(returns) - block + 1, size=(paths, blocks_needed))
    sampled_index = (block_starts[:, :, None] + np.arange(block)).reshape(paths, -1)[:, :days]
    if circular:
        sampled_index %= len(returns)
    for portfolio_id in returns.columns:
        series = returns[portfolio_id].to_numpy(dtype=float)
        sampled_returns = series[sampled_index]
        values = starts[portfolio_id] * np.cumprod(1 + sampled_returns, axis=1)
        values = np.column_stack([np.full(paths, starts[portfolio_id]), values])
        yield portfolio_id, dates, values


def forward_scenarios(daily, config):
    quantiles = [0.05, 0.25, 0.50, 0.75, 0.95]
    bands, summaries = [], []
    for portfolio_id, dates, values in scenario_paths(daily, config):
        percentile_values = np.quantile(values, quantiles, axis=0)
        bands.append(pd.DataFrame({
            "date": dates,
            "portfolio_id": portfolio_id,
            "p05": percentile_values[0],
            "p25": percentile_values[1],
            "median": percentile_values[2],
            "p75": percentile_values[3],
            "p95": percentile_values[4],
        }))
        drawdowns = values / np.maximum.accumulate(values, axis=1) - 1
        terminal = values[:, -1]
        summaries.append({
            "portfolio_id": portfolio_id,
            "as_of_date": dates[0],
            "start_value": config['initial_capital'],
            "terminal_p05": np.quantile(terminal, 0.05),
            "terminal_median": np.quantile(terminal, 0.50),
            "terminal_p95": np.quantile(terminal, 0.95),
            "probability_terminal_loss": np.mean(terminal < config['initial_capital']),
            "median_max_drawdown": np.quantile(drawdowns.min(axis=1), 0.50),
            "scenario_paths": config['simulation_paths'],
            "horizon_years": config["simulation_years"],
            "bootstrap_block_days": config['bootstrap_block_days'],
        })
    return pd.concat(bands, ignore_index=True), pd.DataFrame(summaries)

"""Downside and paired comparisons under declared sampling assumptions."""

import numpy as np
import pandas as pd

from .forward import scenario_paths


def paired_outcomes(left, right, left_name='Growth', right_name='Balanced'):
    difference = np.asarray(left) - np.asarray(right)
    return dict(left_portfolio=left_name, right_portfolio=right_name,
                probability_underperformance=float(np.mean(difference < 0)),
                difference_p05=float(np.quantile(difference, .05)),
                difference_median=float(np.median(difference)),
                difference_p95=float(np.quantile(difference, .95)))


def risk_review(daily, config):
    experiments = [('Published model', config['bootstrap_block_days'], False),
                   ('126-session moving blocks', 126, False),
                   ('252-session moving blocks', 252, False),
                   ('126-session circular blocks', 126, True),
                   ('252-session circular blocks', 252, True)]
    downside, sensitivity, paired = [], [], []
    capital = config['initial_capital']
    history_days = daily.date.nunique() - 1
    for label, block, circular in experiments:
        if block > history_days:
            continue
        terminals = {}
        for name, dates, values in scenario_paths(daily, dict(config, bootstrap_block_days=block), circular):
            final = values[:, -1]
            terminals[name] = final
            p05 = np.quantile(final, .05)
            sensitivity.append(dict(experiment=label, portfolio_id=name, block_days=block,
                circular=circular, paths=len(final), seed=config['seed'],
                terminal_p05=p05, terminal_median=np.median(final),
                probability_terminal_loss=np.mean(final < capital)))
            if label == 'Published model':
                worst = (values / np.maximum.accumulate(values, axis=1) - 1).min(axis=1)
                downside.append(dict(portfolio_id=name, start_value=capital,
                    terminal_p01=np.quantile(final, .01), terminal_p05=p05,
                    worst5_mean=np.mean(final[final <= p05]),
                    probability_terminal_loss=np.mean(final < capital),
                    terminal_loss_count=np.sum(final < capital), paths=len(final),
                    median_max_drawdown=np.median(worst), severe_max_drawdown_p05=np.quantile(worst,.05),
                    probability_ever_below_80pct=np.mean(values.min(axis=1) < .8 * capital)))
        if 'Growth' in terminals and 'Balanced' in terminals:
            paired.append(dict(experiment=label, **paired_outcomes(terminals['Growth'], terminals['Balanced'])))
    return pd.DataFrame(downside), pd.DataFrame(sensitivity), pd.DataFrame(paired)

"""Shared risk charts and source-backed explanation for the report and website."""

import html
import matplotlib
matplotlib.use('Agg')
import matplotlib.pyplot as plt
from matplotlib.ticker import FuncFormatter
import numpy as np

from .scenario_charts import PALETTE


def risk_charts(output, downside):
    data = downside.set_index('portfolio_id').reindex(PALETTE).dropna()
    y = np.arange(len(data))
    fig, ax = plt.subplots(figsize=(11, 4.8), dpi=180)
    for field, label, marker, color, offset in [
        ('terminal_p01', '1st percentile', 'v', '#b43a43', -.18),
        ('worst5_mean', 'Average of worst 5%', 'D', '#7b529d', 0),
        ('terminal_p05', '5th-percentile cutoff', 'o', '#256f94', .18)]:
        ax.scatter(data[field], y+offset, marker=marker, color=color, label=label, s=45, zorder=3)
        for i, value in enumerate(data[field]):
            ax.annotate(f'${value:,.0f}', (value, i+offset), xytext=(7,0), textcoords='offset points', va='center', fontsize=9)
    capital = data.start_value.iloc[0]
    ax.axvline(capital, color='#252d32', ls='--', lw=1, label=f'Initial ${capital:,.0f}')
    ax.set_xlim(0, max(capital, data.terminal_p05.max()) * 1.25)
    ax.set_yticks(y, data.index)
    ax.invert_yaxis()
    ax.xaxis.set_major_formatter(FuncFormatter(lambda v,_: f'${v:,.0f}'))
    ax.set_xlabel('Ending portfolio value (CAD)')
    ax.set_title('Adverse five-year outcomes from equal investments', loc='left', pad=45)
    ax.legend(loc='lower left', bbox_to_anchor=(0,1.01), ncol=2, frameon=False, fontsize=9)
    ax.grid(axis='x', alpha=.18)
    fig.tight_layout()
    fig.savefig(output/'scenario_downside.png', facecolor='white')
    plt.close(fig)
    fig, ax = plt.subplots(figsize=(11,4.8), dpi=180)
    ax.barh(y-.16, -data.median_max_drawdown*100, height=.3, color='#347f80', label='Median maximum drawdown')
    ax.barh(y+.16, -data.severe_max_drawdown_p05*100, height=.3, color='#b43a43', label='Severe drawdown threshold (5% of paths are worse)')
    for i,row in enumerate(data.itertuples()):
        for offset,value in [(-.16,-row.median_max_drawdown*100),(.16,-row.severe_max_drawdown_p05*100)]:
            ax.text(value+1,i+offset,f'{value:.1f}%',va='center',fontsize=10)
    ax.set_yticks(y,data.index)
    ax.invert_yaxis()
    ax.set_xlim(0,min(110,max(-data.severe_max_drawdown_p05*100)*1.2))
    ax.set_xlabel('Size of peak-to-trough loss during the five years (%)')
    ax.set_title('Losses along the way, even when ending wealth recovers',loc='left',pad=42)
    ax.legend(loc='lower left',bbox_to_anchor=(0,1.01),frameon=False,fontsize=9)
    ax.grid(axis='x',alpha=.18)
    fig.tight_layout()
    fig.savefig(output/'scenario_drawdowns.png',facecolor='white')
    plt.close(fig)


def risk_section(tables, config, prefix=''):
    d = tables['scenario_downside'].copy()
    for key in ['terminal_p01','terminal_p05','worst5_mean']:
        d[key] = d[key].map(lambda v:f'${v:,.0f}')
    for key in ['probability_terminal_loss','probability_ever_below_80pct']:
        d[key] = d[key].map(lambda v:f'{v:.2%}')
    d = d[['portfolio_id','terminal_p01','terminal_p05','worst5_mean','probability_terminal_loss','probability_ever_below_80pct']]
    d.columns=['Portfolio','1st percentile','5th percentile','Worst-5% average','Finish below initial capital','Ever lose over 20% of initial capital']
    paired = tables['scenario_paired']
    paired_text = ''
    base = paired[paired.experiment == 'Published model'] if not paired.empty else paired
    if not base.empty:
        r = base.iloc[0]
        paired_text = f'''<h3>Growth versus Balanced in the same market scenarios</h3>
<p>Subtract Balanced's ending value from Growth's in each shared simulated market sequence. Growth finishes below Balanced in <strong>{r.probability_underperformance:.2%}</strong> of paths. The median difference is <strong>${r.difference_median:+,.0f}</strong>; the 5th-percentile difference is <strong>${r.difference_p05:+,.0f}</strong>. These are conditional simulation results.</p>
<p>Subtracting the portfolios' separate 5th percentiles does not give the 5th percentile of their difference. Their individual adverse outcomes need not occur in the same path. The paired shortfall is not a maximum loss.</p>'''
    s = tables['scenario_sensitivity'].copy()
    s = s[s.portfolio_id.isin(['Growth','Balanced'])][['experiment','portfolio_id','terminal_p05','terminal_median','probability_terminal_loss']]
    for key in ['terminal_p05','terminal_median']:
        s[key] = s[key].map(lambda v:f'${v:,.0f}')
    s.probability_terminal_loss = s.probability_terminal_loss.map(lambda v:f'{v:.2%}')
    s.columns=['Sampling assumption','Portfolio','5th percentile','Median final value','Finish below initial capital']
    return f'''<section id="scenario-risk"><h3>How much could the investment lose?</h3>
<p>The lower fan boundary is a 5th-percentile cutoff: 5% of simulated ending balances lie below it. The average of that worst 5% describes the tail beyond the cutoff. Values below the initial CAD {config['initial_capital']:,.0f} represent losses of principal.</p>
<figure><a href="{prefix}scenario_downside.png" title="Open full-size downside chart"><img src="{prefix}scenario_downside.png" alt="Adverse ending balances compared with the initial investment" style="width:100%;height:auto"></a><figcaption>Same dollar scale across all portfolios. This view focuses on downside without compression from the large upside range.</figcaption></figure>
<div class="table-wrap table" style="overflow-x:auto" tabindex="0" role="region" aria-label="Scenario downside metrics">{d.to_html(index=False,border=0)}</div>
{paired_text}
<h3>What can happen before year five?</h3>
<figure><a href="{prefix}scenario_drawdowns.png" title="Open full-size drawdown chart"><img src="{prefix}scenario_drawdowns.png" alt="Median and severe simulated maximum drawdown for each portfolio" style="width:100%;height:auto"></a><figcaption>Each path's largest fall from a prior peak, including the initial balance. A peak can be above starting capital. Larger bars mean deeper losses.</figcaption></figure>
<h3>Does the conclusion survive different sampling assumptions?</h3>
<p>All comparisons use {config['simulation_paths']:,} paths, seed {config['seed']}, equal capital and the same completed history. Longer moving blocks retain longer sequences but underweight dates near the sample boundaries. Circular blocks wrap the end to the beginning, reducing that weighting effect while introducing artificial joins. The choice affects both dependence and sampled returns. No variant is selected because it produces a preferred ranking.</p>
<div class="table-wrap table" style="overflow-x:auto" tabindex="0" role="region" aria-label="Sampling sensitivity">{s.to_html(index=False,border=0)}</div>
<p><strong>Research finding:</strong> Growth's historical return advantage makes its median attractive under these assumptions, while several measures show greater downside than Balanced. A positive 5th percentile does not rule out principal losses. Higher volatility alone does not require every lower percentile to rank worst. The future return distribution remains unvalidated, and changing sampling assumptions can change the conclusion.</p>
<p>The bootstrap can generate worse compounded outcomes than any observed historical path by repeating adverse blocks. It cannot establish probabilities for risks absent from the sample. The separate volatility-model holdout does not validate these five-year return scenarios.</p>
<p><a href="{prefix}scenario_downside.csv">Download downside metrics</a> · <a href="{prefix}scenario_paired.csv">Download paired comparisons</a> · <a href="{prefix}scenario_sensitivity.csv">Download sampling sensitivity</a></p></section>'''

import numpy as np
import pandas as pd
from sklearn.linear_model import ElasticNet, Lasso, LinearRegression, Ridge
from sklearn.metrics import mean_squared_error, r2_score
from sklearn.model_selection import GridSearchCV, TimeSeriesSplit
from sklearn.pipeline import Pipeline
from sklearn.preprocessing import StandardScaler


FEATURES = ["return_1", "return_5", "return_20", "volatility_20", "market_return_20", "volume_ratio"]


def feature_frame(group, benchmark, volume, horizon):
    r = group.set_index("date").net_return.sort_index()
    frame = pd.DataFrame({"return_1": r, "return_5": (1 + r).rolling(5).apply(np.prod, raw=True) - 1,
                          "return_20": (1 + r).rolling(20).apply(np.prod, raw=True) - 1,
                          "volatility_20": r.rolling(20).std() * np.sqrt(252),
                          "market_return_20": (1 + benchmark).rolling(20).apply(np.prod, raw=True) - 1,
                          "volume_ratio": volume / volume.rolling(20).mean()})
    # The label at t contains returns t+1 through t+h; purge h rows at every split.
    frame["target"] = r.rolling(horizon).std().shift(-horizon) * np.sqrt(252)
    frame["target_end"] = pd.Series(r.index, index=r.index).shift(-horizon)
    return frame.replace([np.inf, -np.inf], np.nan).dropna()


def fit_models(daily, prices, config):
    benchmark = daily[daily.portfolio_id == "Benchmark"].set_index("date").net_return
    volume = prices[prices.ticker == config["benchmark"]].set_index("date").volume
    horizon = config["forecast_days"]
    scores, predictions, coefficients, audits = [], [], [], []
    candidates = {"Linear regression": (LinearRegression(), {}),
                  "Ridge": (Ridge(), {"model__alpha": [0.1, 1., 10., 100.]}),
                  "Lasso": (Lasso(max_iter=20000), {"model__alpha": [0.00001, 0.0001, 0.001]}),
                  "ElasticNet": (ElasticNet(max_iter=20000),
                                 {"model__alpha": [0.0001, 0.001], "model__l1_ratio": [0.25, 0.75]})}
    for name, group in daily.groupby("portfolio_id"):
        if name == "Benchmark":
            continue
        frame = feature_frame(group, benchmark, volume, horizon)
        split = int(len(frame) * (1 - config["test_fraction"]))
        train, test = frame.iloc[:split - horizon], frame.iloc[split:]
        if len(train) < 150 or len(test) < 30:
            raise ValueError("Insufficient history for purged chronological validation")
        if train.target_end.max() >= test.index.min():
            raise ValueError("Training labels overlap the test period")
        cv = TimeSeriesSplit(n_splits=4, gap=horizon)
        for fold, (tr, va) in enumerate(cv.split(train)):
            if train.iloc[tr].target_end.max() >= train.iloc[va].index.min():
                raise ValueError("Cross-validation label leakage")
            audits.append({"portfolio_id": name, "fold": fold, "train_start": train.iloc[tr].index.min(),
                           "last_train_label": train.iloc[tr].target_end.max(),
                           "validation_start": train.iloc[va].index.min(), "validation_end": train.iloc[va].index.max()})
        models = {}
        for model_name, (model, grid) in candidates.items():
            search = GridSearchCV(Pipeline([("scale", StandardScaler()), ("model", model)]), grid,
                                  cv=cv, scoring="neg_mean_squared_error", n_jobs=1)
            search.fit(train[FEATURES], train.target)
            models[model_name] = (np.maximum(search.predict(test[FEATURES]), 0), -search.best_score_, str(search.best_params_))
            for feature, value in zip(FEATURES, search.best_estimator_.named_steps["model"].coef_):
                coefficients.append({"portfolio_id": name, "model": model_name,
                                     "feature": feature, "coefficient": value})
        selected = min(models, key=lambda key: models[key][1])
        models["Persistence baseline"] = (test.volatility_20.to_numpy(), np.nan, "Trailing 20-day volatility")
        for model_name, (prediction, cv_mse, params) in models.items():
            scores.append({"portfolio_id": name, "model": model_name,
                           "rmse": np.sqrt(mean_squared_error(test.target, prediction)),
                           "r2": r2_score(test.target, prediction), "cv_mse": cv_mse,
                           "selected_by_cv": model_name == selected, "parameters": params,
                           "train_end": train.index[-1], "last_train_label": train.target_end.max(),
                           "test_start": test.index[0], "test_end": test.index[-1], "test_rows": len(test)})
            predictions.extend({"date": date, "portfolio_id": name, "model": model_name,
                                "actual": actual, "prediction": value}
                               for date, actual, value in zip(test.index, test.target, prediction))
    return pd.DataFrame(scores), pd.DataFrame(predictions), pd.DataFrame(coefficients), pd.DataFrame(audits)

import os
import sqlite3


def load_mart(output, tables, queries):
    path = output / "investment_analytics.db"
    temporary = output / "investment_analytics.tmp.db"
    try:
        with sqlite3.connect(temporary) as conn:
            if path.exists():
                with sqlite3.connect(path) as old:
                    old.backup(conn)
            for view in ("latest_sector_exposure", "monthly_portfolio_returns", "volatility_ranking", "rolling_portfolio_returns"):
                conn.execute(f'DROP VIEW IF EXISTS "{view}"')
            for name, frame in tables.items():
                frame.to_sql(name, conn, if_exists="append" if name == "pipeline_runs" else "replace", index=False)
            conn.executescript(queries)
            conn.execute("CREATE UNIQUE INDEX IF NOT EXISTS security_date ON security_daily_analytics(ticker, date)")
            conn.execute("CREATE UNIQUE INDEX IF NOT EXISTS portfolio_date ON portfolio_daily_summary(portfolio_id, date)")
            if conn.execute("PRAGMA integrity_check").fetchone()[0] != "ok":
                raise ValueError("SQLite integrity check failed")
        os.replace(temporary, path)
    finally:
        temporary.unlink(missing_ok=True)

import argparse
import hashlib
import json
import logging
import time
import uuid
from importlib.metadata import version
from datetime import datetime, timezone
from pathlib import Path

import pandas as pd

from .analytics import portfolio_analytics, security_analytics
from .extract import extract
from .forward import forward_scenarios
from .scenario_risk import risk_review
from .load import load_mart
from .models import fit_models
from .report import render_report
from .validation import clean_inputs, validate_outputs


def run(root, source="cached", config_path=None):
    started = time.perf_counter()
    output = root / "output"
    output.mkdir(exist_ok=True)
    config = json.loads((config_path or root / "config.json").read_text())
    if not (0 < config["test_fraction"] < 0.5 and config["forecast_days"] >= 2
            and config["initial_capital"] > 0 and config["transaction_cost_bps"] >= 0
            and config["risk_free_rate"] > -1 and config["warmup_days"] >= 20
            and config["simulation_years"] >= 1 and config["simulation_paths"] >= 100
            and config["bootstrap_block_days"] >= 1 and config["trading_days"] >= 1):
        raise ValueError("Invalid research configuration")
    run_id = str(uuid.uuid4())
    logging.info("[1/9] Extracting prices, holdings and security metadata")
    prices, holdings, securities, provenance = extract(root, config, source)
    prices = prices[(pd.to_datetime(prices.date) >= config["start"]) & (pd.to_datetime(prices.date) < config["end"])]
    logging.info("[2/9] Cleaning and validating inputs")
    prices, quality = clean_inputs(prices, holdings, securities)
    logging.info("[3/9] Calculating security analytics")
    security = security_analytics(prices)
    logging.info("[4/9] Simulating portfolios and transaction costs")
    daily, positions, sectors, summary, trades, targets = portfolio_analytics(prices, holdings, securities, config)
    logging.info("[5/9] Validating NAV and allocation reconciliation")
    validate_outputs(daily, positions)
    logging.info("[6/9] Training models with purged time-series validation")
    scores, predictions, coefficients, audits = fit_models(daily, prices, config)
    logging.info("[7/9] Simulating five-year portfolio scenarios from completed history")
    projection_bands, projection_summary = forward_scenarios(daily, config)
    downside, sensitivity, paired = risk_review(daily, config)
    tables = {"securities": securities, "holdings": holdings, "security_daily_analytics": security,
              "portfolio_positions": positions, "portfolio_daily_summary": daily,
              "sector_exposures": sectors, "portfolio_summary": summary, "rebalancing_history": trades,
              "target_weights": targets, "model_scores": scores, "model_predictions": predictions,
              "model_coefficients": coefficients, "validation_splits": audits,
              "forward_projection_bands": projection_bands, "forward_projection_summary": projection_summary,
              "scenario_downside": downside, "scenario_sensitivity": sensitivity, "scenario_paired": paired}
    logging.info("[8/9] Generating offline report and CSV exports")
    charts = render_report(output, tables, provenance, config, quality)
    for name, frame in tables.items():
        frame.to_csv(output / f"{name}.csv", index=False)
    manifest = {"run_id": run_id, "timestamp": datetime.now(timezone.utc).isoformat(),
                "source": provenance, "config": config, "validation": quality,
                "package_versions": {package: version(package) for package in ("pandas", "numpy", "scipy", "scikit-learn", "plotly", "yfinance")},
                "source_hashes": {path.name: hashlib.sha256(path.read_bytes()).hexdigest() for path in (root / "src").glob("*.py")},
                "input_hashes": {name: hashlib.sha256((root / "data" / name).read_bytes()).hexdigest()
                                 for name in ("holdings.csv", "securities.csv")}}
    (output / "run_manifest.json").write_text(json.dumps(manifest, indent=2))
    tables["pipeline_runs"] = pd.DataFrame([{"run_id": run_id, "timestamp": manifest["timestamp"],
                                            "status": "success", "price_rows": len(prices),
                                            "source": provenance["source"], "manifest": json.dumps(manifest)}])
    logging.info("[9/9] Loading SQL data mart")
    load_mart(output, tables, (root / "sql/analysis_queries.sql").read_text())
    logging.info("Pipeline complete: %s prices; %.1f seconds; report: %s", len(prices), time.perf_counter() - started, output / "report.html")
    return tables, charts


def main():
    parser = argparse.ArgumentParser(description="Investment analytics ETL and volatility forecasting")
    parser.add_argument("--source", choices=["cached", "download", "synthetic"], default="cached")
    parser.add_argument("--config", type=Path)
    args = parser.parse_args()
    root = Path(__file__).resolve().parents[1]
    (root / "output").mkdir(exist_ok=True)
    logging.basicConfig(level=logging.INFO, format="%(asctime)s %(message)s",
                        handlers=[logging.StreamHandler(), logging.FileHandler(root / "output/pipeline.log")])
    try:
        run(root, args.source, args.config)
    except Exception:
        logging.exception("Pipeline failed")
        raise


if __name__ == "__main__":
    main()

conn.close()

	cumulative_return	annualized_return	volatility	sharpe	max_drawdown	portfolio_id	total_cost	turnover	excess_annualized_return
0	2.798174	0.191215	0.162911	0.974572	-0.287301	Balanced	776.605214	3.625783	0.028992
1	2.147465	0.162223	0.163749	0.820370	-0.372117	Benchmark	0.000000	0.000000	0.000000
2	5.492029	0.277950	0.286007	0.897374	-0.487163	Growth	1856.877502	5.196306	0.115728
3	2.129369	0.161344	0.160645	0.827860	-0.330561	Income	395.514165	2.350359	-0.000878
4	2.435912	0.175662	0.152889	0.942033	-0.297091	Low volatility	573.676954	2.992934	0.013439

	portfolio_id	as_of_date	start_value	terminal_p05	terminal_median	terminal_p95	probability_terminal_loss	median_max_drawdown	scenario_paths	horizon_years	bootstrap_block_days
0	Balanced	2026-08-31 00:00:00	100000	131664.553083	235937.755808	394953.451525	0.0072	-0.202865	5000	5	20
1	Benchmark	2026-08-31 00:00:00	100000	106181.845788	213678.413038	360350.434855	0.0392	-0.242438	5000	5	20
2	Growth	2026-08-31 00:00:00	100000	114707.843378	329002.427170	872361.536794	0.0308	-0.335489	5000	5	20
3	Income	2026-08-31 00:00:00	100000	119698.008807	211348.942785	341758.067547	0.0156	-0.221340	5000	5	20
4	Low volatility	2026-08-31 00:00:00	100000	129046.399493	222659.609900	355249.560558	0.0084	-0.194353	5000	5	20

	portfolio_id	start_value	terminal_p01	terminal_p05	worst5_mean	probability_terminal_loss	terminal_loss_count	paths	median_max_drawdown	severe_max_drawdown_p05	probability_ever_below_80pct
0	Balanced	100000	102851.338325	131664.553083	114216.685355	0.0072	36	5000	-0.202865	-0.365675	0.0880
1	Benchmark	100000	77752.653230	106181.845788	88260.764821	0.0392	196	5000	-0.242438	-0.464498	0.1612
2	Growth	100000	74185.151236	114707.843378	90946.659836	0.0308	154	5000	-0.335489	-0.539968	0.2424
3	Income	100000	92131.143848	119698.008807	103300.682987	0.0156	78	5000	-0.221340	-0.399387	0.1216
4	Low volatility	100000	103036.835022	129046.399493	112975.309453	0.0084	42	5000	-0.194353	-0.361439	0.0922

	portfolio_id	sector	market_value	weight
0	Balanced	Energy	54729.056875	0.144093
1	Balanced	Financials	94183.667480	0.247971
2	Balanced	Industrials	55795.837249	0.146902
3	Balanced	Technology	82793.440689	0.217982
4	Balanced	Utilities	92315.391825	0.243052
5	Benchmark	Benchmark	314746.547784	1.000000
6	Growth	Financials	90212.881038	0.138959
7	Growth	Industrials	120383.907598	0.185433
8	Growth	Technology	438606.100043	0.675607
9	Income	Energy	61403.248725	0.196216
10	Income	Financials	142866.983701	0.456536
11	Income	Utilities	108666.677989	0.347248
12	Low volatility	Energy	29400.047297	0.085567
13	Low volatility	Financials	132849.082620	0.386649
14	Low volatility	Industrials	37455.714306	0.109012
15	Low volatility	Technology	43578.712575	0.126833
16	Low volatility	Utilities	100307.672655	0.291939

	portfolio_id	model	rmse	r2	selected_by_cv
0	Balanced	Linear regression	0.043156	-0.087686	0
1	Balanced	Ridge	0.041783	-0.019568	1
2	Balanced	Lasso	0.041799	-0.020322	0
3	Balanced	ElasticNet	0.042053	-0.032806	0
4	Balanced	Persistence baseline	0.049813	-0.449087	0
5	Growth	Linear regression	0.099861	-0.057570	0
6	Growth	Ridge	0.099264	-0.044973	1
7	Growth	Lasso	0.099706	-0.054298	0
8	Growth	ElasticNet	0.099741	-0.055036	0
9	Growth	Persistence baseline	0.131365	-0.830095	0
10	Income	Linear regression	0.035779	-0.382895	0
11	Income	Ridge	0.035118	-0.332244	1
12	Income	Lasso	0.035205	-0.338864	0
13	Income	ElasticNet	0.035291	-0.345389	0
14	Income	Persistence baseline	0.031161	-0.048943	0
15	Low volatility	Linear regression	0.037045	-0.210236	0
16	Low volatility	Ridge	0.036026	-0.144576	1
17	Low volatility	Lasso	0.036005	-0.143252	0
18	Low volatility	ElasticNet	0.036156	-0.152897	0
19	Low volatility	Persistence baseline	0.039793	-0.396456	0

Quantitative Investment Analytics ETL Pipeline¶

Portfolio performance and drawdown¶

Equal-capital five-year strategy comparison¶

Downside interpretation¶

Backtesting and look-ahead controls¶

SQL data mart¶

Machine learning validation¶

Implementation¶

extract.py¶

validation.py¶

analytics.py¶

forward.py¶

scenario_risk.py¶

risk_presentation.py¶

models.py¶

load.py¶

pipeline.py¶

	experiment	left_portfolio	right_portfolio	probability_underperformance	difference_p05	difference_median	difference_p95
0	Published model	Growth	Balanced	0.1932	-50892.988104	91045.424557	504010.588330
1	126-session moving blocks	Growth	Balanced	0.3036	-75397.029446	61840.229658	425376.198234
2	252-session moving blocks	Growth	Balanced	0.2998	-78635.832431	66626.113830	417996.993913
3	126-session circular blocks	Growth	Balanced	0.2254	-68713.743504	113837.574874	659185.457107
4	252-session circular blocks	Growth	Balanced	0.2330	-75701.618038	109043.281899	625772.788790

	portfolio_id	fold	train_start	last_train_label	validation_start	validation_end
0	Balanced	0	2019-01-30 00:00:00	2020-04-07 00:00:00	2020-04-08 00:00:00	2021-06-14 00:00:00
1	Balanced	1	2019-01-30 00:00:00	2021-06-14 00:00:00	2021-06-15 00:00:00	2022-08-19 00:00:00
2	Balanced	2	2019-01-30 00:00:00	2022-08-19 00:00:00	2022-08-22 00:00:00	2023-10-26 00:00:00
3	Balanced	3	2019-01-30 00:00:00	2023-10-26 00:00:00	2023-10-27 00:00:00	2025-01-02 00:00:00
4	Growth	0	2019-01-30 00:00:00	2020-04-07 00:00:00	2020-04-08 00:00:00	2021-06-14 00:00:00
5	Growth	1	2019-01-30 00:00:00	2021-06-14 00:00:00	2021-06-15 00:00:00	2022-08-19 00:00:00
6	Growth	2	2019-01-30 00:00:00	2022-08-19 00:00:00	2022-08-22 00:00:00	2023-10-26 00:00:00
7	Growth	3	2019-01-30 00:00:00	2023-10-26 00:00:00	2023-10-27 00:00:00	2025-01-02 00:00:00
8	Income	0	2019-01-30 00:00:00	2020-04-07 00:00:00	2020-04-08 00:00:00	2021-06-14 00:00:00
9	Income	1	2019-01-30 00:00:00	2021-06-14 00:00:00	2021-06-15 00:00:00	2022-08-19 00:00:00
10	Income	2	2019-01-30 00:00:00	2022-08-19 00:00:00	2022-08-22 00:00:00	2023-10-26 00:00:00
11	Income	3	2019-01-30 00:00:00	2023-10-26 00:00:00	2023-10-27 00:00:00	2025-01-02 00:00:00
12	Low volatility	0	2019-01-30 00:00:00	2020-04-07 00:00:00	2020-04-08 00:00:00	2021-06-14 00:00:00
13	Low volatility	1	2019-01-30 00:00:00	2021-06-14 00:00:00	2021-06-15 00:00:00	2022-08-19 00:00:00
14	Low volatility	2	2019-01-30 00:00:00	2022-08-19 00:00:00	2022-08-22 00:00:00	2023-10-26 00:00:00
15	Low volatility	3	2019-01-30 00:00:00	2023-10-26 00:00:00	2023-10-27 00:00:00	2025-01-02 00:00:00