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

Sun Sep 27 21:40:38 EDT 2020

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.
>