Power Query, VBA macros or Python: which to use for finance automation

The question is who maintains it

Most comparisons of these tools focus on capability. For finance automation, capability is rarely the constraint. The constraint is whether the person who runs the process next year can understand it, change it and explain it to a reviewer. A clever macro nobody can read is a liability the day its author goes on holiday.

The options at a glance

Formulas and Power Query: the default

For most monthly finance work, formulas and Power Query cover the job. They live inside the workbook, anyone with Excel can open them, and every Power Query step is listed in order. That transparency matters more than speed. A reviewer can follow what happened without reading code.

Modern functions such as XLOOKUP, LET and FILTER also make formulas far more readable than the nested lookups many close workbooks still use.

VBA: useful, but check your policy first

VBA is still the most capable way to automate desktop Excel, for example looping through files, building a formatted report pack or saving PDFs. It has two practical problems in 2026. Many organisations block macros from files that came from the internet or email, and macro files must be saved as .xlsm. And VBA tends to accumulate in one person's workbook with no comments or change history.

If you use VBA, keep each macro small, comment what it does in plain language, and keep it out of the calculation logic, so the numbers can still be checked without running code.

Office Scripts: the cloud option

Office Scripts automate Excel for the web and can be triggered on a schedule or by an event through Power Automate. They suit teams already working in SharePoint and OneDrive. Check with your administrator that scripts are enabled before designing around them.

Python: when Excel is the wrong home

Python is the right choice when files reach hundreds of thousands of rows, when matching logic needs several passes with real testing, or when the process should run without anyone opening Excel. The trade-off is ownership. Someone has to maintain the environment and the code. If nobody on the team can, a Python build becomes the new key-person risk.

Python in Excel, which runs Python code inside a cell on Microsoft's cloud, sits between the two. It is useful for analysis but is not yet a natural home for a production reconciliation.

A simple decision rule

1. Can formulas and Power Query do it? Use them.
2. Is there one step they cannot do, such as filling a template? Add a small, documented macro or script for that step only.
3. Has the process outgrown Excel in volume or logic? Consider Python, but only with a named owner.

Whatever the tool, the build should come with checks, a change log and a handover guide. See how to document an Excel workflow.