Trial Balance Report with Investment Transactions

Sun Feb 5 09:26:31 EST 2017

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>
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>
> 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 332bd3bb3efeb4c09ca686aa445df5
> 54dc656092
> > 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]
> > *Sent:* Saturday, 4 February 2017 8:57 PM
> > *To:* Chris Good <chris.good at ozemail.com.au>
> > *Cc:* 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>
> > wrote:
> >
> > > Message: 5
> > > Date: Thu, 02 Feb 2017 13:07:10 -0500
> > > From: Richard Lindgren <rlindgren74 at gmail.com>
> > > To: gnucash-user at gnucash.org
> > > Cc: gnucash-user at gnucash.org
> > > Subject: Trial Balance Report with Investment Transactions
> > > Message-ID: <28cd077c-e8f3-4b53-80a0-5bb71474cab0 at typeapp.com>
> > > Content-Type: text/plain; charset=UTF-8
> >
> > >
> > > Hi,
> > >
> > > Richard Lindgren <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
> 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.
>