Financial and Risk Management Toolbox

Portfolio optimization, risk metrics, Value-at-Risk, and asset allocation workbench for Python for Excel

Finance
Quantitative Finance
Risk Management
Portfolio Management
Construct efficient portfolios, calculate historical and parametric Value-at-Risk and Expected Shortfall, attribute portfolio risk, and evaluate tradeoffs directly in Excel.

Result preview

The Financial and Risk Management Toolbox delivers institutional portfolio construction, risk metric evaluation, and down-tail loss estimation directly inside Microsoft Excel. It enables quantitative portfolio managers, risk officers, and investment analysts to optimize asset allocations, derive the Markowitz efficient frontier, compute multi-horizon Value-at-Risk (VaR) and Expected Shortfall (CVaR), and decompose marginal risk contributions without leaving the spreadsheet.

The default Max Sharpe Ratio Allocation model solves for the optimal risk-adjusted allocation across five asset classes (US Large Cap Equity, International Developed Equity, Aggregate Fixed Income, Public Real Estate REITs, and Diversified Commodities) plus Cash, achieving an expected annual return of 8.25% with 9.79% annualized volatility and a Sharpe ratio of 0.435 (at a 4.0% risk-free rate). Switching Start → Active task instantly re-executes the underlying Python optimization pipeline to solve global minimum variance portfolios, target-return constrained allocations, empirical/Student-t down-tail risk assessments, or Euler marginal risk attributions.

For a first run:

  1. Leave Start → Active task as portfolio_opt_sharpe.
  2. Inspect asset expected returns, annualized volatilities, and allocation bounds on Asset Parameters.
  3. View Results for the optimal weights, annualized Sharpe ratio, 1-day 95% Parametric VaR (1.02%), Student-t VaR (1.17%), Historical VaR (1.01%), and Expected Shortfall (1.29%).
  4. Open Analysis to examine the 12-point parametric efficient frontier curve and dominant asset transitions.
  5. Review Diagnostics to verify full investment budget satisfaction, box constraint compliance, covariance conditioning, and Kupiec backtest validation.

For a guided tour, see Getting started.

What this toolbox covers

Modules

  1. Mean-Variance Optimization — Markowitz portfolio construction, maximum Sharpe ratio, global minimum variance, and target-return constrained quadratic programming.
  2. Value-at-Risk & Expected Shortfall — Parametric Gaussian, Student-t fat-tail (\(\nu = 5\)), and empirical historical simulation down-tail quantile estimation.
  3. Marginal Risk Attribution — Euler marginal contribution to risk (MCR), component percentage risk shares, and active weight deviations.
  4. Efficient Frontier Generation — 12-point parametric efficient frontier curve spanning minimum variance to maximum return with diversification ratio evaluation.
  5. Factor Risk & Benchmark Tracking — CAPM beta against benchmark, annualized tracking error, active return, and information ratio.
  6. Model Validation & Diagnostics — Six-point diagnostic integrity suite including full investment budget verification, box constraint feasibility, covariance PSD projection, Student-t tail gap, and Kupiec Proportion of Failures (POF) likelihood ratio backtesting.

Documentation

  • Getting started — Step-by-step walkthrough of first solve, switching tasks, adjusting constraints, and interpreting outputs.
  • Modeling guide — Complete worksheet schemas, parameter bounds, durable correlation matrix structure, historical returns, and solver status semantics.
  • Methods guide — Mathematical formulations of quadratic programming, covariance PSD projection, VaR/CVaR, Student-t scaling, Kupiec POF backtesting, Euler attribution, and MATLAB toolbox comparison.
  • Worked examples — Walkthroughs of five distinct institutional scenarios: Multi-Asset Max Sharpe, Capital Preservation Min Variance, Target Return Liability Matching, Down-Tail Risk & Backtest Validation, and Active Tracking Risk Attribution.
  • Reference — Controls address dictionary, published output tables, status codes, numerical tolerances, and troubleshooting guide.

