0

I've been running this GNC file for over two years now. I close the books each year so that I can get YTD totals out of expense & income accounts. (see here). I have one prior year closing for 2020.

I don't recall having this problem last year when I closed, but it's possible and I just missed it or forgot to go into detail. Or got so befuddled I just moved on.

I closed 2021 and so now I have a balance sheet (eguile) that only contains Assets, Liabilities, and Equity. (Since all Expense and Income accounts are zeroed).

That's nice and simplifying, but alas the last line of the report states: Imbalance Amount $10,000.00 (I rounded since the amount isn't important here.)

Ok... so I tried to assume that's some Equity account out of whack somewhere, but I ran the check & repair function on all accounts (Actions->Check & Repair->C&R All). Nothing changed.

Now I thought about just putting a balancing transaction in -- oh, I don't know: Equity::Opening Balances? Just to see if I can get rid of it. But naturally... as with everything else... I can only enter a double-entry transaction. So if I do so, it still shows up as an imbalance.

Can't expense it: same reason.

So I wonder how I can get out of this.

And I wonder how it ever got messed up in the first place if every entry has a matching balancing entry.

I suspect stock trading noise, but I don't know how to go about fixing that problem. I have lots of securities and transactions to go with them. When I enter a transaction, a new price appears in the price editor for that security. I read a few threads about currency exchange problems and I think GNC handles securities using the currency exchange mechanism, but I clearly don't yet fully understand it all.

P.S. As an experiment, I tried changing some prices for one security in the price editor. It didn't make any difference in the Balance Sheet.

P.P.S. Added an image to go with a comment (can´t add images in comments.)enter image description here

6
  • imbalance is an actual account in the account tree, so you can potentially generate that balancing equity transaction. I've been doing this with occasional imbalance (in my case it was due to miscalculation of mortgage interest by GC)
    – littleadv
    Jan 20, 2022 at 21:42
  • No, it's not the imbalance account. That and the orphan accounts are both zero. This shows as a line on the report and shows A - L - E. There's another line showing total Equity, Trading, and Liabilities. Trading is zero on this report, so that line subtracted from Assets also yields "Imbalance Amount"
    – fbicknel
    Jan 20, 2022 at 21:51
  • 1
    @GregSchmidt, I keep my GNC data in a Git repo, so I absolutely can try that. I am looking forward to getting to that using (at)OneTruDragonGirl's technique below.
    – fbicknel
    Jan 22, 2022 at 11:27
  • 1
    After much messing around with security prices, deleting negative (!) prices and prices obviously wrong or out of line without too much digging, and then getting historical prices for 12/31/20, I finally have a trial balance with unrealized gains (ok better than losses I had before) and things are making sense. Turns out, there were a few bad transactions, but on the order of pennies, not $10K. The rest was fixing the security prices. Yay! This has been an interesting journey. Big thanks to @OneTrueDragonGirl for the suggestions.
    – fbicknel
    Jan 25, 2022 at 22:52
  • 1
    @fbicknel - I'm so glad this worked out for you! Jan 28, 2022 at 0:00

1 Answer 1

3

Step zero is to backup you GnuCash file before fussing with it. :)

First, make sure your balance sheet reports include all accounts, including hidden accounts. It's easy to open a saved report and not realize that you've since created a new account or two totaling $10,000.

Then, you can find what went wrong by using the Trial Balance report (Reports > Income and Expense > Trial Balance). In the report options, go to top tab Accounts > check Show Hidden Accounts, then click Apply. If the trial balance is out of balance today (debits and credits two different totals), then you probably have a broken transaction.

In the General tab, change the Date of Report on the trial balance. Set it to Jan. 1, 2021. If it was out of balance then, you know the transaction that broke is older. If not, you know it was a newer transaction. If Jan 2021 was in balance, set the date to June 30, 2021. If not, set the date to January 1, 2020. You can keep changing the report date. Each time you check another date, you can narrow down when the problem occurred. Split the remaining period in approximately half each time. Eventually, you will find the exact date of the broken transaction. So, you might see that on Oct. 2, 2021, the trial balance was in balance, and on Oct. 3, 2021, the trial balance was out of balance. Then you know the problem started on Oct. 3. Subtract total credits from total debits on the trial balance to find out how much the difference was on that date. You may have multiple broken transactions, so don't just assume the full $10,000 happened on one day.

