Trial Balance Report with Investment Transactions

[Michael Churchill]

Hi All,

David C's explanation of a cost-based TB matches my idea of
what to expect.  I don't have my data handy, but as I recall the TB
accumulated cost numbers did not match what I calculated by hand.
This was true for 2.6.15 and perhaps for 2.6.14.  I just don't recall.
 I did use average cost in all cases, except when I forgot to reset
 the default in 2.6.15.  I really don't understand a Trial Balance
 using current prices rather than cost.  It seems to me that cost is
 what is entered.  Anyway I am not an accountant.  As I recall, I was
 unable to figure out where the gnucash cost values came from.  I
 realized I was not capable of making any headway and gave up. 

Mike

On Mon, 6
 Feb 2017 17:31:50 +1100 "Chris Good" <chris.good at ozemail.com.au> wrote:

> 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.
> 
>  
>