Bank Reconciliation upload

sofia · September 17, 2015, 4:54pm

Hello,

Doing the bank reconciliation would be faster if the user could upload the bank statement and tell ERPNext to compare the bank statement to the current movements in Next.

Can this be done? What would it require?

thanks,
Sofia

rmehta · September 18, 2015, 6:58am

@sofia Yeah this has been on our list for a while, but no one has asked in a while. I am hoping someone from the community can contribute this

sofia · September 18, 2015, 4:52pm

@rmehta,

Ideally the user would be able to upload their bank statement but a temporary manual improvement could be the following:

Rather than having to click every entry and apply the date, it would be nice to be able to select multiple entries and apply the current date to all of them at the same time.

Any chance I could accomplish this with a custom script and custom fields?

thanks!

sofia · September 18, 2015, 4:53pm

In the mean time, I’m going to see if someone here can contribute this.

rmehta · September 21, 2015, 5:30am

@sofia Thanks! We would love to take this up, but we need to fix other things too.

@everyone We are happy if 3rd part developers want to take up paid development. We will be happy to review / accept pull-requests.

kolate_sambhaji · October 13, 2015, 5:03am

@sofia

I am customizing Bank Reconciliation Tool.
In this tool:

JV Records can be fetched using clearance date to view only cleared records from certain date.
Set clearance date to to_date in all selected JV
Calculate Total Reconciled Credit and Total Reconciled Debit

Any suggestion will be helpful to improve tool.

sofia · October 13, 2015, 4:21pm

@kolate_sambhaji, this is great! Thanks!

Do you have a profile up on Guru o Upwork? I think I saw it. Do you do freelance work?

kolate_sambhaji · October 14, 2015, 5:23am

@sofia
We have done some more improvement.
Now when you select bank account in Bank Reconciliation Tool, You will get system balance of Selected Bank Account.

You can fetch record based on clearance date.

Also you will get, Amounts not reflected in bank = summation of all entries whose clearance date is not present
Expected balance as per bank= Amounts not reflected in bank - System Balance

Please advice if there is any missing feature in Bank Reconciliation Tool. I want to contribute this into core product version 6

I am doing freelancing work and registered service provider of ERPNext
https://community.erpnext.com/service-providers/sambhaji-kolate

You can post your job on https://community.erpnext.com/jobs
Its good to use ERPNext community portal than other site for ERPNext Jobs.

Thanks,
Sambhaji Kolate
kolate.sambhaji@gmail.com

sofia · October 14, 2015, 4:17pm

@kolate_sambhaji, okay great. I will look it over. Thanks!

sofia · October 19, 2015, 5:11pm

@kolate_sambhaji, if I were to click on “Set Clearance Date” what would happen? Does that button allow me to set the same clearance date on all of the transactions in the table with 1 click?

This is a very helpful improvement, thanks. Is it possible, in this version or the next, for the user to upload a pdf of their bank statement and have ERPNext match the transactions in the bank statement to the transactions registered in ERPNext?

rmehta · October 20, 2015, 5:12am

You mean CSV?

kolate_sambhaji · October 20, 2015, 5:52am

@sofia “Set Clearance Date” will set the clearance date in all transaction with one click.
Currently we are copying TO Date into set_clearance date(usually its end of Month)

For uploading csv, we write logic for checking csv bank balance with ERP outstanding balance.
Its in our TODO list