How We Automated Payment Reconciliation in 5 Days

A consulting firm was drowning. Not in clients — that part was good. But in the administrative weight of getting paid.

Every two weeks, one person spent 8 to 10 hours reconciling payments. Matching invoices to client accounts. Cross-checking job codes. Flagging exceptions. Generating reports. It was billable work that didn't bill. It was a bottleneck wearing a person.

Last month, we sat down and mapped the workflow. Then we built a system that eliminated it entirely. Five days from kickoff to production. Two years running. Zero incidents.

Here's what we learned.

The Problem: Manual Process, Manual Errors

The firm had a straightforward business: they took on projects, billed clients biweekly, and collected payments through multiple channels — ACH transfers, credit card processors, even the occasional check.

Here's where it got complicated:

• Multiple payment sources. Some clients paid via ACH. Others through Stripe. One paid by check. The data came in different formats, at different times, with different naming conventions.
• Job-to-client mismatches. Clients could have multiple projects, but payments sometimes came in under the wrong reference number. The reconciliation person had to dig, match, and correct.
• Exceptions everywhere. Partial payments. Late payments. Overpayments. Client refund requests. Each one required investigation and manual correction.
• Zero visibility. Between reconciliation cycles, no one knew the status of accounts or outstanding balances. Managers asked questions the firm couldn't answer quickly.
• Single-person dependency. The one person who owned this process couldn't take vacation. When they did, the entire cycle got delayed.

They weren't inefficient. They were constrained by the problem itself.

Why Existing Tools Didn't Work

The firm had looked at solutions. QuickBooks' built-in reconciliation was close, but it didn't handle their specific workflow. Specialized accounting software existed, but it was built for larger firms and over-engineered for their needs.

The real problem: their workflow wasn't standard. The logic they applied — how they matched payments, what they considered an exception, how they flagged edge cases — was specific to them. Off-the-shelf tools force you to conform to their logic. This firm needed the opposite.

The Approach: Map First, Build Second

We spent the first day observing. Not asking questions about what "should" happen, but watching what actually happened.

• What data sources exist?
• What rules do you apply?
• Where do errors creep in?
• What takes the most time?

That day revealed everything. The logic was actually elegant once you saw it. The work was just tedious.

The Build: Five Days, One Pass

Day 1 (Diagnosis): We mapped the workflow, documented the rules, and identified decision points.

Days 2–4 (Build): We built a system that:

• Pulled invoice data from their accounting system via API
• Retrieved payment data from their bank and payment processors
• Applied their reconciliation rules (which we documented during diagnosis)
• Flagged exceptions for manual review
• Stored everything in a database accessible to the team
• Generated weekly summary reports and sent them via email

Day 5 (Testing & Deployment): We tested against 8 weeks of historical data. One issue: a client name mismatch rule needed tweaking. We fixed it. Then we went live.

What Happened Next

The system runs every night. It pulls fresh data, applies the rules, flags exceptions. By morning, the finance team has a clean summary. They review it — takes 20 minutes. They approve. It exports to the accounting system. Done.

Errors dropped to nearly zero. Before, there were usually 2–3 reconciliation mistakes per cycle. Now: zero. The system is more careful than people.

The freed-up time went back into client work. The firm billed more hours. The ROI was positive in month one.

The system has been running for 2 years. We review logs monthly. We've made exactly two changes: once when the firm switched banks, and once when they changed payment processors. Otherwise, it runs itself.

Why This Works

Most automation fails because it's built for the average case. This worked because it was built for this specific case.

The difference:

• We listened first. We didn't architect from an assumption. We shadowed the workflow, documented the rules, and built to match them.
• We accepted exceptions. Rather than designing a system that worked for 80% of transactions and failed on the edge cases, we built handling for the edge cases from the start.
• We kept it simple. The system does one thing well: reconciliation. It's not a full accounting platform. It's a bridge between data sources and decision points.
• We built for operation. From day one, we thought about monitoring, debugging, and maintenance. That's why it's required minimal intervention.

Could This Approach Work for You?

If you have a process like this — hours of manual work every week, multiple data sources, rules that only one person knows, exceptions that don't fit templates — you might be a good fit for custom automation.

The constraint isn't complexity. It's specificity. If your logic is unique to your business, off-the-shelf tools will always feel like a bad fit. Custom automation that matches your exact workflow isn't luxurious. It's efficient.

Most of our clients are in situations like this. They've outgrown small-team manual work. But they're not ready for enterprise consulting. They just need a system that works the way their business actually works.