AdvancedCalc Engine

Overview

Excel workbooks are the de-facto tool for ad-hoc analysis — and the hardest to automate safely. AdvancedCalc Engine is an agentic workflow system built on secure MCP tooling: it inspects a workbook, plans a calculation, executes it through whitelisted MCP tools, writes results back safely, and validates the outcome — with a full audit trail and a backup before every modification.

The workflow is deterministic: a task is decomposed into an Architect → Engineer → Validator pipeline by rule-based code in app/workflow/, with no LLM in the loop. A hardened MCP server exposes fifteen whitelisted tools that the workflow executes. Safety is the product: path confinement, formula sanitization, mandatory backups, and audit logging are enforced in code, not by convention.

This is an end-to-end example of a deterministic agentic engineering pipeline: structured plan/execute/verify orchestration, typed tool contracts, defense-in-depth security, and automated verification. The current implementation requires no LLM; a future phase could add LLM-driven planning without changing the tool surface or the validation gates.

Related MCP server: Excel MCP Server

Key Features

• Fifteen whitelisted MCP tools over stdio — read-only metadata, calculation and analytics tools plus two guarded mutation tools and the agentic workflow runner.

• Multi-agent architecture — four OpenCode subagents with strict separation of concerns (plan / execute / verify / audit), formalized as a deterministic in-process workflow with per-run correlation ids.

• Safe Excel mutation — a timestamped backup is created before every write; the workbook is reopened and validated afterwards.

• Security-first design — paths are confined to data/input, formulas and cell references are sanitized, arbitrary code execution is impossible.

• Audit trail — every operation, success or failure, is recorded as a JSONL event with no secrets or workbook contents; workflow runs share a single workflow_id across all their events.

• Typed contracts — Pydantic request/response models for every tool.

• Agentic workflow runner — a natural-language task is parsed into an execution plan, executed, and independently validated without an LLM; the same pipeline is exercised end to end by advanced-calc demo and by the agent evaluation harness.

• 754-test suite with 98% coverage, plus a live MCP smoke test.

• CLI (advanced-calc) for serving, smoke-testing, auditing, validating, running the agentic demo, and evaluating the ML layer (ml-eval) — CI runs on Linux and Windows with a 90% coverage gate.

Architecture

┌────────────────────────────────────────────────────────────────┐
│                     OpenCode agent orchestration                │
│                                                                 │
│  Architect ──plan──▶ Engineer ──modify──▶ Validator             │
│      │                  │                     │                 │
│      └──────────────────┴─── audit events ────┴──▶ Audit log    │
│                                                                 │
│              Security Auditor (reviews, never modifies)         │
└──────────────────────────────┬─────────────────────────────────┘
                               │ MCP (stdio)
┌──────────────────────────────▼─────────────────────────────────┐
│                    MCP server (app/mcp_server.py)               │
│  read_excel_metadata   rolling_average    percentage_change     │
│  rolling_volatility    write_formulas     validate_workbook     │
│  summary_statistics    correlation_matrix anomaly_detection     │
│  linear_forecast       ml_forecast        ml_anomaly_detection  │
│  data_quality_report   write_output                             │
│  run_analysis_workflow (Architect → Engineer → Validator)       │
└──────────────────────────────┬─────────────────────────────────┘
                               │
┌──────────────────────────────▼─────────────────────────────────┐
│                     Application layer (app/tools)               │
│  Calculation engine   Excel metadata   Formula writer           │
│  Workbook validator                                            │
└──────────────────────────────┬─────────────────────────────────┘
                               │
┌──────────────────────────────▼─────────────────────────────────┐
│                      Core layer (app/core)                      │
│  security.py   backup.py   audit.py   config.py                 │
└─────────────────────────────────────────────────────────────────┘

The layering is strict: mcp_server.py contains transport logic only. Excel business logic lives in app/tools, and every cross-cutting concern (security, backups, audit, configuration) lives in app/core. Agents never touch the filesystem directly — they only call MCP tools.

Agentic Workflow

The workflow is enforced by agent definitions in .opencode/agents/ and formalized as deterministic code in app/workflow/ (no LLM required):

