QIF bank statement imports

Hi,

I'm looking into adding a bank statement import feature to erpnext.  I appreciate that functionality to import data / vouchers, however this is not the format I receive my banking data from the bank.

The bank supplies me with transaction descriptions, a date and a value.  Eg:

!Type:Bank
D08/05/2013
T-12.76
PBYTEMARK HOSTING YORK
^

What I would like to be able to do is to upload my transactions, and have these be marked as 'unreconciled'.  Once these are uploaded, I would like to match each of these to an appropriate expense account, or an invoice that I have issued.

In general, I do not run an explicit accounts payable internally as 99% of the expense transactions my business makes are 'point of sale' transactions paid by credit/debit card.

The process for importing this type of data would look like this:

- Import bank statement for a specific bank account - this creates a voucher/vouchers with journal entries which are marked as unreconciled - either by pointing to an 'unreconciled' account, or by being imbalanced.
- Go to a reconciliation screen where all transactions that are not reconciled are shown, with options to select an appropriate account or invoice(s) to debit/credit within the system for each transaction.
- There are also additional complexities such as dealing with part payments for invoices already logged in the system etc.

This is essentially what Xero does, which is what I have been using to date for accounting - it is a very fast way of getting my expense information up to date, and marking invoices as paid.  Xero also checks for duplicate imports, but that is another matter.

Seeing as I am looking for something that has the crm, hr and other features that ERPNext has, and I write python for a living, I was hoping to implement the above in ERPNext so I could switch to it. 

Before I begin, I thought I would ask here if there where any reasons why this would not make sense in the context of ERPNext and whether my approach is feasible?

Best regards, and thanks for producing such a quality piece of software!

Damian



Note:

 

If you are posting an issue,

  1. We should be able to replicate it at our end. So please give us as much information as you can. Please see it from the point of view of the person receiving the communication.
  2. Paste your code at http://pastebin.com or http://gist.github.com and send only the URL via email
  3. For sending images, use http://imgur.com or other similar services. Do not send images as attachments. Links are good. Same goes for any file you are going to send.

     

    End of Note



    You received this message because you are subscribed to the Google Groups “ERPNext Developer Forum” group.

    To unsubscribe from this group and stop receiving emails from it, send an email to erpnext-developer-forum+un...@googlegroups.com.

    For more options, visit https://groups.google.com/groups/opt_out.
Damian,

Thanks for the mail - I think it would make great sense to build such a feature

Replies inline below:

On Wednesday, October 9, 2013 1:23:52 PM UTC+5:30, Damian Dimmich wrote:
Hi,

I'm looking into adding a bank statement import feature to erpnext.  I appreciate that functionality to import data / vouchers, however this is not the format I receive my banking data from the bank.

The bank supplies me with transaction descriptions, a date and a value.  Eg:

!Type:Bank
D08/05/2013
T-12.76
PBYTEMARK HOSTING YORK
^

What I would like to be able to do is to upload my transactions, and have these be marked as 'unreconciled'.  Once these are uploaded, I would like to match each of these to an appropriate expense account, or an invoice that I have issued.

In ERPNext you could create them as Draft and Submit them once you match the account.
 

In general, I do not run an explicit accounts payable internally as 99% of the expense transactions my business makes are 'point of sale' transactions paid by credit/debit card.

The process for importing this type of data would look like this:

- Import bank statement for a specific bank account - this creates a voucher/vouchers with journal entries which are marked as unreconciled - either by pointing to an 'unreconciled' account, or by being imbalanced.

These could also be a separate series
 
- Go to a reconciliation screen where all transactions that are not reconciled are shown, with options to select an appropriate account or invoice(s) to debit/credit within the system for each transaction.
- There are also additional complexities such as dealing with part payments for invoices already logged in the system etc.

This is essentially what Xero does, which is what I have been using to date for accounting - it is a very fast way of getting my expense information up to date, and marking invoices as paid.  Xero also checks for duplicate imports, but that is another matter.

Seeing as I am looking for something that has the crm, hr and other features that ERPNext has, and I write python for a living, I was hoping to implement the above in ERPNext so I could switch to it. 

Awesome! We would be happy to help you build this. What will be motivating for us is if you regularly push your code on GH - this we can help you much faster.

Our platform is not very standard (but we are getting there quickly) so there might be a slight learning curve.
 

Before I begin, I thought I would ask here if there where any reasons why this would not make sense in the context of ERPNext and whether my approach is feasible?

What you need to do is create a new Page / DocType called "Bank Import" - similar to Stock Reconciliation

You attach your statement and write the code to create the Journal Vouchers in the controller of this DocType

1. Parse the statement
2. Build Journal Vouchers for each account.
3. You could query un-matched Invoices with matching amounts to automatically match (!)

