Python Automation for EPMIS

Problem

The EPMIS (Enterprise-wide Procurement and Inventory Management Information System) generated raw inventory exports that required significant manual effort to turn into usable reports. Staff were manually filtering, sorting, and copy-pasting data across spreadsheets — a slow, error-prone process that ran every reporting cycle.

Two distinct data problems needed solving: a static stock balance view across regional facilities, and a dynamic stock ledger tracking ongoing inventory transactions like transfers, receipts, and material issuances.

Approach

Treat both as data pipeline problems. Write Python scripts that ingest raw EPMIS exports, apply cleaning and validation rules, route exceptions into dedicated worksheets, and produce formatted Excel reports — end to end, without manual steps.

The goal was one-click execution: run the script, get a report that is already audit-ready.

Implementation

Stock Balance Automation

Built an automated pipeline to clean and summarize active warehouse inventory records across 25 regional zones:

• Data cleaning: stripped trailing spaces, removed blank entries, and filtered system-generated summary rows from raw exports
• Facility name standardization: resolved inconsistent naming conventions across hospital profiles — handling shorthand variants and suffix tags — and mapped every record to a canonical facility name
• Category mapping: wrote a prefix-based matching function to assign inventory records to operational service groups (Facilities Engineering, Clinical Logistics Services, and others)
• Exception handling: records with missing category mappings were automatically captured into an Unmapped Items worksheet; non-warehouse and in-transit records were routed to an Excluded Rows worksheet for audit traceability
• Report generation: structured a dynamic summary grid that calculates inventory values by hospital and service line, fills missing combinations with zero values, and applies bold headers, consistent number formatting, and auto-adjusted column widths to the output workbook
• Audit worksheet: generated a reconciliation sheet comparing processed totals against excluded values so every source record is accounted for

Stock Ledger Automation Framework

Built a suite of three targeted scripts to parse a master stock ledger into specialized operational reports:

• transfer_report.py: identifies stock transfer transactions by detecting STR voucher prefixes and transfer-related keywords, then matches negative deductions at source facilities to corresponding positive arrivals at destinations — reconstructing full transfer routes and flagging unmatched or in-transit items
• receiving_report.py: filters inbound inventory by detecting PURCHASE RECEIPT and MAT-PRE-* references, then calculates quantity received and inventory value per facility
• issuance_report.py: isolates material consumption by extracting MAT-STE transactions and excluding transfer records to prevent double-counting, producing a clean view of actual inventory usage

All three scripts share the same validation and formatting layer: invalid hospital names, unmapped item categories, and unmatched transfer records are routed into dedicated exception worksheets, and output workbooks apply standardized formatting without manual editing.

Challenges

The hardest part was data quality at the source. Raw EPMIS exports contained inconsistent naming, ambiguous transaction references, and records that could legitimately belong in multiple categories. Every edge case had to be handled explicitly rather than silently dropped — otherwise the reports would undercount without anyone knowing.

The exception worksheets became the key design decision: rather than failing on bad data or silently ignoring it, the scripts expose exactly what was excluded and why. That makes the output auditable and the exceptions actionable.

Outcome

Delivered two automation systems that replace recurring manual spreadsheet work:

• The stock balance pipeline produces a validated, audit-ready summary across all 25 regional zones — formatted and reconciled, ready for management review
• The ledger framework produces three specialized reports (transfer_summary.xlsx, receiving_quantity_summary.xlsx, issuance_quantity_summary.xlsx) from a single source file, with built-in verification that every transaction is accounted for

Processing time dropped significantly, human error was removed from the data transformation step, and the reports became consistent enough to rely on for recurring financial and operational review.

Lessons Learned

Automation without exception handling is just a different way to lose data. The reporting was straightforward; the work was in deciding what to do with every record that did not fit cleanly — and making that decision visible in the output rather than buried in code.