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

[Christopher Lam]

FWIW the qif failure when "S" field being empty is now fixed for the next
release; and will default to the "Unspecified" account.
The internal-transfer duplication is not fixed yet.

On Sun, 23 Feb 2020 at 17:10, James Peterson <lyle at austin.rr.com> wrote:

> Actually that example should be easy.  You have three transactions.
> The first (and the last) will be simple double entry bookkeeping.
>
> (At this point, I can either (a) try to fake the QIF or (b) actually
> use Quicken to create the QIF.  If I do (a), I'll probably miss some
> minor details, and you could use that against my saying it's easy,
> but to do (b) requires turning on my Windows laptop, bringing up
> Quicken, creating a test file, putting in all the accounts,
> and the transactions, then exporting a QIF file and bringing that
> over to my Linux laptop.  Sigh.  I'm going to go eat dinner first.
>
> ...
>
> So attached is test3.qif, which is the QIF file for what you
> described.  It's easy to match the transactions.  Notice that
> when a transfer is done, two transaction records get created,
> one for the source, one for the destination.  But the source
> lists the destination as it's L field
> L       Category (Category/Subcategory/Transfer/Class)
>
> in checking:
> D2/23'20
> U-100.00
> T-100.00
> NTXFR
> PSavings
> L[Savings1]
> ^
>
> and the destination lists the source as it's L field:
>
> in savings1:
> D2/23'20
> U100.00
> T100.00
> PSavings
> L[Checking]
> ^
>
> So you match transactions if (a) the date is the same,
> (b) the amount is the same, and (c) both transactions
> point at each other.
>
> So the main problem is if there are more than one transfer
> from account A to account B on the same day, for the same
> amount.  test4.qif shows this.  Here we transfer two $100
> amounts from checking to savings1.  Now (a) why would we
> do that? Well, it shouldn't matter, but in this case I
> took the previous case (of savings1 and savings2), deleted
> all the savings2 transactions and added a new $100 transfer
> from checking to savings1.  One is memoed "Grandma" and
> the other "Aunt" (so birthday gifts maybe).  Looking at
> the QIF file, we see that both transactions are memoed
> the same too, so we can add to our "matched transactions"
> have to (d) match memos, if any are given.
>
> for checking:
> D2/23'20
> U-100.00
> T-100.00
> NTXFR
> PSavings
> MAunt
> L[Savings1]
> ^
>
> for savings1:
> D2/23'20
> U100.00
> T100.00
> PSavings
> MAunt
> L[Checking]
> ^
>
> but we have uncovered a peculiarity -- presumably a minor bug
> in Quicken.  Note that for the transaction that was created with
> a memo (Aunt), both are the same.  But for the transaction that
> was created without a memo, and then the memo was added later
> (Grandma), the Memo was not copied to the destination
> entry.
>
> for checking:
> D2/23'20
> U-100.00
> T-100.00
> NTXFR
> PSavings
> MGrandma
> L[Savings1]
> ^
>
> for savings1:
> D2/23'20
> U100.00
> T100.00
> PSavings
> L[Checking]
> ^
>
> So for (d) matching memos, we have to elaborate it as:
> (d) if both have non-empty memos, they must match.
>
> Now we do still have the case, suppose the transactions
> have the same date, same amount, point to each other, and
> have the same memo (or don't have memos).  Now how do
> we match them?
>
> It doesn't matter.  If we make two transactions (1) and (2)
> that have the same date, the same amount, the same accounts,
> and the same memos, then match them any way you want.  If
> you match (checking:1) with (savings:2) instead of (savings:1),
> who cares?  The two are effectively identical.
>
> Of course, obviously, we need to only match 1 to 1 -- so once
> we match checking:1 to savings:1, we can't then match checking:2
> to savings:1 too.
>
> So the most difficult matching would be two identical matching
> transactions, but one has a memo and the other doesn't and
> another in the same situation like:
>
> checking:1, no memo
> checking:2, memo A
> savings:1, memo B
> savings:2, no memo
>
> We can match checking:1 with savings:1, and checking:2 with
> savings:2, but if we match checking:1 with savings:2 (exactly
> the same non-memo), and then we can't match checking:2 and
> savings:1 -- different memos.  (Actually, if we produce the
> memo/no memo by editting the memo after the transactions
> are created, and Quicken doesn't "forward" the memo, I wonder
> if I can create matching transactions with different memos?
> Let's try.  We will create the transaction, then edit each of
> the two separately....  Yes.  See test5.qif -- I could edit the
> Grandma transaction to be Aunt in the entry that is in Savings1,
> and the Grandma memo did not change.  So if memos differ, we may
> still need to match the transactions, so matching rule (d) is
> a suggestion, but not necessary.
>
> Now, for Quicken, the transactions are listed in the order that
> they are in the transaction registers, and double-entry transactions
> will always be created at the same time, and so will appear in the
> same order in both lists.  So if we have a bunch of transactions
> for account 1 and account 2, for the same day, they should match
> in order (for the subset that point to each other -- you could
> have transactions from other accounts interleaved), so the amounts
> should match, and the memos "should" match (but may not).
> But QIF files, in general, would not have to be sorted that way,
> so probably best not to assume that.  If we have one transaction
> for $73.47 and another for $23.55, on the same day, and somehow
> they are in one order for one account and the other order for
> the second account, we should still match them (absent other
> transactions for those amounts for those days).  I would think
> that "most" of the time, the days and amounts make it unique
> when accounts point at each other, and order and memo are
> unnecessary.  So do we really want to code for when order and/or
> memo are deciding factors, or just match anyway.
>
> Now splits are a complicating case.
> But it seems a transaction A with splits
> 1, 2, 3 should be identical to 3 separate transactions A1, A2, A3
> in the obvious way.  So if we map splits into multiple transactions
> when things are read in,
> can we just drop the concept of a split?
>
> jim
>
>
>
> On Sat, 2020-02-22 at 22:54 +0000, Christopher Lam wrote:
> > 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.
> > > > > > >
> > >
>