Claude in Excel + MCP + xlwings Lite + Claude Code: Combining the 4 for power impact.

Published: March 4, 2026

This is a live example of a multi step scenario analysis model using Claude in Excel to pull data with an MCP & setup a formula based scenario analysis model (no python) .. Claude Code to validate it offline with python script and xlwings Lite to create python charts in Excel. MCP, Formulas and xlwings Lite make it repeatable. Offline python hardens validations.

This is S&P 500 forward returns analysis. 750 trading days of data (Jan 2023 to Dec 2025). For each day, compute 30 individual forward returns, extract quintile distributions (P20 through P80), confidence intervals, positive/negative day counts, validation sheets, and Python charts. A proper scenario model where you change the parameters and everything recalculates. Here is the file with all the formulas, validation reports and scripts - S&P 500 30 Days Forward Return - Scenario Analysis

This particular analysis is not useful for actual trade setups (that is a totally different ball game) but it is good-to-know information. How does the S&P dance over the next 30 days? What does the distribution look like across different periods?

Key Steps

Step 1: Data Pull - Claude in Excel with MCP

I connected my YFIN MCP server (Yahoo Finance data) to Claude in Excel. Asked it in natural language to pull S&P 500 daily price data for 3 years. It connected to the backend and pulled the data.

This is where I hit the first issue. Claude tries to pull the entire date range in one shot, dumps everything into its context window, then tries to write it all to Excel at once. Context bloat plus writing thousands of cells - it chokes up and collapses. It basically thinks it is Claude Code and tries to do everything in memory.

You have to guide it - pull in smaller chunks, write using office.js tools, then things go smooth.

If you are looking at thousands of rows, this is not the right tool. A few hundred rows, maybe up to a thousand - fine. Beyond that, use xlwings Lite or Claude Code for the data pull and work with the file directly. But for pulling summaries, KPIs and all ... it's great.

Why is MCP important for Client Work?

When you build backend automations for clients - data processing, report generation, AI-based or regular Python workflows - you expose them as MCP servers. The client connects from Claude in Excel (or any MCP-compatible front end). Now if I change anything in the backend logic, the client's Excel interface works exactly the same. No front-end changes needed. They just talk in natural language, the backend does the work. In my other posts (links at end) I go into depth into MCP servers including security.

Step 2: The Scenario Model - Claude in Excel (native)

This is where Claude in Excel is genuinely strong. I asked it to build a 30-day forward return model...a proper scenario model with a control panel where I can change forward days (30, 15, 10, whatever), change the date range, look at specific periods, and everything recalculates.

It built a MAP/LAMBDA formula that processes all 749 rows at once. For each trading day it grabs the next N close prices, computes percentage returns for each, extracts percentile quintiles, counts positive and negative days. The core formula is genuinely complex - nested MAP, LAMBDA, LET, SEQUENCE, INDEX, PERCENTILE.INC all working together.

Then period summaries - averages by year (2023, 2024, 2025), full period, and a custom date range. AVERAGEIFS and COUNTIFS with proper filtering. Confidence intervals - 95% CI with mean, standard deviation, margin of error. The SUMMARY sheet has all of this with proper statistical workup.

All the formatting, control panels, sheet structure, index sheet with descriptions - Claude did that too based on my instructions.

Step 3: Claude Code for validations - why Two Tools for Validation?

This is where it gets interesting and where the combination of tools shows its value.

First, I had Claude in Excel set up 5 blocks of in-Excel validation covering typical validations that I would have carried out. All passed.

But here is the thing - random row validation passing doesn't always catch formula errors. I wanted a parallel independent computation.

This is where Claude Code comes in. I gave Claude Code the logic - not the formulas, just the description of what we are doing. 3-4 points from the documentation. What data, what computation, what output. Gave it the raw data in tab format. It created an offline Python script, ran it, came back with results.

And it caught an error (minor one though, but still). There was something off in one of the Excel formulas - the random row validations had passed but the aggregate recomputation flagged a mismatch. I went back to Claude in Excel, asked it to fix it. It fixed it. Reran the validation. Everything tallied.

If I had relied on only one tool, that formula error would have slipped through. The parallel validation from a completely different tool and completely different code path is what caught it.

In the validation sheet I am also embedding the Python validation script as a file attachment in the Excel workbook. So somebody reviewing the workbook can see both the Excel validations and the offline Python script that confirmed them.

Step 4: What About Charts?

I wanted proper distribution charts. Fan charts showing quintile ranges with confidence intervals across periods. Ridgeline plots showing the full distribution shape by year. Raincloud charts combining density, box plots and individual data points.

Claude in Excel cannot do this. It has a Python sandbox but it cannot create charts and bring them out of the sandbox into the worksheet. Plus this needs row-level data - 700+ rows need to be plotted. Wrong tool for this job.

So I used Claude Code to generate the xlwings Lite Python script. Claude Code writes the chart code, xlwings Lite executes it in Excel. The charts pull directly from the CALC sheet data, generate in Python (matplotlib/seaborn), and drop into the CHARTS worksheet.

And here is where the model becomes truly useful: I change the parameters in the control panel - different forward days, different date range - the formulas recalculate, and I just rerun the xlwings Lite script. New charts generated from the new data. The whole thing stays alive and repeatable.

For xlwings Lite code generation, even Gemini CLI works well and it has a generous free tier. I used Claude Code here but it is good to know you have options.

Step 5: Documentation - Claude in Excel

Had Claude in Excel create a full DOCUMENTATION sheet. Formula explanations, methodology, what each sheet does, what each column means, how the control panel works. It documented the MAP/LAMBDA formula piece by piece - what each part does in plain English with analogies.

This is the kind of thing that nobody does manually. But when Claude can generate it in minutes based on the formulas it just built, there is no reason not to have it.

The Deliverable

The final workbook has 6 sheets: INDEX (workbook map), SCENARIOS (control panel + per-day computations + period summaries), DOCUMENTATION (methodology + formula breakdown), CALC (computation engine), VALIDATION (85 checks across 5 methods + embedded Python script), CHARTS (distribution visualizations), and SP500_3YR (raw data).

Change the parameters, formulas recalculate, rerun the chart script, new output. Repeatable and auditable.

Which Tool for Which Job?

Each of these tools is a master of its own game.

Claude in Excel (native) - all Excel work. Models, scenarios, complex formulas, validation sheets, formatting, documentation.

Claude in Excel + MCP - natural language interface to your backend automations. Data pulls, report generation, API-based workflows. Powerful for client-facing work where the backend does the heavy lifting.

xlwings Lite - heavy Python in Excel. Advanced automations, charts that need programmatic generation, full-stack Excel apps with control sheets and parameter-driven workflows.

Claude Code - offline Python and JavaScript. Full-stack apps, heavy data processing, parallel validation, script generation for xlwings Lite.