QIF imports from NetBank and Vanguard using GnuCash 1.4.7

Bill Gribble grib@billgribble.com
Wed, 1 Nov 2000 12:08:26 -0600

Sorry about the delay in responding to this.  I've been out of town
for a couple of weeks.

On Tue, Oct 17, 2000 at 03:10:59PM -0400, Derek Atkins wrote:
> I've got accounts with Vanguard and NetBank, both of whom support QIF
> downloads of account information.  None of my downloaded QIF files
> load properly into GnuCash.  I'm trying to understand the QIF parsing
> code to see what's going on, but I can't find any documentation about
> either a) QIF File Formats, or b) how the QIF importer actually works.

Welcome to the club :( The QIF format is underspecified and
over-"extended" by whoever wants to do something clever with it, it

Your two problems are real and tricky.  Let me do what I can to
describe how the QIF importer works and how that interacts with your

There are two parts to the QIf importer, the Scheme code which is the
real guts of it and the Gnome dialog code which drives it.  The Gnome
code is all in druid-qif-import.c (dialog-qif-import.c for 1.4.x and
early 1.5.x, but lots of code in common; line numbers are for current
CVS 1.5).  This is really just assembling arguments to pass to the
Scheme code.  The Scheme source code is in src/scm/qif-import/.  I use
a rudimentary record system based on the Guile record type; the
"classes" are defined in qif-objects.scm.

First, we read each QIF file and build a structure (a <qif-file>)
containing its data.  The <qif-file> has a list of <qif-xtn> which are
the transactions.  Transactions have slots corresponding to the
possible QIF fields.  in the first phase (qif-import:read-file,
qif-file.scm:20), we assign raw strings to the slots.  Afterwards,
(qif-import:parse-fields, qif-file.scm:399) we try to parse the
parsable strings (date, numbers, action symbols) to convert them to
corresponding Scheme data.  This is a PITA since there are lots of
possible date formats and number formats and we have to DTRT.

Each QIF file is scanned to pick out the Quicken accounts and
categories it references and then to build a map associating that
Quicken name with a Gnucash account.  The dialog rows are constructed
by qif-dialog:make-account-display and
qif-dialog:make-category-display in qif-dialog-utils.scm.

Then we scan to find and eliminate duplicate transactions.  more
detail later.  This is where most of your problems lie for #1.

Finally, we create any necessary gnucash accounts
(qif-import:find-or-make-account, qif-to-gnc.scm:18) and then convert
each QIF transaction to a gnucash transaction (qif-import:qif-to-gnc,

>     However, the splits don't get matched up properly.  The fund A
>     QIF says that it's buying N shares at price X for a value of
>     $100 from LUnknown, and in fund B it says that its selling M
>     shares at price Y for a value of $100 to LUnknown.  I change the
>     LUnknown to L'Foo' (filling in 'Foo' appropriately for each
>     transaction).

The logic here is in the function qif-import:xtn-has-matches?,
starting at src/scm/qif-import/qif-to-gnc.scm:660 or so; the process
is driven from qif-import:mark-matching-xtns, qif-to-gnc.scm:532.
Basically, the logic goes like this: if the date matches, and the
from-acct of one transaction is the same as the to-acct of the other,
and the total amounts are equal (with a possible sign reversal of
either or both amounts for certain values of the Action (N) field)
then the transactions match.  The number of shares and so on are

My guess, given your later bug report about NetBank files, is that
your files are using some new Action values that my code doesn't
handle (I only know of the ones I use now by example; I've never seen
a canonical list) and using all-positive values.  Therefore, since I'm
not properly sign-reversing some amounts, the match fails.

> 	ATM/POS Withdrawal
> 	Check
> 	Deposit
> 	Interest Credit
> 	Miscellaneous Debit
> 	Overdraft Instant Credit
> 	Transfer Deposit
> 	Withdrawal ACH
> 	Withdrawal Correction

Definitely don't handle these. 
>    My guess is that the parser doesn't understand these terms, so it's
>    defaulting to 'income'.  But I'm not sure.  I could also create a
>    PERL script to munge the QIF file and change the Action fields into
>    something more appropriate, but honestly I don't know what to
>    change them to.

The ones I understand are enumerated in qif-parse:parse-action-field,
qif-parse.scm:188.  Please let me know if this is the problem with
your case #1 (Action fields different from those in parse-action-field).

This is a sort of weird function because I wanted to handle
internationalized action symbols and other kinds of synonyms.  The
side effect is that we can have any number of synonyms for a
particular Quicken symbol.  (btw: string-to-canonical-symbol turns a
string such as "Interest" into a symbol in all-lower-case: 'interest).
If you determine that your Actions are exactly synonymous with
Quicken's, please let me know the mappings and we can add your terms
to the synonym list.  If not, we can add case code for the new

> PS: I noticed that Vanguard now support OFX downloads, and I can
> download a single OFX file with all of my funds in it..  Maybe that
> will help down the road?

OFX support is in the fantasyland stage right.  We would love to have
help in making it a reality.