Overview
Built a Python utility that takes bulk check deposit exports from the accounting system and automatically splits them into per-payee files mapped to the correct deposit accounts. Replaced a tedious manual process that the accounting team was doing by hand every week.
Key Features
- Reads CSV or Excel files with banking transaction data
- Maps check payees to deposit accounts via a centralized lookup table
- Validates required fields: date, check number, payee, customer, account, memo, amount, class
- Data normalization with string/datetime conversion and field truncation
- Splits output into individual Excel files per payee
- Warns on missing deposit account mappings with color-coded console output
- Interactive file dialog for input selection
- Dated log files for audit trail
Technical Highlights
- Python with pandas for data transformation and openpyxl for Excel output
- Centralized payee-to-account mapping via Excel lookup table
- Colorama for color-coded terminal feedback
- tkinter file dialog for user-friendly input selection
- Structured logging with per-run log files
- Data validation layer catches malformed records before processing