SPB Git

Repository Public

VALIDATION.md 37.0 KB

# SheetAnalytics — validation

Every public routine of this package is covered by reference tests. Numeric methods are compared with SciPy 1.17, NumPy 2.4, pandas 3.0, statsmodels 0.14.6, scikit-learn 1.6 and arch 8 (Python 3.14) through JSON fixtures generated by the scripts in Tests/<Target>Tests/Generators/ and committed in Tests/<Target>Tests/Fixtures/; where a library is itself unreliable (noted in the tables) closed forms or round trips are used instead. Deterministic analyses (Audit, WhatIf) are checked against hand-derived exact expectations on workbooks built in the tests.

"Tolerance achieved" is the tolerance asserted by the passing tests (relative unless stated). Methods listed as not shipped are not in the public API.

Regenerate fixtures: cd Tests/StatisticsTests/Generators && for f in gen_*.py; do [ $f = gen_common.py ] || /opt/homebrew/bin/python3 $f; done (same in Tests/FinanceModelsTests/Generators). Run the suite: swift test (310 tests, about 2 600 #expect sites, many inside loops over fixture cases).

# Statistics — core, distributions, descriptive statistics, inference

method reference tolerance achieved status
Regularised incomplete gamma P, Q (a from 0.05 to 2500, x from 1e-8 to 4000) scipy.special.gammainc/gammaincc rel 1e-11 on each tail shipped
Regularised incomplete beta I, 1 − I (a, b from 0.1 to 450) scipy.special.betainc/betaincc rel 1e-11 on each tail shipped
ln Γ scipy.special.gammaln rel 1e-14 shipped
Normal: pdf, cdf, sf, quantile, isf (p down to 1e-300) scipy.stats.norm rel 1e-10 shipped
Student t: pdf, cdf, sf (df 0.5 … 1e5), quantile/isf for p ≥ 1e-20 scipy.stats.t rel 1e-10 shipped
Student t quantile/isf for 1e-300 ≤ p < 1e-20 round trip sf(isf(q)) = q and the Cauchy closed form (SciPy's inverse overflows or stalls there) rel 1e-9 (round trip), 1e-12 (closed form) shipped
Chi-square: pdf, cdf, sf, quantile, isf (df 0.3 … 5000, p down to 1e-300) scipy.stats.chi2 rel 1e-10 shipped
F: pdf, cdf, sf, quantile (p ≥ 1e-100), isf (q ≥ 1e-5) scipy.stats.f rel 1e-10 shipped
F isf for q < 1e-5 closed form F(2,2): isf(q) = (1−q)/q, and round trips (SciPy's f.isf is inaccurate below ~1e-8) rel 1e-11 (closed form), 1e-9 (round trip) shipped
Beta distribution: pdf, cdf, sf, quantile, isf scipy.stats.beta rel 1e-10 shipped
Studentized range: cdf (k 2–20, df 2–1000), quantile scipy.stats.studentized_range; closed forms for k = 2 cdf abs 1e-9 / rel 1e-8; quantile rel 1e-7 shipped
xoshiro256** (seeding by SplitMix64, raw output, jump) independent pure-Python implementation of the public-domain reference code bit-exact shipped
Uniform [0,1), open-interval uniform, normal deviates by inversion, unbiased bounded integers, shuffle reference stream (exact) + moment and χ² uniformity checks at fixed seeds exact / statistical (5 SE) shipped
Input distributions: quantiles, mean, variance (normal, lognormal, uniform, triangular, PERT, discrete, empirical) scipy.stats (norm, lognorm, uniform, triang, beta, rv_discrete), numpy.quantile(method="inverted_cdf") quantile rel 1e-10; moments rel 1e-12 shipped
Matrix product, thin QR, SVD, symmetric eigen, Cholesky, solve, inverse, condition number, log-determinant (Accelerate LAPACK) numpy.linalg rel 1e-11 – 1e-13 shipped
Brent root finding, bracketing, secant scipy.optimize.brentq, analytic roots rel 1e-12 (1e-9 for the flat exponential case) shipped
Golden-section and Brent scalar minimisation scipy.optimize.minimize_scalar(bounded) x rel 1e-7 (√ε limit), f rel 1e-12 shipped
Nelder–Mead, unbounded (Rosenbrock) analytic minimum and scipy.optimize.minimize(method="Nelder-Mead") x rel 1e-7 shipped
Nelder–Mead with box bounds + restarts analytic constrained minimum (plain clipping, as in SciPy, stops short; restarts reach it) x 1e-6, f rel 1e-10 shipped
Mean, median, variance/SD (sample & population), min, max, range, sum, SEM, CV, geometric/harmonic/trimmed mean, central moments NumPy, scipy.stats rel 1e-12 (moments 1e-11) shipped
Skewness G1 / g1, excess kurtosis G2 / g2 (Excel SKEW, SKEW.P, KURT and population) scipy.stats.skew/kurtosis with bias=False/True rel 1e-11 shipped
Quantiles, Hyndman–Fan types 1, 2, 4, 5, 6, 7 (default), 8, 9; IQR numpy.quantile(method=…), Excel PERCENTILE.INC / QUARTILE.EXC documentation examples rel 1e-12 shipped
Quantile type 3 (closest observation) — — not shipped (NumPy and SAS disagree on the rounding rule; no single reference)
Mode (smallest most-frequent value; all modes) scipy.stats.mode exact shipped
Histogram counts and edges numpy.histogram exact counts, edges rel 1e-12 shipped
Covariance matrix (sample/population), Pearson, Spearman, Kendall τ-b matrices pandas.DataFrame.cov/corr, scipy.stats.kendalltau rel 1e-11 shipped
Missing values: listwise and pairwise deletion (matrices, counts, multi-column summary) pandas (corr, cov, dropna) rel 1e-11 shipped
Confidence interval of a mean (t, and z with known σ) scipy.stats.t.interval, norm.interval rel 1e-11 shipped
Proportion intervals: Wilson, Wald, Clopper–Pearson statsmodels.stats.proportion.proportion_confint rel 1e-11 (Clopper–Pearson 1e-10) shipped
Confidence interval of a variance (χ²) scipy.stats.chi2.ppf rel 1e-11 shipped
Confidence interval of a difference of means (pooled, Welch) statsmodels CompareMeans.tconfint_diff rel 1e-11 shipped
t-tests: one-sample, pooled, Welch, paired (two-sided, less, greater), CI, Cohen's d scipy.stats.ttest_1samp/ttest_ind/ttest_rel statistic rel 1e-12, p rel 1e-10 shipped
z-tests: one- and two-sample (known σ or sample variances), one- and two-sample proportions statsmodels ztest, CompareMeans.ztest_ind, proportions_ztest; closed forms for known σ statistic rel 1e-12, p rel 1e-11 shipped
Chi-square goodness of fit (equal, given probabilities, reduced df) scipy.stats.chisquare statistic rel 1e-12, p rel 1e-10 shipped
Chi-square independence (expected counts, Yates 2×2 option), Cramér's V scipy.stats.chi2_contingency, contingency.association statistic rel 1e-12, p rel 1e-10 shipped
One-way ANOVA (SS, df, MS, F, p, η²) scipy.stats.f_oneway, statsmodels anova_lm rel 1e-11, p rel 1e-9 shipped
Two-way ANOVA without replication statsmodels anova_lm(typ=2) rel 1e-10, p rel 1e-9 shipped
Two-way ANOVA with replication (balanced) statsmodels anova_lm(typ=2) rel 1e-10, p rel 1e-9 shipped
Two-way ANOVA, unbalanced designs — — not shipped (needs Type II/III sums of squares; use the regression module with dummies)
Tukey HSD (Tukey–Kramer): differences, adjusted p, simultaneous CIs scipy.stats.tukey_hsd difference rel 1e-12; p and CI rel 1e-6 shipped
F-test of two variances (three alternatives, CI of the ratio) scipy.stats.f closed forms rel 1e-10 shipped
Levene (mean) and Brown–Forsythe (median) scipy.stats.levene(center=…) statistic rel 1e-11, p rel 1e-9 shipped
Bartlett scipy.stats.bartlett statistic rel 1e-11, p rel 1e-9 shipped
Jarque–Bera scipy.stats.jarque_bera statistic rel 1e-10, p rel 1e-9 shipped
Shapiro–Wilk (Royston AS R94), n = 3 … 4999 incl. ties scipy.stats.shapiro W rel 2e-9, p rel 2e-8 (the reference uses a lower-precision normal quantile) shipped
Mann–Whitney U, normal approximation with tie and continuity corrections scipy.stats.mannwhitneyu(method="asymptotic") U exact, p rel 1e-10 shipped
Mann–Whitney U, exact small-sample distribution — — not shipped (normal approximation only, as documented)
Wilcoxon signed-rank, normal approximation with tie correction (zeros dropped), optional continuity correction scipy.stats.wilcoxon(method="asymptotic") W exact, p rel 1e-10 shipped
Kruskal–Wallis H with tie correction scipy.stats.kruskal statistic rel 1e-11, p rel 1e-9 shipped
Correlation tests: Pearson (t, Fisher-z CI), Spearman (t approximation), Kendall τ-b (normal approximation with ties) scipy.stats.pearsonr/spearmanr/kendalltau(method="asymptotic") estimate rel 1e-12, p rel 1e-9, CI rel 1e-10 shipped
Fisher's exact test, McNemar, Friedman, Kolmogorov–Smirnov, Anderson–Darling — — not shipped (not requested)

# Statistics — regression and econometrics

method reference tolerance achieved status
OLS (QR): coefficients, SE, t, p, CI (95 %, 90 %), covariance statsmodels 0.14.6 OLS rel 1e-10 (p: 1e-8) shipped
OLS fit statistics: R², adj. R², F & p, log-likelihood, AIC, BIC, σ², SSR, TSS statsmodels OLS rel 1e-11 – 1e-12 (F 1e-8) shipped
OLS influence: leverage, internally studentized residuals, Cook's distance statsmodels OLSInfluence rel 1e-10 / 1e-8 / 1e-8 shipped
VIF, condition number statsmodels variance_inflation_factor, condition_number rel 1e-10 shipped
OLS without intercept (uncentred R², user-supplied constant detected) statsmodels OLS rel 1e-10 – 1e-12 shipped
Rank deficiency / n ≤ k / constant y / non-finite data → StatError behavioural tests exact shipped
Categorical dummy expansion (drop-first, explicit levels, interactions) pandas get_dummies(drop_first=True) + statsmodels rel 1e-9 shipped
WLS: coefficients, SE, R², log-likelihood, AIC/BIC, residuals statsmodels WLS rel 1e-10 – 1e-12 shipped
WLS leverage numpy projector on √w·X (statsmodels OLSInfluence is inconsistent for WLS) rel 1e-10 shipped
HC0, HC1, HC2, HC3 (z and t inference, robust Wald F) for OLS; HC1/HC3 for WLS statsmodels cov_type='HC*' (use_t False/True) rel 1e-9 (p 1e-8) shipped
Newey–West HAC, Bartlett kernel, with/without n/(n−k) correction statsmodels cov_type='HAC' (maxlags, use_correction) rel 1e-9 shipped
Cluster-robust covariance (with/without G/(G−1)·(n−1)/(n−k); z, or t with G−1 df) statsmodels cov_type='cluster' rel 1e-9 shipped
Breusch–Pagan (Koenker studentized and original), LM and F forms statsmodels het_breuschpagan(robust=True/False) rel 1e-8 shipped
White test (LM and F forms, redundant products dropped) statsmodels het_white rel 1e-8 shipped
Durbin–Watson statsmodels durbin_watson rel 1e-9 shipped (statistic only, no p-value)
Breusch–Godfrey (LM and F forms) statsmodels acorr_breusch_godfrey rel 1e-8 shipped
Ljung–Box, Box–Pierce (with modelDF) statsmodels acorr_ljungbox rel 1e-8 shipped
Jarque–Bera on residuals (skewness, kurtosis) statsmodels jarque_bera rel 1e-8 shipped
Ramsey RESET (F form and Wald χ² form, powers 2–4) statsmodels linear_reset(use_f=True/False) rel 1e-6 shipped
Fixed effects (within), entity and entity+time, balanced and unbalanced: coefficients, SE, t, cov, df, σ², R², F, poolability F, residuals, estimated effects linearmodels PanelOLS + statsmodels LSDV (explicit dummies) rel 1e-9 (F 1e-8; tiny p-values abs 1e-13 because linearmodels computes 1 − cdf) shipped
Fixed effects, heteroskedasticity-robust covariance and robust F linearmodels cov_type='robust' (HC0 × N/df_resid) rel 1e-9 shipped
Fixed effects, entity-clustered covariance: plain sandwich, linearmodels scaling, LSDV correction (z, or t with G−1 df) linearmodels cov_type='clustered', cluster_entity=True; statsmodels LSDV cov_type='cluster' rel 1e-8 – 1e-9 shipped
Stata xtreg, fe vce(cluster) scaling of clustered SE no Stata available — not shipped (cannot be validated)
Random-effects / between / first-difference panel estimators, Hausman test — — not shipped (out of scope)
Logistic regression (IRLS): coefficients, SE, z, p, CI, cov, odds ratios, log-likelihood, pseudo-R², LR test, AIC/BIC, fitted probabilities, classification table, no-intercept statsmodels Logit (Newton, tol 1e-13) rel 1e-9 (null log-lik vs closed form 1e-14; vs statsmodels' numerically fitted null 1e-9) shipped
Logistic: 2×2 closed forms (log-odds, odds ratio, Woolf SE); complete / quasi-complete separation → StatError.notConverged analytic + behavioural tests rel 1e-9 shipped
Externally studentized residuals, DFFITS, DFBETAS — — not shipped (not requested)

# Statistics — time series, multivariate, least squares

method reference tolerance achieved status
TimeSeries.difference (regular, seasonal, repeated) / integrate / differenceHeads numpy.diff; exact round trip 1e-13 rel (vs NumPy); round trip 1e-11 shipped
TimeSeries.acf (divisor-n autocorrelations, Bartlett standard errors, z/√n bound) statsmodels acf(adjusted=False, alpha=0.05) values 1e-12; interval bounds 1e-10 shipped
TimeSeries.pacf (Durbin–Levinson on divisor-n ACF, se 1/√n) statsmodels pacf(method="ldb") and "ywm" 1e-11 (ldb), 1e-9 (ywm) shipped
ExponentialSmoothing.fit recursions at fixed parameters: SES, Holt, damped Holt, Holt–Winters additive & multiplicative (with/without trend, damped), heuristic and known initial states — fitted, level, trend, season, SSE, AIC, BIC statsmodels ExponentialSmoothing(..., initialization_method="legacy-heuristic" / "known").fit(optimized=False) (12 configurations) 1e-10 rel (states 1e-9) shipped
Exponential smoothing point forecasts same; horizons that are not multiples of m 1e-10 rel shipped — at h = m, 2m, … statsmodels reuses the seasonal state of time n − m; ours uses the latest state s_n (textbook formula). The test shows that swapping the state reproduces statsmodels to 1e-10, and a hand-computed case pins the textbook behaviour
Exponential smoothing parameter optimisation (bounded Nelder–Mead on SSE, 5 starts; φ ∈ [0.8, 0.995]) statsmodels .fit() with the same fixed initial states (6 models) our SSE ≤ statsmodels SSE × (1 + 1e-6) in every case; α agrees to 1e-3 for SES shipped
Prediction intervals for linear (class 1) models: ANN, AAN, AAdN, ANA, AAA, AAdA variance multipliers 1 + Σ c_j²; σ² = SSE/n statsmodels ETSResults._relative_forecast_variance closed forms (Hyndman et al.) 1e-11 rel, h = 1…30 shipped
Prediction intervals with multiplicative seasonality no closed form — not shipped (API returns nil)
Multiplicative trend, multiplicative-error ETS, optimisation of initial states — — not shipped (not implemented)
ARIMA(p,d,q) exact Gaussian log-likelihood (Kalman filter on the d-times differenced series, stationary initialisation), innovations, AIC, BIC at fixed parameters statsmodels ARIMA(diff(y, d), order=(p,0,q), trend="c"/"n").loglike / smooth (8 models incl. (3,0,2), (0,2,1), (0,1,0)) log-likelihood, AIC, BIC 1e-10 rel; innovations 1e-8 shipped
ARIMA forecasts and standard errors, d = 0 statsmodels get_forecast at the same parameters 1e-9 rel shipped
ARIMA forecasts and standard errors, d > 0 (exact integration of the forecast-error covariance) integrated statsmodels forecasts of the differenced model (mean, 1e-9); ψ-weight standard errors via arma2ma (1e-6); statsmodels full ARIMA(y,(p,d,q)) with approximate diffuse prior (1e-4) 1e-9 / 1e-6 / 1e-4 shipped
ARIMA maximum-likelihood estimates (Nelder–Mead on the concentrated exact likelihood, PACF transform for stationarity/invertibility, 3 starts) statsmodels ARIMA(...).fit() (7 models) log-likelihood ≥ statsmodels − 1e-6 and within 1e-4 abs; parameters 2e-3; AIC 1e-3 abs shipped — estimation method: exact MLE (CSS not offered)
ARIMA standard errors (observed information, central-difference Hessian) statsmodels fit(cov_type="approx") 2 % rel shipped
Seasonal ARIMA (P,D,Q)m, exogenous regressors — — not shipped (not implemented)
GARCH(1,1) Gaussian log-likelihood, conditional variance and variance forecasts at fixed parameters (constant and zero mean, arch backcast initialisation) arch 8 arch_model(...).fix(params) (6 parameter sets) log-likelihood 1e-11 rel; variance path and forecasts 1e-10 shipped
GARCH(1,1) maximum-likelihood estimates (bounded Nelder–Mead, 6 starts) arch_model(...).fit() log-likelihood ≥ arch − 1e-6 and within 1e-4 abs; parameters 5e-3 rel; AIC/BIC 1e-3 abs; forecasts 5e-3 shipped
GARCH parameter standard errors (robust/Bollerslev–Wooldridge), other orders, t/skew-t innovations — — not shipped (not implemented; no validated standard errors)
Augmented Dickey–Fuller: statistic, used lag, nobs (fixed lag, AIC and BIC autolag; regressions n / c / ct) statsmodels adfuller (72 cases: 4 series × 3 regressions × 6 lag rules) statistic 1e-9 rel; lag and nobs exact; information criterion 1e-9 shipped
ADF MacKinnon (1994) p-value and MacKinnon (2010) critical values statsmodels mackinnonp / mackinnoncrit (same response-surface coefficients) p-value 1e-8 rel; critical values 1e-12 shipped
ADF regression "ctt", t-stat autolag, KPSS / Phillips–Perron — — not shipped (not implemented)
PCA.fit (SVD; covariance or correlation; components, eigenvalues, ratios, singular values, scores, loadings, transform) scikit-learn PCA (raw and standardised data, truncated k, n < p), numpy eigvalsh of cov/corr matrices eigenvalues/ratios 1e-9; components 1e-8 and scores 1e-7 up to sign; sign convention (largest entry
KMeans.fit Lloyd iterations from fixed centroids: labels, centres, inertia scikit-learn KMeans(init=array, n_init=1, algorithm="lloyd", tol=0) (5 cases, k = 2…5) labels exact; centres and inertia 1e-10 shipped
Silhouette (mean and per-observation) scikit-learn silhouette_score / silhouette_samples 1e-10 / 1e-9 shipped
k-means++ seeding with Xoshiro256StarStar + restarts best inertia vs scikit-learn n_init=50 on well-separated data; bitwise reproducibility for a given seed inertia 1e-9 (same optimum); seeded runs identical shipped (plain k-means++, not scikit-learn's greedy variant: individual runs are not comparable)
HierarchicalClustering.linkage (single, complete, average, Ward; Euclidean) and tree cutting scipy linkage / fcluster(maxclust) merge order exact; distances 1e-10; partitions identical shipped (O(n³), no centroid/median/weighted linkages, no other metrics)
LeastSquares.levenbergMarquardt / curveFit (analytic, central- or forward-difference Jacobian; covariance s²(JᵀJ)⁻¹) NIST StRD Misra1a and DanWood certified parameters and RSS (2 starts each); scipy curve_fit with analytic Jacobian (decay, weighted Gaussian); scipy least_squares(method="lm") Rosenbrock parameters 1e-8 (forward differences 1e-6); RSS 1e-9; covariance 1e-6 (analytic 1e-7, forward 1e-4) shipped
Parameter bounds / robust losses in least squares — — not shipped (not implemented)
Linear programming (simplex), general constrained "Solver" wrapper — — not shipped (optional; not implemented)

# Statistics — resampling; WhatIf target

method reference tolerance achieved status
Bootstrap percentile interval (type-7 quantiles of replicates) scipy.stats.bootstrap(method="percentile") fed the same NumPy index matrix (B = 2000, n = 25; mean, sd, median at 95 % / 80 %; paired Pearson r at 90 %) 1e-12 rel shipped
Bootstrap BCa interval (z0 with ties counted ½, jackknife acceleration) scipy.stats.bootstrap(method="BCa"), same index matrix; z0 and acceleration vs SciPy's formulas interval 1e-10 rel; z0 1e-11; acceleration 1e-9 shipped
Bootstrap standard error (sd, n−1, of replicates) and bias SciPy standard_error, NumPy 1e-11 rel (SE), 1e-9 rel (bias) shipped
Bootstrap seeded reproducibility; coverage of 90 % intervals (300 samples, n = 30) bit-identical for equal seeds; coverage within binomial band 0.82–0.96 exact / statistical shipped
Permutation test, two independent samples, exact enumeration (mean and median difference; two-sided, less, greater) scipy.stats.permutation_test(permutation_type="independent") exact p-values and null distribution (462 and 184 756 arrangements) p 1e-13 rel; null distribution 1e-11 shipped
Permutation test, paired (sign flips), exact scipy.stats.permutation_test(permutation_type="samples") (512 arrangements) p 1e-13 rel shipped
Permutation test, Monte Carlo p = (b+1)/(m+1) exact enumeration of the same problem; closed form 1/(m+1) for the most extreme case within 5 binomial standard errors; closed form 1e-12 shipped
Monte Carlo output summary (mean, sd, SE, min/max, median, type-7 percentiles, g1 skewness, g2 excess kurtosis) NumPy / scipy.stats.skew, kurtosis on 3000 fixed outcomes 1e-12 rel (1e-10 for skew/kurtosis) shipped
Monte Carlo histogram (equal-width bins, last bin closed) numpy.histogram(bins=40) counts exact; edges 1e-12 shipped
Monte Carlo convergence of the running mean with standard error NumPy prefix means / sd 1e-11 rel (mean), 1e-10 rel (SE) shipped
Monte Carlo tornado: Spearman rank correlation of each input with the output, normalised ρ² contribution scipy.stats.spearmanr 1e-11 rel (ρ), 1e-10 rel (contribution) shipped
Monte Carlo sampling, independent inputs (inversion, xoshiro256**) bit-reproducible for equal seeds; sample moments vs theoretical moments of each InputDistribution; analytic mean of a product model exact / within 5 standard errors shipped
Iman–Conover rank correlation (Spearman target converted with r = 2·sin(πρ/6)) marginals: each column is an exact permutation of the independent draw; achieved Spearman matrix vs target (N = 20 000) marginals exact; |ρ − target| < 0.02 shipped
Gaussian copula rank correlation achieved Spearman matrix vs target (N = 20 000); marginal moments vs theory |ρ − target| < 0.02; moments within 5 SE shipped
Monte Carlo cancellation / progress prefix of a cancelled run identical to the full run; tick counts exact shipped
Goal Seek (auto-bracket + Brent, secant fallback) analytic annuity inversions (principal, term), IRR of a 3-flow DCF (quadratic root), bisection for the rate, linear cross-sheet model solution 1e-9…1e-11 rel; residual ≤ 1e-9·max(1,|target|) shipped
One- and two-variable Data Tables model algebra (profit model exact; annuity grid) exact / 1e-12 rel shipped
Scenario Manager (capture, apply as one undo step, restore point incl. formulas, JSON persistence in attachments["scenarios.json"], summary) hand-computed scenario outcomes; round trip; identity after undo / restore exact shipped
Sensitivity / tornado (±x %) analytic deltas, swings and elasticities of the linear profit model 1e-12 rel shipped
Monte Carlo on a workbook model same seed through MonteCarlo.run with the model as a closure inputs identical; outputs 1e-12 rel shipped
Break-even finder (single solve and range scan for all crossings) F/(p−c), c+F/v, two roots of a quadratic profit curve 1e-10 rel shipped
Isolation of temporary edits (.clone, .inPlace) snapshot of every cell (value bit patterns, formula text, spill flag) before/after each tool on success, failure, throw and cancellation; undo/redo labels and changeCount identical; clone mode leaves undo/redo/changeCount untouched shipped
Bootstrap-t (studentised) interval, stratified / block bootstrap — — not shipped (not requested; no reference run)
Latin hypercube sampling — — not shipped (not requested)
Break-even at a tangency (double root without sign change) in range-scan mode — — not shipped: the scan refines sign changes only (documented); the single-solve mode handles it through the secant fallback

# FinanceModels

method reference tolerance achieved status
TVM solve for N / rate / PV / PMT / FV (begin/end, compounding ≠ payment frequency, continuous) independent Python formulas + scipy.optimize.brentq; textbook values (PMT 1,199.10) PV/PMT/FV rel 1e-11; N rel 1e-9; rate rel 1e-9 shipped
Rate conversions (periodic ↔ nominal ↔ effective) closed forms in Python rel 1e-13 shipped
Amortization: fixed payment, fixed principal, interest-only, balloon, extra payments, half-up and half-even rounding Python decimal schedules (60-digit context) built from the stated rules; invariants (balance ends at 0.00, Σ principal = loan) exact to the cent, every row shipped
Canadian mortgage (semi-annual compounding; monthly, semi-monthly, bi-weekly, weekly, accelerated bi-weekly/weekly; term balance) Python decimal reference; textbook 100,000 @ 6 % / 25 y = 639.81 exact to the cent shipped
Day counts 30/360 SIA, 30E/360, ACT/360, ACT/365F, ACT/ACT ISDA, ACT/ACT ICMA independent Python implementation from the ISDA/SIFMA/ICMA definitions; ISDA 1998 ACT/ACT memo example (0.497724380567) day counts exact; fractions rel 1e-14 shipped
Day count 30/360 US (Excel basis 0) vs SIA and ISDA 30/360 FunctionsLookup DayCount.days30US (the YEARFRAC/COUPDAYBS implementation validated on Excel-computed values) on 9 456 month-end/February date pairs (excel_compat.json, Generators/gen_excel_compat.sh); rule texts stated independently in Generators/daycount_conventions.py (Excel's 279 basis-0 YEARFRAC rows: Excel rule 279, SIA 273, ISDA 228) exact shipped
Bond price, yield, Macaulay and modified duration under convention: .excel FunctionsLookup Bond.price / yield / duration / modifiedDuration (the PRICE/YIELD/DURATION/MDURATION worksheet functions): 2 700 bonds, 5 bases, f = 1, 2, 4, end-of-February maturities (excel_compat.json) abs 1e-10 shipped
Bond clean/dirty price, accrued interest, coupon schedule under convention: .streetActual independent Python cash-flow discounting (10 bonds, 5 bases, last-period, zero-coupon, negative yield); Excel doc PRICE = 94.63436 rel 1e-12; Excel example to its 5 printed decimals shipped
Bond yield to maturity round trip + scipy.optimize.brentq; Excel doc YIELD = 6.5 % rel 1e-10; Excel example abs 5e-7 (price quoted to 5 dp) shipped
Macaulay / modified duration, DV01 Python definitions; finite differences; Excel doc DURATION = 10.9191453, MDURATION = 5.73567 rel 1e-12 (definitions), 1e-7 (finite difference); Excel examples to printed digits shipped
Convexity central finite difference of the price (h = 1e-5) + zero-coupon closed form rel 2e-5 vs FD (FD truncation), 1e-12 vs closed form shipped
Zero-coupon price / yield closed forms closed form rel 1e-13 shipped
NPV, Excel-style NPV, XNPV direct sums in Python; Excel doc values (1188.44, 2086.647602) rel 1e-12 shipped
IRR with all real roots, sign-change count, tangent roots, diagnostics numpy.roots on the discount-factor polynomial + Newton polish (9 flow patterns incl. 2 and 3 roots, none, negative, 181 flows); Excel doc 8.66 % / −2.1 % roots rel 1e-9; tangent root rel 1e-6 shipped (roots closer than ≈0.07 % in rate may be merged/missed — documented)
XIRR scipy.optimize.brentq on the dated NPV; Excel doc 0.373362535 rel 1e-10 shipped
MIRR Python formula; Excel doc 0.126094 rel 1e-13 shipped
Payback, discounted payback, profitability index, equivalent annual annuity, NPV ranking, crossover rate independent Python + hand cases rel 1e-12 shipped
Break-even (single product, target profit, margin of safety, operating leverage, sales mix) hand-computed exact cases exact shipped
DCF valuation (driver FCF, CAPM WACC, Gordon and exit-multiple terminal value, mid-year, EV→equity bridge, per share, implied multiple/growth, sensitivity grid) independent Python spreadsheet-style calculation (4 configurations); growing-perpetuity closed form rel 1e-12 shipped
DCF live-formula layout (ModelLayout) written into a real CalcEngine, recalculated, compared with the Swift numbers at two origins × 4 configurations; inputs edited in the sheet and re-compared rel 1e-12; no error cells shipped
Returns (simple/log), annualised return and volatility, Sharpe, Sortino, downside deviation, max drawdown numpy rel 1e-11 – 1e-12 shipped
Beta, Jensen's alpha, R², tracking error, information ratio numpy + statsmodels OLS rel 1e-9 – 1e-12 shipped
Historical VaR/CVaR (type-7 quantile), parametric normal VaR/CVaR numpy.quantile, scipy.stats.norm; exact grid case rel 1e-11 shipped
Covariance / correlation matrices, portfolio mean and variance numpy rel 1e-11 shipped
Unconstrained mean–variance: minimum variance, target-return, tangency, frontier bordered KKT linear system solved with numpy; two-asset analytic case weights rel 1e-8, variance rel 1e-9 shipped
Long-only mean–variance (active-set QP): minimum variance, target return, frontier scipy.optimize.minimize(method="SLSQP") on a 6-asset problem with binding bounds; independent KKT check weights abs 2e-6 (SLSQP's own accuracy), variance rel 1e-8, never worse than SLSQP shipped
Financial ratios (liquidity, leverage, profitability, efficiency, DuPont 3- and 5-step; averages; nil on zero denominators) hand-computed statements; DuPont identities exact / rel 1e-14 shipped
CAGR, linear trend (fit, forecast, prediction interval), seasonal naive, drift statsmodels OLS get_prediction (95 % and 80 %), numpy fit rel 1e-11; intervals rel 1e-9 shipped
Holt–Winters forecasting wrapper — — not shipped here: lives in Statistics (time-series fork); the integrator adds the thin FinanceModels wrapper
Long-only maximum-Sharpe (tangency) portfolio — — not shipped (no validated solver for the fractional programme; the long-only frontier points can be scanned instead)
Odd first/last coupon bonds, callable/floating/inflation-linked bonds, ex-dividend accrual — — not shipped (out of scope; no reference available locally)
Negative amortization, payment recast after prepayment, annuity-due loans — — not shipped (schedule throws when the payment is below the interest)

# Audit — Model Auditor and Formula Intelligence

method reference tolerance achieved status
FormulaShape (R1C1-relative normal form), FormulaDiff / FormulaComparison (LCS token diff) hand-derived R1C1 strings and token changes exact shipped
FormulaHighlighter (tolerant tokenizer, UTF-16 spans, stable colour index per distinct reference) hand-derived token streams incl. incomplete/invalid text; full-coverage invariant on 11 inputs exact shipped
FormulaAssist (enclosing function, argument index, signature/summary from the registry, completions: functions, names, tables, table columns, sheets) hand-derived on complete and incomplete inputs exact shipped
DependencyTracer precedents / dependents / neighbourhood graphs (depth limit, node cap, cycle guard, range expansion) hand-derived node and edge sets exact shipped
DependencyTracer.layout (longest-path layering, barycentric ordering, back-edge removal) hand-derived layers; invariants (edges go to higher layers, unique slots) on a 40-node DAG exact shipped
FormulaExplainer step-by-step evaluation (values only from CalcEngine.evaluate), deterministic description independently known intermediate values; every step re-evaluated through the engine exact shipped
ErrorExplainer (origin chain, #DIV/0! denominator tracing, #VALUE!, #NAME?, #REF!, #N/A, #NUM!, #CIRC!, arity) hand-derived chains and cause texts exact shipped
ModelAuditor: 21 rules (error origins, broken references, circular paths, inconsistent formulas, hardcoded overwrite, hardcoded constants, blank references, numeric text in arithmetic / ignored by aggregates, numbers stored as text, mixed table column types, text results in numeric columns, duplicated calculations, volatile functions, volatile + whole-column, complexity, unused / broken names, external links, merged cells in data, hidden content) seeded-defect workbook asserted finding-by-finding (28 findings), clean loan + DCF + sales models asserted to give zero findings (models themselves checked against closed forms, abs 1e-8) exact shipped
ModelAuditor slow-formula profile (times engine.evaluate per sampled formula, top N) ranking asserted on a workbook with one formula ≈ 2000× heavier than the rest; sampling/threshold logic exact ranking only (timings are machine-dependent) shipped
AuditReport Codable round trip, table(), markdown() exact comparison exact shipped
Full cycle search above cycleScanFormulaLimit formulas — — not shipped as a full scan: above the limit only cells showing #CIRC! are analysed (cycles masked by IFERROR are then missed; documented)
Dummy nodes / edge routing for edges spanning several layers — — not shipped (layout returns node positions only)

# Audit — conditional formatting, validation, tables

method reference tolerance achieved status
ConditionalFormatter — cellIs (8 operators, relative/absolute/mixed operands, blanks, text, errors) hand-derived expected cell sets on 1…10 and mixed columns exact shipped
ConditionalFormatter — formula rules (relative refs anchored at top-left of first range, multi-range, #REF! on shift off-sheet) hand-derived expected cell sets exact shipped
ConditionalFormatter — 2/3-point colour scales (min, max, num, percent, percentile, formula, autoMin/autoMax points; RGBA interpolation incl. alpha) closed-form linear interpolation, colours chosen so every channel is exact exact (8-bit channels, round half away from zero) shipped
ConditionalFormatter — percentile points Excel PERCENTILE.INC definition, h = p·(n−1) (test distinguishes it from PERCENTILE.EXC) exact shipped
ConditionalFormatter — data bars (positive, straddling zero with axis, all-negative, automatic bounds, no spread) closed form (v−lo)/(hi−lo), axis −lo/(hi−lo) ≤ 1e-15 shipped
ConditionalFormatter — icon sets (OOXML one-point-per-icon and cut-point forms, percent/percentile/num, reversed, 3 and 5 icons) hand-derived buckets exact shipped
ConditionalFormatter — duplicates/unique, top/bottom N and %, above/below average (± equal), contains text, blanks, errors hand-derived (Excel rules: INT(n·p/100) ≥ 1, ties included, case-insensitive text) exact shipped
ConditionalFormatter — priority order, property-wise style merge, stopIfTrue, scale + bar + icon + style combination hand-derived exact shipped
ConditionalFormatter — statistics cache (100 000-row column, 40 windows of 50 rows) statisticsComputations counter: 1 scan per rule, rescan only after edit / undo / invalidate() exact counts shipped
Validator — whole, decimal, date, time, textLength with all 8 operators; allowBlank; bounds from cells shifting with the cell; alert styles and messages hand-derived accept/reject cases exact shipped
Validator — list (literal, range, defined name; drop-down items) Excel behaviour: literal lists case-sensitive, cell lists case-insensitive exact shipped
Validator — custom formula with the proposed value substituted for the cell reference (no workbook edit; undo/changeCount unchanged) hand-derived; workbook/undo state compared before/after exact shipped (limitation: multi-cell ranges containing the validated cell read the stored value)
Validator — invalidCells ("circle invalid data"), typed-input validation through InputParser hand-derived exact shipped
TableOps — header detection, column naming, makeTable/addTable (undoable), name/overlap/merge guards, currentRegion hand-derived on a sales table exact shipped
TableOps — auto-expansion decision (row / column / none) hand-derived cases (totals row, blocking content, other table, merge) exact shipped
TableOps — totals-row formulas (SUBTOTAL 101–110 with structured reference, escaping) text compared; round-trip through SheetCore FormulaParser exact text shipped (formula text only: SUBTOTAL is not in SheetCore's core registry, evaluation is the function library's)
TableOps — filter evaluation → rows to hide (value sets, numeric/text conditions, AND/OR pairs, search, top/bottom N or %, date periods, blanks) hand-derived row sets exact shipped
TableOps — multi-column stable sort order (type ranks, blanks last, case-sensitive option) hand-derived orders + agreement with CalcEngine.sort on the sales table exact shipped
TableOps — remove duplicates (rows to delete), missing values, column type inference (incl. date formats), per-column summaries (mean, median, sample SD…) hand-computed statistics ≤ 1e-13 shipped
Text filter wildcards (*, ?) — — not shipped (begins/ends/contains cover the cases; no reference behaviour validated)
Files main
Menu

Theme