Report to locate Unrealized Gains

[David T.]

Chris,

The binary method might work, but it is exceedingly time consuming when one is working with a ten-year data file with 185 commodity accounts and thousands of transactions. I tred that method, in fact, before I sent my question to the list. It’s simply not feasible except when you’re talking a small data set.

Moreover, my first encounter with a transaction with unrealized gains left me confused.

Here is the situation that confuses me:

I have a set of transactions from 2007: an Initial Holdings transaction of 200 shares, with a cost basis of $23.80. I sold 100 shares for $3480, with $63.99 commission and $.06 fee, with $3415.95 coming along to me. My broker statement for that transaction listed capital gains of $3404.05 (3480-63.99-.06-(23.80/2)), which is what I entered as a separate capital gains transaction on the same date. (See screen shot below)

The Balance Sheet report for this date lists unrealized gains of $104.30 (just to be clear, this is the only transaction affecting any commodities in my data file on this date). This figure stumped for a LONG time, until I realized that 

104.30 (Unrealized gainson report) =(63.99 [commission]+.06 [fee]-(23.80/2) [initial cost basis])*2

In other words, the Balance Sheet report was off by the total fees, reduced by the initial cost basis.

That seems wrong to me, and I certainly don’t know how fix this. I mean, I already *have* the gain transaction entered (and it’s the right one, according to my broker). So, what am I supposed to do to fix the Balance Sheet report?

For humor, I decided to Lotify this account (open the View Lots window and scrub account). When I do this, it inserts a new Gain transaction for $3468.10 (i.e., not deducting fees), and the unrealized gain goes away. However, I am now staring at TWO gain transactions in the amount of over $3000, so I would expect the Unrealized gains number to be negative, or something. But this doesn’t happen; the gains remain at zero. Obviously, the gains have been realized, but by a set of transactions that gives completely incorrect information about the overall gain.

I am open to being edified here—both on how I have misconstrued the problem AND on how to actually fix it going forward.

Oh—and that’s only the first transaction I encountered. It’s from 2007. It’s a long way to Tiperary.

David



> On Feb 7, 2017, at 11:01 AM, Chris Good <chris.good at ozemail.com.au> wrote:
> 
> Hi David C,
>  
> Totally agree.
>  
> Regards, Chris Good
>  
> From: David Carlson [mailto:david.carlson.417 at gmail.com <mailto:david.carlson.417 at gmail.com>] 
> Sent: Tuesday, 7 February 2017 1:32 PM
> To: Chris Good <chris.good at ozemail.com.au <mailto:chris.good at ozemail.com.au>>
> Cc: David T. <sunfish62 at yahoo.com <mailto:sunfish62 at yahoo.com>>; gnucash-user at gnucash.org <mailto:gnucash-user at gnucash.org>
> Subject: RE: Report to locate Unrealized Gains
>  
> Chris,
>  
> This will be short as I am still on my goofy tablet.  
>  
> I think that there are many users like him and me that have mountains of transactions that may not have realized gains correctly entered so one report printed out or saved to PDF could be used one. Sunday afternoon after the football season is over towork on them without going through the lather rinse repeat process. Would be of great benefit.
>  
> Since the lot identification process does not always work for realized gains that were entered using a different method than currently prescribed this is still a potentially Herculean task.
>  
> I started to propose a manual way to generate such a report but it needs refinement to weed out useless clutter.
>  
> David C
>  
> On Feb 6, 2017 7:27 PM, "Chris Good" <chris.good at ozemail.com.au <mailto:chris.good at ozemail.com.au>> wrote:
>> > Message: 8
>> > Date: Mon, 6 Feb 2017 17:25:41 +0500
>> > From: "David T." <sunfish62 at yahoo.com <mailto:sunfish62 at yahoo.com>>
>> > To: Gnucash <gnucash-user at gnucash.org <mailto:gnucash-user at gnucash.org>>
>> > Subject: Report to locate Unrealized Gains
>> > Message-ID: <5031F489-F1E3-4487-A37D-F97A4659D559 at yahoo.com <mailto:5031F489-F1E3-4487-A37D-F97A4659D559 at yahoo.com>>
>> > Content-Type: text/plain; charset=utf-8
>> >
>> > Hello,
>> >
>> > The recent discussion regarding the Trial Balance and Balance Sheet
>> reports
>> > got me to wondering whether there is any way to list transactions that
>> create
>> > unrealized gains? It would be nice to be able to locate those transactions
>> in
>> > the ten years of data I have in my data file. Trying to dig them out from
>> the
>> > many accounts and transactions is not a fruitful use of time?
>> >
>> > TIA,
>> > David
>> 
>> Hi David,
>> 
>> There is no such report as far as I know.
>> Such a report would be useful although only for those who haven't known to
>> run a TB after each sale to check their capital gain/loss transaction is
>> correct.
>> A work-around would be to use the trial balance to do a binary search over
>> the transaction date range to identify which is the first date containing
>> transactions that cause the TB to go out of balance, and then use a
>> transaction report, which lists transactions for all investment accounts in
>> date order, to identify which investments had Sell transactions on those
>> dates. Repeat after fixing each problem.
>> 
>> 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 <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.