Financial and Risk Management Toolbox: Reference

Complete reference of worksheet controls, input and output schemas, status codes, numerical tolerances, and troubleshooting.

← Overview · Getting started · Modeling guide · Methods guide · Examples · Reference

Reference Guide

This document provides the exhaustive technical reference for all workbook controls, input ranges, published outputs, status classifications, numerical parameters, and error resolution procedures in the Financial and Risk Management Toolbox.

1. Controls Reference Table

All interactive execution controls reside on the Start worksheet in cells A5:B10:

Cell Name Data Type Validation / Allowed Values Default Purpose & Description
Start!B6 Active task String List validation: portfolio_opt_sharpe, portfolio_min_variance, portfolio_target_return, var_es_historical, risk_attribution portfolio_opt_sharpe Selects which portfolio optimization or risk evaluation model to dispatch.
Start!B7 Risk-free rate Float Real number \(\ge 0.0\) (e.g., 0.04 or 4.0%) 0.04 Cash hurdle rate used in Sharpe ratio calculation: \((\mu_p - r_f)/\sigma_p\).
Start!B8 Confidence level Float List validation: 0.90, 0.95, 0.99 0.95 One-tailed cumulative probability level for Value-at-Risk and Expected Shortfall calculations.
Start!B9 Horizon (days) Integer Positive integer \(\ge 1\) 1 Down-tail risk holding horizon in trading days. Scales VaR and CVaR by \(\sqrt{h/252}\).
Start!B10 Target return Float Real number \(\ge 0.0\) (e.g., 0.08 or 8.0%) 0.08 Annualized minimum return hurdle enforced when Active task is portfolio_target_return.

2. Input Schemas

Asset Universe Table (Asset Parameters!A5:H20)

Supports up to 15 assets. The default template populates rows 6 through 11:

Column Header Data Type Units / Range Description
A Asset String Alphanumeric ticker Unique identifier for each asset class (e.g., US_Equity). Case-sensitive join key.
B Name String Text label Human-readable asset description (e.g., US Large Cap Equity).
C ExpectedReturn Float Annual decimal (e.g. 0.11) Expected annualized arithmetic return \(\mu_i\).
D Volatility Float Annual decimal \(> 0\) Expected annualized return standard deviation \(\sigma_i\).
E MinWeight Float Decimal \(\in [0.0, 1.0]\) Minimum permitted portfolio allocation weight \(l_i\).
F MaxWeight Float Decimal \(\in [0.0, 1.0]\) Maximum permitted portfolio allocation weight \(u_i\). Must satisfy \(u_i \ge l_i\).
G BenchmarkWeight Float Decimal \(\in [0.0, 1.0]\) Strategic benchmark weight \(w_{b,i}\). Sum should equal 1.0.
H CurrentWeight Float Decimal \(\in [0.0, 1.0]\) Current portfolio holding weight \(w_{0,i}\) used for risk assessment tasks.

Durable Correlation Matrix (Asset Parameters!J5:P20)

  • Range: Header row J5:P5, asset row index J6:J11, data grid K6:P11.
  • Validation Rules:
    • Diagonal values must be \(1.00\).
    • Matrix must be symmetric: \(C_{ij} = C_{ji}\).
    • Off-diagonal values must lie in \([-1.0, 1.0]\).
    • Spectrum must be positive semi-definite: \(\lambda_i \ge 0\). (Eigenvalue clipping applies automatically if violated).

Historical Returns Series (Historical Returns!A5:H45)

  • Range: Table spanning 36 monthly rows (January 2024 to December 2026).
  • Columns:
    • Period (A): Format YYYY-MM.
    • Asset return columns (B through G): Historical monthly returns \(r_{i,t}\) for each asset.
    • Benchmark (H): Historical monthly return \(r_{b,t}\) of the strategic benchmark index.

3. Published Output Schemas

Portfolio Summary Block (Results!A5:B17)

Published under Boardflare anchor summary with autoHeight: True:

