Turn an Excel Model into a Python Function

Many decisions run on an Excel model: a pricing sheet, a business case, a capacity calculation. The model belongs in Excel because the people who own the assumptions can read and change it there. But some questions need it run many times — what happens across two thousand combinations of volume and price, which inputs move profit the most, how likely a loss is — and recalculating a workbook two thousand times through Excel or LibreOffice is slow and fragile. The formulas library offers another route. It reads a workbook's formulas, builds the dependency graph between cells, and can compile the part of it between chosen input cells and output cells into an ordinary Python function. The workbook stays the single source of the logic; Python just calls it. This page compiles a small profit model, checks it against LibreOffice, runs a Monte Carlo simulation with it, and shows how to detect formulas the library cannot evaluate. It belongs to Working with Excel Formulas in Python.

Prerequisites

pip install formulas openpyxl numpy

The model is a single sheet: five inputs in B1:B5 — units, price, unit cost, fixed costs and tax rate — and formulas below them for revenue, costs, profit, tax and net margin. LibreOffice is used once to confirm the library's answers.

Step 1 — Load the model and calculate it once

import formulas

model = formulas.ExcelModel().loads("profit_model.xlsx").finish()
solution = model.calculate()

def cell(ref):
    return f"'[profit_model.xlsx]MODEL'!{ref}"

for ref, label in [("B6", "Revenue"), ("B9", "Operating profit"), ("B11", "Net profit"), ("B12", "Net margin")]:
    print(f"{label:17} {solution[cell(ref)].value[0][0]:>12,.4f}")

loads parses the workbook, finish builds the dependency graph, and calculate evaluates every formula from the stored inputs. The results are keyed by fully qualified cell references — workbook, sheet and cell, with the sheet name in capitals — which is verbose, so a small helper builds the keys. Each value is a small two-dimensional array, because the library treats every reference as a range; [0][0] takes the single value. With the stored inputs the model gives revenue of 294,000, operating profit of 43,600 and net profit of 32,700, a net margin of about 11.1%.

Step 2 — Check the answers against a real spreadsheet engine

soffice --headless --convert-to xlsx --outdir checked profit_model.xlsx > /dev/null 2>&1
python -c "
from openpyxl import load_workbook
ws = load_workbook('checked/profit_model.xlsx', data_only=True)['Model']
print({ws[f'A{r}'].value: ws[f'B{r}'].value for r in (6, 9, 11, 12)})"

Before relying on a second implementation of Excel's calculation rules, compare it with an established one. LibreOffice recalculates the same workbook and saves the results, which openpyxl reads with data_only=True; the values match the library's exactly. It is worth repeating this check whenever the model changes, and especially when new functions appear in it, because the library implements Excel functions one by one and its coverage, while broad, is not complete. The recalculation route on its own is described in Recalculate Excel Formulas Without Excel in Python.

Step 3 — Compile the model into a function

profit = model.compile(inputs=[cell("B1"), cell("B2")], outputs=[cell("B11"), cell("B12")])

def run(units, price):
    net, margin = profit(units, price)
    return net.value[0][0], margin.value[0][0]

print(run(12000, 24.5))
print(run(10000, 27.0))
print(run(6000, 22.0))

compile takes a list of input cells and a list of output cells and returns a callable. The arguments are the input values in the same order; everything not listed as an input — unit cost, fixed costs, tax rate — keeps the value stored in the workbook. The function evaluates only the formulas between the inputs and the outputs. Selling 10,000 units at 27.00 instead of 12,000 at 24.50 raises net profit from 32,700 to 37,500, because the higher price more than makes up for the lost volume. At 6,000 units and 22.00 the business makes an operating loss of 27,200, the MAX(0, …) in the tax formula keeps tax at zero, and the net margin comes out at about −20.6%.

Step 4 — Run two thousand scenarios

import time
import numpy as np

rng = np.random.default_rng(42)
units = rng.normal(11000, 1500, 2000).round()
prices = rng.uniform(22.0, 27.0, 2000).round(2)

t0 = time.perf_counter()
results = np.array([run(u, p) for u, p in zip(units, prices)])
elapsed = time.perf_counter() - t0

net = results[:, 0]
print(f"{len(net)} scenarios in {elapsed:.1f} s")  # about 1.7 s on the machine used for this page
print(f"median net profit {np.median(net):,.0f}; 5th–95th percentile {np.percentile(net, 5):,.0f} to {np.percentile(net, 95):,.0f}")
print(f"probability of a loss: {(net < 0).mean():.1%}")

