Trial Balance report -operator error or bug
David Carlson
david.carlson.417 at gmail.com
Fri Feb 10 17:36:42 EST 2017
The behavior of Gnucash seems to not be following the traditional rules
that Accountants used to used in their trial balance calculations.
My understanding of accounting is very much in line with yours. I would
add only that the current area of discussion is limited to certain
transactions in and around security accounts. Like you I have been using a
spreadsheet to track the purchase transactions currency into each security
account, and expenses that come into play later for tax purposes. Also,
for sale transactions I track the currency out and calculate the currency
that went in to the share account when those particular shares were
purchased and subtract to determine the Realized Gain for those shares in
the share account. I also, like you, track the sales expenses and
calculate the 'round trip' net realized gain to report on my tax return.
That number normally matches what my broker reports, but GnuCash reports
only the gain in the security account without the commission and fee
expenses when they are separately tracked.
When tracking the unrealized gain associated to a certain security it is
important to identify correctly the amount of currency used to purchase
each share so that when it is sold the cost of the shares that are not sold
still have the correct value. The correct cost value should be based only
on the currency spent to acquire those shares. When the last share of a
security account is sold the last dollar of cost is converted from the
value of that security account to a realized gain, and the account value
should thereafter be zero in the chart of accounts.
*The market value of currently held shares is compared to the actual cost
basis to determine the unrealized gain as of some particular date*.
When commissions and fees are separately tracked, they are entered as
expenses paid directly out of the brokerage account and are not included in
the value of the underlying security account. This is only part of the
number that your broker reports to the IRS.
GnuCash does not give users any simple mechanism to track costs on a share
by share basis. The 'Lots: feature is an add-on in an attempt to
accomplish that part of the realized gain calculation. That is a key part
of the unrealized gain. I think that it works correctly but it apparently
requires that the data be entered in a different way than the older help
manuals suggested as the preferred method.
Now it seems that something has changed in the way that the Trial Balance
Report works which has been observed by several people as a difference
between the calculations made by previous versions of GnuCash compared to
release 2.6.15. It now appears that this report is not correct and perhaps
there is no report that correctly reports realized gains or unrealized
gains.
As you have observed, the unrealized gain changes depending on what method
is used to determine the market value on the date selected for the report.
Further, GnuCash has a table of market values for each security which
includes a mixture of some values calculated when purchases and sales are
entered and market prices collected from the Internet if that was set up
for that security. Over various GnuCash releases the method used to enter
the data into that table has changed. In my opinion this invalidates any
method to establish a value for a security on a given date other than a
value obtained from a trusted market source if GnuCash is not scrupulously
consistent in using this table..
I have submitted Bug 778455
<https://bugzilla.gnome.org/show_bug.cgi?id=778455> containing a documented
example of a file with incorrect data in the price table which, in my
opinion, is a part of the cause for this new change in the way the Trial
Balance Report works. However, now I am not sure which old releases, if
any after release 2.4.something, actually work correctly.
Thus I would just continue to use a spreadsheet as you have been doing
until the dust settles on this issue.
David C.
On Fri, Feb 10, 2017 at 10:15 AM, Pam <hppweu at frontier.com> wrote:
> David,
> I'm not sure what you mean by "anyone who tracks (un)realized gains". I'm
> not trained in accounting, and sometimes get confused by the subtlety in
> the definition & interchangeability of terms used. What I do is use a
> separate spreadsheet to record the purchase, dividends reinvested and sale
> of investments. I track gross purchase & sale price, net purchase & sale,
> commissions, fees, net gain(profit) and gross gain(profit). I also keep
> track of the lots, such that the basis for stocks are FIFO & mutual funds
> are average. When I sell a stock, for example, I make a split entry in
> gnucash which looks like:
>
> Debit
> Net Sale Assets:Investments:Brokerage
> Profit Assets:Investments:Stock
> Comm'sn Expense:Investments:Fee
> Trans Fee Expense:Investments:Fee
>
> Credit
> Gross Sale Assets:Investments:Stock
> Cap Gain Income:Investments:Stock
>
> The amount that I enter for the Profit/Cap Gain is my calculated gross
> gain.
>
> When you talk about tracking (un)realized gain, are you referring to the
> difference between running reports using the commodity price of nearest in
> time v.s. average cost?
>
> If that is what you mean, then there is a problem with 2.6.15,regardless
> of which option you use for commodity price. In 2.6.14 all reports
> balanced using either nearest i time or average cost. Upon updating to
> 2.6.15 the trial balance is unbalanced for either nearest in time or
> average cost and the balance sheet, using average cost, shows an unrealized
> loss value that is not present in 2.6.14.
>
> Thanks,
> Pam
> -------------------------------------------
> On Thu, 2/9/17, david.carlson.417 <david.carlson.417 at gmail.com> wrote:
>
> Subject: Re: Trial Balance report -operator error or bug
> To: "Michael" <mjchurchil at aol.com>, gnucash-user at gnucash.org
> Date: Thursday, February 9, 2017, 6:42 PM
>
>
> It looks like anyone who tracks (un)realized gains should
> run their reports in an older version of GnuCash before
> adopting release 2.6.15.
> I think there is a bug, but it must be a subtle one. If
> anyone can build a small sample file to illustrate the
> issue, it would be helpful.
> I will try to remember to do that before I update but it
> may take me a couple of months to get to that priority.
> David C
>
> -------- Original message --------
> From: Michael via gnucash-user <gnucash-user at gnucash.org>
>
> Date: 2/9/17 1:13 PM (GMT-06:00)
> To: gnucash-user at gnucash.org
>
> Subject: Re: Trial Balance report -operator error or bug
>
> Hi hppweu,
>
> I have had the same experience with 2.6.15 vs 2.6.14. I
> certainly don't
> know what the problem is, but I have spent a lot of
> fruitless time
> trying to figure out what might be happening. I believe at
> least one
> other person has had the same problem. I believe he
> switched to
> 2.6.12. Anyway, I have kept a version 2.6.14 around just
> for this
> reason. Mike Churchill
>
> On 02/09/2017 09:25 AM, hppweu wrote:
> > I have been tracking my brokerage accounts and personal
> finances in gnucash for a couple of years now. Last year I
> had some problems and found out what the statement:
> "GnuCash does not automatically calculate your net capital
> gains" really meant. I asked and got help from the list
> (thanks David Carlson).
> >
> > 1. I started tracking all of my individual stock,
> mutual fund, and unit trust transactions in a spread sheet
> to calculate gross & net gains. (Some have
> commissions, others do not, some dividends are reinvested,
> some are not, etc.)
> > 2. I used the Transaction Report to ensure that the
> total value debits and credits were equal, after all shared
> had been liquidated.
> >
> > After getting out of the weeds, so to speak, with the
> above process I was good for year end 2015. The trial
> balance report balanced, the balance sheet balanced, all
> transaction reports for liquidated investments balanced.
> >
> > Now I am trying to do the same for 2016. Low and
> behold 2016 doesn't balance. In tracking down where the
> error happened, I went back & back, and I finally
> tracked the imbalance to a sale of some Microsoft shares in
> July 2013. That didn't seem correct, since everything had
> balanced at the end of 2015. I had updated to gnucash
> version 2.6.15 in January. I saw one of the changes was
> Bug 775368. Long story short, I have run several reports
> against the same data using gnucash ver. 2.6.14 & 2.6.15
> with different results. I realize that the default for the
> commodity price source was changed between ver. 2.6.14 &
> 2.6.15, so I also ran each report using average cost and
> nearest in time. I have attached a file showing the
> results.
> >
> > The results showed that using version 2.6.14 my trial
> balance is still equal for year end 2015. Using version
> version 2.6.15, my trial balance is off beginning with a
> sale of some Microsoft shares in July 2013.
> > For the trial balance report:
> > On 7/15/13 the debits = credits for nearest in time
> & for average cost for both versions of gnucash.
> > On 7/16/13 the debits = credits for nearest in time
> & average cost for 2.6.14. But in version 2.6.15 the
> credit is greater than debit by $3.35 for nearest in time
> & average cost.
> > For the Balance Sheet report:
> > On 7/15/13 the assets = total equity (liabilities = 0)
> for nearest in time & for average cost for both versions
> of gnucash.
> > On 7/16/13 the assets = total equity (liabilities = 0)
> for nearest in time for both versions of gnucash and for
> average cost in version 2.6.14. But for average cost in
> version 2.6.15, there is an unrealized loss of $3.35, and
> the assets are not equal to the total equity.
> >
> > I have double checked my gain calculations for the
> stock sale that appears to have caused the issue in version
> 2.6.15.
> >
> > My question is whether there is a bug in version 2.6.15
> or did those changes highlight a bug in my process?
> >
> > Thanks in advance for any help,
> > Pam
> >
>
>
More information about the gnucash-user
mailing list