stefhooy/mpower_finance_streamlit

Automates monthly PMEC payment reconciliation for MPower Ventures Zambia. Upload three government files, download a structured Excel report. # MPower PMEC Reconciliation App A Streamlit web application that automates the monthly PMEC payment reconciliation for MPower Ventures Zambia. Upload the three government return files for any month and get a structured Excel reconciliation output instantly, with no technical knowledge required. --- ## What it does Each month, the Zambian government processes MPower's payroll deduction submissions and returns three files. This app reads all three, runs the reconciliation logic, and produces a downloadable Excel workbook with a full breakdown of successful payments, insufficient funds, rejections, and the expected bank transfer amount. --- ## Input files The app expects the three files that Misozi receives from PMEC each month: | File | Format | Description | |---|---|---| | Successful payment report | `.txt` | Fixed-width government export, e.g. `E892 - MPower Ventures Zambia.txt`. Rows beginning with a pipe character that contain the period code (e.g. `202604`) are parsed by position: Personnel Area, SubArea, Employee No, Name, NRC, Period, Amount (ZMW). | | Insufficient funds list | `.xlsx` | Employees whose salary headroom fell below the 30% threshold, e.g. `E892.xlsx`. Required columns: Employee No., Employee Name, Personnel SubArea Text, Start Date, End Date, Amount, Reason. Amount can be blank, treated as missing rather than zero. | | Rejection list | `.xlsx` | Hard rejections, e.g. `E892 R.xlsx`. SAP/PMEC column names: PERNR, FIRST NAME, SURNAME, BEGDA, ENDDA, BETRG, COMMENTS. Known reasons: SEPARATEES, LOCKED, DEATH, DISMISSAL, TERMINATION OF SERVICE, RETIREMENT AT MANDATORY AGE, RESIGNATION WITH NOTICE, UNPAID LEAVE, INVALID EMPLOYEE NUMBER, INVALID END DATE, EXISTING RECORD. | --- ## Output A seven-sheet Excel workbook, downloaded directly from the browser: | Sheet | Contents | |---|---| | `1_Reconciliation_Summary` | Totals by category, PMEC fee calculation, expected bank transfer, and data quality flags | | `2_Successful_Payments` | F …