1. Architect (app/workflow/architect.py) analyzes the workbook via read_excel_metadata, parses the task with task_parser.py, and produces a numbered execution plan plus a validation spec. It never modifies anything.

2. Engineer (app/workflow/engineer.py) executes only whitelisted plan steps through the MCP tool handlers (write steps use write_output with automatic backup), and records every outcome.

3. Validator (app/workflow/validator.py) independently recomputes the result — raw openpyxl cell reads and pure-Python math, sharing no code path with the calculation engine — and reports PASS or FAIL without ever modifying the workbook.

4. The orchestrator (app/workflow/orchestrator.py) runs the whole pipeline, generates a per-run UUID workflow_id, and threads it through every audit event of the run.

5. Every step is recorded in the audit log; the Security Auditor reviews the tool surface and configuration for risk.

Planning failures (unsupported or ambiguous tasks) raise structured ACE_* errors; execution or validation failures are returned as a FAIL response with the validator's reason.

The pipeline is fully deterministic today — planning, execution, and validation are plain Python with no LLM in the loop. A future phase could replace the rule-based planner with LLM-driven reasoning while keeping the same whitelisted tool surface, structured ACE_* errors, and independent validation gates.

The repository's AGENTS.md codifies the core rules: analyze before modifying, never blindly modify a workbook, always back up before destructive operations, validate after modification, never ignore errors, and never execute arbitrary user code.

MCP Tools

The MCP server (python -m app.mcp_server) exposes fifteen tools:

* The workflow itself never mutates the source workbook; a task that asks to write results produces a new file under data/output.

ml_forecast fits a seeded bagged-ridge forecaster on causal lag and rolling features, splits the series temporally (train prefix, holdout suffix), reports RMSE / MAE / MAPE / R² for the model and the persistence, mean and linear baselines on the same holdout, and recursively forecasts future periods. The seed, test fraction, minimum points and row cap come from the ACE_ML_* settings; when fewer than two supervised training rows survive feature warm-up it falls back deterministically to the same linear trend fit used by linear_forecast.

ml_anomaly_detection grows a seeded isolation forest (subsampled, random-threshold isolation trees driven entirely by numpy.random.default_rng(seed)) and flags the most isolated contamination share of a column's values, ties broken by position so the result is fully reproducible. With evaluate=true it injects known far-outlier anomalies on an in-memory copy of the series, reruns the detection, and reports precision, recall and F1 against the injected labels — the workbook is never touched. The seed, minimum points and row cap come from the ACE_ML_* settings; insufficient data raises a structured ACE_CALCULATION error.

All inputs are validated through Pydantic request models; all outputs are serialized through typed response models with explicit JSON-safe conversion (NumPy scalars, timestamps, and non-finite floats). Failures are returned as structured MCP errors carrying an ACE_* taxonomy code (ACE_VALIDATION, ACE_SECURITY, ACE_NOT_FOUND, ACE_CALCULATION) instead of raw exception strings.

ML Evaluation Harness (ml-eval)

advanced-calc ml-eval runs the complete read-only ML evaluation of a workbook column in one command and prints the metrics tables:

advanced-calc ml-eval
# options: --file, --sheet, --column, --periods, --window, --seed,
#          --output, --no-artifact

It reuses the exact same deterministic pipelines as the ml_forecast and ml_anomaly_detection MCP tools (app/ml/harness.py): the seeded bagged-ridge forecaster is evaluated on a temporal holdout (train prefix / test suffix, never shuffled) and compared against the persistence, mean and linear baselines evaluated on the same holdout targets; the seeded isolation forest injects known far outliers on an in-memory copy of the series and reports precision, recall and F1 against them. A reproducible JSON artifact (no timestamps, everything derived from the seed) is written to data/output/ml_evaluation.json unless --no-artifact is given, and the run is recorded in the audit log as an ml_eval event.

Measured results — data/input/sales_data.xlsx (seed 42)

The demo workbook holds 60 daily sales rows (2026-01-01 → 2026-03-01, first 12 rows preserved from the original sample, rows 13–60 a rising sales trend). With the default configuration (ACE_ML_RANDOM_SEED=42, ACE_ML_TEST_FRACTION=0.3, lags 1–3, rolling window 7, 25 bagged ridge estimators) the evaluation splits into 35 training / 17 holdout rows:

