Need better documentation on using Lots [Was: Chapter 8 - investment accounts]

David Carlson david.carlson.417 at gmail.com
Wed Sep 9 01:17:23 EDT 2015


My last message only covered the simple case where the security asset
account had been completely emptied.  Another case that is very common and
hopefully the one that the Show Lots dialog was intended to address is the
one where there are still shares remaining in the security account.  That
is the one where it is crucial that the realized capital gain for each
transaction is correct for the particular shares involved, so that the
remaining cost basis (and unrealized gain) is correct for the remaining
shares.

Since there is a decision about which shares to select from a pool of
shares that may not all have the same cost basis per share, the Show Lots
dialog must be intended to assist with this decision, or at least with
tracking the cost basis and the resulting gain or loss.  It should be
capable of finding the correct cost per share of whichever shares are
selected to be sold.  By inference, then, the aggregate cost of the
remaining shares (and the unrealized gain on those shares) should be
correct.  Commissions are real and they should be treated consistently
whether they are tracked separately or included in the cost basis and sale
proceeds.  Then there should be a way to generate a report of what the
dialog did and what is left after the process is completed.  I suspect that
part remains to be implemented.

I think that GnuCash is not equipped to show directly which lots are which,
and it does not have a test to show if the cost basis (and unrealized gain)
in a security account is correct when there are still shares remaining in
the account.

The bottom line that leaves me uncomfortable then is that without a lot
report there is no tool to verify whether the show lots dialog was used,
whether the realized gains were correctly transferred out of the security
account as the lots were closed and whether future sales will indeed be
assigned to different lots than were previously sold.

Also, if any of the cost basis/unrealized gain remains unresolved after a
lot is closed, eventually the books will be incorrect as shown by the trial
balance report for overall balance, and by detailed balance reports for the
accounts that have errors.  Unfortunately, balance reports for accounts
either show the cost basis or the unrealized gain, but not both at the same
time, nor does the report show whether it is showing Average Cost, Weighted
Average, Most Recent or Nearest in time values (two or three of which come
from the internal price table in the data file.  I have no clue where
Weighted Average comes from.)   The user just has to remember which price
source he selected when he configured the report.


David C



On Tue, Sep 8, 2015 at 8:33 PM, David Carlson <david.carlson.417 at gmail.com>
wrote:

