Turning Excel Workbooks into AI-Readable Data with an MCP Server

Python
LLM
AI
MCP
Tutorials
Teaching
Author

Rahul

Published

April 28, 2026

Modified

May 4, 2026

Excel is still where business logic lives. Pricing models, risk calculations, and financial projections accumulate over years in workbooks that no single person fully understands anymore. When the time comes to migrate them to Python or R, you face the same problem every time: the formulas are all there, but the structure, the dependencies, the named ranges, and the computation order remain invisible.

This post describes excel-formula-mcp, a read-only Model Context Protocol (MCP) server that solves this structural visibility problem by exposing Excel formula analysis as structured, AI-consumable tools.

The inspiration to build this came from my own experience working with firms trying to migrate their legacy Excel models to more systematic R and Python codebases for automation and efficiency gains. I built this opinionated MCP tool from my perspective as a quantitative analyst with thinking hat of forward-deployed engineer who frequently translates these legacy models. While enterprise AI copilots have been incredibly helpful for these tasks, manually copy-pasting formulas and context into a chat window is tedious. This tool automates that workflow via MCP, streamlining the translation process with human-in-the-loop editing. While it isn’t a silver bullet, it provides a massive head start for systematically understanding Excel models with minimal prior knowledge of the specific workbook.

The Problem with Excel Migration

A typical legacy workbook has:

  • Hundreds of formula cells spread across dozens of columns
  • Named ranges encoding business constants that are never documented
  • Cross-sheet references that create hidden dependencies
  • Formulas that vary row-to-row in subtle ways

You can open the file in Excel and read the formulas. But reading is not understanding. To migrate faithfully to Python, you need to know:

  1. Which columns are inputs (constants entered by a user) versus derived (computed from other columns)?
  2. In what order must derived columns be computed?
  3. What do the named ranges mean, and which formulas use them?

Answering those questions manually for a 50-column workbook takes hours. With excel-formula-mcp and GitHub Copilot in agent mode, it takes minutes.

What Is MCP?

The Model Context Protocol is an open standard that lets language models call external tools in a structured, discoverable way. An MCP server declares tool schemas (name, description, input parameters). An MCP client (GitHub Copilot, Claude Desktop, or any compatible agent) can discover and call those tools dynamically during a conversation.

The key insight is that MCP decouples capability from model. The server knows how to read Excel files; the model knows how to reason about the results. Neither needs to know the internals of the other.

Architecture

┌──────────────────────────────────────────────────────────┐
│                      MCP Client                          │
│           (GitHub Copilot / Claude Desktop)              │
└──────────────────────────┬───────────────────────────────┘
                           │ stdio (JSON-RPC)
┌──────────────────────────▼───────────────────────────────┐
│               excel-formula-mcp server                   │
│                   (FastMCP / Python)                     │
│                                                          │
│  ┌──────────────────┐  ┌────────────────────────────────┐ │
│  │   excel_loader   │  │       formula_parser           │ │
│  │  (openpyxl +     │  │  (regex tokeniser for          │ │
│  │   LRU cache)     │  │   same-sheet, cross-sheet,     │ │
│  └────────┬─────────┘  │   named range, external refs)  │ │
│           │            └──────────────┬─────────────────┘ │
│  ┌────────▼────────────────────────────▼────────────────┐ │
│  │              dependency_builder                      │ │
│  │  (column-level adjacency map + flat graph)           │ │
│  └──────────────────────────────────────────────────────┘ │
│                                                          │
│  ┌──────────────────────────────────────────────────────┐ │
│  │              vba_extractor  (.xlsm only)             │ │
│  │  (olefile + static source reader, no COM/execution)  │ │
│  └──────────────────────────────────────────────────────┘ │
│                                                          │
│  Formula tools:                  VBA tools:              │
│  · excel_analyze_range           · excel_get_vba_modules │
│  · excel_get_column_formulas     · excel_get_vba_udf_    │
│  · excel_get_dependencies            usage               │
│  · excel_get_sheet_dependencies  · excel_get_vba_context │
│  · excel_get_named_ranges                                │
└──────────────────────────────────────────────────────────┘

