Import of bank transaction/statement data, QIF ?

[ofbiz.user]

Dear List,

anyone done that before?

How do you do it?

Regards,

[Jacques Le Roux]

Since she did not in her answer, I suggest you read Sharan's excellent book on accounting in OFBiz
See "Getting Started with Apache OFBiz Accounting" at https://cwiki.apache.org/confluence/display/OFBADMIN/OFBiz+Related+Books

HTH

Jacques

Le 01/07/2015 22:38, [hidden email] a écrit :

> Dear List,
>
> anyone done that before?
>
> How do you do it?
>
> Regards,
>
>
>

[Sharan-F]

Thanks for the recommendation Jacques. The book doesn't cover importing bank statements.(although there is a  small appendix about importing data via the webtools menu)

I think that if you want to look at importing a bank statement then an xml import is the way to go (so if you can convert it to xml you can import it). Effectively all you are doing is importing transactions.

The challenge you may have is going to be the format and linking with the GL and other areas. I'd suggest you look at importing it as a financial account bank account to start with. That way you can have a play around with the different statuses and reconciliations available and  posting the transactions to the GL account you want.

It's been a while since I looked at it but I remember some functionality around reconcilation that matches the closing balance of one reconciliation against the following one, which would help as an ongoing balance check for ongoing imports.

It's a bit hard to say much more without more information.

Anyway, hope this helps.

Thanks
Sharan

[taher]

Hello,

To continue with what Jacques and Sharan stated, OFBiz does not support QIF, OFX or QFX out of the box. With that being said, you only need to understand a few entities (tables) to map your source data to. The main entities coming to mind are AcctgTrans, AcctgTransEntry and GlAccount.

Anyway, don't start from scratch as someone almost always did it for you. From research I found this Java library for importing / exporting QIF -> https://code.google.com/p/jqif/. Are you by the way bringing this data from GnuCash?

Cheers,

Taher Alkhateeb

----- Original Message -----

From: "Sharan-F" <[hidden email]>
To: [hidden email]
Sent: Friday, 3 July, 2015 2:17:52 PM
Subject: Re: Import of bank transaction/statement data, QIF ?

Thanks for the recommendation Jacques. The book doesn't cover importing bank
statements.(although there is a small appendix about importing data via the
webtools menu)

I think that if you want to look at importing a bank statement then an xml
import is the way to go (so if you can convert it to xml you can import it).
Effectively all you are doing is importing transactions.

The challenge you may have is going to be the format and linking with the GL
and other areas. I'd suggest you look at importing it as a financial account
bank account to start with. That way you can have a play around with the
different statuses and reconciliations available and posting the
transactions to the GL account you want.

It's been a while since I looked at it but I remember some functionality
around reconcilation that matches the closing balance of one reconciliation
against the following one, which would help as an ongoing balance check for
ongoing imports.

It's a bit hard to say much more without more information.

Anyway, hope this helps.

Thanks
Sharan



--
View this message in context: http://ofbiz.135035.n4.nabble.com/Import-of-bank-transaction-statement-data-QIF-tp4670715p4670740.html 
Sent from the OFBiz - User mailing list archive at Nabble.com.

[Hans Bakker]

I did wrote an import program like this before, i simply used the
payment entity to import to with the related payment method, also try to
find the related invoice and made the connection where possible. The
bank account number can be used to find the related parties.

I do not recommend to create accounting transactions directly, let them
be created from the payment.

Regards,
Hans

On 03/07/15 19:05, Taher Alkhateeb wrote:

> Hello,
>
> To continue with what Jacques and Sharan stated, OFBiz does not support QIF, OFX or QFX out of the box. With that being said, you only need to understand a few entities (tables) to map your source data to. The main entities coming to mind are AcctgTrans, AcctgTransEntry and GlAccount.
>
> Anyway, don't start from scratch as someone almost always did it for you. From research I found this Java library for importing / exporting QIF -> https://code.google.com/p/jqif/. Are you by the way bringing this data from GnuCash?
>
> Cheers,
>
> Taher Alkhateeb
>
> ----- Original Message -----
>
> From: "Sharan-F" <[hidden email]>
> To: [hidden email]
> Sent: Friday, 3 July, 2015 2:17:52 PM
> Subject: Re: Import of bank transaction/statement data, QIF ?
>
> Thanks for the recommendation Jacques. The book doesn't cover importing bank
> statements.(although there is a small appendix about importing data via the
> webtools menu)
>
> I think that if you want to look at importing a bank statement then an xml
> import is the way to go (so if you can convert it to xml you can import it).
> Effectively all you are doing is importing transactions.
>
> The challenge you may have is going to be the format and linking with the GL
> and other areas. I'd suggest you look at importing it as a financial account
> bank account to start with. That way you can have a play around with the
> different statuses and reconciliations available and posting the
> transactions to the GL account you want.
>
> It's been a while since I looked at it but I remember some functionality
> around reconcilation that matches the closing balance of one reconciliation
> against the following one, which would help as an ongoing balance check for
> ongoing imports.
>
> It's a bit hard to say much more without more information.
>
> Anyway, hope this helps.
>
> Thanks
> Sharan
>
>
>

[Jacques Le Roux]

Yes that's an important point Hans indeed

Jacques

Le 04/07/2015 10:25, Hans Bakker a écrit :
> I do not recommend to create accounting transactions directly, let them be created from the payment.