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

[Chris Good]

> -----Original Message-----
> From: David Reiser [mailto:dbreiser at icloud.com]
> Sent: Sunday, 10 July 2016 5:44 AM
> To: Chris Good <chris.good at ozemail.com.au>
> Cc: gnucash-user at gnucash.org
> Subject: Re: Anyone know if GnuCash can import ofx bank transactions from
> Australian ING Direct bank?
> 
> > On Jul 8, 2016, at 5:46 AM, Chris Good <chris.good at ozemail.com.au>
> wrote:
> > 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
> 
> 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
> dbreiser at icloud.com
> 

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
-------------- next part --------------
A non-text attachment was scrubbed...
Name: smime.p7s
Type: application/pkcs7-signature
Size: 4817 bytes
Desc: not available
URL: <http://lists.gnucash.org/pipermail/gnucash-user/attachments/20160710/049e04f2/attachment.p7s>