Architecture

The toolbox is designed around a clean separation of worksheet parameter storage, empirical data ingestion, numerical optimization, and reactive publication:

[Start Worksheet]            [Asset Parameters]          [Historical Returns]
- Active task selection       - Expected returns          - 36 monthly returns
- Risk-free rate, horizon     - Volatilities, bounds      - 6 assets + Benchmark
- Confidence & target return  - J5:P11 Correlation matrix
         \                           |                          /
          \                          |                         /
           +-------------------------v------------------------+
           |       marimo Reactive Execution DAG              |
           |  1. bf.inputs(...) ingestion & type coercion     |
           |  2. Correlation symmetry & PSD clipping          |
           |  3. SLSQP Constrained Quadratic Optimization     |
           |  4. Parametric / Student-t / Empirical VaR & ES  |
           |  5. Euler MCR Attribution & Kupiec POF Backtest  |
           |  6. 12-Point Efficient Frontier Sweep            |
           +-------------------------+------------------------+
                                     |
                               bf.publish(...)
                                     |
         +---------------------------+--------------------------+
         |                           |                          |
         v                           v                          v
 [Results Worksheet]         [Analysis Worksheet]     [Diagnostics Worksheet]
 - Summary (12 metrics)      - Frontier curve (12 pts) - Budget constraint
 - Allocations (8 columns)   - Dominant asset trace   - Box bounds check
                             - Diversification ratio   - PSD condition number
                                                       - VaR fat-tail gap
                                                       - Kupiec POF test
                                                       - Benchmark tracking

Key Architectural Strengths:

  • Durable In-Sheet Correlation Matrix: Asset correlations reside on Asset Parameters!J5:P11 rather than inside hidden code, allowing direct inspection and customization while preserving symmetry and positive semi-definiteness.
  • Empirical Return Engine: The Historical Returns sheet hosts 36 monthly return observations (2024-01 to 2026-12) used to evaluate empirical historical VaR/CVaR, compute active tracking error, calculate CAPM beta, and execute the Kupiec backtest.
  • Scalable Asset Universe: Preconfigured for 6 institutional asset classes and expandable up to 15 assets without code restructuring.

Why Python

Institutional portfolio construction and down-tail risk modeling require numerical capabilities that exceed standard spreadsheet formulas:

  1. Constrained Quadratic Programming: Solving Markowitz portfolio problems with simultaneous equality constraints (full investment \(\sum w_i = 1\)), lower and upper bounds (\(l_i \le w_i \le u_i\)), and target return inequality constraints (\(w^T \mu \ge r_{\text{target}}\)) requires robust Sequential Least Squares Programming (SLSQP).
  2. Matrix Regularization & PSD Verification: User-edited correlation matrices can easily lose positive semi-definiteness due to rounding or estimation inconsistencies. Python automatically computes the spectral decomposition, clips negative eigenvalues (\(\lambda_i \ge 10^{-7}\)), and renormalizes diagonal elements.
  3. Non-Gaussian Fat-Tail Risk: Real financial asset returns exhibit leptokurtosis and heavy tails. Python estimates Student-t distribution VaR and empirical historical quantiles directly alongside parametric Gaussian benchmarks.
  4. Hypothesis Testing & Backtesting: Calculating the Kupiec Proportion of Failures likelihood ratio statistic (\(\text{LR}_{\text{POF}} \sim \chi^2_1\)) provides certified backtesting evidence directly in the diagnostics sheet.

Operating workflow

  • Author: A quantitative risk manager, portfolio strategist, or asset allocator specifies asset classes, calibrated capital market assumptions (expected returns and volatilities), pairwise correlations, and institutional allocation limits.
  • Workbook user: An investment analyst, wealth advisor, or committee member selects active tasks on Start, evaluates target return feasibility, inspects optimal asset allocations, and presents frontier tradeoff curves to stakeholders.

