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

David Carlson david.carlson.417 at gmail.com
Mon Sep 28 19:14:05 EDT 2020


That is interesting.  I had not seen that bug report.  It goes back to
GnuCash Release 2.2.something.  It seems like a real stretch to have a zero
value transaction in account 'C' to record a transfer between accounts 'A'
and 'B'. Quicken did and does allow some strange things  I don't recall
having that as an issue back in 2004 when I briefly tried Moneydance, then
another program before switching to GnuCash in 2007 or thereabouts.

I am not sure if that's the gist of the difference between Moneydance
exports and Quicken exports, But I would agree that GnuCash should prefer
to import Quicken files correctly over Moneydance files. Perhaps another
user might be interested in writing a Bash program to convert these
mal-formed QIF files to to something that GnuCash could import.  Another
thing to look into is whether it is still possible to find the QIF
conversion to CSV macro for MS Office or Libre Office.

On Mon, Sep 28, 2020 at 4:49 PM B Wooster <bwooster47 at gmail.com> 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.
>>>
>>

-- 
David Carlson


More information about the gnucash-user mailing list