Every enterprise has them: critical business calculations running in Excel workbooks that have grown into unmaintainable monsters. Hundreds of formulas, cross-sheet references, VBA macros, and a single owner who is terrified of breaking anything. When that person leaves, the organization has a critical knowledge gap wrapped in a binary file format.
I recently worked through converting a complex Excel workbook — a financial calculator with 8 interconnected sheets, holiday calendars, and business day calculations — into a maintainable Python application. The process combined AI coding assistants with a structured decomposition workflow. Here is what I learned about using AI to modernize spreadsheet logic.
Watch the 30-second summary
Auto-generated summary — read the full article below for details.
The Problem with Spreadsheet Business Logic
Before diving into the solution, let me frame why this matters:
- Opacity: a formula like
=VLOOKUP(B5,holidays!A:B,2,FALSE)encodes business logic invisibly. There are no tests, no documentation, no version control. - Fragility: inserting a row can break every absolute reference. Copy-pasting a sheet “for backup” creates divergent versions.
- Binary format lock:
.xlsb(Excel Binary) files cannot be diffed, reviewed, or tested in CI/CD pipelines. - Knowledge concentration: typically one person understands the full workbook. That is an organizational single point of failure.
- Audit inability: regulators increasingly require explainable, testable calculation logic. “It is in the spreadsheet” is not an acceptable answer.
The goal: convert the workbook into version-controlled, testable Python code that produces identical results, with a Streamlit frontend for business user interaction.
The 5-Step Workflow
Step 1: Extract the Workbook Preserving Formulas
The first critical decision: do NOT open the workbook and “Save As CSV.” That captures VALUES, not formulas. You need the formulas.
For .xlsb files, openpyxl cannot read them directly (it handles .xlsx only). Use pyxlsb for reading binary workbooks:
from pyxlsb import open_workbook
import csv
def extract_sheet_formulas(xlsb_path, sheet_name, output_csv):
with open_workbook(xlsb_path) as wb:
with wb.get_sheet(sheet_name) as sheet:
rows = []
for row in sheet.rows():
row_data = []
for cell in row:
if cell.formula:
row_data.append(f"={cell.formula}")
else:
row_data.append(cell.v)
rows.append(row_data)
with open(output_csv, 'w', newline='') as f:
writer = csv.writer(f)
writer.writerows(rows)
For .xlsx files, openpyxl works directly:
from openpyxl import load_workbook
wb = load_workbook('calculator.xlsx', data_only=False)
for sheet_name in wb.sheetnames:
ws = wb[sheet_name]
for row in ws.iter_rows():
for cell in row:
if cell.data_type == 'f':
print(f"{cell.coordinate}: {cell.value}") # prints formula
AI assistant prompt for this step: “I have an XLSB workbook with 8 sheets. I need a Python script that extracts every cell — preserving formulas as strings, not computed values — into separate CSV files per sheet. Use pyxlsb. Handle merged cells and empty rows gracefully.”
The AI will generate a working extraction script in one or two iterations. Corrections needed: handling of shared formulas (where Excel stores one formula and applies it to a range) and proper escaping of commas within formula strings.
Step 2: Fix Multi-Sheet Handling
The initial extraction script typically handles a single sheet correctly but fails on multi-sheet references. Common issues:
- Cross-sheet formula references like
=holidays!B5appear as string literals in some parsers. - Named ranges resolve differently per sheet.
- Hidden sheets (often used for lookup tables) get skipped.
AI prompt pattern: “My extraction script misses cross-sheet references. Here is an example formula that references another sheet: =NETWORKDAYS(start_date, end_date, holidays!A2:A50). Update the script to preserve these references as-is in the output CSV.”
After this step, you should have one CSV per sheet with formulas intact. Verify by spot-checking 10-20 complex formulas against the original workbook.
Step 3: Cell-by-Cell Formula Detection and DataFrame Export
Now comes the decomposition. For each sheet, categorize every cell:
- Static value: numbers, dates, or text that never change (reference data).
- Input cell: values that the user provides (parameters).
- Computed cell: a formula that derives from other cells.
import pandas as pd
import re
def categorize_cells(csv_path):
df = pd.read_csv(csv_path, header=None)
cell_map = {}
for row_idx, row in df.iterrows():
for col_idx, value in row.items():
cell_ref = f"{chr(65 + col_idx)}{row_idx + 1}"
if pd.isna(value):
continue
elif str(value).startswith('='):
cell_map[cell_ref] = {
'type': 'formula',
'value': str(value),
'dependencies': extract_references(str(value))
}
else:
cell_map[cell_ref] = {
'type': 'static',
'value': value
}
return cell_map
def extract_references(formula):
"""Extract all cell references from a formula."""
pattern = r"(?:(\w+)!)?([A-Z]+[0-9]+)(?::([A-Z]+[0-9]+))?"
return re.findall(pattern, formula)
This categorization is the foundation for the dependency graph in the next step.
Step 4: Build the Dependency Graph and Generate Python Code
This is the most intellectually challenging step — and where AI assistance provides the most leverage.
Map the dependencies between sheets:
calculator.csv --> holidays.csv (for NETWORKDAYS calculations)
calculator.csv --> rates.csv (for interest rate lookups)
summary.csv --> calculator.csv (aggregates results)
AI prompt for dependency analysis: “Here are the formulas extracted from my calculator sheet (paste first 50 formulas). Identify all cross-sheet dependencies and create a dependency graph showing which sheets must be computed first.”
AI prompt for code generation: “Convert these Excel formulas into equivalent pandas operations. The holidays sheet contains a list of dates in column A. The calculator sheet uses NETWORKDAYS(start, end, holidays!A:A) in cells C5:C100. Generate a Python function that computes the same result using numpy busday_count with a custom holiday calendar.”
Example of formula-to-Python conversion:
Excel formula:
=IF(NETWORKDAYS(B5, C5, holidays!A2:A50) > 30,
D5 * rates!B3,
D5 * rates!B4)
Python equivalent:
import numpy as np
from numpy import busday_count
def compute_fee(start_date, end_date, amount, holidays, standard_rate, reduced_rate):
business_days = busday_count(
start_date,
end_date,
holidays=holidays
)
if business_days > 30:
return amount * standard_rate
else:
return amount * reduced_rate
The key insight: prompt the AI with the FORMULA and the BUSINESS CONTEXT. “This calculates a penalty fee based on business days elapsed” produces much better code than “convert this formula.”
Step 5: Build a Streamlit Demo
Once the calculation logic is extracted into clean Python modules, build a Streamlit interface for business user validation:
import streamlit as st
from calculator_module import FinancialCalculator
st.title("Financial Calculator")
st.write("Converted from Excel workbook - producing identical results")
# Input parameters
start_date = st.date_input("Start Date")
end_date = st.date_input("End Date")
principal = st.number_input("Principal Amount", min_value=0.0)
# Compute
calc = FinancialCalculator(holiday_calendar='2026')
result = calc.compute(start_date, end_date, principal)
# Display results
st.metric("Business Days", result.business_days)
st.metric("Calculated Fee", f"${result.fee:,.2f}")
st.metric("Total Due", f"${result.total:,.2f}")
# Comparison mode
st.subheader("Validation Against Excel")
excel_result = st.number_input("Enter Excel result for comparison")
if excel_result:
diff = abs(result.total - excel_result)
if diff < 0.01:
st.success("Results match within $0.01")
else:
st.error(f"Discrepancy: ${diff:,.2f}")
The Streamlit app serves dual purposes: it is the production interface AND the validation tool. Business users can run their known test cases and verify the Python output matches Excel.
The Prompt Engineering Pattern
Working with AI coding assistants on this type of project, I found a consistent pattern that produces good results:
1. Start Broad, Then Narrow
Bad first prompt: “Convert my entire Excel workbook to Python.”
Good first prompt: “I have an Excel workbook that calculates financial penalties. Sheet 1 (calculator) uses business day calculations referencing Sheet 2 (holidays). Here are the first 10 formulas from the calculator sheet. Explain what business logic they implement.”
Start with comprehension, then move to conversion.
2. Iterate with Corrections
The AI will get formulas approximately right on the first pass but miss edge cases. Feed back the discrepancies:
“Your NETWORKDAYS implementation returns 22 for these dates but Excel returns 21. The difference is because Excel counts the start date as day 0 and the end date as the last business day. Adjust the calculation.”
3. Separate CLI from Functional Code
Always ask the AI to generate two things separately:
- Module: pure calculation logic with no I/O, no Streamlit, no CLI parsing. Just functions that take inputs and return outputs.
- Interface: CLI or Streamlit app that imports the module.
This separation enables reuse. The same calculation module can power a Streamlit demo, a REST API, a batch job, or unit tests.
Prompt: “Refactor this code. Extract all calculation logic into a calculator.py module with pure functions. Create a separate app.py that imports the module and provides the Streamlit interface. The module should have zero dependencies on Streamlit.”
Validation Strategy
Never trust AI-generated calculations without systematic validation:
- Unit tests from known inputs/outputs: take 20-30 scenarios from the Excel workbook (known input sets and their calculated outputs) and write pytest tests.
import pytest
from calculator_module import FinancialCalculator
@pytest.mark.parametrize("start,end,principal,expected_fee", [
("2026-01-05", "2026-02-15", 10000, 125.50),
("2026-03-01", "2026-03-10", 50000, 312.75),
("2026-06-15", "2026-09-30", 25000, 890.00),
])
def test_fee_calculation(start, end, principal, expected_fee):
calc = FinancialCalculator(holiday_calendar='2026')
result = calc.compute(start, end, principal)
assert abs(result.fee - expected_fee) < 0.01
Bulk comparison: run all rows from the original workbook through both Excel and Python, compare outputs programmatically.
Edge cases: first/last business day of month, holiday adjacency, leap years, zero-amount inputs.
Lessons for AI-Assisted Legacy Modernization
This project reinforced several principles that apply broadly to any AI-assisted modernization effort:
AI excels at pattern translation, not invention
Converting a known formula to equivalent Python is a translation task — AI handles it well. Deciding WHAT to build or HOW to structure the application still requires human judgment.
Decomposition is the human’s job
The AI cannot look at a 500-formula spreadsheet and determine the optimal module structure. The human architect must decompose the problem into digestible units that the AI can process effectively.
Verification is non-negotiable
AI-generated numerical code WILL have subtle bugs. Off-by-one errors in date calculations, integer vs. floating-point division, timezone handling — these require explicit test cases, not trust.
The 80/20 rule applies
AI gets you 80% of the way in 20% of the time. The remaining 20% (edge cases, precise business logic matching, performance optimization) takes 80% of the time. Plan accordingly.
Documentation emerges naturally
One unexpected benefit: the AI prompts and responses serve as documentation. You now have plain-English explanations of what every formula does — something the original Excel workbook never had.
Conclusion
Converting spreadsheets to production code is not glamorous modernization work, but it addresses real business risk. The combination of structured decomposition (extract, categorize, graph dependencies, convert, validate) with AI coding assistance makes previously prohibitive conversions feasible.
The workflow scales: I have used this same 5-step pattern on workbooks ranging from 3 sheets to 15 sheets. The complexity scales linearly with the number of inter-sheet dependencies, not with the number of cells.
If you have a critical spreadsheet with no documentation and a single maintainer, start with Step 1 today. Just extracting the formulas into version-controlled CSV files is already an improvement — even before you write a single line of Python.