Next, for the date when the problem started, run the transactions for that date.

  • I like to use Find (Edit > Find) and set the search criteria as Date Posted - is on - Oct. 3, 2021. It doesn't allow you to edit options easily, but it has a very clean display and it is quick to use.
  • Alternatively, use a Reports > Transaction Report. Again, for this you will need to include hidden accounts, then use the Select All Accounts button. Set the date as the date the problem started, and change the Sorting tab to have as Primary Key - Register Order. This one allows you to change the dates in report options and hit Apply.

Look over the transactions for that date. See if any match in amount to the difference between your debits and credits on the trial balance on that date. If you only have four transactions, and one has part that matches the amount, then you've got it. Somehow, even if it looks like that transaction is balanced, something about it is broken. Make note of what it was, then:

  1. delete that transaction
  2. refresh the trial balance for that date, confirm that it now balances
  3. go back into one of your registers (that stock register, if it was a stock transaction) and recreate that transaction, making sure the transaction balances
  4. refresh the trial balance again, confirming that the trial balance still balances
  5. set the trial balance to today's date and see if it still balance; this determines if you need to repeat the finding process for a later date or if your books are now balanced

Once your books are balanced, go back to December 21, 2021 and see if your old book closing transactions still work or if you need to fix them. Making this correction might mean that there is now more or less capital gain (or some other income/loss difference) in your 2021 books, which means the closing transactions might need to be corrected. The easiest way might just be to delete those closing transactions and recreate them, but that is up to your judgement.

Source: I don't close my books, but I had to go through this same process to fix them. I had two stock trading transactions that were broken. I could have sworn there was something on the GnuCash website that helped me, but I can't find it now.

5
  • Great problem solving technique and super how-to explanation. Thanks. I can't wait to try it. :)
    – fbicknel
    Jan 22, 2022 at 11:22
  • I did manage to locate the first transaction with a problem. A stock sale with the wrong cap gains (off by $.03, so easy to find with your help.) I also located a source of confusion amongst the 3 types of bal sheets: trial bal, bal sheet, and bal sheet (eguile). First two show ¨unrealized gain¨. Last one shows ¨Imbalance Amount¨. These all match. So now I need to figure out what´s going on there.
    – fbicknel
    Jan 25, 2022 at 13:14
  • Another transaction was a simple opening balance of stock from 2018: all the numbers looked perfect, including balances in the stock register and in the opening balance register. Yet the Balance Sheet Series (BSS) all showed that on this day, everything was off by $.01. (Another easy one to find.) I deleted the transaction and added it back: the BSS now balances on that day... Ok, I think I got the pattern. Looks like lots of work ahead. :)
    – fbicknel
    Jan 25, 2022 at 13:27
  • See screenshot in OP question. So on 3/14/19, another $.01 imbalance/unrealized loss popped up. I ran a t.r. for that day and it shows no transactions. I also note that below on the report there's a section showing exchange rates used for this report -- and these show exactly the same values for 3/13 as for 3/14 for the ONE stock on those two dates. (another simplifying factor) Now I've hit a dead end: nothing seems to be changeable on that date to fix the imbalance.
    – fbicknel
    Jan 25, 2022 at 13:46
  • Ah. I see what happens. I had closest to report date selected for commodity prices. If I change that to last up through report date, then the imbalance appears on 6/8/19, which is exactly the date of a new price for that stock in the price database for securities. I'm betting that most of my problems later on are price differences for securities. I have to figure out somehow what this all means and how to reconcile it (not in the sense of register reconciling).
    – fbicknel
    Jan 25, 2022 at 14:09

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .