Repository Public Research preview
1#!/usr/bin/env node2/**3 * =============================================================================4 * SP BOUCHER | SOFTWARE & RESEARCH5 * ARTIFICIAL INTELLIGENCE • FINANCE • ENGINEERING6 * =============================================================================7 *8 * Project : LLM API toolkits9 * File : xlsx/mcp/server.mjs10 * Module : xlsx11 * Repository : https://git.spboucher.ai/llm-api-toolkits12 * Created : 2026-11-1013 * Last Modified : 2026-11-1014 *15 * Author : Simon-Pierre Boucher16 * Position : Professor of Finance17 * Institution : Université du Québec en Outaouais (UQO)18 * Organization : SP Boucher19 * Website : https://spboucher.ai20 * Git : https://git.spboucher.ai21 *22 * Preferred Contact: dev@spboucher.ai23 * Development : dev@spboucher.ai24 * Support : support@spboucher.ai25 * Security/Admin : admin@spboucher.ai26 *27 * Copyright (c) 202628 * Simon-Pierre Boucher29 * All rights reserved where applicable. This file is governed by the30 * applicable project license, copyright notices and relevant law.31 * Existing third-party rights and notices remain unaffected.32 *33 * License : None specified (no license file in this repository)34 *35 * Do not embed credentials, private keys, access tokens,36 * certificates or confidential personal information.37 *38 * =============================================================================39 * END OF SP BOUCHER SOURCE HEADER40 * =============================================================================41 */4243/**44 * xlsx-audit-mcp — audit Excel workbooks without Excel.45 *46 * Reads .xlsx / .xlsm packages directly (ZIP + XML, no dependency, nothing executed) and reports47 * what a careful reviewer looks for: errors, constants buried in formulas, broken patterns,48 * circular references, failing controls, stale values; compares a submission with a reference49 * and prepares rubric-based grading evidence. Files are confined to XLSX_AUDIT_ROOT.50 * The only writes are reports, to new files that never overwrite.51 */52import { McpServer } from "@modelcontextprotocol/sdk/server/mcp.js";53import { StdioServerTransport } from "@modelcontextprotocol/sdk/server/stdio.js";54import { realpathSync } from "node:fs";55import { fileURLToPath } from "node:url";56import { z } from "zod";57import { NOTICE, createApi } from "./lib/api.mjs";58import { DISCLAIMER, SUBSET } from "./lib/audit.mjs";59import { XlsxError } from "./lib/zip.mjs";6061export const VERSION = "1.0.1";6263const out = z.looseObject({});64const READ = { readOnlyHint: true, destructiveHint: false, idempotentHint: true, openWorldHint: false };65const WRITE = { readOnlyHint: false, destructiveHint: false, idempotentHint: false, openWorldHint: false };66const path = z.string().min(1).describe("Workbook path (.xlsx or .xlsm), absolute or relative to the allowed root directory");67const sheet = z.string().optional().describe("Sheet name (case-insensitive). Default: every sheet");68const limit = z.number().int().min(1).max(2000).optional().describe("Maximum number of items returned (default 200)");69const tolerance = z.number().min(0).optional().describe("Absolute tolerance on numbers (default 0.005, half a cent)");7071export function createServer(env = process.env) {72 const root = env.XLSX_AUDIT_ROOT || process.cwd();73 const api = createApi({ root, env });74 const server = new McpServer({ name: "xlsx-audit", version: VERSION, title: "Excel workbook audit" }, {75 capabilities: { tools: {} },76 instructions: `Audits Excel workbooks (.xlsx, .xlsm) by reading the file itself: Excel is never started and nothing in the workbook is executed. Start with workbook_summary, then find_errors, find_circular, find_hardcoded, formula_consistency, check_controls and recalc_check, or export_report for all of them at once. Use trace_precedents / trace_dependents to explain a number. compare_workbooks and grade_submission compare a submission with a reference. ${SUBSET} Values come from the file's stored results when present. Files must be inside ${root}. ${NOTICE}`,77 });78 const ok = (data) => ({ content: [{ type: "text", text: JSON.stringify(data, null, 2) }], structuredContent: data });79 const fail = (e) => {80 const payload = { error: { code: e instanceof XlsxError ? e.code : "tool_error", message: String(e?.message ?? e).slice(0, 600) } };81 return { isError: true, content: [{ type: "text", text: JSON.stringify(payload, null, 2) }], structuredContent: payload };82 };83 const tool = (name, cfg, fn = api[name]) => server.registerTool(name, { outputSchema: out, ...cfg }, async (args) => { try { return ok({ ...(await fn(args ?? {})), _notice: NOTICE }); } catch (e) { return fail(e); } });8485 tool("workbook_summary", {86 title: "Workbook summary",87 description: "Overview of a workbook: sheets (hidden ones included) with used range and counts of formulas, numbers and errors, defined names, functions used, share of formulas outside the evaluated subset, macros and external links. Call it first.",88 inputSchema: { path }, annotations: READ,89 });90 tool("read_range", {91 title: "Read a range",92 description: "Read the non-empty cells of a range or of a whole sheet, page by page: value, type, formula, number format, and where the value comes from (constant, cached, evaluated, unknown).",93 inputSchema: { path, range: z.string().optional().describe("Range with its sheet, e.g. Calc!A1:G20 or 'My sheet'!B2"), sheet: z.string().optional().describe("Sheet to read entirely when no range is given"), offset: z.number().int().min(0).optional(), limit: z.number().int().min(1).max(2000).optional().describe("Cells per page (default 200)") },94 annotations: READ,95 });96 tool("list_formulas", {97 title: "List formulas",98 description: "List formulas with their R1C1 pattern, value, number of precedents and dependents, and whether the built-in evaluator covers them. With group=true, returns one line per distinct pattern and sheet: the fastest way to see how a model is built.",99 inputSchema: { path, sheet, range: z.string().optional().describe("Restrict to a range, e.g. Calc!B2:G40"), group: z.boolean().optional().describe("Group identical patterns (default false)"), offset: z.number().int().min(0).optional(), limit },100 annotations: READ,101 });102 tool("find_errors", {103 title: "Find error cells",104 description: "Find cells in error (#REF!, #DIV/0!, #N/A, #VALUE!, #NAME?, #NUM!, #NULL!) and formulas holding a broken #REF! reference. Separates root causes from errors that merely propagate, and names the cell each one comes from.",105 inputSchema: { path, sheet, limit }, annotations: READ,106 });107 tool("find_hardcoded", {108 title: "Find hard-coded numbers",109 description: "Find numeric constants written inside formulas (an assumption buried in a calculation) and typed numbers sitting between two cells that share the same formula pattern (a formula that was overwritten). Suggests the expected formula for the latter.",110 inputSchema: { path, sheet, ignore: z.array(z.number()).max(50).optional().describe("Constants not worth reporting (default [0, 1])"), limit },111 annotations: READ,112 });113 tool("formula_consistency", {114 title: "Check formula patterns",115 description: "Compare formulas along rows and columns in R1C1 form and report the cells that break the pattern of their run, with the formula they were expected to hold. Totals closing a run and the opening cell of a recurrence are not reported.",116 inputSchema: { path, sheet, min_run: z.number().int().min(3).max(100).optional().describe("Minimum number of adjacent formula cells forming a run (default 3)"), limit },117 annotations: READ,118 });119 tool("find_circular", {120 title: "Find circular references",121 description: "Detect circular references across the whole workbook, sheets included, and print each loop cell by cell. Cells inside a loop have unreliable stored values.",122 inputSchema: { path, limit }, annotations: READ,123 });124 const traceArgs = { path, cell: z.string().min(1).describe("One cell with its sheet, e.g. Outputs!B5"), depth: z.number().int().min(1).max(25).optional().describe("Levels to follow (default 2)"), limit };125 tool("trace_precedents", {126 title: "Trace precedents",127 description: "Explain where a number comes from: the cells a formula reads, level by level and across sheets, with their formulas and values. Constants reached are marked as inputs. Says whether the cell belongs to a circular reference.",128 inputSchema: traceArgs, annotations: READ,129 });130 tool("trace_dependents", {131 title: "Trace dependents",132 description: "Show what a cell feeds: the formulas that read it, level by level and across sheets. Use it before changing an input or deleting a row, and to see how far an error spreads.",133 inputSchema: traceArgs, annotations: READ,134 });135 tool("check_controls", {136 title: "Check controls",137 description: "Read the model's own controls: a sheet named like Controls / Checks / Reconciliation, defined names starting with chk or check, or the ranges you give. A control passes when it is TRUE, OK, or a difference within tolerance. Reports failing controls, typed (fake) controls, and whether the overall 'all checks pass' cell agrees.",138 inputSchema: { path, sheet: z.string().optional().describe("Controls sheet, when its name is not recognised automatically"), ranges: z.array(z.string()).max(50).optional().describe("Extra control cells, e.g. [\"Summary!H2:H9\"]"), tolerance, limit },139 annotations: READ,140 });141 tool("recalc_check", {142 title: "Recalculation check",143 description: "Recompute every formula of the supported subset from the stored values of the cells it reads and compare with its own stored value. Reveals stale values (file saved without recalculation or written by a library) and altered results. States the share of formulas it could not recompute, and why.",144 inputSchema: { path, sheet, abs_tolerance: z.number().min(0).optional(), rel_tolerance: z.number().min(0).optional(), limit },145 annotations: READ,146 });147 tool("compare_workbooks", {148 title: "Compare with a reference",149 description: "Compare a submission with a reference workbook, cell by cell: right by formula, right but typed as a constant, wrong, missing. Also reports structural differences (sheets, names, used ranges), changed inputs and extra formulas. Default scope: every formula cell of the reference.",150 inputSchema: { reference: path.describe("Reference workbook (the solution)"), submission: path.describe("Workbook to compare"), ranges: z.array(z.string()).max(200).optional().describe("Compare only these ranges of the reference, e.g. [\"Calc!B3:F7\"]"), tolerance, rel_tolerance: z.number().min(0).optional(), limit },151 annotations: READ,152 });153 tool("grade_submission", {154 title: "Grading aid",155 description: `Apply a rubric to a submission against a reference and return per-item evidence: correct, right value typed as a constant, carried-forward error, wrong, missing. Suggests points; never a final grade. ${DISCLAIMER} With output, also writes the evidence to a new .md or .json file.`,156 inputSchema: {157 reference: path.describe("Reference workbook (the solution)"), submission: path.describe("Workbook to grade"),158 rubric: z.object({159 items: z.array(z.object({160 id: z.string().optional(), label: z.string().optional(), range: z.string().optional().describe("Graded cells, with the sheet: Calc!B3:F3"), ranges: z.array(z.string()).optional(),161 points: z.number().min(0), tolerance: z.number().min(0).optional(), rel_tolerance: z.number().min(0).optional(), formula_required: z.boolean().optional().describe("A typed number earns only constant_credit"),162 constant_credit: z.number().min(0).max(1).optional(), carried_forward_credit: z.number().min(0).max(1).optional(), partial: z.enum(["proportional", "all_or_nothing"]).optional(),163 })).min(1).max(500),164 tolerance: z.number().min(0).optional(), rel_tolerance: z.number().min(0).optional(), formula_required: z.boolean().optional(), constant_credit: z.number().min(0).max(1).optional(), carried_forward_credit: z.number().min(0).max(1).optional(), partial: z.enum(["proportional", "all_or_nothing"]).optional(),165 }).optional().describe("Rubric given inline. Top-level fields are defaults for every item"),166 rubric_path: z.string().optional().describe("Path of a rubric JSON file, instead of rubric"),167 output: z.string().optional().describe("Write the evidence to this new file (.md or .json). Never overwrites"),168 },169 annotations: WRITE,170 });171 tool("export_report", {172 title: "Export an audit report",173 description: "Run the whole audit (summary, errors, circular references, hard-coded numbers, patterns, controls, recalculation) and return it as Markdown or JSON, or write it to a new file inside the allowed directory. An existing file is never overwritten.",174 inputSchema: { path, format: z.enum(["markdown", "json"]).optional().describe("Default: markdown, or json when output ends with .json"), output: z.string().optional().describe("Destination file (.md or .json). Omit to get the report in the reply"), tolerance, limit: z.number().int().min(1).max(1000).optional().describe("Items per section (default 100)") },175 annotations: WRITE,176 });177 return server;178}179180const isMain = (() => { try { return !!process.argv[1] && realpathSync(process.argv[1]) === realpathSync(fileURLToPath(import.meta.url)); } catch { return false; } })();181if (isMain) await createServer().connect(new StdioServerTransport());182