The problem
The client had a recurring problem: loans slipping through the cracks. The loans team was manually cross-referencing daily transaction files against a weekly payout report, and the process was error-prone enough that one missed payout had already triggered a due notice. That was the incident that kicked off this project.
The root cause wasn’t negligence — it was volume and format mismatch. Five separate CSV files arriving on different days, one consolidated report arriving on Friday, and no automated way to reconcile them. Something was always going to get missed.
What we built
Every Monday to Friday, transaction data arrives by email as CSV attachments. The system collects all five daily files automatically, including the Friday CSV. Also on Friday, the Loan Payout Report arrives.
Once the application has confirmed it holds the full week — five daily CSVs and the Loan Payout Report — it runs the reconciliation. All daily CSVs are imported into a single DataFrame. The Loan Payout Report is imported into a second. The system then performs a comprehensive lookup across both: every ID in the payout report is checked against the transaction data. Any ID that doesn’t match is flagged.
The output is a clean, professional HTML table containing every unmatched payout. That table is emailed directly to the loans team so they can action outstanding payouts the same day, before the weekend.
The automation angle
The key design constraint was zero manual steps. No one should need to download files, open spreadsheets, or run a script. The system watches for the emails, collects the attachments, waits until the full week’s data is present, runs the match, and fires the report — all without intervention.
The HTML email format was a deliberate choice. The loans team receives a report they can act on immediately without downloading anything or opening a separate tool. The flagged IDs are right there in their inbox, formatted for quick scanning.
How it’s used
- Loans team receives the Friday report in their inbox and works through any flagged IDs the same afternoon.
- Management has a weekly paper trail showing which payouts were reconciled and which required manual follow-up.
- Operations no longer manually touch any files — the process runs entirely on autopilot.
The result
Since the system went live, the client has had zero missed loan payouts. The Friday reconciliation that previously required manual effort and was prone to error now runs on autopilot every week. The loans team receives a clear, actionable report rather than a pile of spreadsheets to compare by hand.
“This solar project completely transformed how we handle our loan payouts. Before this solution, we had a recurring problem where loans would slip through the cracks. One missed payout led to a due notice, which is why this project was built. Since implementing this automated Friday process, we’ve eliminated missed loans entirely. The weekly reconciliation is now reliable, efficient, and runs on autopilot every Friday — saving significant time and removing the risk of human error.”
— Client, Financial Company