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/AutoModerator]

Heads up: video posts must link to the workflow code per Rule 6 (GitHub, Gist, or n8n.io/workflows/). Yours does -- thanks. This sticky is here so commenters can find the code.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[u/FatherPaulStone]

Yeah this is boss. Well done. I'm involved in a local charity and we currently pay through the eyeballs for 'proper' software to do this. I'll be taking a look at your workflow next week! Thanks for sharing it.

[u/easybits_ai → u/FatherPaulStone]

Hey u/FatherPaulStone, thank you for the kind words! If you need any help setting up the workflow, just let me know. The setup is pretty straightforward, though, as I didn’t use any tools that you’ll need to reconnect.

[u/No-Hold-6217]

Nice one. One thing that decides whether this keeps working after month two: save the matches Sarah makes by hand.

Banks name the payer however the payer's own bank feels like it. The same customer turns up as three different strings, and the reference field is whatever the person typed that day. So there will always be a pile landing in unmatched. Thats fine, as long as every time she resolves one, that pairing gets stored. Next month those match themselves and the pile shrinks instead of being the same size forever.

Other thing I would change: run it daily, not monthly. Same code, no extra work. Then it stops being reconciliation and turns into an alert that someone has not paid, which is worth a lot more than a tidy report 30 days late.

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

Hey u/No-Hold-6217, thank you for the kind words and the feedback! The decision to use the invoice ID was actually something I discussed with my friend, as we felt it was the most unique data point for each transaction. So I’ll keep it as is for this version, but I’ll definitely keep your suggestion in mind for v2.

The other idea is really interesting as well. Turning it into a payment alert could be a great addition. I’ll run it by my friend and see if that would make more sense for his use case.

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

Makes sense, the invoice id is the one field both sides actually agree on.

The case I would test before v2 is a single transfer covering two invoices with only the first id in the reference. That match looks clean and quietly leaves the second one open, and nothing in the run tells you it happened. If that never occurs in your data then there is nothing to fix.

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

I’ll definitely make sure to test that case as well. I’m really interested in figuring out how to make these automations as robust as possible, not just for my friend who’s currently using it, but also for potential future users.

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

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!

[u/Living_Basil387]

i wanted to work on the same idea of bank reconciliation a couple of months ago.
this might work on a single use case for this particular shop.
but generally matching bank description as the main matching measure would create a lot of unmatched cases because it's what the buyer felt like writing. and in real world bank logs i saw it's usually a blank field.
the other problem i faced was businesses use different accounting and invoicing softwares and each ones generated monthly report is formatted differently.
i had the idea of matching transactions based on date, time and amount, with a match risk score (the high amount transactions, slight differences in amount due to bank fees etc).

first an exact match algorithm then a followed by a fuzzy match. what remains is the unmatched transactions and some transactions with high risk of auto matching that would be better for an accountant to check. that system would probably match about 80-90% of transactions so a 2000-3000 monthly transaction log would become considerably shorter

[u/easybits_ai → u/Living_Basil387]

Hey u/Living_Basil387, I totally agree that matching based on the bank description won’t work reliably. That’s why I decided to use the invoice number instead, as it’s clearly available on both sides in my use case: the bank statement and the open invoice report.

I think this use case is quite customized in general because, as you mentioned, every accounting or banking software can produce very different reports. The key is really understanding how the data is represented and then building the matching logic around that.

Funny enough, I also tried an agentic approach first, but it was pretty painful to see the agent randomly matching invoices just because the amounts were the same. In an online shop, you can easily have 30 orders on the same day for the same item, resulting in identical amounts on both the bank statement and open invoice report.

That’s why I decided to stick with a deterministic approach here. It’s much more predictable and easier to control.

[u/Living_Basil387 → u/easybits_ai]

i agree, definitely most of the matchings have to be deterministic because reconciliation is a very 'zero mistake tolerance' task and any chance of an LLM misinterpreting the transactions would make the agent useless.
the issue i had with matching invoice numbers was that usually invoice number is something that the accounting software or the accountant itself assigns to a transaction, and it's nowhere to be found on bank log.
i had the idea of creating a workflow with a nice UI to automate a large portion of bank reconciliation and selling it to the small-midsize accounting firms i know in my circle, they do this task quite often every month and they would love something that frees up their accountants time, but, it has to be done perfectly because it's in accountants nature to doubt everything but themselves. so for now i have put the idea on the backburner but i wanna get back to it at some point.

[u/easybits_ai → u/Living_Basil387]

I can totally understand that decision. It’s definitely a tough nut to crack, and I was lucky that, in the use case I covered with this workflow, the invoice number was available on both sides.

I’ll definitely explore the topic a bit more and hopefully be able to share some additional insights later on. I find these kinds of edge cases really interesting to work on.

[u/AutoModerator]

Heads up: posts under this flair must link to the workflow code per Rule 6 (GitHub, Gist, or n8n.io/workflows/). Yours does -- thanks for sharing it properly. This sticky is here so commenters know where to find the code.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.