[GNC-dev] [GNC] QIF file import failed -- but no errors or warnings
lyle at austin.rr.com
Sat Feb 22 14:03:37 EST 2020
I'm seeing generic problems with QIF importing with
both gnucash and Kmymoney and I think skrooge too,
that can't handle the semantics of my QIF file. For
example with splits and unassigned categories and
such. Also, Quicken seems to assume an unnamed cash
balance for investment accounts as the source and
destination for buy/sell/interest/dividends, which
no one seems to handle properly.
So I'm currently planning to write a QIF to QIF
program that will "clean up" and "simplify" these
sorts of things. It can create a named account for
the anonymous cash balance (which seems to be what
the brokerages are doing anyway), delete zero balance
and empty lines, and output a new QIF that doesn't
have these problems. Don't know what I can do about
duplicate internal transfers -- that's sort of the
core of double-entry bookkeeping.
I started by looking in github for "QIF file" and
have 130 different programs which take in or produce
QIF files, so I'll review those first, although most
On Sat, 2020-02-22 at 05:15 +0000, Christopher Lam wrote:
> Thank you, very useful.
> We can already try fix the empty-category issue to allow importing, but is
> likely to cause other problems.
> The internal transfers issue is much more difficult. The QIF importer
> attempts to detect duplicates whereby the datafile already has QIF
> transactions, but not for internal transfers. Hence some more data files
> (e.g. internal transfers between 3 accounts all in QIF, or between 3
> accounts whereby 2 are in QIF etc) are still useful.
> On Fri, 21 Feb 2020 at 18:27, James Peterson <lyle at austin.rr.com> wrote:
> > This second QIF file illustrates the original problem I had --
> > it simply "Failed" with no errors or warnings or messages
> > of any kind (that I can find). Adding the debug print to
> > print each transaction as it is processed in qif-to-gnc.scm
> > showed it was a transaction with no category listed.
> > So this QIF file, test2.QIF, picks up from the previous
> > test1 -- two accounts, checking and credit card, the same
> > payment from checking to the credit card, but now I actually
> > try to buy something with my $400.
> > First I spend $100 at Fry's for a Flash Drive, but give no
> > category at all. So this is a simple transaction with no
> > category.
> > Then I buy two CDs at Best Buy, using a split transaction.
> > The split transaction as a whole has a category (Music, as
> > a sub-category of Entertainment), but within the split, I
> > don't give an additional category, so you get the "S"
> > line with no information at all -- just S on a line by itself.
> > To really make it complicated, we have the two purchases
> > I thought I made (Beatles and Adele) (both without categories)
> > and then I remembered another Dr. Demento CD. Quicken, when it
> > does Split transactions, opens up a window for the Split with
> > 30 lines (16 showing and you can scroll down for the next
> > 14). Each line has a Category (S), Memo (E), and Amount ($)
> > The Beatles was on line 1, the Adele on line 2, then two blank
> > lines and the Dr.Demento on line 5, so you have two entries
> > with no category, no memo, and $0.00 for the amount.
> > gnucash can't load this file at all, but I suspect that if
> > you fix the "Failed" on the import, the amounts would still
> > be all screwed up.
> > I don't know how to take screen shots on a Windows 10 system,
> > but the QIF files is all you should need.
> > I hope these test cases help. Now I have to go back and try
> > to repair my quicken files. One of the most maddening things
> > about Quicken is that there is no journal of what I've done.
> > If you are typing in January, but forget the new year, and enter
> > a transaction, it will immediately be sorted by date and
> > disappear from view -- the view is still at the end of the
> > account, but the just entered transaction is way back in
> > January of the previous year. And woe betide you if your
> > fat fingers mess up the year entirely and you don't even
> > know what year you typed -- the transaction is safely
> > sorted back into 2001 or someplace. And everything is
> > automatically updated, on disk, in memory. There is no
> > undo, no trace of what you did. I seem to have put the
> > wrong category into one of my previously missing categories
> > from back in 1985 and now I have accounts that should be
> > zeroed out and closed that have $-36K and $-7K balances
> > which I need to fix. At least I can diff the QIF files
> > to see how they changed in the last few days.
> > jim
> > On Fri, 2020-02-21 at 07:09 +0800, Christopher Lam wrote:
> > > Sorry to see you go.
> > >
> > > You have, in one swoop, uncovered 3 issues:
> > >
> > > 1. S field being empty is not processed correctly. It would still be
> > nice if
> > > you could attach some screenshots from quicken, and a tiny qif.
> > >
> > > 2. Duplicated transactions. This happens because I think qif is not
> > limited
> > > to 1 account. If it has more than 1 account and there are internal
> > > transactions, qif importer cannot reliably detect those because the
> > > specification, unlike ofx, does not have a unique transaction ID. Not
> > sure
> > > whether KMyMoney handles this well. Again a small test case is still
> > welcome
> > > to try fix this, including quicken screenshots.
> > >
> > > 3. Importing stock or multi currency transactions is difficult. The
> > importer
> > > does try to handle it. Ditto test case and screenshots.
> > >
> > > Lastly someone else said gnuc 3.1 managed to import; may be worth
> > trying?
> > >
> > >
> > > On Fri, 21 Feb 2020, 1:46 am James Peterson, <lyle at austin.rr.com> wrote:
> > > > I appreciate your responses to my postings -- you really helped
> > > > me find why gnucash did not like my particular QIF file.
> > > >
> > > > But once I got past that, it's clear that gnucash is badly
> > > > mangling the meaning of the transactions I have. I end up
> > > > with an overall balance of -2 million. And my investment accounts
> > > > (like my IRA) with multiple mutual funds are particularly
> > > > mangled. Plus treating categories as accounts makes it hard
> > > > to concentrate on just the account balances. And it seems that
> > > > when I write a check from my checking account to Discover
> > > > to cover my balance, it ends up being creditted twice, so
> > > > instead of a zero balance, I end up knowing that I paid $518K
> > > > over the past 27 years. While I want to be able to find that
> > > > out (maybe), it's not something I need to know all the time.
> > > > (I realize this is a problem of some kind with double entry
> > > > bookkeeping, but that means everything like this is probably
> > > > wrong.)
> > > >
> > > > So gnucash does not look like a suitable replacement for
> > > > my use of Quicken. KMyMoney seems to be closer to being
> > > > correct, so I'm going to see if I can fix the problems I'm
> > > > running into with that, and see if that works better.
> > > >
> > > > jim
> > > >
> > > >
> > > >
> > > > On Thu, 2020-02-20 at 11:47 +0800, Christopher Lam wrote:
> > > > > Qif importer does have special handling for empty categories.
> > Changing
> > > > this
> > > > > is likely to break things elsewhere though.
> > > > >
> > > > > It would be useful to attach the minimal qif file from selective qif
> > > > export
> > > > > from quicken, and insert screenshots from quicken too. Maybe best
> > file
> > > > bug
> > > > > in Bugzilla.
> > > >
More information about the gnucash-devel