July 3, 2009 at 7:32 am
Anyone out there strong enough with XML to be able to decode Quicken's QFX file format? I've had some small successes with XML in general, although that was some time ago, and I was using the XML that you could install on XP and reference it's methods using VBScript. At this point, I've forgotten most of that, and I could really use some help in determining how to handle the Quicken QFX file format. If I could import data to SQL Server from QFX files, I really wouldn't need to keep buying newer versions of Quicken every year or two or three. I'm attaching a sample QFX file that I've modified only to eliminate personal information by changing amounts, transaction ID's, names, and such, along with adding CRLFs and tabs to make it readable. In the actual file, no CRLFs or tabs exist, except for the first 9 lines.
I'm looking for basics here, or pointers to where to pursue more information. The last time I tried this, I was getting nowhere fast, so I figure it's time to start from scratch. Any/all help appreciated.
Steve
(aka smunson)
:-):-):-)
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
July 3, 2009 at 4:43 pm
What would be your expected result?
It looks like it would be required to fill more than just one table...
Edit: you might want to look up "XQuery" in BOL as well as searching for "XML Workshop" (series of articles by J. Sebastian) on this site to start with.
July 3, 2009 at 8:03 pm
My objective is to be able to take the transaction data and import it to the correct table based on the specific account it's for. Mind you, I haven't set up these tables yet, but I first need to learn how to handle XML, and have a technique or two for when it's not well formed, as some of those files tend to be. The last thing I want to have to do is manually edit them first to clean up the XML formatting. I'll look up XQuery, but does it rely on XML meeting any specific standard of formatting?
Steve
(aka smunson)
:-):-):-)
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
July 3, 2009 at 9:22 pm
Now I remember what I ran into before. Missing end-tags, because the true format of the file is just SGML, and not true XML. XQuery fails because of this. I guess I'll have to see if I can find a utility that can clean up missing end tags. Or does anyone have a better suggestion? I can't even be sure you could be sure to put end tags in the right place all that easily, but who knows? Some end tags are already there, but others are not.
Any ideas, folks?
Steve
(aka smunson)
:-):-):-)
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
July 5, 2009 at 8:51 am
UPDATE:
I searched high and low for an "XML repair" tool, and I couldn't find any freebies, nor even any paid for products that actually tout such a feature. I was amazed. Perhaps it's the nature of the problem associated with things like the SGML present in the Quicken QFX files. I'm not familiar enough with either XML or SGML to know if the SGML in a QFX file actually meets the SGML standard or not, but if you stop and think about Intuit for a minute, it's a lot easier to believe that it doesn't. They changed over their database format to something that for all practical purposes is encrypted, and provided no means for external reporting against such data, effectively taking away one of the most useful features of the older program versions - the DBF files. They had realized that leaving things that way meant less revenue for them, and more for those who would likely develop something much better, so off to proprietary land they went. It didn't take them long to realize that QIF had to go the same route as well, and thus, QFX in crappy SGML was born. If they'd used regular XML, it would have been just a tad too easy for others to make use of.
All I can say now, is too bad for Intuit... I've solved the problem for myself with a small VBScript. It's been successful at rendering into XML the data I've downloaded from BOA, Amex, and ING, along with my bank for my checking account as well. Thus I'm going to eventually have an application that will replace Quicken, and they'll just have to live without my continuing to pay for new versions of their software. They wouldn't have this problem if they provided that software at a more reasonable cost. $10 - $20 is one thing, but $100 or more is just too much. Now they'll get zero instead.
Maybe I ought to sell the script for $20, customized to point to the path to the user's QFX files? Are there enough people out there who both could develop the SQL database and application to replace Quicken and also the ETL to import the XML? I'm not in the mood for competing with Intuit, but giving them a micro-needlepoint in the backside would certainly feel good.
Thoughts?
Steve
(aka smunson)
:-):-):-)
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
July 5, 2009 at 11:19 am
Hi Steve,
to verify the QFX structure you could set up an XSD file containing the expected structure.
When you try to assign the QFX file to an XML variable bound to the XSD, you'll get an error telling you exactly where the tag is missing.
I just tried the example used in J. Sebastians article[/url] and created the schema.
After that I tried the correct xml structure to verify it works.
Then I deleted the first end tag .
I received the following error message:
Msg 6926, Level 16, State 1, Line 56
XML Validation: Invalid simple type value: 'CreditCard
'. Location: /*:PaymentDetails[1]/*:Type[1]
At least this would tell you the position of the first missing tag. (It won't give you all missing tags at once though...). But maybe it's something to start with.
Slightly off topic:
What Quicken version do you use? I'm still on 2004 (for good reasons, obviously...). To import the QLF export file seems doable, even though I didn't try it...
July 5, 2009 at 1:26 pm
I'll check out that article, but I've already solved the problem. I also suspect that the SGML in the QFX files is not actually up to spec, because when I tried using a utility called sx, it added end tags as if every occurrence of a tag with a value doesn't get it's missing end tag supplied if there's another tag with a value immediately following it, and that makes for two problems: 1) unnecessarily complex XML, and, 2) XML that still can't be navigated via xQuery and the data function. The only way I can get around this is to use a VBScript I developed yesterday to supply missing end tags in the appropriate places, and I get the XML I actually want, rather than whatever the SGML or XML standard would require. Intuit, as a software manufacturer intent on protecting it's revenue stream, would not benefit from the OFX/SGML format actually being compliant with XML standards, so they're not likely to change away from it, and as they're not the only users - the financial institutions use it even more than Intuit does - it's not going anywhere anytime soon. It's SGML structure is actually a hindrance to getting the data out in a way conducive to importing to any tool not named Quicken, and Intuit likes it that way.
Also, setting up a template in XSD isn't practical, as the various financial institutions don't implement the SGML quite the same way. American Express, for example, has different fields included, and my bank for my checking account requires an extra step in the script in order to allow for the Amex coding to be handled. I'm eventually going to see if I can merge that step back into the previous one somehow.
By the way, I use Quicken 2009 Premier Home and Business.
My script's methodology is to just add an end tag for any tag that has a value, and then start a new line.
The process reads the file 1 character at a time, due to some of the differences I found with Amex, but writes the data out one line at a time.
Steve
(aka smunson)
:-):-):-)
lmu92 (7/5/2009)
Hi Steve,to verify the QFX structure you could set up an XSD file containing the expected structure.
When you try to assign the QFX file to an XML variable bound to the XSD, you'll get an error telling you exactly where the tag is missing.
I just tried the example used in J. Sebastians article[/url] and created the schema.
After that I tried the correct xml structure to verify it works.
Then I deleted the first end tag .
I received the following error message:
Msg 6926, Level 16, State 1, Line 56
XML Validation: Invalid simple type value: 'CreditCard
'. Location: /*:PaymentDetails[1]/*:Type[1]
At least this would tell you the position of the first missing tag. (It won't give you all missing tags at once though...). But maybe it's something to start with.
Slightly off topic:
What Quicken version do you use? I'm still on 2004 (for good reasons, obviously...). To import the QLF export file seems doable, even though I didn't try it...
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply