Dynamic Document Automation
Developed a Python-based tool to replace a manual Excel mail-merge process. By ingesting structured exports and grouping records via Pandas, I reduced monthly document generation from 4 hours to just 5 minutes.
Fig 1.0: Transition from 4-hour manual prep to automated execution.
Fig 1.1: Custom Tkinter UI for non-technical staff deployment.
The Problem
Legacy Limitations: Standard mail-merge tools struggled with the "one-to-many" relationship (one letter containing a table of multiple clients).
Time-Intensive: The monthly process required manual data flattening and preparation in Excel, taking approximately 4 hours per cycle.
Complex Grouping: The team had to manually filter and group records for each Recipient, which was highly prone to copy-paste errors.
The Solution
- Automated Data Ingestion: Built a script to ingest structured exports directly from the system.
- Relational Grouping: Applied pandas grouping logic to automatically segment records by Entity ID, ensuring 100% data accuracy.
- Dynamic Document Generation: Programmed the script to generate individual letters, dynamically embedding a formatted table of client data within each document.
- Versatility: Reused and adapted the core logic for multiple communication streams, including "Service Rates" and "Account Status" letters.
- Accessibility: Packaged the solution with a lightweight GUI interface, allowing non-technical team members to run the automation independently.
The Logic Layer
Final Output Samples (Sanitised)
Business Impact
98% Efficiency Gain
Reduced processing time drastically, bringing the 4-hour manual cycle down to just 5 minutes of automated execution.
Operational Capacity
Freed up staff to focus on higher-value tasks and operational priorities rather than administrative formatting.
Consistency & Error Reduction
Standardised all communication outputs, ensuring a professional and uniform appearance across all Entities while completely eliminating the risk of copy-paste grouping errors.