Payment statistics consolidator

Institutions in the CEMAC zone report payment statistics as Excel workbooks: cheques and transfers, cards, mobile money, money transfers, loans. Each workbook follows the same template, but tabs vary from one institution to another.

Consolidating a variable number of workbooks by hand creates three risks: missing rows, counting the same flow twice and mixing different periods. Renamed or split tabs (by card network, by month) make manual consolidation even more fragile.

• Automatically consolidate every uploaded workbook, with no hard-coded list of indicators.
• Group results by country and for the whole CEMAC zone.
• Never guess blindly: any uncertain match must stay visible and easy to validate.
• Work fully offline: no data leaves the machine.

Payment statistics workbooks (Excel) covering 11 themes, one workbook per institution. The country is detected from the file name, among the 6 CEMAC countries.

The public repository and its tests rely only on fictitious workbooks. Real files are never published.

• Indicators identified by their position in the template, which is identical across institutions, rather than by their label.
• Renamed tabs recognised through sequence alignment against the template structure, never by name alone; an uncertain match becomes a one-click proposal.
• Safeguards: a tab from an earlier year is set aside, an implausible monthly jump (factor above 15) is excluded from the total and flagged, and the same file uploaded twice is only counted once.
• Total amount per country computed without double counting totals and subtotals.

• Upload and settings grouped in the sidebar; the main area only shows results.
• Excel exports: a full workbook with a total per country and a CEMAC total for each of the 11 themes, plus a summary “Country / Total amount (XAF)”.
• Interactive charts: trend by country, breakdown by country and by item, in count or amount, and breakdown by card network.
• Multi-period history stored outside the application folder.
• Windows executable built by a script, for use without installing Python.

Automatic detection exposes 45 data-bearing rows on the reference files, against 8 with the former hard-coded list of indicators.

295 automated tests run on every change. Some simulate a real file upload in the app, others recalculate the exports with LibreOffice to make sure no formula breaks.

• Everything relies on the workbook template: a tab that does not match it must be assigned manually.
• A local, single-machine application, without sharing between several users.
• Export recalculation tests are skipped when LibreOffice is not installed.