Cell Metric Label Data Type Formatting Description
B6 Active strategy String Text Descriptive label of the active model task (e.g., Max Sharpe Ratio Allocation).
B7 Family String Text Model category (Optimization or Risk).
B8 Status String Status code Solver execution status (OPTIMAL, CONVERGED, INFEASIBLE, or FAILED).
B9 Expected Return (Annual) Float Percentage (0.00%) Annualized expected portfolio return: \(w^T \mu\).
B10 Annualized Volatility Float Percentage (0.00%) Annualized portfolio standard deviation: \(\sqrt{w^T \Sigma w}\).
B11 Sharpe Ratio Float Decimal (0.000) Portfolio risk-adjusted efficiency: \((\mu_p - r_f)/\sigma_p\).
B12 Parametric VaR (\(h\)d, \(\alpha\)%) Float Percentage (0.00%) Gaussian Value-at-Risk over horizon \(h\) at confidence level \(\alpha\).
B13 Student-t VaR (\(h\)d, \(\alpha\)%) Float Percentage (0.00%) Fat-tailed Value-at-Risk using Student-t distribution with \(\nu = 5\).
B14 Historical VaR (\(h\)d, \(\alpha\)%) Float Percentage (0.00%) Non-parametric empirical quantile of 36 monthly returns scaled to horizon.
B15 Expected Shortfall (CVaR) Float Percentage (0.00%) Analytical conditional down-tail loss beyond the VaR cutoff.
B16 Portfolio Beta (vs Bmk) Float Decimal (0.00) Historical sample covariance beta relative to the policy benchmark.
B17 Tracking Error (Ann.) Float Percentage (0.00%) Annualized sample standard deviation of active returns \((r_p - r_b)\).

Asset Allocations & Risk Shares (Results!D5:K20)

Published under Boardflare anchor allocations:

Column Header Data Type Formula / Source Description
D Asset String Asset Parameters!A Asset class unique identifier.
E Weight Float \(w_i\) Optimal or evaluated portfolio allocation weight.
F BenchmarkWeight Float \(w_{b,i}\) Policy benchmark weight.
G ActiveWeight Float \(w_i - w_{b,i}\) Active overweight or underweight position.
H ExpReturn Float \(\mu_i\) Expected annual return assumption.
I Volatility Float \(\sigma_i\) Annualized volatility assumption.
J MarginalRisk Float \(\text{MCR}_i = (\Sigma w)_i / \sigma_p\) Euler marginal contribution to risk.
K RiskSharePct Float \((w_i \cdot \text{MCR}_i / \sigma_p) \times 100\) Relative percentage of total portfolio risk contributed by this asset.

Efficient Frontier Table (Analysis!A5:F25)

Published under Boardflare anchor frontier:

Column Header Data Type Description
A FrontierPoint String Identifier P1 through P12.
B Return Float Annualized expected return at this frontier locus.
C Volatility Float Minimum achievable volatility at this target return.
D SharpeRatio Float Risk-adjusted efficiency ratio \((r - r_f)/\sigma\).
E DominantAsset String Asset class receiving the largest allocation weight at this point.
F DiversificationRatio Float Ratio of weighted asset volatilities to portfolio volatility \(\sum w_i \sigma_i / \sigma_p\).

Diagnostics Table (Diagnostics!A5:C25)

Published under Boardflare anchor diagnostics with autoHeight: True:

Column Header Data Type Allowed Values Description
A Check String Text Name of the diagnostic integrity check.
B Severity String PASS, WARN, FAIL Diagnostic validation outcome.
C Detail String Text Quantitative evidence, condition numbers, or breach details.

4. Status Codes and Diagnostics Glossary

Solver Status Codes (Results!B8)

Status Severity Operational Interpretation
OPTIMAL Nominal The numerical solver successfully converged, the first-order optimality conditions are satisfied, and all box and budget constraints hold within tolerance \(\le 10^{-4}\).
CONVERGED Attention The solver converged to a local minimum, but constraint residuals lie between \(10^{-4}\) and \(10^{-2}\). Review asset bounds.
INFEASIBLE Critical Error The constraints are mathematically irreconcilable. Typically caused by \(l_i > u_i\), \(\sum l_i > 1.0\), or \(\sum u_i < 1.0\). No optimization solve was attempted.
FAILED Optimizer Failure SLSQP encountered numerical divergence, singular Hessian approximations, or an unachievable target return hurdle.

Diagnostics Suite Checks (Diagnostics!A5:C11)

