Due to recent snaffoos with the latest Quicken and several of the financial institutions I do business with, I’ve decided to give GnuCash a run.
After the last few years, seeing how the interface still looks like pre-2000 – I finally said, either it’s dead, or it works.
I’ve noticed that at least one of my providers is switching to the OFX format. Hopefully it’s well-formed XML, but I’m not putting it past these people to Flug it all up from the start with missing closing tags, etc.
OFX is not strictly based on XML, but more closely relates to SGML, which would not require the closing tags. Even still, OFX is not as strict a standard as its relationship to SGML would imply. You are allowed to include non-standard tags within an OFX file. In XML, you would just define this in your DTD.
I’ve suffered with a bank that provides OFX files that just barely pass as acceptable by MS Money, and fail to adhere to even the loose standards established by the OFX definition. I imagine this is not a unique situation. My particualr issue with my bank involves not assigning transactions unique transaction IDs (FITID tags). Since my bank uses a third party to present its online account access, my guess is that the underlying mainframe does not maintain transaction IDs that comply with the OFX standard, so the third party provides its own standard for this field.
Given this example, you can see that not all OFX files are the same. I can’t imagine most banks are going to see this issue as important enough to scrap their mainframe systems. Their existing investment in a reliable system is far more important than keeping up with the latest communication standards. While it may hurt you and me, most people just want to best rates/services for the money that they park there.
Interesting, my understanding was that OFX would be implemented with well-formed XML. If that is not the case, then we’re back to square one.
Do you have a link I can read about this specific issue?
We are a South African based company that do lots of micro transactions and import OFX statements on a daily basis. We also found that the banks do not comply with the spec as far as FITID is concerned.
The problem has far reaching effect for an accounting system. We found that FNB SA will do a reversal of a transaction and give it the same FITID as the original, meaning we never import the reversal and the client ends up with an incorrect balance. Standard bank of SA is even worse. They give you a different range of FITID for every download. We tried to use the Transaction reference as a unique ID but that also changes between statements.
I don’t think the banks should offer OFX downloads if they can’t handle it. We developed our system trusting, that if the download type states OFX, it will comply and now we still have the overhead of manually checking every entry on the bank statement against the OFX import.
I’m considering a transition to GnuCash from MS Money as well and as part of the effort, been working on a few mini-projects to retrieve data from all the financial institutions I have accounts at.
One thing that has intrigued me is using Yodlee to aggregate the accounts in one place and then download transactions in csv format. There’s a python-based UI out there that’s supposed to convert that to OFX for import into GnuCash, but it didn’t work for me so I rolled my own.
Not satisfied with that, I’ve also been playing with using OFX libraries to pull data from the financial sites themselves, but for about half (at least) of my accounts, the OFX information available either isn’t accurate, requires signing-up and paying a monthly fee, etc. So now I’ve been on a steep learning curve scripting out some web automation to download the files.
Anyway, should have said this first, but as part of the Yodlee csv to OFX scripting, I’m generating unique FITID values as the csv file doesn’t contain any. I’m not sure that there’s anything about them that’s significant as far as importing into GnuCash, so maybe you could do something similar to generate/replace the missing/duplicate FITIDs you’re getting.