A typical operating cycle: 1. The user selects the active strategy task and target parameters on Start. 2. Marimo executes the reactive calculation cell, compiling inputs via bf.inputs(...). 3. The optimizer verifies constraint feasibility and solves the assigned objective. 4. The notebook publishes normalized summary metrics, allocation tables, efficient frontier points, and diagnostic checks back to Excel via bf.publish(...). 5. The reviewer validates diagnostic status codes before exporting or presenting decisions.

Download the Excel template

Notebook implementation

The Python engine is structured as a Marimo reactive notebook DAG consisting of discrete stages:

Ingestion and Robust Coercion

Inputs are read into Python via Boardflare’s input bridge:

inputs = bf.inputs(
    controls=bf.ref("Start!A5:B10", headers=True),
    models=bf.ref("Start!D5:H10", headers=True),
    assets=bf.ref("Asset Parameters!A5:H20", headers=True),
    correlations=bf.ref("Asset Parameters!J5:P20", headers=True),
    historical_returns=bf.ref("Historical Returns!A5:H45", headers=True),
)

The coerce_float function handles spreadsheet formatting variants, safely parsing percentage strings (e.g., "8.25%" \(\rightarrow\) 0.0825), currency characters, and empty cells.

Correlation Conditioning & PSD Verification

To guarantee that the covariance matrix \(\Sigma\) is strictly positive semi-definite: 1. Symmetry is enforced: \(\mathbf{C} = \frac{1}{2}(\mathbf{C} + \mathbf{C}^T)\) with \(\text{diag}(\mathbf{C}) = 1\). 2. Eigenvalues \(\lambda_i\) and eigenvectors \(\mathbf{V}\) are computed via np.linalg.eigh. 3. If any \(\lambda_i < 10^{-7}\), eigenvalues are clipped to \(10^{-7}\), the matrix is reconstructed as \(\mathbf{C}^* = \mathbf{V} \text{diag}(\boldsymbol{\lambda}^+) \mathbf{V}^T\), and normalized: \(C^*_{ij} / \sqrt{C^*_{ii} C^*_{jj}}\). 4. The covariance matrix is synthesized: \(\Sigma = \text{diag}(\boldsymbol{\sigma}) \mathbf{C}^* \text{diag}(\boldsymbol{\sigma})\).

Constrained SLSQP Optimization

Optimization tasks are dispatched to SciPy’s Sequential Least Squares Programming (scipy.optimize.minimize(..., method="SLSQP")): - Max Sharpe: Minimizes \(-(w^T \mu - r_f) / \sqrt{w^T \Sigma w}\). - Min Variance: Minimizes \(\sqrt{w^T \Sigma w}\). - Target Return: Minimizes \(\sqrt{w^T \Sigma w}\) subject to \(w^T \mu \ge r_{\text{target}}\). - Feasibility Verification: Independent post-solve verification guarantees that budget violations \(| \sum w_i - 1 | \le 10^{-4}\) and box violations \(\max(l_i - w_i, w_i - u_i) \le 10^{-4}\) hold before certifying status as OPTIMAL.

Down-Tail Risk & Backtest Analytics

  • Parametric Gaussian VaR/CVaR: Scaled by \(\sqrt{\Delta t}\) where \(\Delta t = h / 252\): \[\text{VaR}_\alpha = \sigma_p \sqrt{\Delta t} \Phi^{-1}(\alpha) - \mu_p \Delta t\]
  • Student-t VaR: Scaled with degrees of freedom \(\nu = 5\) using variance factor \(\sqrt{(\nu - 2)/\nu}\).
  • Empirical Historical VaR: Non-parametric quantile of 36 monthly portfolio return series: \[\text{VaR}_\alpha^{\text{hist}} = -q_{1-\alpha}(\{r_t\}) \sqrt{h / 21}\]
  • Kupiec POF Backtest: Evaluates monthly exceedance count \(x\) against theoretical coverage \(p = 1 - \alpha\): \[\text{LR}_{\text{POF}} = -2 \ln \left[ \frac{(1-p)^{N-x} p^x}{(1 - x/N)^{N-x} (x/N)^x} \right] \sim \chi^2(1)\]

