Anyone know if GnuCash can import ofx bank transactions from Australian ING Direct bank?

Discussion:

Chris Good

2016-07-08 09:46:03 UTC

Hi,

GnuCash 2.6.12 Windows 10 / Ubuntu 16.04

I tried importing a .ofx file from Australian ING Direct bank and it accepts
the file but there are no transactions listed in the window, so it seems
there is something wrong with the data.

Does any know if GnuCash can or cannot import ofx transactions from this
bank?

I'd also like to know if I can use aqbanking to download transactions
directly. I cannot find anything on the bank's website about direct connect
but I haven't asked them yet.

Thankfully, this bank allows you to download transactions in csv, ofx or qif
format.

I was able to import .qif but I believe ofx is preferred as

(to quote
https://lists.gnucash.org/pipermail/gnucash-user/2009-September/031420.html)
:

OFX transactions contain a unique transaction ID, so that if

you import transactions every week, and your bank insists on sending

you the most recent 30 days of data regardless of what you request,

then the duplicate recognition is automatic and you never see

transactions matching unique IDs you have already imported.

I was pretty impressed with the qif import process. Good helpful messages
all along the way.

It did take me about 10 transactions before I realised you can highlight
multiple transactions and assign the non-bank account to them in 1 go.

Alternatively, can some-one please provide a de-personalised example .ofx
file that does import so I can figure out what is different about my file?

Here is a de-personalised extract from my file:

OFXHEADER:100

DATA:OFXSGML

VERSION:102

SECURITY:NONE

ENCODING:USASCII

CHARSET:1252

COMPRESSION:NONE

OLDFILEUID:NONE

NEWFILEUID:NONE

<OFX>

<SIGNONMSGSRSV1>

<SONRS>

<STATUS>

<CODE>0

<SEVERITY>INFO

</STATUS>

<DTSERVER>20160708104407

<LANGUAGE>ENG

</SONRS>

</SIGNONMSGSRSV1>

<BANKMSGSRSV1>

<STMTTRNRS>

<TRNUID>1

<STATUS>

<CODE>0

<SEVERITY>INFO

</STATUS>

<STMTRS>

<CURDEF>AUD

<BANKTRANLIST>

<STMTTRN>

<TRNTYPE>CREDIT

<DTPOSTED>20160630000000

<TRNAMT>123.45

<FITID>903889

<MEMO>Bonus Interest Credit - Receipt 903889

</STMTTRN>

<STMTTRN>

<TRNTYPE>CREDIT

<DTPOSTED>20160630000000

<TRNAMT>543.21

<FITID>903889

<MEMO>Interest Credit

</STMTTRN>

.. more STMTTRN's

</BANKTRANLIST>

</STMTRS>

</STMTTRNRS>

</BANKMSGSRSV1>

</OFX>

Regards,

Chris Good

David Reiser

2016-07-09 19:44:24 UTC

Permalink

Post by Chris Good
Hi,
GnuCash 2.6.12 Windows 10 / Ubuntu 16.04
I tried importing a .ofx file from Australian ING Direct bank and it accepts
the file but there are no transactions listed in the window, so it seems
there is something wrong with the data.
Does any know if GnuCash can or cannot import ofx transactions from this
bank?
I'd also like to know if I can use aqbanking to download transactions
directly. I cannot find anything on the bank's website about direct connect
but I haven't asked them yet.
Thankfully, this bank allows you to download transactions in csv, ofx or qif
format.
I was able to import .qif but I believe ofx is preferred as
(to quote
https://lists.gnucash.org/pipermail/gnucash-user/2009-September/031420.html)
OFX transactions contain a unique transaction ID, so that if
you import transactions every week, and your bank insists on sending
you the most recent 30 days of data regardless of what you request,
then the duplicate recognition is automatic and you never see
transactions matching unique IDs you have already imported.
I was pretty impressed with the qif import process. Good helpful messages
all along the way.
It did take me about 10 transactions before I realised you can highlight
multiple transactions and assign the non-bank account to them in 1 go.
Alternatively, can some-one please provide a de-personalised example .ofx
file that does import so I can figure out what is different about my file?
OFXHEADER:100
DATA:OFXSGML
VERSION:102
SECURITY:NONE
ENCODING:USASCII
CHARSET:1252
COMPRESSION:NONE
OLDFILEUID:NONE
NEWFILEUID:NONE
<OFX>
<SIGNONMSGSRSV1>
<SONRS>
<STATUS>
<CODE>0
<SEVERITY>INFO
</STATUS>
<DTSERVER>20160708104407
<LANGUAGE>ENG
</SONRS>
</SIGNONMSGSRSV1>
<BANKMSGSRSV1>
<STMTTRNRS>
<TRNUID>1
<STATUS>
<CODE>0
<SEVERITY>INFO
</STATUS>
<STMTRS>
<CURDEF>AUD
<BANKTRANLIST>
<STMTTRN>
<TRNTYPE>CREDIT
<DTPOSTED>20160630000000
<TRNAMT>123.45
<FITID>903889
<MEMO>Bonus Interest Credit - Receipt 903889
</STMTTRN>
<STMTTRN>
<TRNTYPE>CREDIT
<DTPOSTED>20160630000000
<TRNAMT>543.21
<FITID>903889
<MEMO>Interest Credit
</STMTTRN>
.. more STMTTRN's
</BANKTRANLIST>
</STMTRS>
</STMTTRNRS>
</BANKMSGSRSV1>
</OFX>
Regards,
Chris Good

In your example transactions, the <FITID> values are identical. If those numbers are the ones provided by the bank, then no, you won’t be able to import them without modifying the files prior to import. FITIDs don’t need to be anything special, they just need to be guaranteed unique forever per account. The biggest issue you face is that anything you do to fix the FITID values yourself is likely to negate the primary advantage of ofx vs qif — near certainty of identifying transactions you have already downloaded once (duplicate transaction rejection).

If qif works for you, I’d recommend staying with that. Gnucash does provide some help identifying potential duplicates, but confirming the duplicates requires manual action on your part during the import.

If those FITIDs are the bank’s numbers, then they are violating the ofx spec. Perhaps you can embarrass them into fixing their code. OTOH, banks don’t seem to find incompetence to be embarrassing these days.

Dave
--
Dave Reiser
***@icloud.com

_______________________________________________
gnucash-user mailing list
gnucash-***@gnucash.org
https://lists.gnucash.org/mailman/listinfo/gnucash-user
-----
Please remember to CC this list on all your replies.
You ca

Chris Good

2016-07-09 22:40:17 UTC

Permalink

-----Original Message-----
Sent: Sunday, 10 July 2016 5:44 AM
Subject: Re: Anyone know if GnuCash can import ofx bank transactions from
Australian ING Direct bank?

1 go.

Post by Chris Good
Alternatively, can some-one please provide a de-personalised example
.ofx file that does import so I can figure out what is different about my file?
OFXHEADER:100
DATA:OFXSGML
VERSION:102
SECURITY:NONE
ENCODING:USASCII
CHARSET:1252
COMPRESSION:NONE
OLDFILEUID:NONE
NEWFILEUID:NONE
<OFX>
<SIGNONMSGSRSV1>
<SONRS>
<STATUS>
<CODE>0
<SEVERITY>INFO
</STATUS>
<DTSERVER>20160708104407
<LANGUAGE>ENG
</SONRS>
</SIGNONMSGSRSV1>
<BANKMSGSRSV1>
<STMTTRNRS>
<TRNUID>1
<STATUS>
<CODE>0
<SEVERITY>INFO
</STATUS>
<STMTRS>
<CURDEF>AUD
<BANKTRANLIST>
<STMTTRN>
<TRNTYPE>CREDIT
<DTPOSTED>20160630000000
<TRNAMT>123.45
<FITID>903889
<MEMO>Bonus Interest Credit - Receipt 903889
</STMTTRN>
<STMTTRN>
<TRNTYPE>CREDIT
<DTPOSTED>20160630000000
<TRNAMT>543.21
<FITID>903889
<MEMO>Interest Credit
</STMTTRN>
.. more STMTTRN's
</BANKTRANLIST>
</STMTRS>
</STMTTRNRS>
</BANKMSGSRSV1>
</OFX>
Regards,
Chris Good

In your example transactions, the <FITID> values are identical. If those
numbers are the ones provided by the bank, then no, you wonât be able to
import them without modifying the files prior to import. FITIDs donât need to
be anything special, they just need to be guaranteed unique forever per
account. The biggest issue you face is that anything you do to fix the FITID
values yourself is likely to negate the primary advantage of ofx vs qif â near
certainty of identifying transactions you have already downloaded once
(duplicate transaction rejection).
If qif works for you, Iâd recommend staying with that. Gnucash does provide
some help identifying potential duplicates, but confirming the duplicates
requires manual action on your part during the import.
If those FITIDs are the bankâs numbers, then they are violating the ofx spec.
Perhaps you can embarrass them into fixing their code. OTOH, banks donât
seem to find incompetence to be embarrassing these days.
Dave
--
Dave Reiser

Hi Dave,

Thanks very much for your help.
Yes, it looks like all the interest transactions for the same date have duplicate FITID's.
I'll report this problem to the bank.
I see from the OFX spec [1] for my version OFX file (102) that FITIDS can be up to 255 chars and it is recommended to keep them <= 32 chars for performance reasons,
so I should be able to use a script to make them unique, say by using the transaction date, bank FITID, and transaction value.

I tried importing a test file which I edited to have only 2 transactions with unique FITID's (903889 + 903890) but the GnuCash 'List of downloaded transactions' window is still empty. I made sure there were no transactions for the same date already in GnuCash in case it was not showing them because it thought they were already in GnuCash.

BTW the only de-personalising editing I did to the transactions I originally supplied, was to change the transaction values.

I'll have a look and see if there is some GnuCash option for getting log messages that may explain what's wrong with my data.
It would be very helpful if some-one could supply a sample .ofx file that works with GnuCash.

[1] http://www.ofx.org/downloads.html

BTW (2) Does anyone know if it is possible to set MS Outlook so that every line in an email I send to the GnuCash mailing lists, isn't followed by a blank line in the replies?

Regards, Chris Good

Chris Good

2016-07-10 07:41:44 UTC

Permalink

-----Original Message-----
Sent: Sunday, 10 July 2016 9:25 AM
Subject: Re: Anyone know if GnuCash can import ofx bank transactions from
Australian ING Direct bank?

Post by Chris Good
I'll have a look and see if there is some GnuCash option for getting
log messages that may explain what's wrong with my data. It would be
very helpful if some-one could supply a sample .ofx file that works
with GnuCash.
[1] http://www.ofx.org/downloads.html
BTW (2) Does anyone know if it is possible to set MS Outlook so that
every line in an email I send to the GnuCash mailing lists, isn't
followed by a blank line in the replies?
Regards, Chris Good

sample, unedited, 4 transactions only, tested to see that it works
Liz

Derek Atkins

2016-07-14 14:08:16 UTC

Permalink

Chris,

"Chris Good" <***@ozemail.com.au> writes:

[snip]

Post by Chris Good
Without the bank account identifying info, GnuCash doesn't find any
transactions to import.
After I manually added them to my file, the .ofx transactions imported fine.
I'll write a script to add the missing BANKACCTFROM info and also ensure the
FITID is unique for every transaction within an account.

Note that the FITID has to be *globally* unique within the account.
GnuCash remembers/stores the FITID of imported transactions and uses it
to detect duplicates. So you need to make sure that you never re-use
the same FITID, ever, for a particular account.

Post by Chris Good
Please remember to CC this list on all your replies.
You can do this by using Reply-To-List or Reply-All.

-derek
--
Derek Atkins, SB '93 MIT EE, SM '95 MIT Media Laboratory
Member, MIT Student Information Processing Board (SIPB)
URL: http://web.mit.edu/warlord/ PP-ASEL-IA N1NWH
***@MIT.EDU PGP key available
_______________________________________________
gnucash-user mailing list
gnucash-***@gnucash.org
https://lists.gnucash.org/mailman/listinfo/gnucash-user
-----
Please remember to CC this list on all your replies.
You can do this by using Reply-To-List or Reply-All.

Christopher Lam

2016-07-20 13:42:11 UTC

Permalink

Haha I've reported exact same issue to ING bank last year. I'm not wasting
my time waiting for them to fix this! C

Post by Chris Good

-----Original Message-----
Sent: Sunday, 10 July 2016 9:25 AM
Subject: Re: Anyone know if GnuCash can import ofx bank transactions from
Australian ING Direct bank?

sample, unedited, 4 transactions only, tested to see that it works
Liz

Hi Liz,
Thanks very much for supplying the sample .ofx file. It enabled me to
quickly
identify the problem with my file which was that the xml entity BANKACCTFROM
<BANKACCTFROM>
<BANKID>1234
<ACCTID>12345678
<ACCTTYPE>SAVINGS
</BANKACCTFROM>
Without the bank account identifying info, GnuCash doesn't find any
transactions to import.
After I manually added them to my file, the .ofx transactions imported fine.
I'll write a script to add the missing BANKACCTFROM info and also ensure the
FITID is unique for every transaction within an account.
I have emailed these problems to ING Direct Australia bank and they seem
very
helpful, so hopefully they will be fixed.
I've no idea if ING Direct Australia uses the same software as other ING
Direct banks around the world.
Just to document, I noticed the file Liz supplied also had after
</BANKTRANLIST>
<LEDGERBAL>
<BALAMT>1234.56
<DTASOF>20160701
</LEDGERBAL>
but this doesn't seem to be needed for importing into GnuCash.
Thanks again Liz and David,
Regards, Chris Good
_______________________________________________
gnucash-user mailing list
https://lists.gnucash.org/mailman/listinfo/gnucash-user
-----
Please remember to CC this list on all your replies.
You can do this by using Reply-To-List or Reply-All.

_______________________________________________
gnucash-user mailing list
gnucash-***@gnucash.org
https://lists.gnucash.org/mailman/listinfo/gnucash-user
-----
Please remember to CC this list on all your replies.
You can do this by using Reply-To-List or Reply-All.

Chris Good

2016-07-26 07:47:29 UTC

Permalink

From: Christopher Lam [mailto:***@gmail.com]
Sent: Wednesday, 20 July 2016 11:42 PM
To: Chris Good <***@ozemail.com.au>; gnucash-***@gnucash.org
Subject: Re: Anyone know if GnuCash can import ofx bank transactions from Australian ING Direct bank?

Haha I've reported exact same issue to ING bank last year. I'm not wasting my time waiting for them to fix this! C

-----Original Message-----
Sent: Sunday, 10 July 2016 9:25 AM
Subject: Re: Anyone know if GnuCash can import ofx bank transactions from
Australian ING Direct bank?

sample, unedited, 4 transactions only, tested to see that it works
Liz

Hi Liz,

Thanks very much for supplying the sample .ofx file. It enabled me to
quickly
identify the problem with my file which was that the xml entity BANKACCTFROM

and its content elements was missing. I.e. before <BANKTRANLIST> should be:
<BANKACCTFROM>
<BANKID>1234
<ACCTID>12345678
<ACCTTYPE>SAVINGS
</BANKACCTFROM>

Without the bank account identifying info, GnuCash doesn't find any
transactions to import.
After I manually added them to my file, the .ofx transactions imported fine.
I'll write a script to add the missing BANKACCTFROM info and also ensure the

FITID is unique for every transaction within an account.

I have emailed these problems to ING Direct Australia bank and they seem
very
helpful, so hopefully they will be fixed.
I've no idea if ING Direct Australia uses the same software as other ING
Direct banks around the world.

Just to document, I noticed the file Liz supplied also had after
</BANKTRANLIST>
<LEDGERBAL>
<BALAMT>1234.56
<DTASOF>20160701
</LEDGERBAL>
but this doesn't seem to be needed for importing into GnuCash.

Thanks again Liz and David,

Regards, Chris Good

Hi Christopher,

Thanks for letting me know you have already reported this to ING.

I havenât given up on them fixing this yet, but Iâm not waiting for them either. I know large companies can be slow to implement changes.

You may be interested in an app I have written to fix the problems with the ING Australian OFX file.

As well as fixing the 2 problems noted above, it enables you to limit the transactions output by date range to make it easier to avoid duplicates.

This works with Linux and Windows.

It may also work with Mac OS/X but I cannot test this.

Instructions for Linux:

https://github.com/goodvibes2/IngAusOfxFixLinux/blob/master/README.md

Instructions for Windows:

https://github.com/goodvibes2/IngAusOfxFixWin/blob/master/README.md

I have added it to the GnuCash Published Tools Wiki:

http://wiki.gnucash.org/wiki/Published_tools

(I know I could have done this much faster in awk or many other languages instead of java with a gui, but hey, I enjoy java!)

Regards,

Chris Good