investment lots

[David Carlson]

On 5/13/2016 1:42 AM, Chris Good wrote:
>> Date: Wed, 11 May 2016 19:39:51 -0500
>> From: David Carlson <david.carlson.417 at gmail.com>
>> To: gnucash-user at gnucash.org
>> Subject: Re: investment lots
>> Message-ID: <67514e2d-881e-460c-999d-32c8d8368fbb at gmail.com>
>> Content-Type: text/plain; charset=windows-1252; format=flowed
>>
>> On 5/8/2016 6:03 AM, Chris Good wrote:
>>> Hi all,
>>> GnuCash 2.6.7 Windows 10
>>> I see from [1]:
>>> Gains are always calculated using FIFO cost basis
>>> This agrees with what I see in the Advanced Portfolio Report after a
>>> sale - cost basis and realized/unrealized gain were calculated using
>>> the costs of the oldest stock acquisitions.
>>>
>>> A friend partially sold some stock and elected to sell those lots with
>>> the highest cost, not the oldest. (Legal in Australia I think).
>>>
>>> This seems to mean if one partially sells an investment without
>>> selling the oldest first, the Adv Port Rpt (cost basis and gains
>>> figures) are now wrong for the residual stock going forward.
>>>
>>> Is there some way to do a transaction or some lot magic that would
>>> allow me to adjust the cost of the sold stock to be from the lots
> actually
>> sold?
>>> I tried adding a dummy stock account transaction to decrease cost of
>>> remaining stock:
>>>    Shares 0, Price 0, Sell = Cost Adjustment
>>> but this made Adv Port Rpt incorrectly overstate the Realized Gain by
>>> the value of the Cost Adjustment
>>> (and what should the other side of the double entry be?).
>>>
>>> I have heard about GnuCash 'lots' being used to keep track of which
>>> stock lots are unsold.
>>>
>>> I searched the GnuCash xml data file for stock lots but couldn't find
>>> any (I searched for the figures I would expect to find).
>>>
>>> When I go Actions, View Lots from the Stock account, there is nothing
>>> in the 'Lots in this Account' or
>>>    'Splits in lot' panes, and just info from the stock transactions
>>> splits in the 'Splits free' pane.
>>> Nothing to indicate how much of a particular 'lot' is unsold.
>>>
>>> The signs of the (cost) values in the 'Splits free' pane do not seem
>>> to be what I would expect:
>>> the opening balance cost value is +ve, DRP's are -ve, and a sale is +ve.
>>>
>>> Am I perhaps going into the View Lots screen incorrectly?
>>>
>>> Are the stock lots perhaps only calculated by the report and not
>>> persisted to the saved xml file?
>>>
>>> I know the report has 'Basis calculation method' options Average, FIFO
>>> or LIFO but none of these match the correct cost.
>>>
>>> If anyone can shed some light on this I'd really appreciate it.
>>>
>>> [1]
>>>
> https://lists.gnucash.org/pipermail/gnucash-user/2010-February/033668.html
>>> Regards,
>>> Chris Good
>>>
>> I think that you should update to GnuCash 2.6.12 then try an experiment on
> a
>> test data file.  Turn on "Use Lots" preference for that test file, which I
>> do not
>> remember the exact location for, since I do not use it.
>>
>> I did test it once and the Scrub Accounts function seemed to allow
> selecting
>> arbitrary lots for sale transactions, but there was some reason that I did
>> not
>> decide to do it on my real data.  I do not recall now why I did not like
> it
>> but it
>> may have not worked very well for me because I track commissions
>> separately.
>>
>> Perhaps others can help you more.
>>
>> David C
>>
> Hi David,
>
> Thanks for your suggestion.
>
> I have upgraded to 2.6.12 but cannot find any 'Use Lots' option in
> Preferences, Properties, Adv Port Rpt options,
> the guide or the help documentation?
>
> (Note what follows is what I believe is true from my investigations - I'm
> happy
>   to be corrected).
>
> I have figured out how to use the Actions, View Lots, "Lots in Account"
> window
> to sell specific acquisitions in order to calculate the gain/loss based on
> the cost
> of the actual lots sold, not just take the default model of selling the
> oldest
> stock first (FIFO). This seems to work pretty well, with the correct
> gain/loss
> calculated.
> (I'll document this in the guide later once I'm more certain of what we
> should
> be telling people - I don't want to show how to use a feature that will only
>
> cause problems).
>
> Note that stock lots are either manually created in the "Lots in Account"
> Window or created by Scrub'ing using the "Lots in Account" window.
> This is why I couldn't see any lots previously as I hadn't scrub'ed then.
>
> However, even though it seems GnuCash now knows:
>    which lots were sold (and their gain/loss),
>    the number of shares remaining in each lot
>   and created corresponding gain/loss transactions,
> the Advanced Portfolio Report still calculates the ongoing cost basis and
> gains
> based on the 'Basis calculation method' report option (Average, FIFO or
> LIFO).
> I.e. it does not use any of the lot information, just the stock splits.
>
> Given that the report doesn't use any of the lot info, it is pointless
> entering
> It, unless you always sell LIFO.
> i.e.
> If you always sell FIFO, you can use the Scrub account option to scrub the
> oldest stock, and use the FIFO option to report. You only really need to
> scrub if you want GnuCash to create the gain/loss transaction for you.
>
> If you always sell LIFO, create lots for the oldest stock and scrub them and
> the report should be correct when you use the LIFO option.
>
> If you use Average, if you scrub, the gain will be calculated using the FIFO
> Cost, but the report will use average cost. So do not scrub, manually
> calculate and create the gain/loss transactions.
>
> Hopefully some nice person will sometime modify the Advanced Portfolio
> Report to use the lot info if found, and then use the report option
> (FIFO, LIFO or Average) for calculating costs of stock not in lots.
>
> I'll have a look at doing this myself, but so far, 'scheme' is beyond me.
>
> Regards,
> Chris Good

Hi Chris

I see that you did your homework and I also am now getting some partial 
flashbacks to my experiences so far.

I believe that there has been some fairly recent development work done 
on both the Lots feature which you see in the security account as well 
as is the Advanced Portfolio Report, but as you found, they are not 
coordinated.  Perhaps this is a bug.  Part of my confusion still lies in 
the interaction between these features and the Preference:Use Trading 
Accounts option, which I definitely do not use, so I cannot comment on 
that.  That is probably why I did not start using either feature.

I also have not tested whether those features respect the user's 
preference regarding tracking expenses such as commissions or short term 
vs long term capital gains in countries where it matters, such as the 
United States of America.

I also found that about 14 years ago there was an intent to apply the 
lots feature to inventory control, which, as far as I know, has not 
happened.

I am just a user, not a developer.  I know they are looking for 
volunteers to help wherever possible.

David C