The server is intentionally layered. Four core modules each handle a single concern:

  • excel_loader: loads and caches workbooks with openpyxl using an LRU cache (size 5) so repeated tool calls within a session avoid redundant disk reads.
  • formula_parser: tokenizes raw formula strings using four compiled regular expressions to extract same-sheet references, cross-sheet references, named ranges, and external workbook references.
  • dependency_builder: aggregates parser output across formulas to produce typed dependency details per column, then flattens those into a simple adjacency map for client consumption.
  • vba_extractor: reads VBA project streams directly from .xlsm OLE containers using static extraction, without invoking Excel COM automation or executing any macro code.

The eight tools are thin wrappers over these modules. They accept Pydantic v2 models for input validation, call the appropriate core functions, and return either JSON or Markdown depending on response_format.

Formula Analysis Tools

excel_analyze_range

The workhorse. Given a file path, sheet name, and A1-notation range, it returns a complete column-by-column inventory.

{
  "columns": {
    "D": {
      "header": "Revenue",
      "formula_count": 10,
      "unique_templates": ["=B{r}*C{r}"],
      "constant_count": 0,
      "empty_count": 0,
      "is_fully_derived": true,
      "named_ranges_used": [],
      "cross_sheet_refs": []
    }
  },
  "named_ranges_in_use": ["UnitCost"],
  "summary": {}
}

This single call answers the question: what kind of column is this?

excel_get_column_formulas

Focused inspection of one column. Returns unique formula templates, formula count, constant count, and a sample of raw cell values.

excel_get_dependencies

Returns the column-level dependency graph. With include_details: true, each column entry lists four dependency categories:

Category Example
depends_on_columns [“B”, “C”]
depends_on_sheets [“Data!B”]
depends_on_named_ranges [“UnitCost”]
external_references [“[Rates.xlsx]”]

With include_details: false, it returns a flat adjacency map that is easy to feed into a topological sort.

excel_get_sheet_dependencies

Returns a workbook-level inter-sheet dependency graph. It scans formula cells across all sheets (or an optional whitelist) and reports direct sheet-to-sheet edges, plus named-range bridges that implicitly connect sheets.

excel_get_named_ranges

Lists every defined name in the workbook. For each name it reports scope, target sheet, target range, and whether it is formula-based rather than a direct range reference.

VBA Analysis Tools

Legacy .xlsm workbooks frequently embed Visual Basic for Applications (VBA) modules that act as custom worksheet functions — Public Function procedures callable directly from formula cells. These user-defined functions (UDFs) are entirely opaque to formula-only analysis: a formula cell may call =MyPricingUDF(A2,B2), but the dependency graph shows no inputs because the UDF’s internal range reads are invisible at parse time. The three VBA tools close this gap through static source extraction without executing any macro code.

excel_get_vba_modules

Discovers and optionally returns the source text of every embedded VBA module. For each module it reports the module name, type classification (standard, class, document, form), line count, and exported public procedure names.

{
  "modules": [
    {
      "name": "PricingFunctions",
      "type": "standard",
      "line_count": 142,
      "public_functions": ["CalcMargin", "AdjustedPrice"]
    }
  ]
}

Setting include_source: true appends the raw source text for each module, enabling subsequent static analysis by the LLM.

excel_get_vba_udf_usage

Maps each worksheet formula that calls an embedded VBA Public Function to the corresponding call site — sheet, cell address, full formula string, and raw argument expressions. An optional function_names whitelist narrows the scan; an optional sheets whitelist restricts which worksheets are scanned.

This tool answers the question: which cells depend on which UDFs, and with what arguments?

excel_get_vba_context

The deepest of the three tools. It accepts one of two selector modes:

  • function_name: locate all worksheet call sites for a named VBA function and return the source excerpt, heuristic token references to Excel object model identifiers (Range, Cells, Worksheets, Names, Application.Caller), and column-level workbook context for each call site.
  • sheet_name + cell_address: resolve the UDF referenced in a specific formula cell and return equivalent context.

