Statistics and Machine Learning Toolbox

Classification, regression, clustering, and PCA workbench for Python for Excel

Train, evaluate, and diagnose classification, regression, clustering, and dimension reduction models directly on spreadsheet data tables.

Result preview

The Statistics and Machine Learning Toolbox is a production-grade machine learning and statistical modeling workbench built natively inside Excel. The workbook decouples model definition, hyperparameter configuration, solver training, holdout inference, and automated diagnostics so that a single unified Excel surface supports classification, regularized linear modeling, unsupervised clustering, and dimensionality reduction.

The Start sheet selects one of five included statistical models and configures operational run controls (test split ratio, pseudo-random seed, cross-validation folds, and feature normalization). The Data sheet holds tabular observations with feature columns and target variables. The Model Config sheet provides algorithm-specific hyperparameter controls with dynamic clamping. When modified, the notebook automatically ingests the active configuration, executes the model pipeline in scikit-learn, and publishes structured outputs across Results, Predictions, and Diagnostics.

The default Customer Churn — Logistic Regression model trains a binary classifier with an L2 penalty on customer behavioral metrics. On the baseline run (Standard scaling, Test size 0.25, seed 42, 3 CV folds), the model achieves: - Primary metric (Accuracy): 1.000000 (100.0%) - Secondary metric (F1 score): 1.000000 - Tertiary metric (ROC-AUC): 1.000000 - Cross-validation mean: 0.944444 (94.4%) - Top predictive feature: Feature3 (MonthlySpend / Complaints), Weight = 1.135471, Odds Ratio = 3.1127, AbsImportance = 1.135471, Rank 1 - 7-Check Diagnostics: all 7 checks PASS

Switching Start → Active model instantly recompiles the pipeline for Ridge property valuation (\(R^2 = 0.986518\), RMSE = \(14.398038\)), K-Means flower morphology clustering (\(\text{Silhouette} = 0.540379\), Inertia = \(11.238210\)), Decision Tree credit default underwriting (\(\text{Accuracy} = 1.000000\), CV Mean = \(0.722222\)), or Principal Component Analysis (\(\text{Total Explained Variance} = 1.000000\)).

For a first run:

1. Leave Start → Active model as customer_churn_logistic and Scale features as Standard.
2. Inspect the feature and target distributions on Data.
3. View Results for overall performance metrics and feature odds ratios (\(\exp(w_j)\)).
4. Open Predictions to inspect sample-level classification probabilities, holdout splits, and prediction accuracy.
5. Review Diagnostics to verify data adequacy, condition numbers, residual normality, and generalization stability.

For a guided walkthrough, see Getting started.

What this toolbox covers

Modules

1. Classification Algorithms — Regularized logistic regression and decision tree classifiers with class probabilities, odds ratios, and ROC-AUC evaluation.
2. Regularized Regression — Ridge regression with analytical L2 Tikhonov penalty shrinkage, \(R^2\), RMSE, and MAE residual scoring.
3. Cluster Partitioning — K-Means centroid clustering with k-means++ seeding, inertia, and silhouette score diagnostics.
4. Dimensionality Reduction — Principal Component Analysis (PCA) with singular value decomposition (SVD), explained variance ratios, and factor loadings.
5. Model Validation & Holdouts — Dynamic train/test holdout partitioning (Test size on Start!B7) and stratified \(k\)-fold cross-validation.
6. 7-Check Diagnostics Suite — Automated statistical validation covering data adequacy, solver convergence, metric thresholds, generalization gaps, multicollinearity condition numbers, class balance, and standardized residual outliers.

Documentation

• Getting started — First classification solve, changing tasks, tuning hyperparameters, and reading outputs.
• Modeling guide — Tabular data specifications, target column conventions, scaling options, and hyperparameter tables.
• Methods guide — Mathematical formulations, loss functions, optimization algorithms, diagnostic equations, and MATLAB capability mapping.
• Worked examples — Detailed walkthroughs of all five included machine learning and statistical models.
• Reference — Control values, input cell ranges, output schemas, diagnostic thresholds, tolerances, and troubleshooting.

Why Python

Excel is where operational business data lives: customer records, financial accounts, pricing logs, and operational measurements. However, native Excel formulas lack built-in machine learning algorithms, regularized loss functions, cross-validation partitioning, and singular value decomposition.

Python provides scikit-learn’s verified numerical engine directly inside Excel. Analysts can run reproducible cross-validation, feature standardization, and multidimensional clustering without exporting files to external scripts or maintaining disconnected Jupyter notebooks. All computations execute within the browser’s Pyodide WebAssembly runtime with zero server dependencies.

Operating workflow

Author: A data scientist, quantitative researcher, or analytics engineer sets up the model schema, validates cross-validation folds, tunes hyperparameter bounds on Model Config, and inspects diagnostic tolerances.

Workbook user: A business analyst, risk officer, or operations manager pastes fresh observations into Data, selects the active model on Start, and utilizes the predicted scores and holdout classifications on Predictions for operational reporting.

Download the Excel template

• Statistics and Machine Learning Toolbox workbook
• Getting started
• Complete reference

