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

Jim DeLaHunt list+gnucash at jdlh.com
Tue Sep 29 00:42:44 EDT 2020


I don't use QIF files, so I have no direct stake in this question. But, 
reading the discussion in bug 551459 
(https://bugs.gnucash.org/show_bug.cgi?id=551459), it seems to me that 
we have the following situation:

1. different bookkeeping applications (Quicken, Moneydance) use what is 
ostensibly the same QIF format in different ways (as Derek Atkins 
pointed out in comment #1)

2. The different uses of QIF are such that GnuCash import behaviour 
which works for one application fails for the other.

3. For Bug 105179 - QIF import reverses deposit/withdrawal - a change 
was made to GnuCash to favour Moneydance's use of QIF at the expense of 
Quicken's use of QIF. This change kept QIF import structured as a single 
unit with no choice of behaviour.

4. This doesn't seem like a difficult technical problem. We have samples 
of the different QIF files. I expect we can find where to change the 
code. Rather it's a problem of product definition and of interaction 
design. The product definition part is identifying how many Quicken 
users are harmed how severely by one behaviour, and how many Moneydance 
users are harmed how severely by the other behaviour. (BWooster alludes 
to this in comment 14.) The interaction design problem is to figure out 
how to let GnuCash accommodate both  behaviours in an easy-to-use way. 
An option on import?  Adding a second QIF for Moneydance option to the 
import menu?

5. And once there is agreement on the design of a fix, someone needs to 
do the development work. It will happen faster if someone with the right 
skills offers to do it.

6. We don't need to stay stuck with the current "Moneydance QIF works at 
the expense of Quicken users" situation, if we don't want to be. To 
answer a Quicken user's question: yes, there is hope.

       —Jim DeLaHunt

On 2020-09-28 14:48, B Wooster wrote:
> 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.
>>>
> _______________________________________________
> 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



More information about the gnucash-user mailing list