Excel + Python + AI workflow for Financial Modelling - chatting with Claude
I’ve spent 20+ years building financial models in Excel and VBA. Recently I’ve been exploring how AI tools fit into that world — not as a replacement, but as a catalyst for a better architecture. What follows came out of a working conversation with Claude (Anthropic’s AI), where we mapped the landscape and ended up designing something I think is genuinely useful. This is based on our knowledge of tools available today - who knows how it will look tomorrow!
The problem
I should confess up front - my views are a bit tainted by the fact I have spent a lot of time in the Excel/VBA/Word space over the last year or two. Excel is great, but I continue to dislike Word and, while quite liking VBA for its automation capaicties, recognise its time has come (maybe a decade or so ago really).
Meanwhile, every vendor is racing to put AI inside the spreadsheet — Copilot, Claude in Excel, Gemini in Sheets — but none of them can actually execute VBA, and most can’t touch named ranges or data tables. We’re in an awkward middle ground.
So this post briefly addresses what we have in this space and how we might use it in an AI post VBA/Word world.
What the AI tools can and can’t do (Feb 2026)
As a starting point, I asked Claude to help me map the actual capabilities across every tool I could find. The full comparison is in the blog linked below, but Claude’s headline findings:
→ Copilot Agent Mode is the most Excel-native AI. It can generate named ranges, formulas, pivot tables, and charts — and you can now switch between GPT 5.2 and Claude Opus 4.5 as the underlying model. But it can’t execute VBA, and it’s not available in the EU/UK.
→ Claude in Excel reads your actual formulas and cell dependencies, can edit in-place, and has financial data connectors (S&P, FactSet, Pitchbook). But it’s blind to VBA and named ranges.
→ Python in Excel (Microsoft’s =PY() function) is excellent for in-grid analytics but has monthly compute quotas, can’t access local files, and explicitly is not a VBA replacement.
→ No AI tool can execute VBA. All of them can write VBA code for you, but you’re still the one pressing F5.
The architecture that emerged
The most interesting part of the conversation wasn’t the tool comparison — it was what came after. We designed an architecture where:
- The financial model is built simultaneously in both Python and Excel, with AI generating both versions from the same specification. Claude skills facilitate this initial build.
- The two models are reconciled automatically — essentially using Python as a second analyst, which gives you built-in model audit as a byproduct
- Excel becomes the ‘sole source of truth’ for assumptions — stakeholders maintain inputs in a familiar environment, which is still a fully operational model and can be changed like any spreadsheet (both assumptions and formulae, depending on protection settings).
- Python reads from Excel (via xlwings) and handles everything which Excel is bad at: Monte Carlo simulation, sensitivity analysis, advanced visualisation and reporting (in some formats, eg VBA/Word).
- Quarto pulls from both Excel and Python sources as required and renders parameterised, reproducible reports — replacing the worst part of VBA/Word reporting with something maintainable and massively enhancible with the more advanced anaytical/viz tools now available in this configuration.
The key insight: this architecture exists because of the current limitations of AI tools, but then redesigns the process to take advantage of the other toolsets which AI makes discoverable to, we think, deliver a far better outcome.
The formula snapshot trick
One problem we worked through: if someone edits a formula in the Excel model, the value-level reconciliation catches the discrepancy but can’t explain why. The solution is a formula snapshot — when the model is first reconciled, export every formula string (not values) as a baseline. When reconciliation fails, diff the formulas against the baseline. You go from “NPV differs by £2.3m” to “cell G47 formula changed from straight-line to declining-balance depreciation.” openpyxl can read formula strings without needing Excel installed.
That snapshot then feeds into a proper PR workflow. The formula diff shows up in a pull request as readable text — reviewers approve or reject the change as text with spreadsheet open, it gets a changelog entry, and the baseline updates only after approval. In a CI/CD setup, reconciliation runs automatically on every PR.
Solving the “binary file in Git” problem
The other thing we worked through: .xlsx files are binary, so Git can store them but can’t diff them. The solution is to treat the Excel file as a build artifact rather than the source. The actual source — the thing you version-control and diff — is a set of YAML files: assumptions (keyed by parameter name), a formula map (every formula string keyed by cell reference), and a structure definition (sheet names, named ranges, cell mappings). These are text files, fully diffable, fully reviewable. The Excel workbook gets generated from them or validated against them. Stakeholders still work in Excel; governance happens in Git, but Excel is still transparent and fully auditable.
We have prototyped this approach using the relatively simple cases of SEC files (so xbrl taxonomies exist), but are currently working on feasibility models using detailed Claude skills by project type using FAST or similar guidelines. So currently an ongoing exploration!
For anyone thinking about this
The migration doesn’t need to be all-or-nothing. Start with Python in Excel for analytics you currently do with complex array formulas. Write your next automation task in xlwings instead of VBA. Move one painful report to Quarto. Build from there.
The full summary guide (17 pages, with comparison matrices and a step-by-step migration strategy) is in the blog linked below. It was genuinely co-produced — my domain knowledge, Claude’s research and synthesis. I think this way of working with AI is more interesting than either “AI will replace us” or “AI is just autocomplete.” It’s a thinking partner, and the output is better than either of us would have produced alone, we believe.
Written by Steve Parton in conversation with Claude (Anthropic) — February 2026
#FinancialModelling #Excel #Python #AI #VBA #ProjectFinance #DCF #Quarto #xlwings #MonteCarlo #Finance
Steve Parton is a finance and analytics consultant building AI-powered financial analysis tools at SJP Consulting. CBL Analytics transforms SEC filings into actionable intelligence.