Please create an account to participate in the Slashdot moderation system

 


Forgot your password?
Close
typodupeerror
×
Programming IT Technology

Specifications of Intuit's .QFX Format? 56

Posted by Cliff from the text-is-open-but-the-tags-are-proprietary dept.
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?"
This discussion has been archived. No new comments can be posted.

Specifications of Intuit's .QFX Format? More

Specifications of Intuit's .QFX Format?

Comments Filter:
  • If that works, it could be your ticket.

    OTOH, it would be in violation of the DMCA, not to mention stealing from Intuit and your bank, so maybe you better not confess to it here.

  • I believe that Quicken has some sort of API for adding transactions. I haven't used it in the past several years, but I know that around '98 or so, they had some sort of pluggable system for connecting to various internet banking systems. Perhaps you should stat by talking to Intuit.

  • perl (Score:5, Informative)

    by delorean ( 245987 ) on Monday December 09, 2002 @02:49PM (#4845312) Homepage
    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>;
    -----
    • I forgot to mention, that was QIF files. But, you can probably make it work as a Q** to QIF to import.
    • Careful! You just posted a security altering device that may fall under the jurisdiction of the DMCA. Perpare for an appropriate long term prison sentence.
    • You're doing unfiltered transformations - sed or awk is just as good if not better.

      Perl is for doing all sorts of fancy things.

      Awk is for applying rules (unlimited on what they can do) based on regex matching.

      Sed is for linear transformations based on regex matching.

      Yours is a linear transformation and can be done in awk or sed which are basically file filters.

      sed "s/^D/C/" file | sed "s//\n/" > file.out

      Where is and is CR.

      Your mileage may vary and you may have to use a different comparator.

      Awk syntax is very similar to C in an interpreted environment, and can be done on the command line as well, or alternatively in a file. It's what C might have been like if the C interpreter had ever taken off.

      Sed and Awk are very powerful for regular expression matching. Perl to me is better used as a decision making tool than a transformation tool.

      Sed and awk also allow for back-references in the regexes if memory serves.

      My 2 cents.

  • Risky (Score:1, Troll)

    by 0x0d0a ( 568518 )
    hack mine into submission

    You trust your electronic banking to an unsupported hack you're going to whip up?

    You're braver than I am...

    • Re:Risky (Score:3, Insightful)

      by Jeremiah Cornelius ( 137 )
      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. :-)
  • I have a problem with Quick 2003 for the Mac. Citibank says to use a PIN to access my data. I have set up an account at the Citi online website, but *neither* the PIN or my Citi online password access the data. What gives? Does anybody else know which of these two to use? One of them worked at one time, but now nothing!!
  • almost no banks support doing this for non-Windows
    what business advantage does Intuit (or my bank) have in restricting

    The key word there is support. Just because something isn't supported, that doesn't mean it wont work. Have you tried it?

    Very often this is simply done to reduce the load on the company's tech support line. (Either Intuit or the bank or both). Its hard enough to get competent folks to man your support lines, without having to train them on things they will only encounter once in a hundred calls. So they save some money by going lowest-common-denominator.

    • without having to train them on things they will only encounter once in a hundred calls

      Two things. First, the Macintosh has between 5-8% of the destkop space. That's not one out of every hundred, that's, just on raw marketshare, one out of twenty. Second, if the one per one hundred call is even remotely accurate, that means that only 20% of those Mac users have those kinds of problems.

      Sure, not every user uses Quicken (or, even has it installed), but that's a poor excuse for poor customer service. That bank's customer uses a Macintosh. That is all there is to it. It is not sufficient to say that the majority of people use Windows, so let's just support Windows users. That's exactly what Microsoft counts on: monopoly control over the market without spending money to quash competitors. Is that really acceptable?

      • I didn't say it was acceptible, but it is reality. Banks are like any other company, they have fixed budgets for things like support, and in these economic times those budgets are typically stretched thin or are being cut.

        Managers are going to look for any way to stretch that budget, and if that means ignoring 5% of the user base, frankly some of them would be willing to take that risk. (Plus they can always play the 'fingerpointing' game with Intuit if push comes to shove).

        What percentage of the available base of tech support personnel have Macintosh experience? Is it similar to the 5 to 8% you quote for desktop penetration? If so, that bank is going to have a really hard time hiring some techs who can answer Macintosh questions intelligently. As a result they're going to have to pay them more to retain them. You can imagine where this leads in a manager's mind.

        It may well suck, but decisions that come down to money often do.

        • 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.
        • Yes, you're right that it is reality at this point in time. That doesn't mean that we have to accept it :-) If we are serious as a group about wanting choice re: the OS we run on our computer(s), we must pressure those who are weak-willed, and willing to let Microsoft and Intuit and whomever else bend them over without so much as a kiss hello into doing the Right Thing(tm): support open, cross-platform standards.

          BUT, as a previous poster mentioned, the file is cross-platform, at least in content (if not line endings), and shouldn't be a problem. So, the real culprit here is Intuit, for charging banks an extra fee for, essentially, transforming line endings. That doesn't absolve us of the responsibility for chastising and dressing-down those banks that are being stupid, but it does mean that perhaps we also need to be beating down Intuit's door.
  • This probably isn't it, but it wouldn't hurt for you to open the file and change the cr to crlf since that is a distinction b/t dos and bsd.

    • Just to clear up:

      • DOS: CR + LF
      • Unix/BSD/Linux: LF
      • Mac OS 9.x and earlier: CR
      • Mac OS X: LF

      'recode' can take care of converting between these and other formats, along with converting between character encodings.

  • QFX - The Royal Scam (Score:5, Informative)

    by Anonymous Coward on Monday December 09, 2002 @03:26PM (#4845627)
    Intuit is a bastard of a company.

    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 ... fire up Quicken ... click Import:

    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.

    • Viola! The file imported with no issues

      Any other musical instruments appear? Why is it so difficult for people to use the right words?

    • Re:QFX - The Royal Scam (Score:2, Interesting)

      by Watcher ( 15643 )

      I've written the software to generate the QFX file and transmit it to the client system. As long as your browser has been configured correctly for the MIME type (application/vnd.intu.QFX), your browser should be able to pipe the data to Quicken. It sounds like your bank either did not set the MIME type in the script, or your browser didn't have the appropriate entries to pass data of that type to Quicken.

      QFX (really OFX) is platform independent. The tech rep was lying to you.

      • Re:QFX - The Royal Scam (Score:1, Interesting)

        by Anonymous Coward
        I know it is.
        You know it is.

        Evidentially, the banks (and Intuit) are the only ones that think different.

        This is not a question of MIME types. Quicken read the file fine. During its import, it takes the bank info and check to see if the bank "supports" macs.

        If, according to Intuits database, the bank does not support Macs, the import is killed with an error. If the bank does support Macs, the import is allowed to continue.

        Imagine you had a Apex DVD player and a Sony TV connected via standard S-Video cables. An analogous situation would be the DVD player turning off its S-Video out because it doesn't "support" Sony. This would be rediculous - S-Video is S-Video.

        By the same token, QFX is QFX and it should not matter where the file comes from, Quicken should be able to import the file WITHOUT checking that the bank "supports" macs and certainly WITHOUT going online.
        • Out of curiousity, try unplugging your network cable and see if it will import.
          On a separate note, I wonder if this could lead to a class-action suit against Intuit?
    • This sounds like a classic story for Infoworld's Gripe Line column.
    • I have the same problem. Please post the information so taht I can update my files.
  • There's no reason why you have to use the QFX format for this. Most lending institutions will support the QIF format, which can be imported quite nicely into Quicken 2003 for Mac OS X. (I know, because I did it just last night. :) I don't know what the QFX format does, but I suspect that it allows Quicken to actually interact with your credit card, allowing payments to be made from within Quicken. QIF, on the other hand, merely provides a record of transaction that must be manually imported into Quicken each time.

    Now, I can't speak for Quicken 2003, because I haven't tried it, and because Intuit's site doesn't go into details...but, Intuit does have a guide on how to get QFX files working on the Mac [intuit.com]. Have you tried that?

    -Waldo Jaquith
    • When I tried to use QIF, transactions would get imported more than once if they appeared in two separate downloads.

      My CC download facility had a bug where transactions would not always download - if I pulled transactions 2 days ago, and then, today, pulled transactions that happened since my last download, there was a good chance that a transaction that occurred 2 days ago would not be recorded.

      So, I got into the habit of downloading either the last 90 days or all my transactions every time. If I did this with QIF, I would invariably end up with duplicate transactions in my register. QFX did not have this problem.

      Don't know what the issue was, once QFX fixed it, I didn't research any further.
  • I have several years of financial data on Quicken which I found tedious to use. But Quicken lets you export the data in the (now not-supported) QIF format, which turns out to be a fairly straightforward text file, which then can be read into some other custom-made application. (I am in the process of loading this into a simple homemade MS ACCESS application).

  • Intuity's Developer Network let's you download both the QuickBooks SDK, as well as their QuickBase API. Here it is:

    http://developer.intuit.com/ [intuit.com]

    The QuickBase API evidently supports VB, Perl, and Java, so you have a platform independent option available.

    While I can't definately state that this will help you, in my own experience, I've been able to get at/do everything I need to in QuickBooks using these products.

    Good luck!

  • Wrong Question (Score:3, Funny)

    by poincaraux ( 114797 ) on Monday December 09, 2002 @04:04PM (#4845956)
    "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?"

    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 .. 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."

    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 .. 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.

  • QFX is OFX (Score:3, Informative)

    by Watcher ( 15643 ) on Monday December 09, 2002 @04:33PM (#4846200)

    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 [ofx.net]

  • While QFX was an early standard, it is now only supported by Quicken and legacy versions of Money. Years ago OFX was started to act as a sort of standards body for downloadable transactions.

    I believe that GNUCash has OFX support in development and there is a LGPL OFXLib on Freshmeat or on SourceForge. You could also go to www.ofx.net [ofx.net] to get the specs (though you will need to sign up).

  • This is slightly off-topic, so I am going to self-mod this post down to "1" in fairness (mods, please don't mod down further)...

    Anyhow, what I am wanting to impress upon readers are the issues that I hate about online banking as well as import of files from a bank - to save time from hand entering transactions.

    On the surface, the idea that you could import these transactions, or call them all up in a web browser, sounds good at first glance. Saves you the typing, no need for writing down the transactions, etc, right? But that is where the problem lies.

    When you write down your transactions, or hand enter them (from receipts) - at the end of the month when you balance your account your version had better match up with the bank's version - if it doesn't then there is a problem, and the job is to figure out if it is on thier end, or your end.

    Say you go to a restaurant and order dinner, and pay for it with your debit card. You enter the transaction at home that night. A month later you receive your bank statement, and during your account balancing session you notice that on the same night are two transactions for the same amount at that restaurant - but you know you only had dinner once, based on your accounting entry (this is not really a made up story - I have had it happen to me several times, at different restaurants).

    Had you been importing the account transactions, or been looking at the "web-statement" online, you might very well miss the problem (there is a way around this, kinda a "reverse-balancing" system - whereby you compare the statement to your collection of receipts, to see if all the receipts are accounted for and only used once - but this system has flaws, mainly due to needing to read each line, possibly skipping lines, and other issues) - but you would never know you lost some money...

    It really is bad accounting practice - I doubt banks and businesses would use such a system - so why should individuals? I tend to think this issue isn't brought up much because these mistakes, since they aren't as easily caught by individuals, actually cause more money to be earned by the banks and businesses (especially if the businesses and customers bank at the same bank - the money never leaves the bank, so bank doesn't care, and the business has made double money off of one transaction - why should they care?).

    Good accounting practices are to keep a separate ledger of your own, and don't keep two separate running copies of the ledger (to avoid double entry errors). Then, at the end of the month compare your ledger to the bank's.

    This is one thing I like about the older CheckFree software (and the only thing keeping me on Windows) - you had basically a check ledger for each of your accounts, and you could enter in transactions - at the end of the month you enter starting and ending amounts, and check off each transaction as you see it on your statement. If you have the same number of transactions with the same amounts, the total at the end comes out to be $0.00 - indicating the account it balanced with no descrepancies. It has save me many times.

    I tried to go with on-line service with BofA (where I bank), but it doesn't offer that kind of feature, nor does it have the option to pay online to merchants who don't take EFT payments through them (whereas CheckFree will cut a paper check and mail it). I have yet to find a solution under Linux that will work for me (GNUcash would be it if it had EFT support, but without it I would need to use some other electronic payment system, and also do double entry, which breaks one of the main good accounting practices). If anyone knows of one, let me know (I have considered running the CheckFree software under Wine, as well)...

    • I'm not sure I follow you.
      I enter my debit card receipts every night (and then my budget spreadsheet since the Intuit budget feature is worthless...). Weekly, or at least every payday, I download the QIF file from my bank (and fix it with my perl script); I then import it. Each record is either a) matched to any entry, or b) flagged new by Quicken.
      I then scroll down the list and verify that the matched entries are the same (sometimes flipping back to the browser to see the name). Accept, Accept, UnMatch, Manual... and then I'm done. I've just reconciled my checkbook. If I had a double charge, I would (and have) see it.
      What's wrong with that picture?

      • If Quicken does the matching and flagging, then there is nothing wrong - Quicken will flag it, and you can check from there.

        My comment wasn't about Quicken (it is nice to know that Quicken will handle the situation of double entries), but of only downloading the transactions, and not hand entering them. I can see in your case of entering the transactions, then downloading them to compare and balance - but if you are only downloading them (or only looking at an online version), and not doing your own recording - that is where the problem lies.

        I think a lot of people probably do this, to avoid having to sit down and enter transactions - assumming and trusting the bank to always be correct. Maybe the bank is flawless (though I highly doubt it), but the merchants aren't.

        It sounds like you are using the Quicken import as a "double check" as well as for balancing - this is fine - it is when you rely only upon the entries in that download (and not on what you enter) that the problem occurs...

Slashdot Top Deals

The most exciting phrase to hear in science, the one that heralds new discoveries, is not "Eureka!" (I found it!) but "That's funny ..." -- Isaac Asimov

Close