GNOME Bugzilla – Bug 726142
Cope with some banks recycling FITIDs in OFX files
Last modified: 2018-06-29 23:28:08 UTC
Created attachment 271565 [details] OFX file which failed to import $1144.60 I've noticed a transaction which fails to import via an OFX export from my ING Direct bank account. I haven't had this problem before. The figure is $1144.60 (see below), and this transaction is the same as a lot of other transactions from our credit card acquirer: <STMTTRN> <TRNTYPE>Deposit <DTPOSTED>20140225000000 <TRNAMT>1144.60 <FITID>103966 <MEMO>Deposit - Tyro Settlement 24 Feb Brockley - Receipt 103966 </STMTTRN> The OFX file that contains this one is attached. I can't understand why it won't accept this figure and tried many different things. I have manually searched to see if this figure is already present somewhere in the system and it is not. This makes me nervous that there are other figures failing to import, so I tried a different import via .csv and I cannot now get any of these to import at all (getting a column not understood error) - I'll log this under a different bug and reference this one. I hope someone can help me as I run my business using GNUCash. Thanks very much, Julian
Try making a copy of your gnucash file. Rename it with a .gz extension. Decompress the file. Open it in a text editor. In the file, can you find the string "103966" (your FITID)? If so, the transaction has already been imported, or your bank's id's are not unique. If not, I don't know what is happening, the file looks fine.
Yes, thank you, you are right - there is a duplicate FITID from the 26th of October, so the bank must be recycling their id's which is a disaster. I'll get onto them then. Is this common?
I've seen banks send all zeros in the FITID field, but never a bank sending varrying but not unique FITIDs. The only "solution" I would see is to give an option to give the user a big warning is the transaction matches on with a really far date (say 3 month +). But unless this case is very frequent, I wouldn't hold my breath on anyone actually coding such a workaround for banks doing such a stupid thing. I wonder if/how Quicken actually handles this. The only workaround I can suggest it to edit your xml file to remove the online identifier on the offending transaction. But that's beyond inconvenient...
Oh dear, it's happened again. This is a really big bank - ING Direct that is recycling its receipt numbers - it is 2 years later and they are only 6 digit numbers: <STMTTRN> <TRNTYPE>Deposit <DTPOSTED>20110927000000 <TRNAMT>2.00 <FITID>102349 <MEMO>Deposit - Tyro Settlement 25 Sep Brockley - Receipt 102349 </STMTTRN> <STMTTRN> <TRNTYPE>Deposit <DTPOSTED>20141110000000 <TRNAMT>910.00 <FITID>102349 <MEMO>Deposit - GLAMORGAN SPRING GSBC PAYMENT - Receipt 102349 </STMTTRN> I just don't see how I can avoid this or spot them, unless the import system could check the FITID against the TRNAMT to make sure they are different transactions?
Well then, the only thing we can do is check for dates very far apart (like a yeart) and consider the transaction new in that case.
Here is the response from ING Direct below - it makes me wonder if this is an issue for other people and they haven't noticed the odd transaction being dicarded as I have only come across two duplicates that I know of in 4 years of using GNUCash, but I wouldn't necessarily spot a smaller transaction. From: ING DIRECT Sent: 16/02/2015 (2:38 PM Sydney Time) Hi Julian, Thank you for your patience. I have looked into this further for you with our IT department. They have confirmed that due to the volume of transactions performed each day our receipt numbers are recycled through the system. As these are not filtered or skipped depending on the account it is possible you will receive the same receipt number at some point throughout the time you hold your account. I apologise for any inconvenience, I have lodged some feedback on your behalf as this may be something we wish to change in future. If you have any further queries please do not hesitate to contact us. Our Customer Care Specialists are available 24 hours, 7 days a week on 133 464 or +61 2 9028 4077 (if you are overseas). Kind Regards, Kate Any advice in this My Message does not take into account your objectives, financial situation or needs and you should consider whether it is appropriate for you.
Julian, As a workaround, my IngAusOfxFix utility may be of use to you as it appends the date to each FITID. See http://wiki.gnucash.org/wiki/Published_tools
Chris thanks, I have been using your excellent tool to solve my problem for quite a while. I was able to check 5 years of import data with it. Very useful.
GnuCash bug tracking has moved to a new Bugzilla host. The new URL for this bug is https://bugs.gnucash.org/show_bug.cgi?id=726142. Please continue processing the bug there and please update any external references or bookmarks.