Trial Balance Report with Investment Transactions

[Chris Good]

Hi Richard,

 

(Thanks David C for adding all that useful info.:))

 

I’ll add…

 

What do you mean by: “This test was done in the Default test condition”?

 

Back to the drawing board indeed…

 

I’ve done some testing in 2.6.15 and found that my Trial Balance still balances regardless of if I use Commodity Average Cost or Nearest In Time Cost, although I’ve only had a few sales, and never had multiple purchases of the same stock on the same day for different costs. I also only use 1 currency. It will still be useful to hear from Mike Churchil if using Average Cost fixes his problem.

 

Trial Balance problems are cumulative. Assuming it is not a GnuCash bug in the Trial Balance, if the Trial Balance is out of balance, you need to find the earliest transaction (sale and/or capital gain) that forces it to go out of balance, fix that if you can, then continue the same way fixing any other problems. If you cannot figure out what is wrong, then ask on this mailing list, and if we cannot explain, it may be necessary to raise a bug.

 

You seem to have misinterpreted my manual reference as 2 separate references. Please check the latest manual again and follow the procedures for identifying the first problem transaction in the “Tip” [1]. It should be a sale (and its related gain/loss transaction) unless something is really wacky.

 

You should use a “binary search” to identify the date the trial balance first goes out of balance. I.e. Pick a date midway between the date of your first and last transactions and run a TB as at that date. If it still doesn’t balance, repeat using a date midway between your first transaction date and the last date you picked. If it does balance, pick a new date midway between your last pick date and your last transaction date. Using this method, each run eliminates half of the date range. In not many runs, you will know exactly which date contains transactions that first cause the TB to go out of balance. Sorry if I’m telling you something you already know. Too much info is better than not enough.

 

If you are not familiar with the term “Scrubbing” then you have not read the latest manual. You may not wish, or be able (due to points listed in “Considerations”), to use “Scrubbing” to automatically calculate and create capital gain transactions, but the theory may help you understand the problem. If you are able to use scrubbing, then using GnuCash to calculate and create the capital gain transactions (after deleting your manually entered capital gain transactions) may show you where you are going wrong.

 

Your screen dumps are not terribly helpful. You haven’t resized the columns so that the full account codes show, and you are using really long account codes. That’s OK and is your choice but it does make it much harder for you to see all the details and to show all the details in screen dumps. I suggest you use abbreviations to make them much shorter. You can rename the account segments and the transactions will automatically use the new names.

 

Trial Balance problems to do with investments, are usually from incorrectly accounting for the gain or loss from sales. Stock Market pricing should have no effect on the gain or loss. It is only the price paid for acquisitions (including DRIP’s) and the price received for sales that determine the gain or loss. Acquisitions should have no effect on the Trial Balance difference between total debits and total credits. Only Sales with incorrect gain/loss values should have any effect.

 

[1] https://www.gnucash.org/docs/v2.6/C/gnucash-guide/invest-sell1.html#invest-sellTrialBal

 

Regards, Chris Good

 

From: David Carlson [mailto:david.carlson.417 at gmail.com] 
Sent: Monday, 6 February 2017 1:27 AM
To: Richard Lindgren <rlindgren74 at gmail.com>
Cc: Chris Good <chris.good at ozemail.com.au>; gnucash-user at gnucash.org
Subject: Re: Trial Balance Report with Investment Transactions

 

Richard,

I do not have release 2.6.15 running here yet so I cannot confirm your issue, but I have used the Trial Balance report in the past quite successfully.  Michael's initial response that release 2.6.15 gives different numbers than previous versions is troubling.

First, it looks like a bug in release 2.6.15 to me because the trial balance report should only look at actual transactions and not look at the commodity prices,  If you only have purchase transactions, it will simply add up all the shares purchased and the money spent.  There should not be any mis-match and there should be no difference between GnuCash releases.  

The need for "adjustment" only arises when there is a sale or closing transaction.  Then the adjustment is exactly the difference between the amount spent for the shares sold and the amount received. This amount is the Realized Gain.  For most cases, the broker's statement (in the US, at least) will give you the number that you need to use for the adjustment.  GnuCash users who track commissions separately have another problem, but if you use net cost in purchases and net proceeds in sales, it always works.

The concept is to match the number of shares with the money spent on them.  I think that accountants call this the cost basis.

 

Your example is simple enough that you could take a pencil and paper or use a spreadsheet to manually calculate the correct values and see if GnuCash is in error and the exact amount of the error.