Anomaly evaluation (isolation forest, n_estimators=100, max_samples=256, contamination=0.1, 5 injected outliers): precision 0.833333, recall 1.000000, F1 0.909091 (6 points flagged, 5 of which are the injected outliers).

The first 12 rows repeat a flat pattern, so the mean and linear baselines are badly wrong on the trend — while the ML model's causal lag and rolling features track it with a 1.1% average error, roughly half the persistence baseline's error. Every number above is fully reproducible: the harness is deterministic for a fixed seed, and the artifact contains the complete report (data/output/ml_evaluation.json).

Model persistence decision

Phase 10.5 evaluated whether fitted estimators should be persisted to data/models/. It is not implemented: both estimators are fully deterministic for a given seed and are re-fit on demand (a stored model could never differ from a fresh fit), the system is stateless with no cross-request model reuse or serving path, and reproducibility is already guaranteed by the seed plus the audit trail. Skipping serialization removes the entire unsafe-deserialization surface (pickle/joblib object graphs, user-supplied model artifacts) without losing anything — see SECURITY.md for the full rationale.

Security Model

Security is enforced in app/core/security.py and applies to every tool:

• Path confinement — every workbook path is resolved and must stay inside the configured input_dir. .. traversal, drive-relative paths, cross-drive paths, and UNC paths outside the root are rejected (Windows-specific cases handled explicitly).

