[GNC-dev] New balsheet (and P&L report), API considerations, and (slow?) KVP in Account.cpp

Christopher Lam christopher.lck at gmail.com
Fri Aug 17 09:20:33 EDT 2018

Hi David

I refer you to a prior discussion: 

I appreciate balance-sheet is a formal accounting report. The problem 
is, as always, within the details.

Initially I thought we could leave currencies unconverted.

Then Geert reminded me *all* currencies *must* be allowed to convert to 
a user-selected common-currency. Because a balance-sheet is really a 
valuation of all of your assets, into a legal currency of your choice. 
The price used must be either (1) actual transactional prices - either 
average-cost or weighted-average - both terms whose exact definitions 
currently elude me despite looking at code (2) from the pricedb - 

(* PS what's the strategy if accounts are in EUR/USD/GBP, with plenty of 
pricing data in these 3 currencies, but user prefers common-currency to 
be JPY, and pricedb has EUR/USD, USD/GBP, GBP/AUD and AUD/JPY amounts? 
Answer: Internal logic will try to find an intermediate pricedb pair, 
eg. GBP->AUD->JPY at the nearest date available, and EUR/USD will result 
to 0 JPY.... Except my report will convert GBP->JPY correctly and leave 
EUR/USD in original currency <g>)

The next issues are all to do with Equity:

(1) trading-accounts are optional. As we know, trading accounts will 
compute _REALIZED GAINS,_ so that EUR>USD>EUR transfers at different 
prices *must* result in a realized-gain recording. If the 
trading-accounts are disabled, the current balance-sheet will make an 
attempt at computing its own Realized Gains. It assumes the user hasn't 
recorded realized-gains during currency conversions. IMHO There's 
currently a logic error if there are trading-accounts in a book with 
book property trading-accounts disabled. Moreover, if trading-accounts 
are disabled, and the user has recorded but underestimated realized 
gains, I think the balsheet *should* report the delta amount as 
unrealized-gains too???

(2) selection of a report common-currency must compute _UNREALIZED 
GAINS_, e.g. if EUR/USD/GBP accounts are reported for balsheet in JPY, 
and JPY has increased in value between transaction-dates and 
report-date, presumably all amounts must increase? This is not proven 
correctly handled yet. I still think there's an error - the Asset 
report-currency amount will use the higher price, but the unrealized 
gains are not computed when trading-accounts are disabled.

(3) income and expense amounts are technically closed to equity on 
balsheet-date but we don't enforce this, so, their difference must 
account for _RETAINED EARNINGS_ or some other similar heading. If user 
has closed books correctly on balsheet-date, then the balsheet should 
report retained earnings to be $0.

If anyone can help me, please do. Spreadsheets or formula sheets 
welcome. Double-entry is all fun and games until trying to compute a 
balance-sheet! The above, not an accountant yadda yadda.