You can use webnotes.bean([{"doctype":"Journal Voucher", "company":''....}, {"doctype":"Journal Voucher Detail", "account":"My Bank", "debit": 400 .... }, .. ]).insert()

to create a new voucher. Its not quite hard.
 

Best regards, and thanks for producing such a quality piece of software!

Thanks!
 

Damian



Note:

 

If you are posting an issue,

  1. We should be able to replicate it at our end. So please give us as much information as you can. Please see it from the point of view of the person receiving the communication.
  2. Paste your code at http://pastebin.com or http://gist.github.com and send only the URL via email
  3. For sending images, use http://imgur.com or other similar services. Do not send images as attachments. Links are good. Same goes for any file you are going to send.

     

    End of Note



    You received this message because you are subscribed to the Google Groups “ERPNext Developer Forum” group.

    To unsubscribe from this group and stop receiving emails from it, send an email to erpnext-developer-forum+un...@googlegroups.com.

    For more options, visit https://groups.google.com/groups/opt_out.
Thank you for the detailed answer and pointers!

Much appreciated - will have a hack at it and keep you posted on progress.

Best,
Damian

On Wednesday, October 9, 2013 5:07:15 PM UTC+4, rushabh wrote:
Damian,

Thanks for the mail - I think it would make great sense to build such a feature

Replies inline below:

On Wednesday, October 9, 2013 1:23:52 PM UTC+5:30, Damian Dimmich wrote:
Hi,

I'm looking into adding a bank statement import feature to erpnext.  I appreciate that functionality to import data / vouchers, however this is not the format I receive my banking data from the bank.

The bank supplies me with transaction descriptions, a date and a value.  Eg:

!Type:Bank
D08/05/2013
T-12.76
PBYTEMARK HOSTING YORK
^

What I would like to be able to do is to upload my transactions, and have these be marked as 'unreconciled'.  Once these are uploaded, I would like to match each of these to an appropriate expense account, or an invoice that I have issued.

In ERPNext you could create them as Draft and Submit them once you match the account.
 

In general, I do not run an explicit accounts payable internally as 99% of the expense transactions my business makes are 'point of sale' transactions paid by credit/debit card.

The process for importing this type of data would look like this:

- Import bank statement for a specific bank account - this creates a voucher/vouchers with journal entries which are marked as unreconciled - either by pointing to an 'unreconciled' account, or by being imbalanced.

These could also be a separate series
 
- Go to a reconciliation screen where all transactions that are not reconciled are shown, with options to select an appropriate account or invoice(s) to debit/credit within the system for each transaction.
- There are also additional complexities such as dealing with part payments for invoices already logged in the system etc.

This is essentially what Xero does, which is what I have been using to date for accounting - it is a very fast way of getting my expense information up to date, and marking invoices as paid.  Xero also checks for duplicate imports, but that is another matter.

Seeing as I am looking for something that has the crm, hr and other features that ERPNext has, and I write python for a living, I was hoping to implement the above in ERPNext so I could switch to it. 

Awesome! We would be happy to help you build this. What will be motivating for us is if you regularly push your code on GH - this we can help you much faster.

Our platform is not very standard (but we are getting there quickly) so there might be a slight learning curve.
 

Before I begin, I thought I would ask here if there where any reasons why this would not make sense in the context of ERPNext and whether my approach is feasible?

What you need to do is create a new Page / DocType called "Bank Import" - similar to Stock Reconciliation

You attach your statement and write the code to create the Journal Vouchers in the controller of this DocType

1. Parse the statement
2. Build Journal Vouchers for each account.
3. You could query un-matched Invoices with matching amounts to automatically match (!)

You can use webnotes.bean([{"doctype":"Journal Voucher", "company":''....}, {"doctype":"Journal Voucher Detail", "account":"My Bank", "debit": 400 .... }, .. ]).insert()

to create a new voucher. Its not quite hard.
 

Best regards, and thanks for producing such a quality piece of software!

Thanks!
 

Damian



Note:

 

If you are posting an issue,

  1. We should be able to replicate it at our end. So please give us as much information as you can. Please see it from the point of view of the person receiving the communication.
  2. Paste your code at http://pastebin.com or http://gist.github.com and send only the URL via email
  3. For sending images, use http://imgur.com or other similar services. Do not send images as attachments. Links are good. Same goes for any file you are going to send.

     

    End of Note



    You received this message because you are subscribed to the Google Groups “ERPNext Developer Forum” group.

    To unsubscribe from this group and stop receiving emails from it, send an email to erpnext-developer-forum+un...@googlegroups.com.

    For more options, visit https://groups.google.com/groups/opt_out.

Hi Guys…

Can anybody tell me if this ever got off the ground?

Thanks

Paul

1 Like