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

• Thirteen 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.

• 432-test suite with 99% coverage, plus a live MCP smoke test.

• CLI (advanced-calc) for serving, smoke-testing, auditing, validating, and running the agentic demo — 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       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 thirteen tools:

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

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.

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 thirteen 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 is included).

Run the live MCP smoke test — it starts the server exactly as OpenCode does, lists the thirteen 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

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 thirteen 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.

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
│   ├── mcp_server.py             # MCP transport; registers the 13 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
│   ├── 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 (gitignored)
│   └── backups/                  # Pre-mutation backups (gitignored)
├── logs/                         # audit.jsonl (gitignored)
├── tests/                        # 432 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)

License

MIT