Capability reference

The table below outlines current and planned capabilities of the Statistics and Machine Learning Toolbox:

Capability	Status	Current implementation	Engine / Algorithm
L2-Regularized Logistic Regression	Available	Scikit-learn LogisticRegression	L-BFGS solver, logit link, odds ratio \(\exp(w_j)\)
Ridge Regression (L2 Regularization)	Available	Scikit-learn Ridge	Analytical closed-form SVD / Cholesky solver
Decision Tree Classification	Available	Scikit-learn DecisionTreeClassifier	CART algorithm, Gini impurity splitting, depth pruning
K-Means Clustering	Available	Scikit-learn KMeans	Lloyd’s algorithm, k-means++ seeding, silhouette scoring
Principal Component Analysis	Available	Scikit-learn PCA	Full SVD decomposition, scree variance attribution
Train / Test Holdout Partitioning	Available	Scikit-learn train_test_split	Stratified (classification) and random holdout splits
K-Fold Cross-Validation	Available	Scikit-learn cross_val_score	Stratified and standard cross-validation across 2–5 folds
Feature Standardization	Available	Scikit-learn StandardScaler	\(z = (x - \mu) / \sigma\) centering and unit variance
Min-Max Feature Scaling	Available	Scikit-learn MinMaxScaler	Range scaling to \([0, 1]\) interval
Raw Feature Pass-Through	Available	Direct NumPy copy	Unscaled feature matrix preservation
7-Check Diagnostics Suite	Available	Custom automated diagnostics	Data, convergence, performance, stability, condition, balance, outliers
Feature Attribution & Odds Ratios	Available	Deterministic ranking	Odds ratios, normalized regression weights, Gini importance
Random Forest Ensembles	Planned	Scikit-learn RandomForestClassifier	Bootstrap aggregating with out-of-bag scoring
Support Vector Machines (SVM)	Planned	Scikit-learn SVC / SVR	Linear and RBF kernel maximum-margin boundaries
Gradient Tree Boosting	Exploring	LightGBM / XGBoost	Hist-gradient boosting for high-capacity tabular data

Workbook architecture

The workbook geometry is partitioned into dedicated control, ingestion, and reporting sheets:

flowchart TD
    subgraph Inputs["Authored Input Sheets"]
        Start["Start Sheet\nControls: A5:B10\nModel Catalog: D5:H10"]
        Data["Data Sheet\nDataset: A5:G70\n(SampleID, Features 1-4, Target)"]
        Config["Model Config Sheet\nHyperparameters: A5:D20\n(C, alpha, max_iter, depth, k)"]
    end

    subgraph Runtime["Marimo Reactive Runtime"]
        Ingest["bf.inputs()\nDependency Graph Resolution"]
        Preproc["Feature Scaling & Train/Test Split\n(Standard / MinMax / None)"]
        Solver["Scikit-Learn Estimator\nFit & Cross-Validation"]
        Diag["7-Check Automated Diagnostics\nConditioning, Normality, Stability"]
    end

    subgraph Outputs["Published Output Sheets"]
        Results["Results Sheet\nSummary: A5:B16\nCoefficients: D5:I16"]
        Predictions["Predictions Sheet\nSamples: A5:F55\n(Actual, Predicted, Probs, Split)"]
        Diagnostics["Diagnostics Sheet\nChecks: A5:C25\n(Check, Severity, Detail)"]
    end

    Start --> Ingest
    Data --> Ingest
    Config --> Ingest
    Ingest --> Preproc
    Preproc --> Solver
    Solver --> Diag
    Solver --> Results
    Solver --> Predictions
    Diag --> Diagnostics

Table geometry and cell anchoring

The workbook binds to the Marimo computational pipeline through four dedicated publication anchors:

Sheet	Anchor Cell	Spill Range	Publication Key	Output Description
Results	A5	A5:B16	summary	12 summary metrics (Model, Task, Status, Algorithm, Samples, Features, Primary/Secondary/Tertiary metrics, CV mean, Scaling, Runtime)
Results	D5	D5:I16	coefficients	Feature weights table (Feature, Weight, OddsRatio, AbsImportance, Rank, Significance)
Predictions	A5	A5:F55	predictions	Row-level inference table (SampleID, Actual, Predicted, Probability / Residual / CentroidDist, Split, Correct)
Diagnostics	A5	A5:C25	diagnostics	Automated diagnostics table (Check, Severity: PASS/WARN/FAIL, Detail)

Notebook implementation

The Python implementation (notebook.py) is structured as a reactive Marimo application that executes cleanly in single-worker WebAssembly environments:

1. Imports and Setup (@app.cell): Loads foundational libraries (numpy, pandas, scipy.stats, sklearn, boardflare, marimo).
2. Input Ingestion (@app.cell): Calls bf.inputs() to bind spreadsheet cell ranges reactively:
   • controls: Start!A5:B10
   • models: Start!D5:H10
   • data: Data!A5:G70
   • config: Model Config!A5:D20
