[GNC] $0 splits in QIF imported incorrectly, flipping + and - values

[B Wooster]

Found an existing bug report about this, from 2008, and recent comments
from Quicken users pointing out how this makes it impossible to move to
gnucash.... oh well.

I added my "me too" waiting on the bug (12+ years... wonder if there is
hope?!!) https://bugs.gnucash.org/show_bug.cgi?id=551459#c10

https://bugs.gnucash.org/show_bug.cgi?id=551459#c2  Bug 551459 - Split $0
transactions
says:
"
Rather than completely mangle the import of those files, a decision was
made to reverse the signs on any transaction where:
 split line amounts + "T" line amount = 0
...
So this bug is confirmed, but nothing may be done about it.
"

On Sun, Sep 27, 2020 at 10:38 PM David Carlson <david.carlson.417 at gmail.com>
wrote:

> Very few of us veteran users have imported Quicken files with release 4.x
> importer, but in the 'olden' days many of us found that due to the many
> liberties that Quicken allowed, we needed to start test imports with tiny
> chunks , see whether accounts were assigned correctly or not,  matches
> found, etc.  Then it was often more efficient to edit some things in the
> Quicken file before making fresh qif exports.
>
> Good luck.
>
>
>
> On Sun, Sep 27, 2020, 8:43 PM Christopher Lam <christopher.lck at gmail.com>
> wrote:
>
>> Please file a bug, and include .qif and screenshots from Quicken to
>> illustrate the transaction.
>>
>> On Sun, 27 Sep 2020 at 23:06, B Wooster <bwooster47 at gmail.com> wrote:
>>
>> > Thanks everyone, I think once I clean up my example and terminology,
>> I'll
>> > file a bug.
>> >
>> > Certainly the checking account is all fine as far as total balance is
>> > concerned - but then importing further accounts messes up every account
>> -
>> > including checking.
>> > That is the same even if I include both checking and savings QIF in one
>> > import session.
>> >
>> > Also tested just now with gnucash Version: 4.2 Build ID:
>> 4.2+(2020-09-26)
>> >
>> > On Sun, Sep 27, 2020 at 5:21 PM Jim DeLaHunt <list+gnucash at jdlh.com>
>> > wrote:
>> >
>> > > Hello, BWooster:
>> > >
>> > > I think I may see a terminology difference, which might be interfering
>> > > with you making your question clear in this forum.
>> > >
>> > > On 2020-09-27 11:41, B Wooster wrote:
>> > > > $0 split transactions are not getting imported from QIF correctly.
>> > > > Non-zero transactions get imported fine.
>> > >
>> > > In GnuCash terminology, a "split" typically means a "split line" in a
>> > > transaction. Each transaction consists of two or more split lines,
>> with
>> > > credits and debits which sum to zero. See the Tutorial and Concepts
>> > > Guide, 2.9.3. "Simple vs. Split Transactions",
>> > > <https://www.gnucash.org/viewdoc.phtml?rev=4&lang=C&doc=guide>. A
>> > > "simple transaction" is a transactions with two splits, e.g. one split
>> > > to a chequing account and another split to an account for insurance
>> > > expense, presented in the ledger of the chequing account, so that only
>> > > the split to the insurance expense account is displayed and the split
>> to
>> > > the chequing account is implicit.  A "split transaction" is a
>> > > transaction with three or more splits. It cannot be fully displayed
>> with
>> > > the split to the chequing account implicit.
>> > >
>> > > When I look at the QIF file example you give, it seems that the S, E,
>> > > and $ lines together describe one of what GnuCash would call a
>> "split".
>> > > None of these sets of QIF lines have a value of zero. So your file
>> does
>> > > not have what GnuCash would call a "$0 split".
>> > >
>> >
>> > Right, the file is actual (dummied) data from a large 15+ year Quicken
>> QIF
>> > export.
>> > That transaction originated from a Quicken split entry.
>> >
>> > Whether the final amount to checking is 0.01 (deposit), -0.01
>> (withdrawal),
>> > or 0.00, I was expecting all three to show up similarly in gnucash.
>> > The first two do, the last one does not.
>> > The first two also work correctly  when loading further accounts - it
>> > matches up transactions correctly between savings and checking, etc.
>> >
>> >
>> > > In the transaction "PPay Check Incorrect Withdrawal from Savings", the
>> > > S, E, and $ lines add up to $0. This corresponds to the $0.00 in the U
>> > > and T lines. It appears that you are calling this a "$0 split" or "$0
>> > > split transaction". In GnuCash terms, I would call it a correct,
>> > > balanced transaction.  Note that none of the S lines mention the
>> > > "L[Checking]" account.
>> > >
>> > > In the transaction "PPay Check Correct Deposit to Savings", the S, E,
>> > > and $ lines add up to $1,200. This corresponds to the $1,200.00 in
>> the U
>> > > and T lines. It appears you are calling this a "non-zero transaction".
>> > > In GnuCash terms, I would call it in incorrect, unbalanced
>> transaction —
>> > > unless you assume an implicit entry of $1,200.00 to the "L[Checking]"
>> > > account. That would make it a balanced transaction.
>> > >
>> > > So, it appears that GnuCash is not importing transactions correctly
>> from
>> > > the "L[Checking]" account QIF file if the S, E, and $ lines add up to
>> > > $0, so that the implicit entry for the host "L[Checking]" account is
>> > zero.
>> > >
>> > > What do you expect to see in the GnuCash register for the Checking
>> > > account after importing the  "PPay Check Incorrect Withdrawal from
>> > > Savings" transaction? It seems to me that none of the money movements
>> in
>> > > this transaction affect the Checking account, so there is no reason
>> for
>> > > the transaction to appear in the Checking register.
>> > >
>> >
>> > That is a good point. I'll have to think about it, though.
>> > I think it will mess up the matching phase. Quicken exports the
>> Saving.QIF
>> > data showing a transfer from Checking to Savings. If the original
>> > transaction does not appear in the Checking account, it will again cause
>> > ledger to completely mess up.
>> > Quicken export for Savings only shows that one transfer from Checking to
>> > Savings, it does not show the whole transaction (Salary -> Savings, Tax,
>> > Checking, etc).
>> >
>> > Granted, this is a byproduct of how data was entered into quicken to
>> keep
>> > all Salary events in the Checking ledger in Quicken, so when a
>> particular
>> > Salary was totally transferred to other accounts, the Quicken split
>> > transaction shows all the transfers and 0 remainder into Checking.
>> >
>> >
>> > > It seems to me that such transactions should be put into a QIF file
>> for
>> > > one of its related accounts. Perhaps it belong in a QIF file for the
>> > > "[Savings]" account, where it describes $1,400.00 coming in from
>> > > "Paycheck", -$100 going to "MetLife Auto", and -$100 going "Tax:Soc
>> > > Sec", with the remaining $1,200 implicitly being added to the
>> > > "[Savings]" account balance.
>> > >
>> > > Is it required that these two transactions be in the same QIF file of
>> > > the Checking account?  I am guessing that GnuCash's QIF import works
>> > > better if they are in separate files, for their corresponding
>> accounts.
>> > >
>> >
>> > Unfortunately, these are huge QIF files exported from Quicken 2006 (no
>> OFX
>> > available).
>> > I can certainly edit them before importing into Gnucash, if I can find
>> > simple rules to make the edits.
>> > This does not seem easy to fix up in an editor.
>> >
>> >
>> > >
>> > > Now, I do not use QIF files with GnuCash (for what it's worth, I use
>> OFX
>> > > files instead). I am not an expert in GnuCash's handling of QIF files.
>> > > But maybe getting everyone clear about the terminology will help. And
>> > > maybe my reasoning based on what I see in your examples might shed
>> some
>> > > light on what is really going on.
>> > >
>> > > Best regards,
>> > >        —Jim DeLaHunt
>> > >
>> > > On 2020-09-27 11:41, B Wooster wrote:
>> > >
>> > > > $0 split transactions are not getting imported from QIF correctly.
>> > > > Non-zero transactions get imported fine.
>> > > >
>> > > > The problem is gnucash is flipping what is a withdrawal vs a deposit
>> > for
>> > > $0
>> > > > splits.
>> > > > The following QIF file shows the incorrect import.
>> > > >
>> > > > The signs on the Savings transfer are the same on both transactions
>> > > below,
>> > > > but only the non-zero split gets imported correctly.
>> > > > If I manually edit the QIF to flip the signs on the "Incorrect
>> > > Withdrawal"
>> > > > transaction, it imports correctly.
>> > > > Correct import is that both transactions should deposit to the
>> Savings
>> > > > account, both are negative amounts in the input .qif.
>> > > >
>> > > > Does anyone know if that is a known issue? Or is this a bug?
>> > > > Or is there a workaround to avoid editing this type of QIF export
>> (from
>> > > old
>> > > > Quicken, only .QIF export available).
>> > > >
>> > > > Using GnuCash 3.10 Build ID: 3.10+(2020-04-11)
>> > > >
>> > > > [Am a new user, periodically I try to see if I can get 15+ years of
>> > > Quicken
>> > > > data into gnucash, have found a lot of info about problems with QIF
>> > > import
>> > > > issues, but still trying to see if I can get this to work every
>> year or
>> > > so!
>> > > > Fails a lot currently, this report is one of the problems.
>> > > > Even importing both Checking and Savings QIF file at same time,
>> instead
>> > > of
>> > > > one by one, causes this same problem.]
>> > > >
>> > > > test-chk.qif:
>> > > > !Type:Bank
>> > > > D6/ 8' 0
>> > > > U0.00
>> > > > T0.00
>> > > > CX
>> > > > POpening Balance
>> > > > L[Checking]
>> > > > ^
>> > > > D9/15' 2
>> > > > U0.00
>> > > > T0.00
>> > > > CX
>> > > > PPay Check Incorrect Withdrawal from Savings
>> > > > LSalary
>> > > > SSalary
>> > > > EPaycheck (before tax)
>> > > > $1,400.00
>> > > > S[Savings]
>> > > > ETransfer to Savings
>> > > > $-1,200.00
>> > > > SAuto:Insurance
>> > > > EMetLife Auto
>> > > > $-100.00
>> > > > STax:Soc Sec
>> > > > $-100.00
>> > > > ^
>> > > > D9/16' 2
>> > > > U1,200.00
>> > > > T1,200.00
>> > > > CX
>> > > > PPay Check Correct Deposit to Savings
>> > > > LSalary
>> > > > SSalary
>> > > > EPaycheck (before tax)
>> > > > $3,200.00
>> > > > S[Savings]
>> > > > ETransfer to Savings
>> > > > $-1,500.00
>> > > > SAuto:Insurance
>> > > > EMetLife Auto
>> > > > $-100.00
>> > > > STax:Soc Sec
>> > > > $-400.00
>> > > > ^
>> > > >
>> > > > =================================================
>> > > > After import:
>> > > >
>> > > > Account Summary 12/31/2020
>> > > >
>> > > > Code Account title Balance
>> > > > Auto $0.00
>> > > >        Insurance $0.00     *** should be 200
>> > > > Salary $1,800.00          *** should be 4600
>> > > > Tax $0.00
>> > > >        Soc Sec $300.00    *** should be 500
>> > > > Equity $0.00
>> > > >        Retained Earnings $0.00
>> > > > Checking $1,200.00  *** Correct
>> > > > Savings $300.00     *** Should be 2700
>> > > >
>> > > > ===================================================
>> > > > Transaction Report
>> > > >
>> > > > >From 01/01/2000 to 12/31/2020
>> > > >
>> > > > Date Num Description Memo/Notes Account Amount
>> > > > Insurance
>> > > > 09/15/2002 Pay Check Incorrect Withdrawal from Savings MetLife Auto
>> > > > Auto:Insurance $100.00
>> > > > 09/16/2002 Pay Check Correct Deposit to Savings MetLife Auto
>> > > Auto:Insurance
>> > > > -$100.00
>> > > > Total For Insurance $0.00
>> > > > Checking
>> > > > 06/08/2000 Opening Balance Checking $0.00
>> > > > 09/15/2002 Pay Check Incorrect Withdrawal from Savings Checking
>> $0.00
>> > > > 09/16/2002 Pay Check Correct Deposit to Savings Checking $1,200.00
>> > > > Total For Checking $1,200.00
>> > > > Retained Earnings
>> > > > 06/08/2000 Opening Balance Equity:Retained Earnings $0.00
>> > > > Total For Retained Earnings $0.00
>> > > > Salary
>> > > > 09/15/2002 Pay Check Incorrect Withdrawal from Savings Paycheck
>> (before
>> > > > tax) Salary -$1,400.00
>> > > > 09/16/2002 Pay Check Correct Deposit to Savings Paycheck (before
>> tax)
>> > > > Salary $3,200.00
>> > > > Total For Salary -$1,800.00
>> > > >
>> > > > Savings
>> > > > 09/15/2002 Pay Check Incorrect Withdrawal from Savings Transfer to
>> > > Savings
>> > > > Savings -$1,200.00    *** should be + not -
>> > > > 09/16/2002 Pay Check Correct Deposit to Savings Transfer to Savings
>> > > Savings
>> > > > $1,500.00
>> > > > Total For Savings $300.00
>> > > >
>> > > > Soc Sec
>> > > > 09/15/2002 Pay Check Incorrect Withdrawal from Savings Tax:Soc Sec
>> > > -$100.00
>> > > > 09/16/2002 Pay Check Correct Deposit to Savings Tax:Soc Sec $400.00
>> > > > Total For Soc Sec $300.00
>> > > > Grand Total $0.00
>> > > > _______________________________________________
>> > > > gnucash-user mailing list
>> > > > gnucash-user at gnucash.org
>> > > > To update your subscription preferences or to unsubscribe:
>> > > > https://lists.gnucash.org/mailman/listinfo/gnucash-user
>> > > > If you are using Nabble or Gmane, please see
>> > > https://wiki.gnucash.org/wiki/Mailing_Lists for more information.
>> > > > -----
>> > > > Please remember to CC this list on all your replies.
>> > > > You can do this by using Reply-To-List or Reply-All.
>> > > >
>> > > --
>> > > .   --Jim DeLaHunt, jdlh at jdlh.com     http://blog.jdlh.com/ (
>> > > http://jdlh.com/)
>> > >        multilingual websites consultant
>> > >
>> > >        355-1027 Davie St, Vancouver BC V6E 4L2, Canada
>> > >           Canada mobile +1-604-376-8953
>> > >
>> > > _______________________________________________
>> > > gnucash-user mailing list
>> > > gnucash-user at gnucash.org
>> > > To update your subscription preferences or to unsubscribe:
>> > > https://lists.gnucash.org/mailman/listinfo/gnucash-user
>> > > If you are using Nabble or Gmane, please see
>> > > https://wiki.gnucash.org/wiki/Mailing_Lists for more information.
>> > > -----
>> > > Please remember to CC this list on all your replies.
>> > > You can do this by using Reply-To-List or Reply-All.
>> > >
>> > _______________________________________________
>> > gnucash-user mailing list
>> > gnucash-user at gnucash.org
>> > To update your subscription preferences or to unsubscribe:
>> > https://lists.gnucash.org/mailman/listinfo/gnucash-user
>> > If you are using Nabble or Gmane, please see
>> > https://wiki.gnucash.org/wiki/Mailing_Lists for more information.
>> > -----
>> > Please remember to CC this list on all your replies.
>> > You can do this by using Reply-To-List or Reply-All.
>> >
>> _______________________________________________
>> gnucash-user mailing list
>> gnucash-user at gnucash.org
>> To update your subscription preferences or to unsubscribe:
>> https://lists.gnucash.org/mailman/listinfo/gnucash-user
>> If you are using Nabble or Gmane, please see
>> https://wiki.gnucash.org/wiki/Mailing_Lists for more information.
>> -----
>> Please remember to CC this list on all your replies.
>> You can do this by using Reply-To-List or Reply-All.
>>
>