You do not need a rewrite of the business. You need the macro's logic in a script that can log, retry, and not corrupt the workbook when someone sneezes.
Macros rot because only one person knows which button to press and which sheet not to touch. I sit with that person, write the steps as comments, then implement them in Python with pandas or even just csv.
Week one: run in parallel and compare. Week two: Python is the source, Excel is the export. Week three: schedule it. Week four: take the emergency macro off the desktop.
Keep an export they recognize
Finance may still want a workbook. Fine. Generate it. Do not keep the logic inside it.
Name the differences
When Python and the macro disagree, that is a meeting, not a silent override. Sometimes the macro was rounding in a way nobody documented. That is a feature you would have deleted by accident.
