MCP 服务器

AdvancedCalc Engine

Provides secure, agentic Excel analysis and calculation capabilities, enabling users to inspect workbook structure, perform statistical computations, write formulas, and generate outputs through whitelisted MCP tools with audit trails and backups.

README

<p align="center"> <h1 align="center">AdvancedCalc Engine</h1> <p align="center">Agentic Excel analysis and calculation platform</p> <p align="center"> <a href="https://github.com/surajpanwar/advanced-calc-engine/actions/workflows/ci.yml"> <img src="https://github.com/surajpanwar/advanced-calc-engine/actions/workflows/ci.yml/badge.svg" alt="CI"> </a> <img src="https://img.shields.io/badge/python-3.11-blue" alt="Python 3.11"> <img src="https://img.shields.io/badge/tests-432%20passing-green" alt="Tests"> <img src="https://img.shields.io/badge/license-MIT-blue" alt="License: MIT"> </p> </p>

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

Key Features

Fourteen 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

Agent	Role	Can modify?
Architect	Understands the request, inspects workbook structure, produces an execution plan	No
Engineer	Executes the approved plan through the MCP tools, verifies modifications	Approved cells only
Validator	Independently verifies results and reports PASS/FAIL	No
Security Auditor	Reviews the application for vulnerabilities and unsafe tool usage	No

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

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.
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.
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.
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.
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 fourteen tools:

Tool	Description	Mutates?
`read_excel_metadata`	Read workbook structure: sheets, columns, sample rows	No
`rolling_average`	Rolling mean of a column (configurable window, default 14)	No
`percentage_change`	Period-over-period percentage change of a column	No
`rolling_volatility`	Rolling standard deviation of percentage change	No
`write_formulas`	Write Excel formulas into a sheet; backup is created first	Yes
`validate_workbook`	Validate sheet existence and expected cells	No
`summary_statistics`	Count, missingness, mean, median, std, min/max, quartiles	No
`correlation_matrix`	Pearson correlation matrix of numeric columns	No
`anomaly_detection`	z-score or IQR anomaly detection on a column	No
`linear_forecast`	Linear trend fit (numpy) plus future period forecasts	No
`ml_forecast`	Seeded ML forecast with temporal holdout evaluation against persistence/mean/linear baselines	No
`data_quality_report`	Per-column type, completeness, uniqueness, numeric stats	No
`write_output`	Write computed rows into `data/output`; backup before overwrite	Yes
`run_analysis_workflow`	Run a task through the Architect → Engineer → Validator pipeline	No*

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

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 fourteen 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 fourteen 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:

Workflow trace

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

Audit trail

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:

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

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 fourteen 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:

uv sync --dev — installs the project and dev toolchain.
pytest with coverage — 90% coverage gate (--cov-fail-under=90).
ruff check . — lint (app code; tests are excluded by config).
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 14 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