[GNC-dev] [GNC] QIF file import failed -- but no errors or warnings

[Christopher Lam]

Re: duplicate internal transfers -- I guess would be difficult in any
software. Consider the following transactions, *all* in the same book --
and *all* accounts (bank chequing, savings1, savings2) are selected for QIF
exports.

10/01/20 Save $100
Chequing -$100
Savings1 +$100

10/01/20 Save $100
Chequing -$100
Savings1 +$20
Savings2 +$80

10/01/20 Save $100
Chequing -$100
Savings2 +$100

The QIF will have:
chequing
 10/01/20 -100 save $100
 10/01/20 -100 save $100
 10/01/20 -100 save $100
savings1
 10/01/20 +20 save $100
 10/01/20 +100 save $100
savings2
 10/01/20 +80 save $100
 10/01/20 +100 save $100

And somewhere internal *must* contain logic to marry up the 7 splits into 3
separate transactions *without* an unique transaction_ID. I don't think
it's possible to make one safely. The easy answer is to export each account
into a separate QIF, and import piecemeal. i.e. after importing
chequing.qif, import savings1.qif and match the transactions manually, then
savings2.qif. Even then I'm not 100% sure the gnucash QIF importer or any
other software can handle this safely.

On Sat, 22 Feb 2020 at 19:03, James Peterson <lyle at austin.rr.com> wrote:

> 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
> of those seem to be in Python, Ruby, JavaScript, C#, ...
>
> jim
>
>
>
> 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.
> >
> > C
> >
> > 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.
> > > > >
>
>