Erpnext: Bank reconciliation enhancements

[PawanMeh]

1) Add "Incoming" and "outgoing" accounts for bank reconciliation
2) Add functionality for automatic reconciliation with bank statements via CSV upload template

[PawanMeh]

• [ ] Import Bank Statement
  
  
  
  • User chooses 'Bank Account' & 'Statement Date' to be imported
  
  
  • Bank statement CSV template to be provided for upload.On click of 'Upload',user can choose
    
    CSV file to be uploaded.'Statement' date on screen should match with 'Statement date' in file.
  
  
  • Once uploaded the data shows up in the grid
    
    
  
  
• [ ] Change logic behind existing ’Get payment Entries’ button on 'Bank Reconciliation' page
  
  
  
  • If bank statement exists, data set for matching of transactions should be loaded based on
    
    criteria of statement 'value date' between 'From date' and 'To Date' entered on the page
  
  
  • Matching for 'clearance date' defaulting should happen based on payment ref,
    
    amount,bank account & clearance date should be defaulted accordingly
  
  
• [ ] Show amount of "unmatched transactions" and items on a page and ability to create JV for them from the summary page

[spa49]

hi all,
how will the different dataformats of different banks be handled?
I recon different banks will provide different .csv files
Also, will the Bankstatements automatically be associated to the invoices?

[jwrober]

I recon different banks will provide different .csv files

I concur. There needs to be a setup area where we define the csv structure that the bank gives us and not force the admin to use what erpnext wants the format/template to be. Things the setup form would need to include:

• number of lines from top of file where data begins (so we can ignore any header records)
• The value of each column in the csv to map to the erpnext field (table)

I would also recommend that you provide option of having more that one setup record per company incase more than one bank is used.

[jmatsushita]

Hi there,

We're really looking forward to this feature and glad to see it's shortlisted for v7.3 ! Are there other issues to track progress (and maybe do some early testing?)

Cheers,

Jun

[rmehta]

@jmatsushita this is not yet started and no ETA on this yet.

If you want this urgently, maybe get someone to build it for you or start a bounty.

[rodrigoscoelho]

It already exists a international standard on file format, the ofx files.

Better than csv. What do you think?

[rodrigoscoelho]

Mostly of banks supports ofx, and it doesn't need to be mapped, since it is
standard for all banks.

The only thing that is holding me to migrate definitely to erpnext is the
missing feature of bank reconciliation (import ofx).

I have a python code written for odoo that does all the import process.
Does it help on implementation for erpnext?

I heard about the possibility to finance that implementation, but, how
much? What would be the cost (an idea) to implement it?

Thanks

Em 16 de jul de 2017 16:29, "rodrigoscoelho" [e-mail]
escreveu:

It already exists a international standard on file format, the ofx files.

Better than csv. What do you think?

—
You are receiving this because you are subscribed to this thread.
Reply to this email directly, view it on GitHub
https://github.com/frappe/erpnext/issues/5903#issuecomment-315631287,
or mute the thread
https://github.com/notifications/unsubscribe-auth/AGl5mu7UETocMLU4RcBEWgnl3o0RzuIzks5sOmSIgaJpZM4JXDyL
.

[hanselke]

took a quick look at OFX and it seems like a better standard to follow, then have localization teams build CSV > OFX converters. found this which converts SG bank csv to OFX

https://basilsalad.com/how-to/how-to-import-data-from-singaporean-banks/

So the issue with singapore bank CSVs is that they put vendor name in the 'description' fields, and im sure there are many different formats and methods different banks use to generate their CSV.

@rodrigoscoelho could you share your code? If it calls the create journal entries in odoo, the part where the OFX format is converted to objects would be useful in creating the ERPNext module.

[rodrigoscoelho]

Here the OFXparser library for python

https://pypi.python.org/pypi/ofxparse/0.14

and attached follows the OFX importer for ODOO 10.0, using this library.
ofx.zip

Who has knowledge in programming to adapt this ??

On Mon, Jul 17, 2017 at 12:15 AM, hansel [e-mail] wrote:

took a quick look at OFX and it seems like a better standard to follow,
then have localization teams build CSV > OFX converters. found this which
converts SG bank csv to OFX

https://basilsalad.com/how-to/how-to-import-data-from-singaporean-banks/

So the issue with singapore bank CSVs is that they put vendor name in the
'description' fields, and im sure there are many different formats and
methods different banks use to generate their CSV.

@rodrigoscoelho https://github.com/rodrigoscoelho could you share your
code? If it calls the create journal entries in odoo, the part where the
OFX format is converted to objects would be useful in creating the ERPNext
module.

—
You are receiving this because you were mentioned.
Reply to this email directly, view it on GitHub
https://github.com/frappe/erpnext/issues/5903#issuecomment-315660858,
or mute the thread
https://github.com/notifications/unsubscribe-auth/AGl5mqGaHL8bSpGTRdYGtknxUXQM-Kl8ks5sOtG1gaJpZM4JXDyL
.

[jwrober]

Just looked and my US based bank supports this OFX format as well. They call it Microsoft OFX. I assume that is the same thing. This is looking really good!

[rodrigoscoelho]

It is an international standard.

https://en.wikipedia.org/wiki/Open_Financial_Exchange

On Mon, Jul 17, 2017 at 5:14 PM, James Robertson [e-mail]
wrote:

