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

James Peterson lyle at austin.rr.com
Sun Feb 23 12:10:47 EST 2020


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.
> > > > > > 
> > 
-------------- next part --------------
A non-text attachment was scrubbed...
Name: test3.QIF
Type: application/x-qw
Size: 5751 bytes
Desc: not available
URL: <http://lists.gnucash.org/pipermail/gnucash-devel/attachments/20200223/14695e0d/attachment.bin>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: test4.QIF
Type: application/x-qw
Size: 716 bytes
Desc: not available
URL: <http://lists.gnucash.org/pipermail/gnucash-devel/attachments/20200223/14695e0d/attachment-0001.bin>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: test5.QIF
Type: application/x-qw
Size: 592 bytes
Desc: not available
URL: <http://lists.gnucash.org/pipermail/gnucash-devel/attachments/20200223/14695e0d/attachment-0002.bin>


More information about the gnucash-devel mailing list