Econometrics Toolbox

Time series modeling, ARIMA, VAR, cointegration, and forecasting workbench for Python for Excel

Economics
Finance
Statistics
Forecasting
Fit Box-Jenkins ARIMA and SARIMAX models, estimate Vector Autoregressions, test for unit roots and cointegration, evaluate residual diagnostics, and generate multi-step out-of-sample forecasts directly in Excel.

Result preview

The Econometrics Toolbox is an institutional-grade time series modeling, estimation, and forecasting workbench built for spreadsheet workflows. Rather than treating econometric modeling as a static linear regression formula or an isolated forecasting script, the workbook separates model selection and controls, time series observation inputs, dynamic lag specifications, maximum likelihood estimation, out-of-sample prediction intervals, and residual diagnostic validation.

The Start sheet selects one of six included macroeconomic and financial models. The Time Series Data sheet holds historical observations across regular quarterly, monthly, or annual intervals. The Specification sheet defines autoregressive, differencing, moving average, seasonal, and vector lag orders. When inputs change, the Python for Excel notebook validates the data sequence, dispatches to the appropriate dynamic time series backend in statsmodels, calculates exact parameter standard errors and information criteria, runs a comprehensive 5-part residual and stationarity battery, and publishes normalized results across Results, Forecasts, and Diagnostics.

The default US Real GDP Growth — ARIMA model estimates an ARIMA(1,0,1) specification on quarterly growth series, achieving exact maximum likelihood convergence (OPTIMAL) with an AIC of 199.9972 and generating 4-quarter ahead point forecasts and 95% confidence bands. The same workbook can instantly be switched to monthly CPI inflation persistence, quarterly retail sales with exogenous consumer sentiment indicators, two-variable Treasury yield curve Vector Autoregression (VAR), Engle-Granger consumption-income cointegration with an Error Correction Model (ECM), or a dual ADF/KPSS stationarity testing workbench.

For a first run:

  1. Leave Start → Active model as us_gdp_growth.
  2. Inspect the quarterly annualized growth observations on Time Series Data.
  3. View Results for log-likelihood, AIC, BIC, HQIC, and AR/MA coefficient significance.
  4. Open Forecasts to view point forecasts and 95% confidence intervals through 2025-Q4.
  5. Review Diagnostics to verify passing tests for absence of residual autocorrelation (Ljung-Box), normality (Jarque-Bera), and homoskedasticity (Engle’s ARCH LM).

For a guided tour, see Getting started.

What this toolbox covers

The workbook provides a unified econometric analysis workflow covering univariate and multivariate linear time series models, statistical stationarity inference, and multi-step prediction uncertainty.

Modules

The workbench is organized around six core econometric modules:

  1. ARIMA & SARIMAX — Box-Jenkins autoregressive integrated moving average modeling with conditional sum-of-squares initialization, Kalman filter state-space formulation, and exact Gaussian maximum likelihood. Supports seasonal orders and exogenous regressors (\(X\)).
  2. Vector Autoregression (VAR) — Multi-equation dynamic systems capturing endogenous feedback loops between macroeconomic indicators, companion matrix stability checks, and Granger causality testing.
  3. Cointegration & Error Correction (ECM) — Engle-Granger two-step procedure testing for stationary linear combinations between non-stationary series, with dynamic Error Correction Model estimation recovering long-run equilibrium and short-run adjustment speeds.
  4. Stationarity & Unit Root Diagnostics — Augmented Dickey-Fuller (ADF) tests evaluating the null hypothesis of a unit root against KPSS tests evaluating the null hypothesis of stationarity.
  5. Out-of-Sample Forecasting — Multi-step ahead point projections with analytical prediction error variance accumulation and user-configurable confidence intervals (e.g., 90%, 95%, 99%).
  6. Residual Diagnostic Battery — Automated post-estimation verification testing for serial correlation (Ljung-Box \(Q\)), skewness and excess kurtosis (Jarque-Bera), and conditional autoregressive heteroskedasticity (Engle’s ARCH LM).

Documentation

The Toolbox provides a comprehensive documentation bundle:

  • Getting started — First solve, switching models, interpreting VAR and cointegration results, modifying forecast horizons, and updating lag orders.
  • Modeling guide — Complete worksheet schemas, date formatting conventions, series role mappings (Series1, Series2, Series3), lag parameter keys, and how to author custom time series models.
  • Methods guide — Mathematical formulations, state-space representations, exact likelihood functions, OLS VAR estimation, Engle-Granger mechanics, diagnostic test statistics, and MATLAB Econometrics Toolbox capability mapping.
  • Worked examples — Detailed walkthroughs of all six included macroeconomic models, covering economic intuition, mathematical formulations, inputs, and expected outputs.
  • Reference — Comprehensive control specifications, worksheet input/output ranges, status glossaries, parameter bounds, sample size constraints, and numerical troubleshooting.

