Reporting: weighted average price source

[David G. Hamblen]

Charles Day wrote:
> On Mon, Jul 7, 2008 at 8:53 AM, Derek Atkins <warlord at mit.edu 
> <mailto:warlord at mit.edu>> wrote:
>
>     "David G. Hamblen" <dhamblen at roadrunner.com
>     <mailto:dhamblen at roadrunner.com>> writes:
>
>     > A few years back (v1.8x), I had problems with these absolute
>     values, and I
>     > patched report-utilities.scm,and commodity-utilites.scm so that
>     the balance
>     > sheet would balance.  In addition to completely removing all the
>     numeric:abs,
>     > I also had to do something about the division by zero in
>     commodity-utilities
>     > when there was a zero share balance.  I'm using the "Nearest in
>     Time"  and the
>     > problem went away in the 2.x updates.  If anyone's interested, I
>     can dig up
>     > my old postings.
>     >
>     > Anyway, I vote for getting rid of absolute values in bookkeeping.
>
>     It's not a question of book keeping.  It's a question of computing
>     the share price.  GnuCash stores buys as a positive and sells as
>     a negative.  As Christian pointed out before, if you buy x shares
>     for $y and then later SELL x shares for $y then a non-abs weighted
>     average gives you a price of 0/share!  Obviously this is wrong.
>     The weighted average should be $(y/x) per share.   But without
>     the ABS you get:
>
>       y * x + (-y)*x        xy -xy       0
>       --------------  ==    ------  ==  --- = 0
>          x + x                2x         2x
>
>     When you use abs() here you get the right answer.
>
The fundamental issue here is that I don't think that the average price  
should include shares I no longer own.

My tests (all using the Balance Sheet) indicate that share prices (the 
y's) are all positive and are calculated from each transaction (Total 
Buy/Total Shares). The Total Buy (the x*y product) and the Total shares 
(the x's) are both positive or both negative.  What seemed to be 
happening back in 1.8x, and is still happening in 2.4) was that when you 
have zero shares, the answer for the weighted average is irrevelant, but 
I have a situation where I sold all my shares (as in your example), but 
subsequently purchased a few more shares of the same stock.  The 
weighted average (without all the abs()'s) looks like this: (I can't 
wait to see what Thunderbird does with my ascii equations :-) )

   y * x + y * (-x) + z * w               z * w
   -----------------------------------    ==    ---------     ==  w
           x + (-x) + z                          z

With the abs(),  I get a nonsensical average price for my holdings in 
this asset.  In my specific test case, with the initial purchase/sell of 
100 shares at $5 (as in your example), and a subsequent purchase of 10 
shares at $20, I get a weighted average price of $5.714, and an asset 
worth $57.14.  Using the "Nearest in Time" or the "Most Recent"  price 
source, I get an average share price of $20, and an asset value of $200. 

Without the abs(), you get 0/0 for the weighted average in your example, 
so that needs to be trapped.


The good news is that in 2.4, the Balance Sheet balances in all cases 
(not true in 1.8x).


>     Of course, then you need to make sure you re-apply the negative
>     for sales.
>
>     So it's not a question of absolute values in bookkeeping.  It's
>     a question of absolute values in computing share prices.  Not
>     the same thing.
>
>
> I agree with Derek. That is, the "weighted average" price source is 
> not intended to compute the cost of your holdings, but rather to 
> compute the volume-weighted average price of all buys AND sells. So it 
> needs to keep using the absolute value. However, it does need to be 
> changed: it should ignore exchanges with a zero "amount" in the split.
>
> So I think we need to add a new price source of "Cost" which computes 
> without absolute value and does include zero "amount" splits. That's 
> where taking a look at David's code might come in handy (though 
> implementing this seems like a pretty simple job: copy existing 
> weighted average function, get rid of absolute value, add report option).
>
> Whether to even keep the "weighted average" price source is a question 
> worth asking; I don't think it is a useful way to revalue current 
> holdings. Historical volumes don't seem relevant. Even if the volume 
> weighting was removed, the formula also weights quotes that are 
> clustered within a small period of time more heavily than quotes that 
> are spread apart. In other words, if I have 30 quotes from this month 
> and one quote from last year, the recent quotes practically drown out 
> the quote from last year. If we want to aim for a historical price 
> average then using something like linear regression seems like a 
> better way to go. Could even add some time factors, like getting the 
> midpoint of the portion of the line covering the last 30, 60, 90 days, 
> whatever. But now I am brainstorming new price sources.
>
> I do think a volume-weighted average price can be useful; it's just 
> more appropriate for measuring your personal trading performance. 
> Maybe there should be a performance report designed around that.
>
> -Charles
>
>     derek
>     --
>           Derek Atkins, SB '93 MIT EE, SM '95 MIT Media Laboratory
>           Member, MIT Student Information Processing Board  (SIPB)
>           URL: http://web.mit.edu/warlord/    PP-ASEL-IA     N1NWH
>           warlord at MIT.EDU <mailto:warlord at MIT.EDU>                    
>        PGP key available
>
>