Setting include_static_evidence: true extends the response with document-module classification, detected event procedures, and line-level source offsets — evidence relevant to assessing whether a UDF has side effects (writes, selections, event triggers) that would require special handling during migration.

The tool provides static evidence only. It does not execute VBA or trace runtime behaviour.

Design Decisions

Column-level granularity, not cell-level

Cell-by-cell dependency graphs for a 500-row workbook are rarely usable for an LLM because they explode in edge count. Column granularity mirrors how analysts reason about spreadsheet models.

Read-only by design

All eight tools carry read-only MCP hints. The server never writes to the workbook and never executes VBA, making it safe to call repeatedly in automated workflows.

LRU-cached workbook loading

Large workbook loads are expensive. The LRU cache (keyed by absolute path) avoids repeated parse overhead across tool calls.

Pydantic v2 input validation

Each tool uses strict Pydantic models with extra="forbid", and validates A1-range strings before any file I/O happens.

The Migration Workflow

The server includes two reusable VS Code prompt templates in .github/prompts.

Phase 1-4: Analysis (/excel-formula-analyze)

  1. excel_get_named_ranges: establish named constants vocabulary
  2. excel_analyze_range: classify columns as input, derived, or mixed
  3. excel_get_dependencies (include_details: true): build dependency graph and topological order
  4. excel_get_sheet_dependencies: map workbook-level cross-sheet structure

For .xlsm workbooks with embedded VBA, a complementary prompt (/excel-vba-analyze) appends three additional steps:

  1. excel_get_vba_modules (include_source: true): catalogue embedded modules and retrieve source
  2. excel_get_vba_udf_usage: identify which formula cells delegate computation to VBA UDFs
  3. excel_get_vba_context (per UDF): retrieve source evidence and Excel object model usage patterns

Optionally, save all outputs into:

reports/<sheet_name>-analysis.md

Phase 5: Migration (/excel-formula-migrate)

The second prompt reads the analysis report and generates:

  • Constants mapped from named ranges
  • Input schema as a typed dataclass
  • One computation stub per derived column in topological order
  • A run_model() orchestrator in dependency order
  • A context-sensitive migration checklist

This split makes analysis reusable and allows iterative migration passes.

Using the Server with VS Code

The repository includes .vscode/mcp.json with a stdio server entry:

{
  "servers": {
    "excel-formula-mcp": {
      "type": "stdio",
      "command": "${env:VIRTUAL_ENV}/bin/python",
      "args": ["-m", "excel_formula_mcp.server"],
      "cwd": "${workspaceFolder}",
      "env": {
        "PYTHONPATH": "${workspaceFolder}/src"
      }
    }
  }
}

With the environment activated and the server registered, Copilot can call the tools directly from chat.

Use excel_analyze_range to analyze sheet "Pricing" in data/stage/model.xlsx over range A1:H500, then generate a Python implementation plan.

Limitations

  • .xls is not supported (openpyxl supports .xlsx and .xlsm)
  • No formula evaluation engine is included; cells must be saved with cached values for non-formula content to be visible
  • VBA analysis is static only — no runtime execution, side-effect tracing, or dynamic dispatch resolution
  • Cross-workbook references (e.g., [OtherFile.xlsx]Sheet1!A1) are reported as-is but are not followed or resolved
  • Dependency graph is column-level, not cell-level

Stack

Component Library Version
MCP server framework mcp[cli] (FastMCP) >= 1.3.0
Excel parsing openpyxl >= 3.1.2
Input validation pydantic >= 2.7.0
Caching functools.lru_cache stdlib
Tests pytest + pytest-asyncio >= 8.0 / 0.23
Build hatchling -
Python Python >= 3.11

Installation

GitHub repository: r2rahul/excel-formula-mcp

git clone https://github.com/r2rahul/excel-formula-mcp.git
cd excel-formula-mcp
uv pip install -e ".[dev]"
pytest

What Is Next

  • Add optional formula evaluation support
  • Add optional .xls ingestion path
  • Add optional cell-level dependency mode
  • Add Mermaid or DOT graph export support for dependency graphs
  • Extend VBA static analysis with cross-procedure call graph construction

Source: excel-formula-mcp