trial balance - how to find mismatch question

[Deva -]

I am seeing huge imbalances in my trial balance report as well. This is one report I have not been sending to my tax professional until I understand how to interpret it.

This behaviour is seen in 2.6.19 currently, but I am sure I have seen it in previous versions as well (I have been on GC since 2.6.6).

Though I started using GC only since 2015, I actually entered past 18 years of my transaction history from annual spreadsheets I used to maintain before. After reading this thread, I went back year after year change the end date until I hit the first mismatch between credits and debits - just over INR 70 as of Mar 2009. Progressively after that, this report now shows a difference of over INR 600,000!

There’s nothing in imbalance or orphan accounts and my entire datafile is in single currency (INR).

Only thing I can see from the time this first mismatch popped up in 2009 till now is that I have had capital gains from sale of shares since 2009. Though I recorded these gains carefully each year manually from statements, etc., the difference still shows up.

One reason I can think of is this -

When I run the trial balance report (or balance sheet report), the options given for commodity pricing are: average cost, weightage average, nearest in time and most recent. However, when computing capital gains, Indian tax authorities will only accept gains/losses computed on a FIFO basis (FIFO option can be selected when running Advanced Portfolio Report).

Since FIFO is not one of the options I can select for commodity pricing in trial balance and balance sheet reports, I am guessing the difference in credits and debits  is coming from cost basis determined by FIFO and average cost basis methods?

Cheers.

On 16-Feb-2018, at 7:32 AM, gnucash-user-request at gnucash.org<mailto:gnucash-user-request at gnucash.org> wrote:

It seems at least in my case, the imbalance really appears to be Gnucash?s fault.

I narrowed down the date to my opening date. This should be easy right? Not so fast.

Of course, everything is in balance as I expected it to be.

So I decided to compare the report totals for each account to the individual amounts in the accounts themselves.

Bingo.

The Trial Balance report shows the wrong total for one account, by the amount of the discrepancy listed on the report.

The account in question is an asset account for silver bullion held in ounces of XAG. (not entirely appropriate I?m sure, but since I can?t create my own currency, this is the best I could muster and the price is reasonably close for my purposes)

I was retrieving prices automatically, but can?t seem to get finance quote to consistently work, so I just enter the numbers by hand each month. (I?m not an active trader, this is on-hand stuff) The very first price, I set the same as my opening book date and it is manually entered.

It seems Gnucash is shortcutting on this report and causing a rounding error. The commodity itself is held accurate to 6 decimal places, but the USD conversion rounds off to whole ?. I don?t want to add decimals for all of my accounts just to satisfy that report. (which I never use anyway)

There are several opening balance lines in this account for each type of bullion I had on that date. The trial balance report is taking the total ounces, converting to dollars and using that figure. It is not using the individual USD figures it calculated in order to balance against the equity account.

Therefore, this report will always be off and will always tell me I?m out of balance, even when I?m not.

(it incidentally reports unrealized losses, presumably based on that account, but since it?s only for one day, I can?t fathom how such a calculation is possible)

Regards,
Adrien

On Feb 15, 2018, at 5:52 PM, Adrien Monteleone <adrien.monteleone at gmail.com<mailto: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<mailto: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<mailto:etschme at gmail.com>> wrote:



On 02/15/2018 02:28 PM, gnucash-user-request at gnucash.org<mailto: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<mailto:adrien.monteleone at gmail.com>>
To: "gnucash-user at gnucash.org<mailto:gnucash-user at gnucash.org>" <gnucash-user at gnucash.org<mailto:gnucash-user at gnucash.org>>
Subject: Re: trial balance - how to find mismatch question
Message-ID: <823DA1A8-B449-470F-AB10-E665056865EB at gmail.com<mailto: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<mailto: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.