Legacy Migration
VBA-to-Python migration for monthly reporting
Problem
Critical financial reporting macro in VBA — undocumented, built by an employee who had left. Frequently broke.
Solution
Reverse-engineered logic, rebuilt in Python with proper documentation, version control, and unit tests.
Result
Reduced monthly maintenance time by 6 hours. Process now resilient and handover-ready.
The situation
The macro had been written four years earlier by a finance analyst who had since moved on. It ran every month to produce the management reporting pack — pulling data from three source files, applying a set of transformations, and outputting a formatted Excel report.
Nobody fully understood what it did. It worked, until it didn’t. Every two or three months something would break — a source file format would change, a sheet would be renamed, or Excel would update and a deprecated VBA function would stop working. Each incident cost the team an afternoon of debugging, often the day before the report was due.
What I did
Step 1 — Understand what it actually does
I read through the VBA code and documented every step: what data it reads, what transformations it applies, what the output looks like. This took longer than expected — the macro had grown through years of one-line edits, with no logical structure and liberal use of On Error Resume Next.
Step 2 — Write the Python equivalent
The rebuild used pandas for data manipulation and openpyxl for Excel output. Each transformation became a named function with a clear input/output contract. Where the VBA was doing something non-obvious, I added inline comments explaining the business logic.
Step 3 — Test before replacing
I ran both versions in parallel for two months, comparing outputs cell by cell. Three discrepancies were found — in two cases the Python version was correct and the VBA had a long-standing bug that nobody had noticed. In one case I had misread the VBA logic and needed to fix the Python.
Step 4 — Handover package
The final delivery included: the Python script, a README with setup instructions and a description of each transformation step, a CHANGELOG for future modifications, and a test suite that verifies key output values on every run.
Key takeaways
- Legacy VBA is often a black box with undiscovered bugs — document before you migrate, not after
- Parallel running isn’t optional if the process is business-critical
- The value of a migration isn’t just the new code — it’s the documentation that didn’t exist before