To manually calculate the adjustment, find the transactions in the security account for the purchase and sale of the security in question.  Add up all the money spent on the shares when purchased and subtract that number from the money received in the sale.  That is the realized gain and it needs to be used to adjust the cost basis for that security account.  If all the shares are sold the calculation is easy, but for partial sales, you need to use lot tracking to match which shares are sold.  That is a topic for advanced users.

If GnuCash release 2.6.15 does not make this calculation correctly, finding the amount of the error may help in reporting the bug, but the next step for you would be to switch back to an earlier release of GnuCash.  

I hope this helps.

David C

 

On Sun, Feb 5, 2017 at 3:35 AM, Richard Lindgren <rlindgren74 at gmail.com <mailto:rlindgren74 at gmail.com> > wrote:

Good Morning

I tried your suggestion and even tried all four (4) Commodity options, both
before and after my test of the new entry. None of these worked. My test
was duplicating what I sent you in the previous email. Sorry., I was hoping
that this would work. This test was done in the Default test condition.

Back to the Drawing Board?

Richard

On Sun, Feb 5, 2017 at 3:48 AM, Chris Good <chris.good at ozemail.com.au <mailto:chris.good at ozemail.com.au> >
wrote:

> Hi Richard & Mike (Churchil),
>
>
>
> As Mike said (*Thu Feb 2 13:49:32 EST 2017) *the 2.6.14 Trial Balance was
> OK but the 2.6.15 was not,
>
> I investigated what may have changed in 2.6.15.
>
>
>
> I found the Trial Balance default commodity price source report option was
> changed from Average Cost
>
> to Nearest In Time in maint commit 332bd3bb3efeb4c09ca686aa445df554dc656092
> on 10 Dec 2016 for
>
> https://bugzilla.gnome.org/show_bug.cgi?id=340991 Default price source
> for reports not good.
>
>
>
> Could you please both retry the Trial Balance after changing the Price
> Source on the Report Options
>
> Commodity tab, back to Average Cost?
>
> Richard, you may have to remove the transactions you added to make the
> report balance - try this in a test copy of your data file.
>
>
>
> Regards, Chris Good
>
>
>
> *From:* Richard Lindgren [mailto:rlindgren74 at gmail.com <mailto:rlindgren74 at gmail.com> ]
> *Sent:* Saturday, 4 February 2017 8:57 PM
> *To:* Chris Good <chris.good at ozemail.com.au <mailto:chris.good at ozemail.com.au> >
> *Cc:* gnucash-user at gnucash.org <mailto:gnucash-user at gnucash.org> 
> *Subject:* Re: Trial Balance Report with Investment Transactions
>
>
>
> Chris
>
>
>
> First off, thank you for getting back to me. This all started when I
> decided to run a Trial Balance on my program. I had not done this in a few
> years...don't ask me why. It always balanced before but now it was off. I
> am currently running GnuCash 2.6.15. This copy was built from git rev
> 1ef17e6+ on 2016-12-17.
>
>
>
> You have given me many questions to answer, and some of these terms I'm
> really not even familiar with. So I will try to give you answers to your
> questions.
>
>
>
> 1 - *Have you followed the suggestions in the GnuCash Tutorial and
> Concepts*
>
> *Guide,*
>
> *​ *
>
> *Part II.*
>
> ​ ​I set my investments using the "
>
> GnuCash Tutorial and Concepts
>
> ​ Guide
>
> ,
>
> ​" I'm not aware of the Part II version, only the version with GnuCash
> 2.6.15​. In fact, I have copied this and refer to it regularly.
>
>
>
> 2 -
>
> *Managing Personal Finances, Ch 9 Investments, Selling Shares,*
>
> *​ *
>
> *Automatic Calculation of Capital Gain or Loss Using Lots, Run a Trial*
>
> *Balance, Tip*
>
> *​ *
>
> *to try to determine exactly which sale transaction(s) make the Trial
> Balance*
>
> *​ *
>
> *go out of balance?*
>
> *​ *Yes I have read this and have my program to automatically update my
> portfolio. I go to tools, price editor, get quotes and then I close the
> screen, go to my portfolio and run an update. My values are then all
> changed according the the results of the new quotes received. ​Yes I am
> able to make the trial balance go out of balance.
>
>
>
> 3 -* Have you used scrubbing to create capital gain transactions or have
> you*
> *manually entered them?* I really am not familiar with the term scrubbing
> to create capital gain transactions. I have in the past entered all of the
> investment transaction manually.
>
>
>
> 4 -
>
> *If you are using scrubbing, have you ensured you have complied with all
> the*
>
> *points in the following "Considerations" paragraph?*
>
> *​* I guess this is something that I don't use do to my not being
> familiar with scrubbing. ​
>
>
>
> 5 -
>
> *Are you using multiple currencies or trading accounts?*
>
> *​ *I am only using the American dollar.
>
>
>
> ​I have attached a shot of one of my DRIP mutual fund investment
> transactions for 12/16/16 that I have manually entered in . You will also
> see that I had to manually enter in an account adjustment to force the
> Trial Balance to balance out. Could my values be changing due to the change
> in stock market pricing? My amount of stocks owned seems to always be there
> and my portfolio values seem to be be true as compared to my Scottrade
> positions. However my Trial Balance does not agree....? Every time a have a
> dividend reinvestment now or I sell a share, I have to enter a correction
>  so the Trial Balance will come out okay. Weird - to say the least!
>
>
>
>
>
> [image: Inline image 3]
>
>
>
> The view below shows where I entered a dividend reinvestment and my Trial
> Balance does not balance out. Have I entered something wrong?
>
>
>
> [image: Inline image 1]
>
>
>
> ​​
>
> ​Any help here will certainly be appreciated.​
>
>
>
> Thank you in advance for your help.
>
>
>
> Richard
>
>
>
>
>
> On Fri, Feb 3, 2017 at 8:01 PM, Chris Good <chris.good at ozemail.com.au <mailto:chris.good at ozemail.com.au> >
> wrote:
>
> > Message: 5
> > Date: Thu, 02 Feb 2017 13:07:10 -0500
> > From: Richard Lindgren <rlindgren74 at gmail.com <mailto:rlindgren74 at gmail.com> >
> > To: gnucash-user at gnucash.org <mailto:gnucash-user at gnucash.org> 
> > Cc: gnucash-user at gnucash.org <mailto:gnucash-user at gnucash.org> 
> > Subject: Trial Balance Report with Investment Transactions
> > Message-ID: <28cd077c-e8f3-4b53-80a0-5bb71474cab0 at typeapp.com <mailto:28cd077c-e8f3-4b53-80a0-5bb71474cab0 at typeapp.com> >
> > Content-Type: text/plain; charset=UTF-8
>
> >
> > Hi,