Publication Contract

publication = bf.publish(
    outputs={
        "summary": df_summary,
        "allocations": df_alloc,
        "frontier": df_frontier,
        "diagnostics": df_diag,
    }
)

Capability reference

Capability map

Capability Status Current implementation
Maximum Sharpe ratio allocation Available SLSQP optimization maximizing excess return per unit volatility under full investment and box constraints
Global minimum variance portfolio Available Quadratic minimization of portfolio variance subject to asset bounds and full investment
Target return portfolio optimization Available Constrained volatility minimization subject to achieving user-specified annual target return
Parametric Gaussian VaR / CVaR Available Analytical multi-horizon (1d to 30d) down-tail Value-at-Risk and Expected Shortfall
Student-t fat-tail VaR Available Leptokurtic tail modeling with \(\nu = 5\) degrees of freedom and variance scaling
Empirical historical VaR / CVaR Available Non-parametric percentile estimation using 36 monthly historical return periods
Kupiec POF backtesting Available Likelihood ratio test statistic and Chi-square (\(df=1\)) p-value for VaR model validation
Euler marginal risk attribution Available Euler marginal contribution to risk (MCR) and percentage risk share (\(w_i \cdot \text{MCR}_i / \sigma_p\))
Benchmark tracking error & CAPM beta Available Empirical active return standard deviation, information ratio, and covariance beta vs benchmark
Parametric efficient frontier Available 12-point sweep spanning minimum variance to maximum feasible return with diversification ratios
Black-Litterman model Planned Bayesian view blending with market equilibrium weights
Copula tail dependence Exploring Student-t and Clayton copula modeling for joint non-linear tail co-dependence

Inputs and assumptions

Workbook map

Sheet Purpose Primary user
Start Active task, risk-free rate, confidence level, horizon (days), and target return controls Author and workbook user
Asset Parameters Asset universe parameters (expected return, volatility, bounds, benchmark/current weights) and correlation matrix Author
Historical Returns 36 monthly return observations across all assets and Benchmark index Author and reviewer
Results Published 12-metric portfolio summary and 8-column asset allocation & risk share table Workbook user and reviewer
Analysis 12-point parametric efficient frontier curve and dominant asset progression Workbook user
Diagnostics 6-check integrity validation suite (budget, box bounds, PSD condition, Student-t gap, Kupiec test, tracking) Reviewer and auditor

Validation and expected results

All models and test scenarios are verified against deterministic numerical test vectors declared in scenarios.py:

Test Vector / Scenario Model ID Family Expected Return Volatility Key Output Verification Status
Baseline (Max Sharpe) portfolio_opt_sharpe Optimization 8.25% 9.79% Sharpe: 0.435; US Equity: 50.0%; Bonds: 25.0% OPTIMAL
Global Minimum Variance portfolio_min_variance Optimization 4.39% 3.42% Sharpe: 0.115; Cash: 40.0%; Bonds: 45.4%; US Equity: 2.85% OPTIMAL
Target Return (7.0%) portfolio_target_return Optimization 7.00% 7.21% Sharpe: 0.416; Returns meet target constraint exactly OPTIMAL
Value-at-Risk & CVaR var_es_historical Risk 7.42% 8.44% 1d 95% Parametric VaR: 1.02%; CVaR: 1.29%; Hist VaR: 1.01% OPTIMAL
Marginal Risk Attribution risk_attribution Risk 7.42% 8.44% Portfolio Beta: 0.9693; Tracking Error: 0.76%; IR: 0.72 OPTIMAL
High Confidence Horizon 99% Conf, 10-day Risk 8.25% 9.79% Parametric VaR (10d, 99%): 4.21%; Student-t: 5.16% OPTIMAL
Infeasible Allocation Bounds Infeasible bounds Optimization — — Lower bound (0.60) > Upper bound (0.40) triggers FAIL INFEASIBLE