Why Python

Macroeconomic forecasting and financial risk modeling require mathematical and statistical algorithms far beyond the capabilities of spreadsheet formulas:

  1. Exact Maximum Likelihood: Estimating ARIMA and SARIMAX parameters requires state-space Kalman filtering and numerical optimization (BFGS/L-BFGS) to compute exact Gaussian log-likelihoods without asymptotic truncation.
  2. Multivariate Vector Systems: Estimating VAR systems and dynamic ECM models requires simultaneous equation estimation, cross-equation covariance matrix computation, and multi-step forecasting incorporating joint prediction error covariance.
  3. Rigorous Hypothesis Testing: Proper econometric diagnosis requires non-standard distribution lookups (MacKinnon finite-sample p-values for ADF, asymptotic critical value interpolation for KPSS, \(\chi^2\) portmanteau distributions for Ljung-Box).

Native Excel formulas provide only simple exponential smoothing or linear trendlines, which fail to capture autocorrelation, mean reversion, seasonality, or confidence interval widening. Python executes statsmodels inside the browser runtime, pairing the institutional rigor of modern econometrics with the accessibility and visibility of a spreadsheet.

Operating workflow

Author: A macroeconomist, quantitative researcher, or financial analyst designs the econometric specification, verifies time series properties, sets lag structures, evaluates information criteria, and establishes model diagnostics.

Workbook user: A planning analyst, risk manager, or corporate finance team member inputs the latest economic releases or business operational metrics into Time Series Data, chooses the active model and forecast horizon on Start, and utilizes the generated forecasts and confidence intervals directly in financial planning.

A typical recurring handoff is:

[Author] Sets lag orders & checks ADF/KPSS/Ljung-Box diagnostics
   │
   ▼
[Workbook User] Pastes new quarterly/monthly figures into Time Series Data
   │
   ▼
[Python Notebook] Re-estimates MLE parameters & generates multi-step forecasts
   │
   ▼
[Workbook User] Reviews Results & Forecasts sheets for planning & budgeting

Download the Excel template

Capability reference

Capability map

