Slashdot Mirror
Specifications of Intuit's .QFX Format?
mad.frog asks: "I recently upgraded my ancient version of Quicken to the latest (Macintosh) version, with the intention of being able to download my credit card transactions directly from my bank into Quicken, rather than entering all that stuff by hand. As it turns out, almost no banks support doing this for non-Windows platforms (not surprisingly, Intuit doesn't point this out on the package). But here's the weird part: the information downloaded is just an xml-like text file (.QFX). Anyone know how (or why) they would make such a generic file platform-specific -- what business advantage does Intuit (or my bank) have in restricting how I use this information? Also, does anyone happen to know details of this (apparently undocumented, Intuit-specific) format so that I can hack mine into submission and use this data anyway, even if it's not on my bank's Platform Of Choice?"
My bank's wouldn't import right. They weren't using windows linefeeds.... they didn't set the cleared flags, either. What use is that?
la lalala! Perl to the rescue.
I did an export, examined the code, and wrote a little script to fix the downloaded file into what I needed (replace linefeeds, add the cleared flag).
Here is my code; it might be good for starters. Not sure what the new version needs.
------------
print "I fix QIF files!\n\nEnter Filename: ";
chomp($filein=<STDIN>);
print "Working on $filein.\n";
open (FLN, "<$filein");
@raw=<FLN>;
close FLN;
open (FLO, ">Fixed_$filein");
foreach (@raw)
{
chomp $raw[$a];
print FLO "$raw[$a]\n";
$test=(substr $raw[$a], 0, 1);
if ($test eq "D")
{print FLO "C*\n"; }
$a++;
}
close FLO;
print "Finished!\nhit return and close window.";
<STDIN>;
-----
"You may all go to hell and I will go to Texas"
Sen. Davy Crocket to US Congress, Nov. 1, 1835
Intuit is a bastard of a company.
... fire up Quicken ... click Import:
I happen to use Quicken 2001 on a Mac.
When I switched banks at the beginning of the year, I asked if they supported Quicken.
They said, "Yes. You can download your transactions into Quicken."
I didn't dig any further since, my assumption was, once I get the file downloaded, Quicken should just import the data. Well, that's not quite the case.
I go online and download the a "Web Connect" (QFX) file for my account and it gets saved to my desktop. I flip over to Quicken and say "Import Web Connect". Quicken opens the file, proceed to connect to the internet and reports back "Quicken is currently unable to verify the financial institution information for this download. Please try again later."
Hmmm... that's strange. Why did it go online? Why didn't it just import the file?
After a few days of trying different things and talking to the bank, I decided to break down and call Intuit Tech Support ($1.95/minute). After being on hold for awhile and talking to a technician, I am told "Your bank doesn't support Macs". The rest of the conversation was along the lines:
Me: "What do you mean?"
Tech: "You can't download transactions into Quicken from your bank because they don't support Macs."
Me: "That doesn't make sense. My bank is not the issue. I have the QFX file - which is the same file that I would get if I were on a PC - Quicken is just refusing to import it."
Tech: "That's because your bank doesn't support Macs."
Me: "I already have the file from the bank; Quicken just needs to read it in."
Tech: "Quicken needs a QFX file formatted for Macs. Your bank would need to pay to have different servers for Macs."
At this point, I knew I was screwed. So, I started thinking, what information can Quicken be sending when it goes online during an import? The OS, probably. But, what else? I fired up BBEdit and opened the QFX file. They probably wouldn't send account or transaction info - leaving mainly the following parameters:
ORG (Bank name)
FID (Some ID internal to Intuit)
INTU.BID (Same as FID)
BANKID (Bank Routing Number)
So, for kicks, I called up a buddy of mine who has an account at another bank that has "Mac Access" and asked him to download a QFX file and give me those parameters.
A quick BBedit
Viola! The file imported with no issues. All the online connection does is connect back to Intuit and ask "Has this bank paid for Mac support?" - if the answer is "no", the import is stopped.
Now, since I don't use any Quicken Online Banking features, I can't vouch for how it affects those (I would expect them to fail since the routing numbers are wrong). But, as a pure import facility, there are no issues doing it this way.... which happens to be the only feature that most people I know use (importing vs. Quicken Online Banking).
So, Intuit is going out of their way to make pay for "Mac Support" when it doesn't cost them a cent more. Sure, development of Quicken for Mac might cost more, but that's why it's double the price of the PC version. We pay for certain functions in Quicken - importing transactions is one and to prevent us from doing so because they want more money from our banks just doesn't seem right.
Give me your PIN and our Citi online password and I will see if I can get it working for you.
"Anyone know how (or why) they would make such a generic file platform-specific -- what business advantage does Intuit (or my bank) have in restricting how I use this information?"
.. well .. that's great, but I think we can get a real business advantage by keeping all of that stuff secret and not using it. Let's just ship the Windows version."
.. they might be working on it. My guess is that the smart thing to do is develop Windows stuff first, make 99% of your users happy, and develop other stuff later.
Yeah, I can see the business meeting now:
Intuit Programmer: "OK. We have written software that works on all possible platforms. It's all debugged and ready to go."
Intuit Business Guy: "Really? It runs perfectly on all platforms, and you're done with the whole debugging cycle? Didn't that cost a whole lot more than just writing code for the one platform that 99% of our users use?"
Intuit Programmer: "No. The magical code elves wrote all of the extra code for us. The code gnomes tested it. We paid them in fairy dust; no actual money involved."
Intuit Buisness Guy: "Hmmn
The question you should be asking is "how much of a business advantage would Intuit gain from investing the resources to develop this stuff for multiple platforms?" My guess is that it's just not worth it to them at this point. But who knows
GNUCash can import OFX/QFX files.
It may well suck, but decisions that come down to money often do.
Interesting that you put it that way, because my decision when it came to my money was to use a bank that supports my platform. I use linux exclusively at work for reasons that are obvious if you look at my e-mail address. Fleet (BankBoston back in the day) has the best online banking software, and best of all, they support Mozilla on Linux and they supported Netscape 4.x on linux when that was standard. Their tech support answers the phone, and they are responsive to bug reports. I've sent in two bug reports, and they were both fixed within 48 hours. I've heard that they are similarly helpful for Mac users (and a quick check shows their software working correctly on my Mac). If Citibank won't support you, I recommend Fleet. As a bonus if you keep a large minimum balance, they have no fees and competitive interest rates to go along with their open-minded platform support policy.
Sucks for all those other banks that I looked into that turn away customers with large balances because they can't invest minimal effort in standards compliance.
I really don't think that translating platform-specific CR->LF chars in an ASCII formatted file is high-up as a "dangerous, unsupported hack" in the list of data integrity risks. :-)
"Flyin' in just a sweet place,
Never been known to fail..."
The QFX file format is a standard implementation of OFX. For those not in the know, OFX (Open Financial eXchange) is an XML standard for financial data exchange. This is supported by a number of third party financial software providers (including my glorious employer). If your bank doesn't have OFX support, then you're pretty much up a creek. QIF, Intuit's older data format, is pretty much dead now. I don't know if they still support it, but from what I've been told support is sunsetting rapidly.
I don't know who told you that no banks support QFX/OFX for non-windows platforms, it is a platform independent standard. Unless, of course, your bank purchased a windows/IE specific solution. I know our software does work on mac versions of Quicken (I wrote it), and if they follow the intuit example code it is trivial to write.
The OFX standard, including the DTD, can be found here