Check Name Target Condition Warn Condition Fail Condition
Full investment budget Deviation \(|\sum w_i - 1| \le 10^{-4}\) \(10^{-4} < \text{Dev} \le 10^{-2}\) \(\text{Dev} > 10^{-2}\)
Box constraint feasibility \(l_i \le w_i \le u_i\) within \(10^{-4}\) Violation \(\le 10^{-2}\) \(l_i > u_i\) or violation \(> 10^{-2}\)
Covariance conditioning & PSD \(\kappa(\boldsymbol{\Sigma}) < 10^5\) and no clipping \(\kappa \ge 10^5\) or clipping active Singular / non-PSD
VaR down-tail coverage & fat tails Always PASS N/A N/A (informational metric)
Kupiec POF backtest validation Kupiec test p-value \(\ge 0.05\) p-value \(< 0.05\) Zero historical observations
Benchmark tracking & active risk Tracking error \(< 25.0\%\) Tracking error \(\ge 25.0\%\) No benchmark data

5. Numerical Tolerances and Algorithmic Settings

Setting Value Implementation Location Purpose
SLSQP Tolerance (ftol) \(10^{-9}\) scipy.optimize.minimize Objective function convergence termination threshold.
Feasibility Absolute Tolerance \(10^{-4}\) (0.01%) Python verification logic Maximum allowable budget or bound violation for OPTIMAL status.
Feasibility Relaxed Tolerance \(10^{-2}\) (1.00%) Python verification logic Threshold separating CONVERGED from FAILED.
PSD Eigenvalue Floor \(10^{-7}\) Spectral clipping routine Lower bound below which eigenvalues are projected upwards to guarantee positive definiteness.
Covariance Condition Limit \(10^5\) Diagnostics check Condition number \(\lambda_{\max}/\lambda_{\min}\) above which ill-conditioning is flagged.
Student-t Degrees of Freedom (\(\nu\)) \(5.0\) Tail risk calculation Captures financial kurtosis (\(\text{Kurtosis} = 3 + 6/(\nu - 4) = 9\)).
Trading Days per Year \(252\) Down-tail time scaling Square-root time factor \(\sqrt{h/252}\) for daily horizon conversions.
Trading Days per Month \(21\) Historical time scaling Square-root time factor \(\sqrt{h/21}\) for monthly historical simulations.

6. Troubleshooting Guide

Error 1: Status Displays INFEASIBLE

  • Symptom: Results!B8 displays INFEASIBLE, and Diagnostics!B7 displays FAIL.
  • Root Cause:
    1. For an asset \(i\), MinWeight (\(l_i\)) exceeds MaxWeight (\(u_i\)).
    2. The sum of minimum weights exceeds 1.0 (\(\sum l_i > 1.0\)), making full investment impossible.
    3. The sum of maximum weights is less than 1.0 (\(\sum u_i < 1.0\)).
  • Resolution: Check Asset Parameters!E6:F11. Ensure \(l_i \le u_i\) for all assets, \(\sum l_i \le 1.0\), and \(\sum u_i \ge 1.0\).

Error 2: Status Displays FAILED on Target Return Solve

  • Symptom: Setting Active task to portfolio_target_return yields FAILED.
  • Root Cause: The Target return set in Start!B10 is higher than the maximum return attainable by any feasible combination of assets within their MaxWeight caps.
  • Resolution: Lower Target return (e.g. to 0.07), or increase the MaxWeight of the highest-returning asset classes on Asset Parameters.

Error 3: Covariance Conditioning Displays WARN

  • Symptom: Diagnostics!B8 displays WARN with detail noting “adjusted via eigenvalue clipping”.
  • Root Cause: The correlation matrix entered in Asset Parameters!J5:P11 has negative eigenvalues, indicating mathematically inconsistent pairwise correlations (e.g., \(A\) and \(B\) correlated \(+0.9\), \(B\) and \(C\) correlated \(+0.9\), but \(A\) and \(C\) correlated \(-0.9\)).
  • Resolution: Python has automatically projected the matrix to the nearest positive semi-definite matrix. If desired, review pairwise correlation values to ensure mutual consistency.

Error 4: Kupiec POF Displays WARN

  • Symptom: Diagnostics!B10 displays WARN with p-value \(< 0.05\).
  • Root Cause: The number of monthly losses exceeding the calculated VaR threshold in the historical 36-month sample exceeds the expected statistical frequency (e.g., more than 4 breaches at 95% confidence).
  • Resolution: Increase the confidence level (e.g. to 0.99), reduce portfolio volatility, or review the historical return series on Historical Returns for extreme outliers.