3. Model Pipeline & Execution (@app.cell):
   • Parses active model selector and control switches (seed, cv_k, scale_choice, test_size).
   • Dynamically ingests and clamps hyperparameters from Model Config within mathematical safety bounds.
   • Applies feature transformations (StandardScaler, MinMaxScaler, or raw pass-through).
   • Generates deterministic train/test splits (stratified for classification, uniform for regression/unsupervised).
   • Trains the active estimator and computes out-of-fold cross-validation scores.
   • Evaluates primary, secondary, and tertiary metrics per task specification.
   • Executes the 7-Check Diagnostics Suite evaluating condition numbers, residual normality, and sample stability.
4. Interactive Markdown & Charts (@app.cell): Generates an active model status card and horizontal feature importance bar chart (matplotlib).
5. Output Publication (@app.cell): Dispatches bf.publish() to deliver four rectangular dataframes into workbook publication ranges.

Validation and expected results

All five models included in the catalog have been validated against analytical closed forms and scikit-learn standard benchmarks:

Model ID	Task Family	Algorithm	Primary Metric	Secondary Metric	CV Mean	Top Feature (Rank 1)	Diagnostic Evidence
customer_churn_logistic	Classification	LogisticRegression	Accuracy: 1.000000	F1: 1.000000	0.944444	Feature3 (w=1.135471)	7 PASS
housing_regression_ridge	Regression	Ridge	\(R^2\): 0.986518	RMSE: 14.398038	0.953845	Feature1 (w=60.173745)	7 PASS
iris_clustering_kmeans	Clustering	KMeans	Silhouette: 0.540379	Inertia: 11.238210	0.540379	Feature2 (w=0.317303)	7 PASS
credit_default_tree	Classification	DecisionTreeClassifier	Accuracy: 1.000000	F1: 1.000000	0.722222	Feature4 (w=1.000000)	6 PASS, 1 FAIL (Generalization gap)
feature_pca	Decomposition	PCA	ExpVar: 1.000000	PC1: 0.999607	1.000000	Feature1 (w=0.500750)	6 PASS, 1 FAIL (Conditioning \(\kappa \ge 100\))

[!NOTE] Diagnostic failures on credit_default_tree (Generalization gap = 0.2778) and feature_pca (Condition number \(\kappa = 431.13\)) are intentional test cases demonstrating the automated diagnostics suite’s ability to flag sample size limitations and severe feature collinearity.

Limitations

• Dataset size: Optimized for interactive browser analysis on datasets up to 1,000 samples and 30 features.
• Runtime environment: Executes in a single-threaded Pyodide WebAssembly runtime; large hyperparameter grid searches should be pre-screened.
• Deep learning: Focused on classical machine learning, regularized linear models, and dimension reduction; neural networks are out of scope.

When to use this approach

Use this toolbox when your datasets originate in or are distributed via Excel, and stakeholders require predictive scoring, feature importance attribution, or customer segmentation with complete auditability and zero external server installations.

Related templates

• Regression — Single-purpose ordinary least squares regression template.
• Optimization Toolbox — Mathematical programming workbench for LP, MILP, QP, and nonlinear models.
• Financial Risk Toolbox — Value-at-Risk and risk attribution workbench.

How this compares with MATLAB and Excel

Dimension	Native Excel	MATLAB Statistics & Machine Learning Toolbox	Boardflare Statistics & ML Toolbox
Algorithms	Simple linear regression trendline	fitclinear, fitrlinear, fitctree, kmeans, pca	Scikit-learn LogisticRegression, Ridge, DecisionTreeClassifier, KMeans, PCA
Hyperparameter Tuning	None	Fit parameters via command line or app	Interactive spreadsheet table on Model Config
Holdout Validation	Manual copy-pasting	cvpartition, automated partition objects	Built-in stratified and random holdout partitioning (Test size)
Cross-Validation	None	crossval with \(k\)-fold loss scoring	Automated out-of-fold \(k\)-fold cross-validation
Diagnostics	Basic summary statistics	Residual plots, ROC curves, confusion matrices	Automated 7-Check Diagnostics Suite (PASS/WARN/FAIL)
Deployment	Volatile workbook formulas	Desktop runtime or MATLAB Web App Server	Interactive spreadsheet running in-browser with zero install

Changelog

v1.1.0 — Comprehensive Machine Learning Workbench Upgrade (Issue #99)

• Dynamic Hyperparameter Ingestion: Connected algorithm parameters (C, alpha, n_clusters, max_depth, n_components, max_iter) dynamically to the Model Config sheet with rigorous bound validation and clamping.
• Holdout Test Partitioning: Added configurable holdout split ratio (Test size on Start!B7) supporting stratified classification and random holdouts, surfaced row-by-row on the Predictions sheet.
• 7-Check Diagnostics Suite: Implemented comprehensive automated diagnostics evaluating data adequacy, convergence, performance, generalization gap, multicollinearity condition numbers, class balance, and residual outlier detection.
• Full Model Coverage: Added PCA dimension reduction model to standard validation suite with verified numerical targets.
• Comprehensive Documentation Bundle: Authored complete technical guides for Getting Started, Modeling, Methods, Worked Examples, and Reference.