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) |