Project · WRK-008
Monthly Finance Report Automation
A reconciliation and month-end report tool for a small trading company. Half a day of copy-and-paste now takes about ten minutes.
- Python
- pandas
- openpyxl
- PyInstaller
Project background
At the start of each month, the accountant combined bank statements, sales exports and an expense sheet, reconciled them, and built a report for the owner. About four hours, all copy and paste.
Problem
- Three sources, three formats, cleaned up by hand every time;
- Unmatched transactions had to be hunted down one by one;
- The report layout never changed, yet it was rebuilt from scratch each month.
Approach
Leave their existing software alone. Build a small desktop tool: drop the files in, run the steps, export the reconciliation and the report.
Key features
- Import that recognises bank, sales and expense files;
- Cleaning for dates, amounts and customer names;
- Automatic matching by amount and date, with leftovers listed separately;
- Monthly summary by customer and product category;
- Export in the company's existing Excel layout.
Architecture
Python and pandas for the data, openpyxl to write Excel, packaged for Windows with PyInstaller. Nothing is uploaded — it all runs on the accountant's PC.
What we handled
Mapping the manual process, writing the rules, building and packaging the tool, and remote installation.
How it went
We asked the accountant to screen-record one full month-end. Each step became its own stage that can be checked on its own, and if one fails, the run stops right there.
Screenshots
File names and amounts in the screenshot are test data.
What went wrong
The bank changed its export format once and the tool crashed. Now it checks the format first and asks, instead of producing a wrong number.
Outcome
Month-end went from about four hours to roughly ten minutes. The remaining time goes into the handful of rows marked "needs review".
Current status
Maintained. Rules get updated when a bank or system changes its format.
Next
Consolidated reports across several company entities.