Advanced portfolio report

[Andrew Sackville-West]

On Fri, Nov 30, 2007 at 02:05:18PM -0500, Mike Alexander wrote:
> --On November 30, 2007 8:14:54 AM -0800 Andrew Sackville-West 
> <ajswest at mindspring.com> wrote:
> 
> > I think the thing to do is figure out which version of this report to
> > use and get it cleaned up and committed as it currently stands. Then
> > go back in and add the feature of another frickin' column. How many is
> > too many?
> >
> > The current report is so broken, that I think we could get the devs to
> > backport a fixed version into 2.2.x to help alleviate the current bug
> > stack and then move with improvements to the report for 2.4
> 
> I decided to take a look at this a bit, although I don't have time to 
> really do too much right now.  So far I've tried Andrew's but not 
> Morrison's.  To get it to work I had to change "_" to "-" in 
> ACCT_TYPE_ASSET and ACCT_TYPE_LIABILITY around line 390 in case anyone 
> else is wondering why it doesn't work as sent.

??

>  I also changed the 
> debugging calls to use "gnc:debug" instead of "output" to avoid getting 
> 15000 lines of debugging output every time I ran the report.
>

I warned that it was still ugly like that ;) There's actually useful
information in there!
 
> There are still some problems with this report.  For one thing it 
> doesn't seem to handle reinvested dividends quite right, although I 
> think you've been discussing this.  I have a fund where I made one 
> purchase and since then there have been reinvested dividends and 
> reinvested capital gain distributions and no sales.  It didn't handle 
> this too badly.  The basis seems correct, but for some reason all the 
> dividends and distributed gains are considered to be realized
> gains. 

yes. we've been discussing ways to do this. I have a couple of ideas,
but don't want to pursue them ATM as I'd rather get a
not-completely-broken report into stable. And it's a tricky problem in
terms of how best to handle it. I'm liking my idea of creating yet
another (optional) column to summarise the dividend data.

> In the case, at least, of the dividends this is not right.
> 
> In the case of Apple, I've got a number of purchases and sales over 
> several years, along with a few stock splits.  However the net result 
> is fairly simple.  All the lots are closed except for the most recent 
> purchase and there have been no splits since then.  It almost handles 
> this correctly except for some shares I donated to charities.  For some 
> reason it considers the value of these shares to be brokerage fees.

That's because you've "Expensed" those shares, right? There is no way,
at this time, to distinguish between different types of expenses. To
the stock account, they all look the same. We would have to actually
create more ACCT_TYPE_FOO entries to make this distinguishable. The
better bet is to change how user's enter this data so that any
"Expense" that is *not* a brokerage fee should pass through a suspense
account of some kind. That would move the expense out one level so
that the report doesn't see it. Same with employer contributions that
go directly to some kind of stock or fund...

> 
> A more complicated example is Altria Group.  I purchased two lots in 
> 2004 and 2005.  In 2007 they spun off Kraft.  There have been no other 
> transactions in that account.  The net result is that the basis for 
> Altria is split between the Altria and Kraft stocks.  Then the 
> resulting fractional shares of Kraft were sold.  I used the lot 
> scrubber to create capital gains splits for this fractional sale.  The 
> report doesn't handle this well at all.  The basis of Altria is 
> unaffected by the spin off instead of being reduced.  The basis of 
> Kraft is close, it's off by only $.74.  I can't see any obvious place 
> this comes from.  However it shows a realized gain for Kraft that is 
> way off.  It's equal to all the money that has flowed in or out plus 
> $.74 so it's off by a factor of 1000 or so.

I'd love a test file of this. I think again, it may be an issue of how
the data is entered versus the logic of the report. How do you enter a
spin-off like this? What txn structure shows the reduction in Altria
basis? Is there an actual reduction in the number of shares or some
other method?

> 
> I also have another stock (Johnson & Johnson) for which I have 3 
> acquisitions, one partial sale, and several splits.  It seems to have 
> had trouble with this one since it thinks the basis is zero, which is 
> clearly wrong.  Again I used the lot scrubber to create a capital gains 
> split for the sale and the lot scrubber gets the correct gain.  The 
> report shows a huge realized loss that is much larger than the sale 
> price, as if the basis for that lot was a large negative number.
> 
> If there are lots assigned to the splits for a stock or other asset, 
> the report should use them.  In this last case, the sale was part of 
> the second of three acquisitions.  This is correctly recorded in the 
> splits (which I realize is hard to do now, but someday it might be 
> easier) and the report should really use this information to determine 
> the basis for the shares sold like the lot scrubber does.

I honestly don't have a clue about lots and how they work. Its very
possible that I've duplicated code that the lots system already
handles. However, it's not immediately clear to me that lots will be
used and so we have to build for the case we know we have, which is
the txn data. At this point, I'm not sure I'm really willing to go
that deep into it, as my main goal is to get something usable out of
the mess that curerntly exists. But maybe there's something obvious
and simple that I'm missing.  Again, a test file would be great.

> 
> I've only looked at a few lines in the report so this isn't a complete 
> list of the possible problems.  Sorry I don't have more time to work on 
> this right now.  I appreciate the work you are doing on it.  This 
> version seems to be better than before and I hope these comments help 
> improve it.  I'm sure you would like to have test data that 
> demonstrates some of these problems.  Perhaps I'll have time to create 
> some, but I can't promise it right now.

If I get one, great! If not, so it goes. 

The biggest issue I'm coming up against is that there are *so many*
possible permutations of things that could be done, that it's
practically impossible to account for them all. There are only a
handful of ways that a report can see information -- those few ways
don't necessarily convey all the information needed. The
donated/expensed shares are a perfect example of this. There is *no
way* for the report to know that this particular expense is different
from any other particular expense. This makes it impossible to code
for that case, or frankly many other cases as well, such as the
employer's IRA contribution.

I think that probably the long term solution is to take the stock
register away from the user and get all these transactions built in
some wizard (this was mentioned previously on one of the lists) so
that the reporting can build reports based on a systematic
structure. This may involve hackery such as a predetermined, immutable
set of accounts or actions for these things*. The interim solution is
to get reasonably stable and predictable performance out of the report
(which I think we're really close to) and then adjust the docs to
reflect that performance.


A

* I think this is essentially what Quicken does -- create a pre-fab
  set of accounts for use in investment transactions. That makes it
  predictable. If you want a report that shows only long-term capital
  gains, then the report looks for that account. We have no way of
  doing this right now, unless you run some other report -- say an
  income statement, filtered to whatever account you've set up for
  LTCG. Right now, there are some pre-set Actions that could be used,
  but there is no guarantee that the user will use them, making them
  meaningless for the report. Again, some kind of wizard could "force"
  the issue, I suppose.
-------------- next part --------------
A non-text attachment was scrubbed...
Name: not available
Type: application/pgp-signature
Size: 189 bytes
Desc: Digital signature
Url : http://lists.gnucash.org/pipermail/gnucash-devel/attachments/20071130/6e797961/attachment.bin