Trial Balance Report with Investment Transactions

[Richard Lindgren]

Chris & David

I want to thank you both for your help in my problem with the Trial
Balance. At this moment my problem is solved.

What I did was I uninstalled my GnuCash program 2.6.15 then 2.6.13 and then
2.6.12. I ended up with 2.6.11...BINGO, my problem is solved! Yes I did
have to go back and remove my account adjustments, before it would balance,
but it now works and I will be staying with 2.6.11. It's now NOT BROKE so I
will stay here and not try to fix it with a newer version until I feel
really confident that the newer version will be debugged.

Again thank you. 🙂

Richard

On Mon, Feb 6, 2017 at 1:31 AM, Chris Good <chris.good at ozemail.com.au>
wrote:

> Hi Richard,
>
>
>
> (Thanks David C for adding all that useful info.J)
>
>
>
> 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>
> 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.
>
>
>