> >
> > Richard Lindgren <rlindgren74 at gmail.com <mailto:rlindgren74 at gmail.com> > writes:
> >
> > I have a problem with the Trial Balance report relative to incorporating
> my
> > investments. My regular accounting portion seems to be okay, but when it
> > comes to using the Trial Balance with my investment transactions there is
> > always a discrepancy!
> >
> > It doesn't seem to matter whether it is a Drip fund account a purchase or
> sell
> > transaction. It keeps track of the number of shares okay, but when it
> comes
> > to the actual dollars...it's wrong. I'm not saying that the gnucash
> program is
> > inaccurate, I'm saying I am EXTREMELY frustrated and can not figure what
> I'm
> > doing wrong. Maybe I have set up the various accounts wrong! I need help
> > here before I switch to something else.
> >
> > Please help me?
> >
> > Thank you in advance for your help in this matter, as it is VERY
> important
> to
> > me.
> >
> > Richard Lindgren
>
> Hi Richard,
>
> Have you followed the suggestions in the GnuCash Tutorial and Concepts
> Guide,
> Part II. Managing Personal Finances, Ch 9 Investments, Selling Shares,
> Automatic Calculation of Capital Gain or Loss Using Lots, Run a Trial
> Balance, Tip
> to try to determine exactly which sale transaction(s) make the Trial
> Balance
> go out of balance?
>
> You need to give us something to work with to help you.
> If you identify a particular sale/capital gain that first causes the trial
> balance problem, and you cannot figure out why,
> please provide all the costs for all buys and sells up to and including the
> sale, and the sale and capital gain transaction info,
> so we can check if you or GnuCash is doing something wrong.
>
> It would be even better if you could provide a test (depersonalised)
> GnuCash
> file which shows the problem so there are no misunderstandings.
>
> Have you used scrubbing to create capital gain transactions or have you
> manually entered them?
> If you are using scrubbing, have you ensured you have complied with all the
> points in the following "Considerations" paragraph?
> Are you using multiple currencies or trading accounts?
>
> Regards, Chris Good
>
>
>


_______________________________________________
gnucash-user mailing list
gnucash-user at gnucash.org <mailto:gnucash-user at gnucash.org> 
https://lists.gnucash.org/mailman/listinfo/gnucash-user
-----
Please remember to CC this list on all your replies.
You can do this by using Reply-To-List or Reply-All.

 

-------------- next part --------------
A non-text attachment was scrubbed...
Name: smime.p7s
Type: application/pkcs7-signature
Size: 4817 bytes
Desc: not available
URL: <http://lists.gnucash.org/pipermail/gnucash-user/attachments/20170206/cc115a61/attachment.p7s>