Python Excel Reconciliation Automation - 13/05/2026 07:06 EDT

I run a recurring reconciliation in Excel 365 that compares a “source-of-truth” user-device list against a second Excel sheet exported from an external system. Today the logic lives entirely in formulas (VLOOKUP/XLOOKUP, IF tests, equality checks on phone numbers, locations, etc.). I want that same logic rebuilt in Python so I can trigger the process with a single command instead of copy-pasting formulas every week.

Here is what I need:

• A clean, well-commented Python script (preferably using pandas, openpyxl or xlsxwriter) that ingests the two Excel inputs, performs the same look-ups and comparisons the workbook does now, and writes an output file that preserves the existing layout while adding coloured flags or status columns exactly as the current template shows.
• A detailed report summarising total records, matches, partial matches, mismatches and any anomalies; this can be a separate worksheet or a standalone file—whichever is simpler to maintain.
• The logic must remain easy to tweak: if the source columns shift or new comparison fields appear later, I should only have to edit a mapping section, not the whole script.
• All processing should stay inside Python—no hidden macros inside the output workbook.

I will provide:
1. The live workbook that contains every formula you’ll be translating.
2. An anonymised sample dataset so you can unit-test your code.

Acceptance criteria: the script produces an output that, when spot-checked against the original formula workbook, shows identical pass/fail results for each record; the detailed report tallies match those counts.

If you have experience automating Excel tasks with Python and can deliver readable, reusable code, I’d love to hear how you would approach this.