Interesting QIF Importer "problem"

[Derek Atkins]

grib@gnumatic.com (Bill Gribble) writes:

> ATM, transfers between accounts in different currencies are A Big
> Problem in Gnucash.  For some circumstances, you can get around it if
> the Securities are different but the Currencies match; in other cases
> you *have* to use an intermediate Currency account that refers to both
> of the commodities in question.  The changes to fix this are mostly
> designed but won't be added until the 1.9 series (which will follow
> the 1.6 release, which should be in a couple of months).
> 
> In your case, it seems that it should be OK to transfer from one
> mutual fund to another because they'll both have the same Currency.

Correct.  In my case I've got three mutual fund accounts that have
different securites but the same currency.  I thought this would work,
but it doesn't.

> I'm getting really confused about which parts of all this are you
> fooling around with the QIF files to test stuff, which parts are
> actual files you downloaded from banks, and how much of your original
> account hierarchy in Gnucash is "legal".  Can you straighten me out? :)

Sure.  I'll send you my files in a private email.  As it is, I cannot
use the importer directly on the files I download from the web
directly.  There are two reasons:

	1) NetBank has malformed QIF.  First, they use non-standard
           "action" strings, even though its a bank type.  They
           include payee and memo fields even if the fields are empty
           (indeed, the memo is empty on every transaction, except for
           checks).  They also have their own non-standard strings for
           the 'N' field, even for checks (the check # is put in the
           memo).

	2) Vanguard always uses "LUnknown" for any transfers,
           purchases, exchanges, etc out of the "current" account.
           This means there is actually no information in the
           downloaded QIF about where the funds are going (or coming
           from)!

So, I built a PERL script that does the following:

	1) For Vanguard accounts, figure out which account this is and
           convert "LUnknown" to "L[XXX]" where XXX is the appropriate
           alternative account.  (I can figure out XXX based upon the
           amount of the transfer).

	2) For NetBank, I check the 'N' field and make changes
           depending on the field (it's basically a NetBank 'action'
           field).  In the below, 'xxx' is the current netbank account
           (either checking or mm):

		* Check: change N to the check # and set the payee to
                  a string "Netbank xxx <chk#>"
		* Debit Transfer: Set "L[Netbank Transfer from xxx]"
		* Interest Credit: Set "L[Netbank Interest]"
		* Overdraft Instant Credit: Set "L[Netbank Overdraft]"
		* Transfer Deposit: Set "L[NetBank Transfer to xxx]"
		* For everything else, leave it alone

All in all, I've got four qif files, one for each of three Vanguard
Accounts, and one for NetBank.  In terms of real-life, NetBank
Checking and Vanguard TreasMM wind up exchanging funds back and forth.
Also, there are funds movements from the Treas MM account into the IT
Tax-Ex and 500 Index accounts.  The accounts are linked (financially)
in this way:

NetBank:Checking <--> Vanguard:Treas MM --+--> Vanguard:IT Tax-Ex
                                          +--> Vanguard:500 Index

> I'm not sure.  I *think* you should be able to do what you describe
> (transfer between funds) without an intermediate account,
> theoretically, but I'm not sure the QIF importer will allow you to
> right now.  I'm just not all that sure about what's going on.  Could
> you email me personally your .gnc file and the downloaded .qif file
> with a description of what you want to have happen?

Yea, I'm not sure either.  It certainly looks like the importer has a
bit of a problem with this.  Or at least in my case it has a problem
when the transaction doesn't have the number of shares for both
commodities.  I.e., it has the number of shares in the current-account
commodity, but not the number of shares in the far-account commodity.

> If the DNS problem will prevent you from reaching me, you can send to
> me personally at grib@billgribble.com.
>
> BTW, the DNS thing should be fixed within an hour or so, though CVS
> won't be back up until tomorrow.

It's still affecting me, but I'll keep an eye on it.

> b.g.

-derek

-- 
       Derek Atkins, SB '93 MIT EE, SM '95 MIT Media Laboratory
       Member, MIT Student Information Processing Board  (SIPB)
       URL: http://web.mit.edu/warlord/    PP-ASEL-IA     N1NWH
       warlord@MIT.EDU                        PGP key available