Improve Bank Reconciliation

[barredterra]

Is your feature request related to a problem? Please describe.

Currently it's challenging to reconcile Bank Transactions with other transactions in ERPNext.

• Bank Transaction holds too little information to make sense of it. Party Name and Party IBAN as per bank statement are missing.
• It takes too many clicks to "pay" for a voucher with a Bank Transaction: create a Payment Entry, fill some data manually, go back to the Bank Reconciliation Tool, search the Payment Entry, reconcile with it.
• Matching Bank Transactions with parties and vouchers is still a very manual process. We can do some more "magic" to automate this.

Describe the solution you'd like

• Semi-automatic, self-improving matching of party names as per bank statement with parties in ERPNext. Fuzzy matching, correction by user, learn from past matches.
• Match parties by bank account number if their bank details are already in the system.
• Provide an easy way to store a parties bank details for future reference and matching.
• Match Bank Transactions with vouchers. Likely matches:
  • The voucher number is contained in the description of the Bank Transaction
  • There's an unpaid voucher for the same party with the same amount
• Provide the option to create and reconcile a Payment Entry with a (matched) voucher, in one click.
• Transparency: tell the user why a voucher was matched or why not

Possible checks

a) Check for open vouchers with a similar amount as the Bank Transaction (5% tolerance) and in a certain time range around the bank transaction (two months backwards, 1 week forwards) b) Check for voucher numbers contained in the description of the Bank Transaction c) Check for a similar party name (fuzzy matching with low tolerance) d) Check for exact party names contained in the description of the Bank Transaction e) Check if previous Bank Transactions with the same bank and account number have been matched with a party f) Check if there's a party with the same bank and account number g) Transaction number in Bank Entry matches transaction number in Bank Transaction

• Automatically reconcile if a AND (b OR e OR f OR g)
• Propose party if c OR d
• Propose voucher if a OR b OR g
• Automatically set party e OR f OR (a AND b)

Competition:

• https://www.youtube.com/live/U1EYxuZ7Q2E?feature=share
• https://learn.microsoft.com/en-ie/dynamics365/business-central/bank-how-reconcile-bank-accounts-separately

Related:

• https://github.com/frappe/erpnext/issues/17685

[lylebarrere]

As a business user of many different accounting systems, I can say the way Xero presents bank reconciliation is the easiest to understand.

The information density of having bank transactions in one column to match to accounting transactions in the other is fantastic. Its auto match between bank transactions similar to how @barredterra described, matching checks based on check number and transactions based on specific amounts. If the auto match is incorrect it has the ability to search for the correct transaction or create a new one without needing to leave the screen.

[marination]

Party auto matching is tackled separately in the linked PR What is left is improvement of Bank Reconciliation for ease of use.

[casesolved-co-uk]

• It takes too many clicks to "pay" for a voucher with a Bank Transaction: create a Payment Entry, fill some data manually, go back to the Bank Reconciliation Tool, search the Payment Entry, reconcile with it.

This is not true. The Bank Reconciliation Tool allows you to create a Payment Entry from a bank transaction (see screen shot). Then you need to do a Payment Reconciliation. I and others would like to see this as a single Reconciliation step: #29497.

• Semi-automatic, self-improving matching of party names as per bank statement with parties in ERPNext. Fuzzy matching, correction by user, learn from past matches.
• Match parties by bank account number if their bank details are already in the system.
• Provide an easy way to store a parties bank details for future reference and matching.

• Match Bank Transactions with vouchers. Likely matches:

  • The voucher number is contained in the description of the Bank Transaction
  • There's an unpaid voucher for the same party with the same amount

The way bank reconciliation matches are currently offered: the database query for each voucher type uses information from the bank transaction (such as the amount) to add to a rank (or score) value to each result, and the results are then sorted by rank, then date. The score value could be improved to take more data into account. Many of the queries are still written in sql rather than the new qb language, so the whole structure could do with a review.

I've also thought the system could use previously reconciled records to do auto-matching.

Once the party matching #34675 has been implemented and is reliable, it could be used to auto-create payment entries.

[barredterra]

@casesolved-co-uk

This is not true.

Well, it is true. I basically said the same thing you described again, and the issue you linked to. Currently we need two steps (many clicks) and we want to reduce that to one step (ideally one click). 😄

[casesolved-co-uk]

Well, it is true. I basically said the same thing you described again, and the issue you linked to. Currently we need two steps (many clicks) and we want to reduce that to one step (ideally one click). 😄

No, it's not. You don't have to create a Payment Entry manually. Bank Reconciliation is a lot better than you implied.

[barredterra]

I never said you had to create a PE manually. I said you first have to create a PE (via bank reconciliation), then reconcile (with the voucher) in a second step.

[casesolved-co-uk]

I never said you had to create a PE manually. I said you first have to create a PE (via bank reconciliation), then reconcile (with the voucher) in a second step.

"create a Payment Entry, fill some data manually, go back to the Bank Reconciliation Tool, search the Payment Entry, reconcile with it."