Capability Status Current implementation
ARIMA / SARIMAX Available Statsmodels ARIMA & SARIMAX with exact maximum likelihood, Kalman filter recursions, and seasonal differencing
Vector Autoregression (VAR) Available Statsmodels VAR multi-equation estimation, lag selection, companion matrix stability, and Granger causality
Cointegration & Error Correction (ECM) Available Engle-Granger cointegration test via coint and dynamic two-step Error Correction Model estimation
Augmented Dickey-Fuller (ADF) Available Statsmodels adfuller with configurable maxlag and MacKinnon p-values
KPSS stationarity test Available Statsmodels kpss testing null hypothesis of level stationarity
Residual diagnostics Available Ljung-Box \(Q\) autocorrelation, Jarque-Bera normality, and Engle’s ARCH LM heteroskedasticity
Model selection criteria Available AIC, BIC, HQIC, and exact Log-Likelihood reporting
Out-of-sample forecasts Available Multi-step ahead point projections with analytical standard errors and normal confidence bands
Frequency & calendar progression Available Automated date advancement for Quarterly (YYYY-Q#), Monthly (YYYY-M##), and Annual (YYYY) series
GARCH volatility modeling Planned ARCH/GARCH conditional variance modeling for financial return series
State-space filtering / Dynamic factor Exploring Unobserved components models and dynamic factor extraction

Inputs and assumptions

Workbook map

Sheet Purpose Primary user
Start Selects active model (us_gdp_growth, cpi_inflation_arma, retail_sales_sarimax, treasury_rates_var, consumption_income_coint, macro_stationarity), forecast horizon (1–12), confidence level (0.50–0.999), and frequency. Author & Workbook User
Time Series Data Chronological observation store. Holds Model, Period, Date, Series1, Series2, and Series3. Workbook User
Specification Parameter orders (\(p, d, q, P, D, Q, s, \text{lags}, \text{maxlag}\)) defined per model ID. Author
Results Normalized summary metrics (AIC, BIC, HQIC, Log-Likelihood, Status) and estimated coefficient table (Terms, Coef, StdError, zStat, pValue, Significance). Workbook User & Reviewer
Forecasts Out-of-sample projection schedule detailing Horizon_Step, Period_Date, Point_Forecast, Lower_CI, Upper_CI, and Forecast_StdError. Workbook User
Diagnostics 7-point validation battery reporting data continuity, convergence status, ADF unit root, KPSS stationarity, Ljung-Box autocorrelation, Jarque-Bera normality, and ARCH heteroskedasticity. Author & Reviewer

Important assumptions

  • Model ID Join Key: All observation rows on Time Series Data and specification rows on Specification are indexed by their matching Model identifier.
  • Observation Ordering: Time series must be entered in ascending chronological order without gaps or missing periods.
  • Date Labels: Standard format strings are recommended: YYYY-Q# for Quarterly, YYYY-M## for Monthly, and YYYY for Annual.
  • Forecast Horizon Cap: Out-of-sample forecasting supports horizons from 1 to 12 steps ahead.
  • Confidence Level Range: The workbook validates confidence levels between 50% (\(0.50\)) and 99.9% (\(0.999\)), defaulting to 95% (\(0.95\)).

How the calculation works

  1. Ingestion & Validation: The notebook reads the active model control, extracts relevant observation rows from Time Series Data, validates chronological consistency, and loads parameter orders from Specification.
  2. Model Formulation & Fitting:
    • ARIMA/ARMA: Configures autoregressive order \(p\), integration order \(d\), and moving average order \(q\). Fits via statsmodels.tsa.arima.model.ARIMA using state-space exact MLE.
    • SARIMAX: Integrates exogenous variables (\(X\)), non-seasonal \((p,d,q)\) orders, and seasonal \((P,D,Q,s)\) orders via statsmodels.tsa.statespace.sarimax.SARIMAX.
    • VAR: Constructs multi-variate vector series \((Y_{1,t}, Y_{2,t})\) and estimates equation parameters via statsmodels.tsa.api.VAR. Evaluates Granger causality.
    • Cointegration & ECM: Evaluates Engle-Granger two-step procedure via coint, fits static cointegrating vector via OLS, extracts error-correction residuals \(\hat{e}_{t-1}\), and estimates the dynamic short-run error correction regression.
    • Stationarity: Estimates univariate AR(1) benchmarks for level and difference series, testing integration properties via dual unit root testing.
  3. Statistical Inference & Model Selection: Computes parameter standard errors, asymptotic \(z\)-statistics, \(p\)-values, log-likelihood (\(\ln L\)), Akaike Information Criterion (\(\text{AIC}\)), Bayesian Information Criterion (\(\text{BIC}\)), and Hannan-Quinn Information Criterion (\(\text{HQIC}\)).
  4. Out-of-Sample Forecasting: Propagates dynamic difference equations over \(h = 1, \dots, H\) periods. For ARIMA/SARIMAX, uses state-space Kalman recursions. For VAR, evaluates vector autoregressive multi-step matrix projections with joint covariance accumulation. For ECM, propagates error-correction adjustments with baseline exogenous drift.
  5. Comprehensive Diagnostic Battery: Computes post-estimation residuals and executes:
    • Augmented Dickey-Fuller (ADF) \(t\)-statistic on series levels.
    • Kwiatkowski-Phillips-Schmidt-Shin (KPSS) LM test for stationarity.
    • Ljung-Box \(Q\) portmanteau test for residual autocorrelation.
    • Jarque-Bera \(\chi^2\) test for residual normality (skewness and kurtosis).
    • Engle’s LM test for autoregressive conditional heteroskedasticity (ARCH effects).
  6. Publication: Dispatches structured DataFrames (summary, coefficients, forecasts, diagnostics) back to Excel worksheet spill anchors via bf.publish.

Validation and expected results

The toolbox package is validated against 6 macroeconomic and financial fixtures representing distinct time series structures:

Fixture Model ID Family Spec Expected Metrics & Acceptance Evidence
US Real GDP Growth us_gdp_growth ARIMA ARIMA(1,0,1) OPTIMAL; Sample: 28; AIC: 199.9972; BIC: 205.3260; Log-Likelihood: -95.9986; const: 2.8336; Step 1 Forecast: 2.8999 (95% CI: [-11.2803, 17.0801]); Ljung-Box: PASS
Consumer Price Inflation cpi_inflation_arma ARMA ARIMA(1,0,0) OPTIMAL; Sample: 24; AIC: 35.5444; BIC: 39.0786; ar.L1: 0.9617 (\(p < 0.001\), high persistence); Step 1 Forecast: 2.9000; Diagnostics: PASS
Retail Sales & Sentiment retail_sales_sarimax SARIMAX (1,1,1)x(0,1,1,4) OPTIMAL; Sample: 28; AIC: 90.2791; BIC: 94.4452; Exogenous consumer sentiment; Step 1 Forecast: 136.6552; Diagnostics: PASS
Treasury Yield Curve treasury_rates_var VAR VAR(1) OPTIMAL; Sample: 28; AIC: -3.2842; BIC: -2.9963; ShortRate const: -0.7143; L1.ShortRate: 0.6158; Granger causality test evaluated
Consumption & Income consumption_income_coint Cointegration Engle-Granger ECM OPTIMAL; Sample: 28; Coint Beta: 0.8440; ECM ECT_lag1: -0.8051 (error correction speed); Stationarity warnings on levels (expected cointegrating behavior)
Macro Stationarity macro_stationarity Stationarity ADF / KPSS NON_STATIONARY; Sample: 28; Level ADF stat: 0.2089 (\(p=0.9728\), unit root); Diff ADF stat: -5.1731 (\(p=0.0000\), stationary); Step 1 Forecast: 151.9513

Architecture

The Econometrics Toolbox is designed around clean structural separation between worksheet inputs, notebook computational logic, and published outputs:

┌────────────────────────────────────────────────────────┐
│                   EXCEL WORKBOOK                       │
│  Start Sheet           Time Series Data Sheet          │
│  [Active Model]        [Date-Indexed Observations]     │
│  [Horizon / Conf / Freq]                               │
│                                                        │
│  Specification Sheet                                   │
│  [Lag orders: p, d, q, P, D, Q, s, lags, maxlag]       │
└───────────────────────────┬────────────────────────────┘
                            │ bf.inputs(...)
                            ▼
┌────────────────────────────────────────────────────────┐
│             PYTHON FOR EXCEL NOTEBOOK                  │
│                                                        │
│  1. Ingestion & Boundary Coercion                      │
│     - Clamp forecast horizon (1 to 12)                 │
│     - Normalize confidence level (0.50 to 0.999)       │
│     - Parse frequency & advance period strings         │
│                                                        │
│  2. Dynamic Model Dispatch                             │
│     - ARIMA / ARMA (exact MLE)                         │
│     - SARIMAX (seasonal + exogenous regressors)       │
│     - VAR (multi-equation vector autoregression)       │
│     - Cointegration & ECM (two-step Engle-Granger)     │
│     - Stationarity workbench (ADF & KPSS tests)        │
│                                                        │
│  3. Statistical Inference & Forecasting                │
│     - Parameter standard errors, z-stats, p-values     │
│     - Analytical multi-step prediction error bands     │
│     - Information criteria (AIC, BIC, HQIC)            │
│                                                        │
│  4. Econometric Diagnostic Battery                     │
│     - ADF & KPSS unit root / stationarity tests        │
│     - Ljung-Box residual autocorrelation               │
│     - Jarque-Bera normality                            │
│     - Engle's ARCH LM heteroskedasticity               │
└───────────────────────────┬────────────────────────────┘
                            │ bf.publish(...)
                            ▼
┌────────────────────────────────────────────────────────┐
│               PUBLISHED OUTPUT TABLES                  │
│  Results:      Summary (A5) & Coefficients (D5)        │
│  Forecasts:    Multi-Step Projections & CI Bands (A5)  │
│  Diagnostics:  7-Point Econometric Battery (A5)        │
└────────────────────────────────────────────────────────┘

Key Architectural Principles

  1. Separation of Observations and Model Specifications: Historical observations reside on Time Series Data without embedded formulas or hardcoded assumptions. Econometric orders (\(p, d, q\)) reside on Specification, allowing users to change lag lengths without altering the underlying data series.
  2. Deterministic Reactive Pipeline: Changing Active model, Forecast horizon, or Confidence level on the Start sheet triggers an immediate, reactive re-estimation without requiring manual Python execution.
  3. Robust Boundary Coercion: All workbook inputs pass through boundary-coercion guards: strings like "95%" are parsed to 0.95, horizons are bounded between 1 and 12, and dates are incremented using calendar progression logic.

Notebook implementation

The Python for Excel notebook executes as a directed acyclic graph (DAG) across five discrete cells:

  1. Imports & Setup Cell: Imports numerical libraries (numpy, pandas, scipy.stats, matplotlib) and econometric routines from statsmodels (ARIMA, SARIMAX, VAR, adfuller, kpss, coint, acorr_ljungbox, het_arch, jarque_bera).
  2. Workbook Ingestion Cell (bf.inputs): Captures cell blocks from Start, Time Series Data, and Specification using bf.ref.
  3. Core Econometric Engine Cell:
    • Parses and clamps user controls.
    • Filters the active time series and checks sample size (\(N \ge 5\)).
    • Dispatches to the corresponding estimator backend based on model_family.
    • Generates multi-step point forecasts and analytical error bounds.
    • Evaluates the 5-point post-estimation diagnostic battery.
    • Assembles df_summary, df_coef, df_forecasts, and df_diag.
  4. Interactive Markdown & Matplotlib Visualization Cell: Generates an inline markdown status card and renders an annotated Matplotlib line plot illustrating historical data, out-of-sample forecast trajectories, and shaded confidence intervals.
  5. Excel Publication Cell (bf.publish): Dispatches the four standardized DataFrames to their designated worksheet anchors (Results!A5, Results!D5, Forecasts!A5, Diagnostics!A5).

Limitations

  • Dataset Regularity: Observations must be sampled at strictly regular intervals (quarterly, monthly, or annual). High-frequency irregular timestamp sequences (e.g. tick data) are not supported.
  • Horizon Range: Multi-step forecasts are bounded between 1 and 12 periods ahead to maintain numerical stability and econometric validity.
  • Single-Threaded Browser Execution: Runs inside the browser WebAssembly runtime; large VAR systems with many variables or high lag orders will take longer to compute.
  • Cointegration Capacity: The built-in cointegration workflow currently implements the Engle-Granger two-step procedure for bivariate systems. High-dimensional systems requiring Johansen rank tests are planned for future releases.

When to use this approach

  • Macroeconomic Forecasting: Projecting GDP growth, inflation, interest rates, or industrial production where autoregressive persistence and seasonality must be modeled.
  • Financial Planning & Analysis (FP&A): Creating confidence-bounded forecasts for sales or revenue that account for macroeconomic covariates (such as consumer sentiment or interest rates).
  • Unit Root & Stationarity Verification: Rigorously testing whether an economic series is difference-stationary (\(I(1)\)) or trend-stationary (\(I(0)\)) before using it in regression models.
  • Policy & Scenario Analysis: Evaluating cross-variable dynamics and Granger causality between monetary indicators (e.g., short-term policy rates vs. 10-year yields).

How this compares with MATLAB and Excel

Dimension Native Excel MATLAB Econometrics Toolbox Boardflare Econometrics Toolbox
ARIMA / SARIMAX Modeling Rudimentary exponential smoothing (FORECAST.ETS); no moving-average terms or exact MLE Command-line arima and estimate functions; requires license and desktop install State-space statsmodels.tsa.arima with exact Gaussian MLE running directly in Excel
Vector Autoregression (VAR) Not supported; requires complex manual OLS matrix inversion formulas Command-line varm with companion matrix analysis Full multi-equation VAR estimation with automated lag handling and Granger causality
Cointegration & ECM Not supported egcitest and vecm functions Automated Engle-Granger two-step testing and dynamic Error Correction Model estimation
Unit Root Tests None adftest, kpsstest, pptest Integrated ADF and KPSS dual-testing battery with automated interpretation
Residual Diagnostics None archtest, lbqtest, jbtest Automated 5-test battery checking autocorrelation, normality, and ARCH heteroskedasticity
Model Selection None aicbic calculation functions Automated AIC, BIC, HQIC, and Log-Likelihood computation across all models
Accessibility & Deployment Familiar spreadsheet grid, but lacks econometric algorithms Specialized script interface; high licensing cost; inaccessible to non-programmers Interactive Python execution embedded inside a standard Excel workbook

Changelog

  • v1.1.0 (Issue #97) — Comprehensive Econometrics Toolbox upgrade:
    • Added seasonal SARIMAX modeling with exogenous regressor support (retail_sales_sarimax).
    • Added two-variable Vector Autoregression (VAR) with Granger causality inference (treasury_rates_var).
    • Added Engle-Granger cointegration testing and dynamic Error Correction Model (consumption_income_coint).
    • Added dual ADF/KPSS stationarity workbench (macro_stationarity).
    • Expanded residual diagnostic battery to include Ljung-Box \(Q\), Jarque-Bera normality, and Engle’s ARCH LM test.
    • Implemented calendar period advancement for Quarterly, Monthly, and Annual frequencies.
    • Added interactive Matplotlib time series visualization in the notebook task pane.
    • Authored complete six-part technical documentation suite.
  • v1.0.0 — Initial release providing basic univariate ARIMA modeling on US GDP growth.