Limitations

  • Universe Capacity: Standard worksheet layout supports up to 15 assets; larger portfolios can be accommodated by extending the table range.
  • Parametric Distributional Bounds: Normal distribution assumptions underestimate extreme tail losses; users should consult Student-t VaR and Historical CVaR metrics in high-volatility regimes.
  • Static Correlation Assumption: Covariance and correlation estimates are assumed constant over the evaluation horizon.
  • Execution Environment: Runs client-side in the single-worker Pyodide WebAssembly runtime inside Excel.

When to use this approach

Use this toolbox when asset allocation decisions, regulatory risk calculations, or client portfolio reviews must be delivered in an audit-ready, interactive Excel spreadsheet backed by institutional-grade numerical optimization.

How this compares with MATLAB and Excel

Dimension Native Excel (Solver Add-in) MATLAB Financial & Risk Toolboxes Boardflare Financial & Risk Toolbox
Portfolio Modeling Limited to 200 decision variables; fragile cell references Portfolio, portopt, dedicated object classes Clean worksheet parameter tables with automated SLSQP dispatch
Constraint Feasibility Prone to silent solver termination without feasibility certificates Programmatic exit flags requiring custom validation scripts Certified post-solve check with explicit OPTIMAL / INFEASIBLE codes
Covariance Conditioning None; invalid matrices trigger silent errors or non-convergence nearcorr, cov2corr spectral transformations Automated eigenvalue clipping (\(\lambda \ge 10^{-7}\)) with diagnostics warning
Down-Tail Risk Metrics Manual NORM.S.INV formulas; no built-in CVaR var, es, backtest regulatory frameworks Parametric, Student-t (\(\nu=5\)), and historical empirical VaR/CVaR
Regulatory Backtesting Requires complex custom VBA or spreadsheet formulas pof, tbom, bin likelihood ratio backtests Built-in Kupiec POF likelihood ratio test with Chi-square p-value
Integration Surface Standard workbook formulas Desktop license; requires external compilation Interactive Python embedded directly inside Microsoft Excel

Changelog

Version 2.0.0 (Issue #98)

  • Durable Worksheet Correlation Matrix: Added explicit correlation matrix on Asset Parameters!J5:P11 with positive semi-definite verification, eigenvalue clipping, and diagonal preservation.
  • Empirical Historical Returns Engine: Added dedicated Historical Returns sheet with 36 monthly return observations (2024-01 to 2026-12) for all 6 universe assets and the Benchmark index.
  • Expanded Optimization & Risk Tasks: Added support for 5 distinct tasks: portfolio_opt_sharpe, portfolio_min_variance, portfolio_target_return, var_es_historical, and risk_attribution.
  • Target Return Quadratic Optimization: Implemented inequality-constrained quadratic optimization guaranteeing exact return achievement.
  • Comprehensive Down-Tail Risk Suite: Integrated parametric normal VaR/CVaR, Student-t fat-tail VaR (\(\nu=5\)), and empirical historical simulation quantiles.
  • Kupiec POF Model Validation: Added Kupiec Proportion of Failures likelihood ratio hypothesis test with Chi-square p-value reporting in Diagnostics.
  • Euler Risk Attribution: Implemented Euler marginal contribution to risk (MCR) and percentage component risk share breakdown.
  • Benchmark Tracking Analytics: Added CAPM portfolio beta, active return, annualized tracking error, and information ratio calculations.
  • Institutional Diagnostics Suite: Implemented 6-check verification table monitoring budget equality, box feasibility, covariance conditioning, fat-tail gap, backtest validity, and active risk.
  • Complete Toolbox Documentation Bundle: Authored comprehensive 6-guide documentation suite covering Overview, Getting Started, Modeling Guide, Methods Guide, Worked Examples, and Technical Reference.