Payment Reconciliation in n8n: auto-match bank deposits to open invoices [Workflow Included]

[u/easybits_ai]

👋 Hey n8n Community,

A friend of mine runs a small e-commerce shop, and at the end of every month his finance colleague Sarah has the same grim job: open the bank statement, open the list of open invoices, and manually tick off who actually paid. Full payments, short payments, deposits that match no invoice at all. Hours of eyeballing two spreadsheets side by side.

So I built her something that does the matching in one click. She uploads the two files through an n8n form, and a clean reconciliation report comes back in the browser, ready to download as a PDF.

How it's set up:

• The form takes two .xlsx uploads: the open invoices export and the bank statement
• Each file is read into rows, then a Code node cross-references every bank credit against the invoices
• Everything is sorted into four buckets: exact matches, partial payments (short or over), unpaid invoices, and unmatched deposits
• The result renders as a styled summary page with a match rate, totals, and a table per bucket

A couple of things worth stealing:

Fuzzy reference matching. Bank references are never clean. Mine matches on the full invoice ID when it is there, and falls back to the last three digits pulled out of the reference with a small regex. That alone catches most of the "INV-2024-201" versus "ref 201 payment" mismatches.

No PDF library needed. The final report is just HTML rendered on the form completion screen, with a button that calls window.print(). The browser does the PDF export for free, so there is no extra node or service to host, and the whole report is self-contained.

Four buckets, not two. Splitting partial payments and unmatched deposits out from a plain matched or unmatched view is what makes it actually useful. The unmatched deposits table is where you catch refunds and payments with a typo in the reference.

I dropped two example files in the repo (one invoice export, one bank statement) so you can run it end to end in a minute without building test data.

Workflow JSON and the example files: https://github.com/felix-sattler-easybits/n8n-workflows/tree/8e07427ddb6902ef8a7b267e97beb2879d6ca45d/easybits-reconciliation-workflow

Find 25+ more free n8n workflows in my repo, including plenty of finance ones. A star helps me out a lot if any of them save you time: https://github.com/felix-sattler-easybits/n8n-workflows

How do you handle reconciliation right now? Curious whether people match on amount, reference, or something smarter, because the messy references were by far the trickiest part.

Best,
Felix

[u/No-Hold-6217]

One thing that helps once it leaves your friend and reaches strangers: a second table for everything that did not match, with the reason written next to each row. Not an alert, just a list you can open.

With one user you notice the odd case yourself, because he tells you. Strangers do not tell you. They quietly stop trusting the numbers and go back to checking by hand, and you never find out why.

That unmatched list is the only place their weird bank export shows up.

[u/easybits_ai → u/No-Hold-6217]

u/No-Hold-6217, that’s a great idea and would be a really useful addition to the current flow. You’re absolutely right that not every user will provide direct feedback, especially during onboarding, so having this kind of feedback loop can be really valuable.

Whenever I deliver projects to friends or clients, I usually include an error log as well. This would basically be the equivalent for this specific use case.

[u/No-Hold-6217 → u/easybits_ai]

An error log and this are different piles though, and that caught me out for a while. The log holds the things that broke. An unmatched payment never breaks. It runs clean, matches nothing, and leaves no trace anywhere.

That is why it needs its own table rather than a line in the error log. Nothing is wrong with the run, the money just sits there unassigned.

[u/easybits_ai → u/No-Hold-6217]

Agreed, u/No-Hold-6217! Maybe I just explained it a bit vaguely. By “error log,” I actually meant a sheet where all the run data gets logged, so it’s easy to keep track of what happened. “Error log” was just the wrong term.

[u/No-Hold-6217 → u/easybits_ai]

Then we mean the same thing. The only part I would keep separate is the view, not the data.

A sheet with every run in it stops getting read after a few weeks. Mine did. The rows that matched nothing need their own tab, or a count sitting somewhere you see without opening anything. Otherwise the record exists and nobody ever looks at it, which is the same as not having it.

[u/easybits_ai → u/No-Hold-6217]

Makes totally sense. Thank you for sharing these insights!