• Formula sanitization — formulas must start with =, are length- and control-character-checked, and may not contain DDE markers (|), external workbook references ([), or functions that exfiltrate data or execute commands (HYPERLINK, WEBSERVICE, IMPORT*, EXEC, POWERSHELL, CALL, REGISTER, …).

• Cell reference validation — references must match Excel bounds (columns up to XFD, rows up to 1048576).

• Whitelisted surface — only the fifteen tools above are registered; arbitrary Python execution is never exposed to agents or users.

• Limits — formula length (512 chars) and cells per write (500) are configurable caps.

The repository's Security Auditor agent and SECURITY.md document the threat model and reporting process.

Backup & Audit Behavior

Backups (app/core/backup.py)

• write_formulas never touches a workbook without first creating a timestamped copy in data/backups.

• If any validation step fails before the backup, the workbook is never modified; a rejected write leaves no trace.

Audit log (app/core/audit.py)

• Every operation — reads, calculations, writes, backups, validations — is written to logs/audit.jsonl as a JSON line with timestamp, agent, operation, status, file, sheet, and details.

• Workflow runs carry a workflow_id on every event they produce, so a single analysis can be traced end to end.

• Failures are recorded together with their error message, then re-raised.

• The log never contains secrets, environment variables, workbook contents, or cell values.

Inspect the trail with the CLI:

advanced-calc audit
advanced-calc audit --workflow <workflow_id>
advanced-calc stats

Installation

Requirements: Python 3.11+ and uv.

git clone https://github.com/surajpanwar/advanced-calc-engine.git
cd advanced-calc-engine
uv sync --dev

This creates the virtual environment, installs the package in editable mode, and exposes the advanced-calc CLI.

Optional: copy .env.example to .env and adjust paths if you do not want the defaults (directories inside the project root).

Quickstart

Place a workbook in data/input/ (a sample sales_data.xlsx with 60 daily rows is included).

Run the live MCP smoke test — it starts the server exactly as OpenCode does, lists the fifteen tools, and reads the sample workbook:

advanced-calc smoke

Start the MCP server over stdio (used by any MCP client):

advanced-calc serve
# or
python -m app.mcp_server

Run the agentic workflow demo — the Architect parses the task, the Engineer computes and writes the 5-day rolling volatility, and the Validator independently checks the result:

advanced-calc demo

The demo prints the workflow trace, the generated workflow_id, and the path of the output workbook in data/output/. Follow the trail with:

advanced-calc audit --workflow <workflow_id>

Validate a workbook from the CLI (relative paths resolve against data/input/):

advanced-calc validate sales_data.xlsx Sheet1 A1 B1

Inspect the audit trail:

advanced-calc audit --tail 10
advanced-calc stats

Run the ML evaluation harness against the demo workbook and regenerate the reproducible metrics artifact:

advanced-calc ml-eval

The command prints the forecast metrics table (RMSE / MAE / MAPE / R² for the ML model and the persistence, mean and linear baselines), the anomaly detection metrics (precision / recall / F1 against injected outliers), and writes data/output/ml_evaluation.json — see ML Evaluation Harness for the measured results and methodology.

Live Demo / Workflow Trace

Watch the full pipeline run end to end, then replay the audit trail for that exact run:

advanced-calc demo
advanced-calc audit --workflow <WORKFLOW_ID>

advanced-calc demo runs a live task through the Architect → Engineer → Validator pipeline and prints the trace — the plan, each executed step, and an independent PASS result with the generated workflow_id and output workbook:

advanced-calc audit --workflow <WORKFLOW_ID> replays every event of that single run — planning, calculations, writes, backups, validation — linked by one shared workflow_id:

What the demo proves:

• Deterministic agent orchestration — the same task always produces the same Architect → Engineer → Validator sequence, executed in code with no LLM in the loop.

• Workflow_id correlation — one id threads through every audit event, so an entire run is traceable end to end.

• Validation before completion — the run reports success only after the Validator independently recomputes and confirms the result.

• Output workbook generation — the task produces a real workbook in data/output/, written through the guarded, backup-first tools.

• Auditable execution — every step, success or failure, is recorded in logs/audit.jsonl for replay and inspection.

The workflow is fully deterministic today; optional LLM-driven planning on top of the same whitelisted tool surface is future work.

OpenCode MCP Setup

opencode.json registers the local MCP server with OpenCode:

{
  "$schema": "https://opencode.ai/config.json",
  "default_agent": "plan",
  "mcp": {
    "advanced-calc-engine": {
      "type": "local",
      "command": [".venv/Scripts/python.exe", "-m", "app.mcp_server"],
      "cwd": ".",
      "enabled": true
    }
  }
}

The configuration is portable — it contains no machine-specific paths. The command and cwd are resolved relative to the directory where OpenCode is launched (the project root), so the only prerequisite is the project virtual environment:

1. uv sync --dev must have been run (creates .venv/).

2. The bootstrap check is simply:

   .venv/Scripts/python.exe -m app.mcp_server   # Windows
   .venv/bin/python -m app.mcp_server           # macOS / Linux

If your environment differs (different venv location, no venv, or macOS/Linux), update the command array accordingly — e.g. [".venv/bin/python", "-m", "app.mcp_server"] on Unix. The fifteen MCP tools are then available to OpenCode agents exactly as before.

Note: advanced-calc serve is equivalent to launching python -m app.mcp_server; use whichever you prefer.

Testing

The suite exercises every layer, from pure functions to a live MCP handshake:

uv run pytest --cov=app --cov-report=term-missing --cov-fail-under=90

Coverage areas:

• Security: path traversal, drive/UNC edge cases, formula and cell reference sanitization, rejected writes leave no trace.

• Calculations: window validation, missing columns, numeric accuracy.

• Backups: collision handling, restore fidelity, no backup on failure.

• Audit: SUCCESS/FAIL events, error messages, no cell values in logs, workflow_id propagation.

• MCP handlers: request validation, JSON-safe serialization, audited failures.

• End-to-end: the full agent workflow including audit-trail assertions.

• Agent evaluation: ten deterministic golden cases (tests/agent_eval/) with known expected values, plus validator-independence tests that sabotage plans and workbooks to prove the Validator catches mistakes.

• ML evaluation: harness shape, determinism, guard propagation and fallback behaviour (tests/test_ml_harness.py), and CLI wiring, artifact writing, error handling and audit propagation for ml-eval (tests/test_cli.py).

CI/CD

GitHub Actions (.github/workflows/ci.yml) runs on every push to main and every pull request on both ubuntu-latest and windows-latest:

1. uv sync --dev — installs the project and dev toolchain.

2. pytest with coverage — 90% coverage gate (--cov-fail-under=90).

3. ruff check . — lint (app code; tests are excluded by config).

4. mypy app — static type checking.

The Linux job additionally exercises the symlink-related path security tests that skip on Windows.

Project Structure

advanced-calc-engine/
├── app/
│   ├── cli.py                    # Typer CLI: serve, smoke, audit, stats, validate, demo, ml-eval
│   ├── mcp_server.py             # MCP transport; registers the 15 tools
│   ├── core/
│   │   ├── security.py           # Path/formula/cell-reference validation
│   │   ├── backup.py             # Mandatory pre-mutation backups
│   │   ├── audit.py              # JSONL audit trail (+ workflow_id context)
│   │   ├── errors.py             # ACE_* structured MCP error taxonomy
│   │   └── config.py             # ACE_-prefixed settings (pydantic-settings)
│   ├── models/                   # Pydantic request/response contracts
│   ├── ml/                       # Deterministic ML layer (evaluation,
│   │   │                         # features, forecast, anomaly detection,
│   │   │                         # evaluation harness)
│   ├── workflow/                 # Agentic orchestration (Phase 9.3)
│   │   ├── orchestrator.py       # run_analysis_workflow runner
│   │   ├── architect.py          # Plan builder (read-only)
│   │   ├── engineer.py           # Whitelisted executor
│   │   ├── validator.py          # Independent recomputation (PASS/FAIL)
│   │   ├── task_parser.py        # Deterministic natural-language parsing
│   │   └── models.py             # Plan/validation-spec models
│   └── tools/
│       ├── calculation_engine.py # Rolling average, % change, volatility
│       ├── analytics.py          # Summary stats, correlation, anomalies, forecast, quality
│       ├── excel_metadata.py     # Workbook structure reader
│       ├── formula_writer.py     # Safe formula writes (backup first)
│       ├── output_writer.py      # Safe output writes into data/output
│       └── workbook_validator.py # Sheet/cell validation
├── data/
│   ├── input/                    # Workbooks the tools may access
│   ├── output/                   # Computed outputs + ml_evaluation.json (gitignored)
│   └── backups/                  # Pre-mutation backups (gitignored)
├── logs/                         # audit.jsonl (gitignored)
├── tests/                        # 754 tests + MCP smoke test
│   └── agent_eval/               # Golden cases + validator independence
├── .opencode/agents/             # Architect, Engineer, Validator, Security Auditor
├── .github/workflows/ci.yml      # Linux + Windows CI
├── AGENTS.md                     # Agent rules and project conventions
├── pyproject.toml                # Metadata, pinned deps, entry point, tool config
└── main.py                       # Thin entry point delegating to the CLI

Roadmap

• Phase 9.2 — Product / ML surface: summary statistics, correlation, anomaly detection (z-score / IQR), linear forecasting, data quality reports, output writing, structured ACE_* errors, overwrite guard and post-write verification. (Implemented)

• Phase 9.3 — Agentic orchestration: deterministic Architect → Engineer → Validator workflow, per-run workflow_id correlation across audit events, golden-case agent evaluation harness, and the demo CLI. (Implemented)

• Phase 9.4 — MCP extensions: MCP resources and prompts; optionally an LLM-driven planner on top of the deterministic workflow. (Planned)

• Phase 9.5–9.7 — Hardening & delivery: property-based and fuzz testing, structured logging and metrics, Docker packaging and release automation. (Planned)

• Phase 10.1–10.2 — ML evaluation layer: pure metrics, baselines, temporal splits, leak-free features, and the seeded ml_forecast pipeline with holdout evaluation. (Implemented)

• Phase 10.3 — ML anomaly detection: seeded isolation-forest ml_anomaly_detection with injected-anomaly precision/recall/F1 evaluation. (Implemented)

• Phase 10.5 — ML evaluation harness: the advanced-calc ml-eval CLI running the combined forecast + anomaly evaluation with a reproducible metrics artifact; the model-persistence decision (not implemented — see ML Evaluation Harness). (Implemented)

• Phase 10.4 / 10.6 / Phase 11 — ML extensions: clustering, classification and further validator/workflow integration. (Planned)

License

MIT