On 17/08/18 00:27, David T. wrote:
> Hi,
> I admit that I haven’t been following the details of thsi thread that closely, but it would seem to me that if a user has selected price source “Latest,” then the report will of necessity include an Unrealized Gains line in order to balance, as John said. I agree with his suggestion that Unrealized Gains might not belong in a Balance Sheet report, but I note that as a personal user of GnuCash, I am rather interested in the current value of my empire, ephemeral though it may be. I’m eager to see how rich I am Right Now! Bwa Ha Ha Ha!
> Seriously, though, since “Balance Sheet” seems to be a commonly-used term in accounting to refer to a particular general type of report and content (I base this on the fact that Googling “Balance Sheet report definition” yields numerous accounting websites with explanations of what a balance sheet is and does), perhaps there could be a separately-identified and named report to give the armchair trader the putative value of their holdings as of a given date. Then the Balance Sheet could be dedicated to the real world situation (i.e., no need for the PriceDB at all—just use the transaction prices), while the new report could include unrealized gains explicitly. A descriptive name (“Trade Value”? “Speculative Value”? “Castles in the Sand”?) could help make clear the difference between the two.
> David
>> On Aug 16, 2018, at 7:30 AM, John Ralls <jralls at ceridwen.us> wrote:
>> Chris,
>> That’s because you used price source = “latest” which uses the most recent price in the pricedb for the purchase as well as the valuation.
>> Regards,
>> John Ralls
>>> On Aug 16, 2018, at 12:58 AM, Christopher Lam <christopher.lck at gmail.com> wrote:
>>> Hi John
>>> Just to be a pain again... I found a small discrepancy - (This is different from the previously noted missing-capital-gains situation)
>>> if trading-accounts are enabled,
>>> a single 100AAPL purchase @ $100 each dated 01/01/18
>>> a new increased price $200 on 01/06/18 is recorded,
>>> price-source is 'latest' to pick up the new price
>>> There's no trade sale yet -- this means 'trading gains' is $0 -- indeed the book will not have any Trading accounts yet.
>>> I'd expect the balance-sheet to record the increased price as 'unrealized gain'.
>>> Yet the balance-sheet just displays an increased FUNDS valuation at $20,000 (i.e. total assets = $20000) without a corresponding increase in right-hand-side (ie total equity+liability=$10000).
>>> I'd think the 'correct' balance sheet with trading-accounts enabled, *should* still report Unrealized Gains, no?
>>> On 13/08/18 22:51, John Ralls wrote:
>>>>> On Aug 12, 2018, at 10:04 PM, Christopher Lam <christopher.lck at gmail.com <mailto:christopher.lck at gmail.com>> wrote:
>>>>> Hi Jralls
>>>>> So just wish to double check my understanding of gnucash's data format for a balance-sheet on date X
>>>>> There are two possibilities for displaying the right-hand-side
>>>>> Liabilities + Equity + Retained Earnings + Trading Balances
>>>>> Liabilities + Equity + Retained Earnings + Unrealized Gains
>>>>> "Retained Earnings" should be NULL if the user has properly closed the books on the balance sheet date X.
>>>>> In my understanding, "Trading Balances" and "Unrealized Gains" are one and the same -- in balance-sheet.scm the unrealized-gain-collector is only populated if book->trading-accounts setting is disabled. (btw this causes a 'bug' whereby a book with 'enable-trading-accounts', but was later switched to 'disable-trading-accounts' will then add both the unrealized-gain-collector and the trading-balance the right-hand-side).
>>>>> This seems to be deconstruct current balance-sheet?
>>>>> (I can't seem to find any unrealized-gain issue... from using different price-sources... perhaps this is beyond my understanding.)
>>>>> On 11/08/18 22:41, John Ralls wrote:
>>>>>> Chris,
>>>>>> Remember that we’ve long advised users that they need not close their books, they can run a balance sheet report for any day. IMO removing that capability would be a major breakage.
>>>>>> I suspect that you needed to use trading accounts because you didn’t book the trading gains and losses as income. Users should do that regardless of using trading accounts and doing so should make it unnecessary for the balance sheet report to include trading accounts.
>>>>>> Unrealized gains are another matter entirely and are a result of using prices from the price database instead of actual cost from the transaction data. IMO the balance sheet report shouldn’t be taking prices from the price db and shouldn’t be able to see unrealized gains, but if price source is going to be an option then an unrealized gains line flows from that so that users don’t waste a bunch of time chasing an imbalance caused by price differences.
>>>>>> https://bugs.gnucash.org/show_bug.cgi?id=775368 <https://bugs.gnucash.org/show_bug.cgi?id=775368> is related because that’s currently how the balance sheet report gets the “actual” costs.
>>>>>> Regards,
>>>>>> John Ralls
>>>>>>> On Aug 10, 2018, at 11:40 PM, Christopher Lam <christopher.lck at gmail.com <mailto:christopher.lck at gmail.com>> wrote:
>>>>>>> Hi John
>>>>>>> I've managed to make the left-side (activa?) match the right-side (passiva?)
>>>>>>> https://screenshots.firefox.com/RNvkjaxnYyxpGkYn/null <https://screenshots.firefox.com/RNvkjaxnYyxpGkYn/null>
>>>>>>> 1) it does require closing books on the balance-sheet date
>>>>>>> 2) it does require adding trading-accounts
>>>>>>> The existing balsheet introduces/calculates the "Retained Earnings", "Trading Gains" and                           "Unrealized Gains", whereas the current iteration of new-balsheet will not.
>>>>>>> To me this is the easiest method to ensure both sides produce the same total, and is now technically correct - if the user has not closed their books, the balance sheet won't                           balance.
>>>>>>> This is giving me a headache :(
>>>>>>> Should a new balsheet calculate and report these '(fake) retained earnings', and 'unrealized gains' ???
>>>>>>> C
>>>>>>> On 09/08/18 08:32, John Ralls wrote:
>>>>>>>>> On Aug 8, 2018, at 8:51 AM, Geert Janssens <geert.gnucash at kobaltwit.be> <mailto:geert.gnucash at kobaltwit.be> wrote:
>>>>>>>>> I haven't been following every detail of this. However I note on most balance
>>>>>>>>> sheets the total assets doesn't match total net worth (or liabilities/equity).
>>>>>>>>> In most, this is fixed by including the retained earnings.
>>>>>>>>> I believe at least in most European countries the "left hand side" (Assets,
>>>>>>>>> Active) and "right hand side" (Passive or liabilities + equity) of the
>>>>>>>>> multicolumn view should balance (it's called balance sheet for a reason).
>>>>>>>>> That would suggest retained earnings does have to be part of the balance
>>>>>>>>> sheet.
>>>>>>>>> However I'm not an accountant and perhaps your book is slightly contrived so I
>>>>>>>>> don't know the exact answer here.
>>>>>>>>> As for the "multi-column" vs one column debate, both present the same data.
>>>>>>>>> The only difference is visual representation or style.
>>>>>>>>> As of recently I have become a strong proponent of separating structure (or
>>>>>>>>> accounting functionality in a different context) from style, I think this
>>>>>>>>> should be delegated to the realm of css. This particular layout variation can
>>>>>>>>> IMO be handled by making divs for each large group and either let them follow
>>>>>>>>> normal flow or use float to move them next to each other. If you will you can
>>>>>>>>> have a European style sheet and an American one, or an Australian or whatever.
>>>>>>>>> As for "categories", I read Frank's earlier reply as if he agreed that at
>>>>>>>>> least for now the account organization is something to be done in the CoA, not
>>>>>>>>> in report code.
>>>>>>>> The Balance Sheet is indeed supposed to balance, but in normal practice it balances only when the book is “closed”, i.e. when all of the income and expense accounts are                             summed up and added to Equity. In US corporate books the cumulative total of income and expenses lives in an Equity account called “Retained Earnings”.
>>>>>>>> In the pen-and-paper days a  “Trial Balance” was computed outside of the books before closing as a way to catch errors before making the closing entries and writing the formal Balance Sheet.
>>>>>>>> GnuCash's existing Balance Sheet Report creates the “Retained Earnings” line so that one need not close the books (Tools>Close Book) in order to get a balanced report. Removing that feature might be more formally correct but it would mean that users would have to close their book before running a balance sheet. That would be a big change and I don’t think that we want to do it. On the other hand “Retained Earnings” isn’t the right term for many cases, so it would be a useful improvement to make it configurable.
>>>>>>>> There’s a second problem with the current report as well: If the user does close their books periodically they’ll have an account for the accumulation that may well be called “Retained Earnings”. The Balance Sheet Report will dutifully report the contents of that account and, if there are income and expenses after the last close, add a second “Retained Earnings” line. That looks a bit odd and might be confusing; ISTR we’ve had comments on the user list about just that.
>>>> Chris,
>>>> To demonstrate the price difference on assets creating an “Unrealized Gain” line, I created a fake account with Trading Accounts off and purchased on 1 January 100 shares of a stock for $100, then created a new price for the stock of $200. The resulting Balance Sheet report is the first screenshot below. Price source is set to “nearest in time”.
>>>> I repeated the process in a new book with trading accounts enabled and got the second screenshot. As you pointed out, the “Unrealized Gains” line changes to “Trading Gains”. Selling the stock made no difference on the report unless I also booked the 10,000 gain to Income:Short Term Cap Gains, after which the calculated line became “Retained Earnings” as illustrated by the third screen shot.
>>>> I went back and did the sale on the non-trading-accounts book and found that indeed “Unrealized Gains” didn’t change after I sold the stock; that’s wrong, it’s a realized gain at that point. Booking the gain to Income changed the line to “Retained Earnings” as it did with trading accounts enabled and as expected.
>>>> Finally, to illustrate the effect of price source I removed the sell transaction and changed the price source in report options to “Avg Cost”. The result is the last screenshot, showing the stock at book value and the “Unrealized Gains” line at 0.
>>>> Regards,
>>>> John Ralls
>>>> <Balance Sheet No Trading.png><BalSheet-Trading.png><BalSheet-Trading-CapGain.png><BalSheetAvgCost.png>
>> _______________________________________________
>> gnucash-devel mailing list
>> gnucash-devel at gnucash.org
>> https://lists.gnucash.org/mailman/listinfo/gnucash-devel

More information about the gnucash-devel mailing list