Knowledge Base Weekly Reconciliation

From Hours to Minutes: Automating Knowledge Integrity at Scale

Background

In 2024, while working on a multi-team governance project within Global Information Security (GIS), I was assigned to support the weekly reconciliation of two mission-critical datasets: the GISKB (GIS Knowledge Base) and POP’s official SPI (Security Policy Inventory) feed. The reconciliation had been a completely manual process, taking 1–2 hours each week to cross-compare line items, identify deltas, and compile audit artifacts.

As the process owner transitioned out, I quickly immersed myself in learning the end-to-end routine—reviewing previous artifacts, interviewing stakeholders, and walking through each manual step. I recognized early on that the reconciliation followed a consistent structure with well-defined comparison points—ideal conditions for automation. I proposed a hybrid VBA and Python-based solution that would replicate the full workflow, including 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

Company

Bank of America
Addison, TX

Role

Senior Knowledge Manager

Services Provided

Process Analysis, Workflow Development, Data Transformation, Report Automation

Additional
Automation Projects

See full descriptions on the
Process Automation page