trial balance - how to find mismatch question

[Christopher Lam]

I like the way this is going. Please describe or file minimal data file
cases in Bugzilla. Perhaps I'll be able to decode the trial balance and we
can decide then what it should really do.

On 16 Feb 2018 19:20, "David T. via gnucash-devel" <
gnucash-devel at gnucash.org> wrote:

> David, Adrien,
> I, too, have encountered many discrepancies in my books with the Trial
> Balance report. I was not able to hammer them out. I gave up, and rely on
> brokerage reports for gains, and assume that the imbalance in the report is
> due to my incomplete understanding of the principles behind it.
> Adrien, I mistakenly said that there would be an imbalance entry on the
> report, but you are noting precisely the discrepancy that I found.
>
> David T.
>
>
>   On Fri, Feb 16, 2018 at 15:58, David Carlson<david.carlson.417 at gmail.com>
> wrote:   Adrien,
>
> I am glad that you took the time to research how the report works for the
> data you were able to provide to it.
>
> You have found some valid concerns.  I had not looked at that report for
> quite some time.
>
> As another user with a lot of stock trades, I sometimes use that report to
> find an issue, although I have developed a process involving outside
> spreadsheets to calculate realized gains per security account as GnuCash
> calculates them and net gains as the IRS wants them, so I rarely need the
> report now.  The last time that I used the report I found that when I had a
> security account with not-so-simple combinations of lots being traded or
> when I had an account involving reinvested dividends it was still
> problematic whether I actually matched and resolved all the gains
> correctly.  I deliberately avoided going down the Trading Accounts rabbit
> hole at that time, but it may be worthwhile to give that a fresh look.
>
> Today I am using Windows GnuCash release 2.6.18, and I found that the
> report has changed since sometime in the early 2.6 series when I last used
> it.  For all I know, it may be different again in release 2.6.19 and in
> 2.7/3.0 coming out soon.
>
> Having given you my perspective, I did notice that the report currently
> uses the 'nearest in time' estimate of the date to use when extracting
> values from the currency and commodity tables.  That estimate is a poor
> method to use in my opinion because that often points to a future date when
> there is no value for the target date.  This happens frequently when target
> dates fall on weekends or holidays.  It is possible that Adrien's residual
> imbalance may be at least exacerbated by this point.
>
> Also, I want to thank David T for noticing that in my earlier comments I
> was referring to the unrealized gain imbalance value that this report
> produces rather than the Imbalance accounts that get created when
> individual transactions are incomplete as sometimes happens in transaction
> imports.  That difference is critical to this discussion.  I did fail to
> make that point clear then.
>
> For completeness I should also mention that there are other parameters or
> variables available to adjust in this report which may or may not result in
> differences in the final values reported by a certain instance of the
> report operating on a certain set of data.
>
> There is an option section on adjusting entries and closing entries.  How
> does the report identify these?  There are three report variations under
> the general tab and there is a whole new section on merchandising that
> could impact a report in an undesired way for users that accidentally
> format some data incorrectly.  There is no help to let the user ascertain
> how or if these things affect him.
>
> I will stop here.
>
>
> David C
>
> On Fri, Feb 16, 2018 at 2:20 AM, Adrien Monteleone <
> adrien.monteleone at gmail.com> wrote:
>
> > At least on my version of the Trial Balance report, there is no
> ‘Imbalance
> > entry’ specifically.
> >
> > There is at the bottom, the Imbalance-XXX and Orphan-XXX accounts listed
> > along with the others.
> >
> > There is also a line for ‘Unrealized Gains’ or ‘Unrealized Losses’ (I
> have
> > the latter, even though the report duration was a single day with no
> price
> > changes, I gave up trying to figure that one out)
> >
> > The ‘imbalance’ I’m speaking of trying to resolve, or at least finally
> > attributed to a rounding error with the XAG account, was simply the
> > difference between what the report shows as Total Debits & Total Credits.
> > (note, these aren’t labeled as such on the report - but they appear at
> the
> > bottom, and that’s clearly what they are) There is no figure on the
> report
> > that shows this difference. I had to calculate it manually. When I
> decided
> > to audit the report for each account is when I found the foreign currency
> > account out of whack. The remaining difference was attributable entirely
> to
> > the ‘unrealized losses’ line.
> >
> > So, the full difference between debits and credits is the SUM of the
> > ‘Unrealized Gains/Losses’ line and the discrepancy due to rounding.
> >
> > At least in my case.
> >
> > So there are two problems with the report:
> >
> > 1) There should be no losses or gains if there were no trading
> > transactions. Certainly, this is impossible if there is only one day on
> the
> > range of the report and the price is the same. If all you have are
> opening
> > entries and you attempt to run a trial balance for that same day, you
> can’t
> > have either a gain or a loss, unrealized or not.
> >
> > 2) Because the Equity:Opening Balances account is the result of rounded
> > figures for each entry in a foreign currency, the report’s method of
> taking
> > the foreign currency ending balance as of a date and doing the exchange
> > rate calculation at the end, will always produce a discrepancy. The
> report
> > would have to sum the book-default currency amounts individually or
> somehow
> > a book-default currency balance would have to be maintained and that used
> > instead. Alternatively, a foreign currency account could use the same
> > precision as the foreign currency itself, thus removing the potential for
> > rounding errors if not eliminating them.
> >
> > Possibly, increasing the decimal places and re-entering the transactions
> > for the XAG account might resolve the rounding issue. (only because now
> the
> > USD amounts sum correctly to match since they don’t round enough) But
> then
> > ALL USD accounts would have this extra precision which is not desirable
> > generally.
> >
> > The alternative would be to reduce the precision of the XAG account, but
> > again, that is undesirable for accurate tracking of ownership quantities
> of
> > the actual metal. (or currency if that’s the case)
> >
> > The per-account precision setting seems to only affect the default
> > currency for that account, in this case, XAG, not USD, which seems only
> to
> > be controlled by the book setting.
> >
> >
> > Regards,
> > Adrien
> >
> > > On Feb 15, 2018, at 10:55 PM, David T. <sunfish62 at yahoo.com> wrote:
> > >
> > > I don’t believe I’ve seen anywhere in this thread any attempt to
> explain
> > that there is a difference between IMBALANCE-XXX (an indication that you
> > have transactions that lacked a balancing split) and the Imbalance entry
> in
> > the Trial Balance report. This latter most likely indicates (as David C.
> > has hinted) that your books have capital or currency gains or losses that
> > haven’t been entered into the books. If you buy a stock for $100 using a
> > balanced transaction, and later sell that share for $150 (we wish!) in a
> > balanced transaction, GnuCash will wonder where you got an additional
> $50.
> > Both transactions balance, but the books don’t. That is why you usually
> > have an entry (either as a separate transaction, or as splits in the sell
> > transaction) that account for this gain.
> > >
> > > Of course, it can get complex.
> > >
> > > David T.
> > >
> > >> On Feb 16, 2018, at 7:31 AM, Christopher Lam <
> christopher.lck at gmail.com>
> > wrote:
> > >>
> > >> Hi Adrien, could you distil this to a minimal test file and submit in
> a
> > bug
> > >> report and include relevant report and report parameters? C
> > >>
> > >> On 16 Feb 2018 10:09, "Adrien Monteleone" <
> adrien.monteleone at gmail.com>
> > >> wrote:
> > >>
> > >>> How timely.
> > >>>
> > >>> Any way to solve this or do I just chalk it up?
> > >>>
> > >>> Regards,
> > >>> Adrien
> > >>>
> > >>>> On Feb 15, 2018, at 8:00 PM, David Carlson <
> > david.carlson.417 at gmail.com>
> > >>> wrote:
> > >>>>
> > >>>> If you have multiple currencies  or if you buy and sell commodities
> or
> > >>> securities  there is another level of opportunities for issues.
> > >>>>
> > >>>> David  C
> > >>>>
> > >>>> On Feb 15, 2018 5:55 PM, "Adrien Monteleone" <
> > >>> adrien.monteleone at gmail.com> wrote:
> > >>>> I just noticed the subject was wrong due to a user-digest error,
> > >>> re-applying the original.
> > >>>>
> > >>>> ———
> > >>>>
> > >>>> I’m having a bit of issue understanding the point of the
> trial-balance
> > >>> report in modern times. (I generally don’t use it as I mentioned)
> > >>>>
> > >>>> If each transaction self-balances, that is, debits = credits, how is
> > it
> > >>> possible to add up the debits individually and the credits
> > individually and
> > >>> not get a result that still balances? You can’t add up 1+2+3 = 5 and
> > 1+2+3
> > >>> = 6. It’s a mathematical impossibility.
> > >>>>
> > >>>> In addition, if you enter a transaction that doesn’t balance,
> Gnucash
> > >>> forces it to balance by using either the imbalance or orphan
> accounts.
> > So
> > >>> at least you’re alerted to amounts you need to fix, but technically,
> > debits
> > >>> still equal credits.
> > >>>>
> > >>>> Let’s assume I entered a transaction backwards and debited my cash
> > >>> account instead of crediting it, and credited an expense account
> > instead of
> > >>> debiting it. This should not affect the trial-balance. Sure, the
> > amounts
> > >>> are wrong for each account, but they still balance. Debits still
> equal
> > >>> credits.
> > >>>>
> > >>>> If I transpose two digits (the divisible by ‘9’ trick) then as long
> as
> > >>> my individual transaction I did this in balances, it still shouldn’t
> > show
> > >>> up as an imbalance on the trial-balance report. The other side would
> > ALSO
> > >>> have to be transposed or incorrect to match it. Gnucash won’t save
> the
> > >>> transaction unless it’s balanced.
> > >>>>
> > >>>> I can’t see what possible error could produce individually balanced
> > >>> transactions (required by Gnucash) and yet still have a trial-balance
> > where
> > >>> the debits do not equal credits AND both the imbalance account and
> > orphan
> > >>> accounts are empty.
> > >>>>
> > >>>> (note, I understand why this report is run with paper records
> because
> > >>> you’re copying stuff all over the place and might do so incorrectly.
> > But
> > >>> this isn’t the case with Gnucash)
> > >>>>
> > >>>> Regards,
> > >>>> Adrien
> > >>>>
> > >>>>> On Feb 15, 2018, at 5:06 PM, Adrien Monteleone <
> > >>> adrien.monteleone at gmail.com> wrote:
> > >>>>>
> > >>>>> The 1899 date seems to make me think that has something to do with
> a
> > >>> setting in your OS concerning how to interpret 2-year dates. (I don’t
> > think
> > >>> Gnucash has this option, it might be hard coded)
> > >>>>>
> > >>>>> Try running the report again and make sure to enter the full
> 4-digit
> > >>> date and not just ’16’. Also, test with 12/30/2016 and 01/01/2017 and
> > see
> > >>> if it does the same thing or only on exactly 12/31/2016. You might
> have
> > >>> some corrupt data. That might even be the source date of the
> imbalance.
> > >>>>>
> > >>>>> I just entered those two dates in the same report and it worked
> fine,
> > >>> so this wouldn’t be a generic bug to the app, though it might be a
> bug
> > for
> > >>> a certain platform. (I’m on macOS 10.13 at the moment)
> > >>>>>
> > >>>>> Since you’ve narrowed down to a few weeks, I’d just take a look at
> > the
> > >>> General Ledger which contains all transactions from all accounts.
> > Click on
> > >>> View > Filter By… and set your date range. (maybe even a day before
> and
> > >>> after just to be thorough) Also be sure to check all boxes on the
> > Status
> > >>> tab in the Filter options. You want to see all transactions for that
> > date
> > >>> range. Then just look over them and see if anything looks out of
> > place. In
> > >>> particular, look for either the amount you are out of balance by or
> > half
> > >>> that amount if the variance is an even number. (meaning you have an
> > entry
> > >>> that is entered in reverse debit/credit, or entirely duplicated)
> > >>>>>
> > >>>>> For myself, I think I want to tackle one other area first. I recall
> > >>> doing some re-organization and I went through a series of unposting
> > paid
> > >>> invoices and reposting them, causing my lot assignments to get out of
> > >>> whack. I know I have some open lots that shouldn’t be there and many
> > that
> > >>> are applied to the wrong documents. I’ll clean that up first, then
> > re-run
> > >>> the trial balance and see if that does the trick. I’m not sure where
> > the
> > >>> Trial-Balance report is pulling its numbers from, but it won’t hurt
> to
> > >>> perform that cleanup anyway.
> > >>>>>
> > >>>>> Regards,
> > >>>>> Adrien
> > >>>>>
> > >>>>>> On Feb 15, 2018, at 2:35 PM, Elmar <etschme at gmail.com> wrote:
> > >>>>>>
> > >>>>>>
> > >>>>>>
> > >>>>>> On 02/15/2018 02:28 PM, gnucash-user-request at gnucash.org wrote:
> > >>>>>>> ------------------------------------------------------------
> > >>> ----------
> > >>>>>>>
> > >>>>>>> Message: 1
> > >>>>>>> Date: Thu, 15 Feb 2018 13:27:17 -0600
> > >>>>>>> From: Adrien Monteleone <adrien.monteleone at gmail.com>
> > >>>>>>> To: "gnucash-user at gnucash.org" <gnucash-user at gnucash.org>
> > >>>>>>> Subject: Re: trial balance - how to find mismatch question
> > >>>>>>> Message-ID: <823DA1A8-B449-470F-AB10-E665056865EB at gmail.com>
> > >>>>>>> Content-Type: text/plain;  charset=utf-8
> > >>>>>>> ...
> > >>>>>>>
> > >>>>>>> Elmar,
> > >>>>>>>
> > >>>>>>> Reduce your ending date so the range is half of what it was.
> Re-run
> > >>> the report. Is it s[t]ill out of balance? Keep doing this till you
> get
> > a
> > >>> balance, then set that ending date to a new start date, and start
> > working
> > >>> forwards till you get out of balance again. This will help you narrow
> > down
> > >>> where on the calendar the error occurred.
> > >>>>>>>
> > >>>>>>> Regards,
> > >>>>>>> Adrien
> > >>>>>> OK - did that and ran into something VERY strange.  Everything
> > stayed
> > >>> in balance up to 07/12/2016.  Setting that as the start date and
> > 12/31/2016
> > >>> as the end date, as soon as I hit "apply", QC overwrites the end date
> > with
> > >>> - get this - 02/27/1899 !!!  This of course produces nonsense.  One
> day
> > >>> later (01/01/2017) works fine and shows the imbalance.  So, have I 1)
> > found
> > >>> a bug, and 2) given I have narrowed the date range to a few weeks,
> what
> > >>> report should I run to find the weirdness? - Elmar
> > >>>>>> _______________________________________________
> > >>>>>> 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-devel mailing list
> > >> gnucash-devel at gnucash.org
> > >> https://lists.gnucash.org/mailman/listinfo/gnucash-devel
> > >
> >
> > _______________________________________________
> > gnucash-devel mailing list
> > gnucash-devel at gnucash.org
> > https://lists.gnucash.org/mailman/listinfo/gnucash-devel
> >
> _______________________________________________
> gnucash-devel mailing list
> gnucash-devel at gnucash.org
> https://lists.gnucash.org/mailman/listinfo/gnucash-devel
>
> _______________________________________________
> gnucash-devel mailing list
> gnucash-devel at gnucash.org
> https://lists.gnucash.org/mailman/listinfo/gnucash-devel
>