Problem
A single master stock ledger contained three distinct transaction types — transfers, receipts, and material issuances — mixed together in one export. Separating them accurately required understanding voucher codes and business rules that weren't documented, with the added constraint that the same physical stock movement could appear in multiple transaction types (requiring explicit deduplication to avoid double-counting).
Approach
Build three targeted scripts that each parse the same master ledger for their specific transaction type. Share a common validation and formatting layer across all three. One source file → three audit-ready reports.
Implementation
transfer_report.py
Identifies transfer transactions by detecting STR voucher prefixes and transfer-related keywords. Matches negative deductions at source facilities to corresponding positive arrivals at destinations — reconstructing full transfer routes. Unmatched or in-transit items are flagged in a dedicated exception worksheet.
receiving_report.py
Filters inbound inventory by detecting PURCHASE RECEIPT and MAT-PRE-* references. Calculates quantity received and inventory value per facility.
issuance_report.py
Isolates material consumption by extracting MAT-STE transactions and explicitly excluding transfer records — prevents double-counting. Produces a clean view of actual inventory usage at each facility.
Shared validation layer
All three scripts route invalid hospital names, unmapped item categories, and unmatched records into dedicated exception worksheets. Output workbooks apply standardized formatting without manual editing.
Outcome
Three separate reports from one source file:
transfer_summary.xlsx— full transfer route reconstructionreceiving_quantity_summary.xlsx— inbound inventory by facilityissuance_quantity_summary.xlsx— material consumption per facility
Every transaction appears in exactly one report, with exceptions surfaced explicitly. The shared exception layer means anomalies aren't silently lost — they're visible and actionable.