> Philip,
>
> When I asked "Does it zero out the value of the security asset account
> that held the
> security before it was sold" I was not referring to any transaction or
> record, whatever that might be.  I meant to ask "does the sum of all
> currency into and out of the now empty security account equal zero?"  You
> can answer that question by running a balance report on that account alone
> and see if both the number of shares is zero and the unrealized gain shown
> at the end of the report is also zero.
>
> The Show Lots dialog should be generating a transaction or series of
> transactions that drives the unrealized gain to zero.  Those transaction(s)
> should (I presume) be the ones that are also generating the orphaned gains
> that need to be re-assigned to the correct Capital Gains(Income) account(s)
> to be linked to the proper brokerage account at tax time.
>
> Then my next question was whether Show Lots dialog includes the brokerage
> charges in those orphaned gains.  If it does, it must be separately
> transferring those expenses from some other account to the orphaned gains
> account, because they cannot come out of the security asset account unless
> the user has chosen to bury commissions altogether, and only use net
> amounts.  In either case, we should know how it works to be sure that our
> results are correct.
>
> Using only net (including commissions) amounts works, if it is done
> consistently, but the trade-off is that GnuCash does not show the actual
> trade price in the buy and sell transactions.  Other threads in this list
> and the Dev list have made it abundantly clear that many users want the
> price to be shown accurately, even if it does not work out exactly due to
> rounding errors.
>
> By the way, using only net amounts makes for simpler (less detailed)
> transactions in GnuCash and gets you closer to seeing numbers that match
> what your broker will report in the 1099 to the IRS.
>
> Which method works better for you?
>
> David C
>
> On Tue, Sep 8, 2015 at 6:07 PM, Philip Walden <pcwalden at comcast.net>
> wrote:
>
>> David Carlson wrote:
>>
>>> On 9/8/2015 1:08 AM, Philip Walden wrote:
>>>
>>>> John Ralls wrote:
>>>>
>>>>> On Sep 7, 2015, at 5:08 PM, Philip Walden <pcwalden at comcast.net>
>>>>>> wrote:
>>>>>>
>>>>>> John Ralls wrote:
>>>>>>
>>>>>>> On Sep 7, 2015, at 9:52 AM, Philip Walden <pcwalden at comcast.net>
>>>>>>>> wrote:
>>>>>>>>
>>>>>>>> John Ralls wrote:
>>>>>>>>
>>>>>>>>> On Sep 6, 2015, at 3:04 PM, Philip Walden <pcwalden at comcast.net>
>>>>>>>>>> wrote:
>>>>>>>>>>
>>>>>>>>>>
>>>>>>>>>> WRT the "Concepts of Lots", per the discussion below I want to
>>>>>>>>>> use lots in my stock accounts, but the document "Lots
>>>>>>>>>> Architecture & Implementation Overview" only talks about the
>>>>>>>>>> concept and does not give many clues as to how one should use
>>>>>>>>>> the View Lots... screen.
>>>>>>>>>>
>>>>>>>>>> For example, what does Scrub Account do? What does Scrub (a
>>>>>>>>>> record) do? What does the Splits Free and Splits in Lots mean or
>>>>>>>>>> do.
>>>>>>>>>>
>>>>>>>>>> I have experimented several times only to get confused as to
>>>>>>>>>> what is happening and end up backing out the work for fear of
>>>>>>>>>> going down a rat-hole and ruining my stock gain/loss reporting
>>>>>>>>>> integrity.
>>>>>>>>>>
>>>>>>>>>> I sort of understand the concept, but how is it implemented in
>>>>>>>>>> the View Lots... screen? I cannot find anything helpful in the
>>>>>>>>>> tutorial.
>>>>>>>>>>
>>>>>>>>>> My particular circumstance is that I have many old stock
>>>>>>>>>> holdings in several accounts with lot tracking done on paper.
>>>>>>>>>> I'd like to get them into gnc if I can. So the "automatic"
>>>>>>>>>> scrubbing and lot set up does not seem to work for me as I
>>>>>>>>>> already have prior lot database I'd like to enter; and then it
>>>>>>>>>> could be that I just do not understand the View Lots... screen
>>>>>>>>>> and its nomenclature.
>>>>>>>>>>
>>>>>>>>>> Thanks in advance for any help or pointers
>>>>>>>>>>
>>>>>>>>> AS you probably know there are a variety of strategies for
>>>>>>>>> grouping lots and for deciding which lot is the one you sold.
>>>>>>>>> GnuCash supports exactly one: A lot consists of a single buy
>>>>>>>>> transaction and sales are first-in, first-out. If that’s not what
>>>>>>>>> you want, you have to handle all of it manually and ignore the
>>>>>>>>> View Lots dialog.
>>>>>>>>>
>>>>>>>>> Regards,
>>>>>>>>> John Ralls
>>>>>>>>>
>>>>>>>> Hi John
>>>>>>>>
>>>>>>>> Thanks for your swift reply. I think FIFO will work for me, but I
>>>>>>>> am having trouble translating the "Concepts of Lots", the only
>>>>>>>> documentation that I can locate, to the "View Lots..." screen
>>>>>>>> functionality. For example, the screen menu button "Scrub" and
>>>>>>>> what it does is not documented as far as I can determine.
>>>>>>>>
>>>>>>>> I was hoping for some kind of tutorial/pointers about how the
>>>>>>>> screen functions translate to the "Concepts of Lots". Maybe there
>>>>>>>> isn't any?
>>>>>>>>
>>>>>>>> Also, you mention a "manual" method not using the "View Lots..."
>>>>>>>> screen. I cannot seem to find any other method, manual or
>>>>>>>> otherwise, for "Lots" anywhere in gnc.
>>>>>>>>
>>>>>>>> _
>>>>>>>>
>>>>>>> Philip,
>>>>>>>
>>>>>>> The original subject of the thread you hijacked was “Chapter 8 -
>>>>>>> Investment accounts”. Have you studied that chapter?
>>>>>>>
>>>>>>> Chapter 8 of the guide is really titled “Investments”, and the
>>>>>>> English version can be read online at
>>>>>>> http://www.gnucash.org/docs/v2.6/C/gnucash-guide/chapter_invest.html
>>>>>>> .
>>>>>>>
>>>>>>> Regards,
>>>>>>> John Ralls
>>>>>>>
>>>>>> Hi John
>>>>>>
>>>>>> Yes I have studied  Chapter 8 intently, particularly the 8.7 Selling
>>>>>> Stock section. There is no mention of the "View Lots..." screen
>>>>>> anywhere in that chapter.
>>>>>>
>>>>>> The 8.7 section discusses manually creating a stock sell splits, but
>>>>>> There is no mention of how to specify a lot associating the sale
>>>>>> with a buy.
>>>>>>
>>>>>> So I am assuming there is no documentation documenting how to use
>>>>>> the "View Lots..." screen.
>>>>>>
>>>>>> After some painful experimentation I have come up with a somewhat
>>>>>> poor recipe to record lots and capture capital gains. The
>>>>>> pain/poorness comes from trying to translate the "proceeds", "cost
>>>>>> basis" and "gain/loss" from my broker account reports into the "sell
>>>>>> price" and "commission" in gnc, such that the capital gains gets
>>>>>> recorded correctly. However, I do not feel that is a gnc problem.
>>>>>>
>>>>>> I'd like to thank "Mike" in a previous post for the clues to figure
>>>>>> it out.
>>>>>>
>>>>>> Here is the recipe I am going to use going ahead.
>>>>>>
>>>>>> 1. record buy of stocks with care to get accurate cost of shares.
>>>>>> 2. Actions > View Lots...: Select the added stock purchase record,
>>>>>> press create lot.
>>>>>> 3. record sell of same stocks. Do not follow Chapter 8.7 as it
>>>>>> includes the gain/loss in the split. The split should only have the
>>>>>> proceeds to the asset account, the commission, and the
>>>>>> shares-price-sale.
>>>>>> 4. Actions > View Lots...: Select the lot with the stocks just sold.
>>>>>> The "Splits in Lot should show the selected purchase. The Splits
>>>>>> Free should show the sale just created.
>>>>>> 5. Select the sale in the Splits Free window.
>>>>>> 6. Press the >> button to associate the Sale with the Buy.
>>>>>> 7. Close the View Lots window.
>>>>>> 8. An Orphaned Gain record should have bee automatically created
>>>>>> with, hopefully, the correct gain or loss amount. Edit the record to
>>>>>> direct the gain/loss to the appropriate short or long capital gain
>>>>>> account.
>>>>>>
>>>>> Philip,
>>>>>
>>>>> I meant to study Chapter 8 to understand how to do it manually. There
>>>>> is no documentation on the View Lots dialog box. What’s more, I’ve
>>>>> tried it a few times and found that it works well in simple cases and
>>>>> less well when there are overlapping buys and sells — but I’ve only
>>>>> tried it using the “Scrub Lots” button which does your steps 5 & 6
>>>>> automatically. Since you seem to have worked out a semi-automatic
>>>>> approach I suggest that you create a fake book and test it out in the
>>>>> scenarios you expect. If it works consistently for you, great!
>>>>>
>>>>> Chapter 8.7 doesn’t include the gain in the split, it creates a new
>>>>> split pair in the transaction. If you prefer a separate transaction
>>>>> that’s OK, just create the splits as 8.7 explains in a new
>>>>> transaction instead of in the sell one.
>>>>>
>>>>> Regards,
>>>>> John Ralls
>>>>>
>>>> Thanks John,
>>>>
>>>> Looks like I can do what I need by avoiding the "Scrub" button
>>>> automation and just use the ">>" and "<<"" buttons to select sale
>>>> transactions to a specific Lot-Buy. One buy per lot.
>>>>
>>>> When I use the above method, a separate Gain is recorded
>>>> automatically. That is why I do not add one to the split within the
>>>> sale.
>>>>
>>>> Again, thanks for your patience.
>>>>
>>>>
>>>> _______________________________________________
>>>> 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.
>>>>
>>>
>>> Philip,
>>>
>>> It is interesting that you have 'discovered' a part of the Show Lots
>>> feature that had been lost due to lack of documentation.  I am curious
>>> now how far that feature goes.
>>>
>>> Does it zero out the value of the security asset account that held the
>>> security before it was sold
>>>
>> It does not zero out any record. Each buy and sell remains a separate
>> record (split). You have to use the View Lots... screen to see the
>> remaining number of shares in each lot.
>>
>>> and generate an offsetting income or loss
>>> value?
>>>
>> When you select a sell with a lot containing a buy, it automatically
>> generates a separate Capital Gain/Loss split. The gain appears to be
>> calculated by the subtracting the cost per share in the buy record, times
>> the number of shares sold,  from the sell price.
>>
>>> Does it also identify both the opening and closing commissions
>>> and include them in the calculation?
>>>
>> No. However, I include the commission in the sell record (split). If you
>> suffer from buy commissions, I would include them in the buy record
>> (split). You may find recording commission easier using a different method.
>>
>>> If so, where does the offset go
>>> for those commissions?  Would that go to a different income/expense
>>> account or to an adjustment to retained earnings?
>>>
>> See previous comment
>>
>>> Does it work if the
>>> original lot was partly sold on one date then the rest sold on another
>>> date?.
>>>
>> Yes, you can select multiple sell records on different dates. I assume
>> you cannot add anymore once the lot quantity is exhausted. I have not
>> tested that. It would be an error case regardless.
>>
>>> Does it work if multiple lots are sold at the same time?
>>>
>> Yes, just record every sale, then individually select each record from
>> the  Free Splits window on the View Lots... screen. Avoid the Scrub
>> mechanism(s) if you want to control the process as it will always just
>> chose the first available lot.
>>
>>>
>>> If it does some but not all those things, that should be documented too
>>> and possibly used as a basis for a request to improve the functionality
>>> of that feature.
>>>
>> I agree that I am able to use the Lot functionality and that
>> incorporating a description in the documentation would be of benefit and
>> would have saved me a few days trying to figure it out.
>>
>>
>>
>> _______________________________________________
>> 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.
>>
>
>


More information about the gnucash-user mailing list