With the model as a function, a simulation is a loop. Units are drawn from a normal distribution around 11,000 and prices uniformly between 22 and 27, and each pair goes through the compiled model. Here two thousand runs took under two seconds — much less than recalculating two thousand copies of the workbook with LibreOffice, where each recalculation means writing a file, starting a conversion and reading the result back. The output is the distribution of net profit: its median, the range covering 90% of scenarios, and the share of scenarios that make a loss — a median of about 23,800, a 90% range from roughly 1,100 to 51,600, and a loss in about 4% of scenarios. The same pattern gives a sensitivity table — vary one input at a time across a range and record the output — or feeds an optimiser looking for the price that maximises profit. Because the logic is still the workbook's, finance can change a formula, save the file, and the next simulation uses the new logic without any change to the Python.

Step 5 — Detect formulas the library cannot evaluate

from openpyxl import Workbook

check = Workbook()
cs = check.active
cs["A1"] = '=_xlfn.XLOOKUP(2,{1,2,3},{10,20,30})'
cs["A2"] = '=WEBSERVICE("https://example.com/rate")'
cs["A3"] = '=_xlfn.STOCKHISTORY("MSFT",1)'
check.save("function_check.xlsx")

results = formulas.ExcelModel().loads("function_check.xlsx").finish().calculate()
for ref in ("A1", "A2", "A3"):
    v = results[f"'[function_check.xlsx]SHEET'!{ref}"].value[0][0]
    flag = "UNSUPPORTED" if str(v) == "#NAME?" else "ok"
    print(f"{ref:4} {v!s:8} {flag}")

When the library meets a function it does not implement, it does not raise an error — it returns #NAME?, the same error Excel shows for an unknown function, and that error then flows into every dependent cell. Modern functions such as XLOOKUP, LET and TEXTJOIN are supported, but functions that reach outside the workbook, such as WEBSERVICE, STOCKHISTORY and the cube functions, are not, and neither are a number of rarely used statistical and engineering functions. Scanning the results of a first calculate() for #NAME? — or any error value — before compiling is a cheap guard against a model that silently returns errors for every scenario.

When this is the right tool

Compiling suits models that are mostly arithmetic, lookups and logic, that finance wants to keep owning in Excel, and that need to be run far more often than a person would recalculate them: simulations, sensitivity tables, optimisation, or a web form that prices a quote. It suits less well models full of external data connections, macros or unsupported functions, and very large models where the first finish() is slow. When Python will own the logic from now on, rewriting the calculation in pandas is clearer still; the formula equivalents in Excel Formula Equivalents in pandas show how. And when the question is a handful of named scenarios rather than thousands, recalculating copies with LibreOffice, as in Run What-If Scenarios on an Excel Model with Python, is simpler and uses a full spreadsheet engine.

Common pitfalls

• Short cell references. Keys are fully qualified: '[book.xlsx]SHEET'!B11, sheet in capitals.
• Forgetting the array. Each value is a 2-D range; take [0][0] for a single cell.
• Trusting it blindly. Compare a run with LibreOffice or Excel first.
• Unsupported functions. They return #NAME? silently; scan for error values.
• Inputs not listed. Cells not passed to compile keep their stored values.

Conclusion

To call an Excel model from Python many times, load it with formulas.ExcelModel().loads(path).finish(), confirm one calculation against LibreOffice, and compile it with the input and output cells you need into a function. That function runs thousands of scenarios in seconds while the logic stays in the workbook finance maintains. Check first that no cell evaluates to #NAME?, since unsupported functions fail silently.

Frequently asked questions

Can Python calculate Excel formulas without Excel? Yes; the formulas library parses and evaluates workbook formulas in pure Python, and LibreOffice can recalculate a workbook headlessly.

How do I run an Excel model with different inputs from Python? Compile it with model.compile(inputs=[...], outputs=[...]) and call the returned function with the input values.

What happens if the library does not support a function? The cell evaluates to #NAME?, which then spreads to dependent cells; scan the results for error values.

Is compiling faster than recalculating with LibreOffice? For many runs, yes: the compiled function evaluates in-process, while each LibreOffice recalculation writes, converts and reads a file.

Related

• Up one level: Working with Excel Formulas in Python — formulas from Python.
• Recalculate Excel Formulas Without Excel in Python — one-off recalculation.
• Run What-If Scenarios on an Excel Model with Python — named scenarios with LibreOffice.
• Map Formula Dependencies in a Workbook with Python — understanding the graph.