Background
In 2024, a multi-team governance project in Global Information Security (GIS) required weekly reconciliation of two mission-critical datasets: GISKB and POP's SPI. The process was entirely manual, taking 1-2 hours weekly, and involved cross-comparison, delta identification, and audit artifact compilation.
When the process owner transitioned out, I immersed myself in the routine, recognizing its consistent structure made it ideal for automation. I proposed a hybrid VBA and Python-based solution for data pull, comparison, summarization, and export of compliance artifacts.
The Challenge
- Manual reconciliation took 1-2 hours weekly and was prone to human error
- Three separate data sources needed to be cleaned, merged, and compared
- Audit deliverables required consistent formatting and documentation
- Weekly deadlines demanded a repeatable, resilient solution
My Approach
- Studied the manual workflow and documented each step in detail
- Built a Python script to import and merge POP and GISKB data sources
- Used VBA to apply structured business rules and compare records
- Automated the generation of three core artifacts: a summary of all deltas, the cleaned and merged dataset, and a full data export of both source inputs for audit traceability
- Validated results through side-by-side comparisons during rollout
- Packaged the workflow with editable configuration for future maintainability
Results
- Reduced total reconciliation time from 1-2 hours to under 3 minutes - a 97% time reduction
- Enabled consistent weekly audits with fully traceable outputs
- Eliminated manual formatting errors in deliverables
- Ensured knowledge retention by embedding logic into documented scripts
- Supported transition of responsibilities with minimal disruption
- Became a model for other cross-team data reconciliation efforts
Key Takeaways
- Automating even moderately complex processes can yield massive time savings
- Pairing Python and VBA allows flexibility across data sources and platforms
- Audit workflows benefit greatly from consistent, machine-generated artifacts
- Knowledge capture during automation builds long-term resilience
- A clear understanding of business rules is essential before writing code