Just looked and my US based bank supports this OFX format as well. They
call it Microsoft OFX. I assume that is the same thing. This is looking
really good!

—
You are receiving this because you were mentioned.
Reply to this email directly, view it on GitHub
https://github.com/frappe/erpnext/issues/5903#issuecomment-315869479,
or mute the thread
https://github.com/notifications/unsubscribe-auth/AGl5mnevHGdO1jCD15lHBP36lbq6Aq5iks5sO8C7gaJpZM4JXDyL
.

[hanselke]

@rodrigoscoelho you mentioned an attachment? think you forgot to attach it

[rodrigoscoelho]

ofx.zip
I'm sorry, here it goes

[felixvarghese]

@rodrigoscoelho send an email to [email protected] to get a better idea of what it would cost to have this implemented through their paid feature development program. https://erpnext.com/pricing/paid-feature-development

[felixvarghese]

This issue and #2184 are basically the same thing. Would it be better to close 2184 and consolidate all discussion to this issue?

[Tropicalrambler]

I don't know if this is the master discussion for bank reconciliation. I was studying the forums posts recently, and it is evident that an "upload bank statement" feature is essential. Most here mention how the .OFX standard is widely used. I agree with this for certain countries. However, where we do business, banks go only so far as to give you a .CSV. (Please see this link in Wikipedia, where it is clear that OFX is still not supported much.) Given this lack of true standardization, I believe that the logic behind the Bank Reconciliation tool should be like this:

1. User configures a specific upload format for each bank account. In this configuration, user specifies whether it is going to be an .OFX, or .CSV file (perhaps even think about XML or JSON in the future. I am not familiar if these exist, but I am sure they eventually will).

• The configuration involves the user mapping the files column or header to ERPNext Reconciliation columns (as of v9):
  payment_document | payment_entry | against_account | amount | posting_date | cheque_number | cheque_date | clearance_date
  -- | -- | -- | -- | -- | -- | -- | --
  Only one active or valid "mapping" configuration for Bank Reconciliation exists.

1. The logic for processing the upload file should ideally attempt to "match" the uploaded transactions with the existing transactions, with the following logic, in order of priority:
   First, Reference or Cheque Number (from bank) should be matched to Cheque Number (from ERPNext)
   If no match, then try
   Transaction Amount (from bank) should be matched to Transaction Amount (from ERPNext)
   If no match, then try
   Clearance Date (from bank) should be matched to Posting Date (from ERPNext)
   If none of these match, then simply Add the transaction to the ERPNext register without much fuss, as a Submitted transaction. It is faster, in my opinion to Cancel and delete a transaction, than to add one from scratch. The option to do this could be given to the user: "Automatically Add transactions that do not exist in ERPNext register"

2.1 The logic for matching by reference number should ideally contain some loose validation, for example:
As the reference number on the ERPNext entry, I enter this: "Bank E-pay #429" for $1,000
The bank statement has this for reference: "0001287429 for $1,000.00
Thus, the software should be able to discern that the bank reference number with 429 at the end really refers to my "Bank E-pay #429". This should enhance the amount of transactions that match by Reference number
2.2 The logic for matching by amount should also contain some loose validation, for example:
As the amount on ERPNext entry I have a float of 1,000.000
The bank statement amount has an float with two decimal points: 1,000.00
or the bank statement just returns an integer, without formatting comma: 1000
or the bank statement returns a float with different formatting: 1.000,00 (even though our standard might be #,###.##)
ERPNext should be able to discern that this transaction is the same given the amount.

2.3 The logic for matching by date should also contain loose validation, for example:
As the posting date in ERPNext I have October 4, 2017
The bank statement has a clearance date of October 13, 2017
The logic of ERPNext should infer that by having an October 13 date, the probability of the bank transaction being the same than the ERPNext registered transaction should be much higher.

Now, If you combine all these three "logic" elements, validating each bank statement transaction with Reference Number, Amount and Date, each gives more weight for a higher probability of being a match, and thus this ends up "confirming" the transaction as a match ("All those transactions listed on the bank statement that have 50% or more matching probability with ERPnext transaction, must then be matching transactions. This will enhance the user experience, and simplify the work, and just add any unmatched transactions and have them in a changed color or formatting (red, orange, etc.), so the user can breeze through reconciliations.

[agritheory]

@Tropicalrambler This is an excellent flow. I was also looking into OFX. I think this is a good candidate for the new List Grid View that Faris has been working on.

[Tropicalrambler]

Other suggestions that my treasurer has shared with me to ease the Reconciliation:

• [ ] Show the ERPNext opening balance in the Bank Reconciliation screen
• [ ] Show the ERPNext closing balance in the Bank Reconciliation screen
• [ ] When a bank statement has been loaded into the Bank Reconciliation screen, allow the use of TAB to move from one transaction row to the next. Currently you need to press TAB twice to locate the focus or cursor on the next line.
• [ ] Along with the TAB once to move per row, enable the use of the spacebar, or ENTER key, on each row, so that when pressed it automatically sets clearance_date = ERPNext posting_date

This last suggestion arises from the fact that most of our posting dates are for bank wire transfers (most of our transactions are electronic, and posted on ERPNext the same day they are processed), thus, this reduces the time in entering the clearance date on the Bank Reconciliation screen.