GNOME Bugzilla – Bug 346062
advanced portfolio doesn't compute basis, gains/losses correctly
Last modified: 2018-06-29 21:08:47 UTC
Please describe the problem: This may be a duplicate bug 344566 (http://bugzilla.gnome.org/show_bug.cgi?id=344566). However, while that bug was specific to a certain type of transaction, my advance portfolio report was simply a big mess after upgrading to 1.9.8 (from 1.8.12). The report managed to compute the correct basis and realized/unrealized gains correctly for only a few of my accounts. In most cases, it was way off. Here are a few particular cases where it seems to be confused: 1) My 401(k) plan has a set of mutual funds that are only available to participants in the plan. Hence, they do not have a ticker symbol, and I can't get price quotes using Finance:Quote. Thus, I use the price editor to enter the prices manually. In any event, for all of the funds in my 401(k) plan, the advanced portfolio report computes my basis, money in, money out, and realized gains to be zero. It thus lists the current market value of the funds as my "unrealized gain," which is obviously incorrect. 2) The report seems to get confused when a fund purchase comes from a split transaction. For example, I purchased two funds earlier in the year. For the first purchase, I bought $2000 worth of one fund. All $2000 that I used for the purchase came from my checking account. My basis, and realized/unrealized/total gains are computed correctly for this fund. However, for the second fund, I bought $2000.55 worth of shares. $2000 came from my checking account, and $0.55 came from the interest earned in the sweep account where I put the first $2000. Thus, I recorded this purchase as a split transaction, with $2000.55 coming into the mutual fund account, and a debit of $2000 to my checking account and $0.55 to my sweep account. In this case, the advanced portfolio report says that I have a basis of $0.55, a realized gain of -$2000, and an unrealized gain of $2025.29. The report seems to think that the $2000 from my checking account was the capital gains from a redemption rather than the cash used for a purchase. 3) I saw some other confusion caused by split transactions as well. For instance, in a different account, I wrote a $4000 check from my checking account to buy $3000 worth of fund A and $1000 worth of fund B. A month or two, later, I bought another $1000 worth of fund B. In this case, the advanced portfolio report is saying that my basis in fund B is $5000 (instead of $2000) with a realized gain of $3000 (should be $0). 4) In some other cases, the numbers being reported were simply bizarre. In one fund account, I made two $3000 purchases and a $300 purchase plus a number of dividend reinvestments. There were no split transactions that could potentially confuse the report as described above. Still, somehow the advanced portfolio report lists $6300 in the "Money In" account (presumably it's not counting dividend reinvestments), a basis of $43,000, and a realized gain of $36,000. I have no idea where those numbers came from. Many of my other accounts had numbers that were equally inexplicable. Steps to reproduce: 1. Start gnucash. 2. Open the "Advanced Portfolio" report under the "Reports" menu. Actual results: See above. Expected results: The bais and gains/losses should be computed correctly. Does this happen every time? Yes. Other information: If necessary, I can e-mail my gnucash data files to a developer in order to reproduce this bug. (I don't want to put all my financial data in an attachment that anyone can read, but as long as it won't end up on the Internet, I'm happy to share it.)
I upgraded to 2.0 today, and this bug is still present. I updated the version number on the bug accordingly.
I'm having very similar results. For example, for my 401k, I record my paycheck with many splits, one for the 401k deposit. On advanced portfolio, I get "money in" as -sum(paychecks minus contributions). I can't figure out what the computation is for "money out," but it doesn't correspond to reality (no money has been withdrawn, but money out is a large negative number). Realized gain, unrealized gain, total gain, and total return are all nonsense as a result. I am also happy to email my data files to a developer in order to reproduce the bug.
Potentially related bugs (feel free to close any of them or this one as "duplicates" if they are in fact identical or a direct subset of this bug): bug#336240 bug#342245 bug#343245 bug#344566 bug#346062 bug#347975
I was playing around with this over the course of the past few days, and I noticed that the advanced portfolio report produces different results depending on how I record realized capital gains and losses. Specifically, depending on whether or not I record a realized capital gain as a split in the transaction where I sold the stock or as a separate transaction, the advanced portfolio report produces different results. The report was still incorrect in both cases, I should note. Still, I think the fact that different results are obtained depending on how the gains are recorded is significant.
*** Bug 376984 has been marked as a duplicate of this bug. ***
*** Bug 428679 has been marked as a duplicate of this bug. ***
tomroeschen@sbcglobal.net
I don't think this is actually a duplicate of #344566 as originally stated. meanwhile, the advanced portfolio report is pretty dumb, frankly, and can't guess the intent of various actions. If someone could provide a test file relevant to the issues listed above, that would be great. likewise, some significant changes were made in svn r16620 that may improve this situation. If any of you can test this, that would be great as well.
Created attachment 101086 [details] A test case for problem 3 in the original report.
In trunk, specifically with the advanced portfolio from r16637, I'm still seeing the same problem as the OP described as 3. In comment 9, I've uploaded an attachment that is a test file for that issue. Bug #355660 may be related. Also, I had to manually enter price data to get anything other than $0. Shouldn't it take the data from the transaction? There's a message to this effect that occurs only in that case. I'd be happy to do more testing or whatever you need.
Bug #355660 is definitely related, in that it's a split txn about which we just can't get enough information. This is another case of needing to move funds through an interim brokerage account and making individual Buy txns.
I don't understand. Are you saying it's not okay to use a split transaction to buy two stocks/funds at the same time? In other words, are you saying that I should enter two buy transactions rather than one split? If so, then respectfully, I disagree. The report should be looking at the stock side of the transaction, rather than the other side. In other words, something like this pseudo-code: basis = sum([(split.price * split.shares) for split in account])
Actually, the basis is computed correctly, as far as I can tell, using the latest version of this report and your test file. It's the other parts -- money-in/out and subsequently gains that are wrong. I agree with you that you *should* be able to enter a split txn with multiple buys in it, but at this point, you *can't* do that and still get accurate information. So in this context, yes that is what you should do. I'm not saying that's right or proper, just the way it is. Trust me, I'm not happy about it either :( There are various crufty things going on in this report that I'm gradually getting a handle on. One is the basis calculation, which so far as I can tell, is now working correctly. In the file you provided, I'm seeing a basis for $500 for each stock. This is correct. I would say issue 3 in the original report is fixed. But the incorrect money-in and money-out parts are a pain. But I think you've clued me in to a potential solution there. Watch for commits on that in a few days. Finally, I think the issue in comment #4 is fixed, if I understand what he's saying correctly.
Sorry, you're correct. It is indeed the Money In column that's wrong in my test case.
The basis is still way off for many of the accounts on my advanced portfolio report. (I have one account with about $6000 worth of assets, and the advanced portfolio report is listing a basis of -$215000!) As I said in my first post, I'm a little leery of putting all my personal financial information on the web where anyone can download it, but I'd be happy to e-mail my data file to a developer if they need a test file to reproduce the bug. (If there's no way to do that, I'll see if I can create a copy of my data file and delete all but the absolutely essential information.)
are you building from svn? or at least pulling the report from svn and running that? This sounds like the kind of error that should be fixed in svn. You are welcome to email the file to me. It will be kept secure.
Okay, I've made some more changes to this in SVN. please test r16684. I *think* all these issues are fixed. Otherwise, I'll need a test file to see exactly what the issue is. thanks.
The report in SVN still has issues for me. I'm going to attach a couple of example files and a diff of some changes I made with the report. The changes are fairly significant: 1. I've added an option to control whether brokerage fees are included in return calculations. By default, they are ignored and you get the results for the investment itself. The default is useful if you're tracking commissions on stock trades, for example, and are interested in comparing the peformance of the underlying stocks. Changing the option is useful if you're tracking, for example, mutual fund load and you want to compare two funds with different loads (or a loaded fund to an index after taking into account the load). 2. Income is assumed to be a dividend. I removed the FIXME here. There's nothing we can do when someone invests directly from and Income to a security. They need to go through a brokerage "cash" account instead. In general, this is how things work anyway, and it matches one of your comments to this effect in one of these bugs. 3. I added an income column to the report. Income (dividends) now shows up in the income column and NOT the realized gains column. It's still calculated in return. Basically, this just adds some granularity so you can see the difference between income and gains. With these changes, the reports for my accounts produce the results I would expect. I am seeing some minor round-off errors, but I was seeing those with the report in SVN as well.
Created attachment 101368 [details] [review] My changes.
Created attachment 101369 [details] A small example with a dividend and no gains.
Created attachment 101370 [details] The dividend example with a brokerage fee.
Richard, thanks for doing this. I was just about to sit down and refactor the conditional that controls the handling of the splits... so perfect timing! ;) I just glanced over your patch and I looks good at first blush. I'll spend some time tomorrow actually reviewing it wiht an eye towards committing it right away. thanks again!
Speaking of splits, I forgot to mention a couple of things: 1. Split dividends are broken. I'm not sure how to code a fix for that. I would like to make it look at the transaction and if there is an income account, treat the corresponding buy split as a dividend. In other words, if I have a 3-split transaction with $500 from Income:Dividends buying $300 of stock A and $200 of stock B, it should allocate $300 as a dividend on stock A, in addition to the basis calculation that it already does on the other side. I had change a few dividend transactions to be two two-split transactions instead of one three-split transaction to make the reporting work. 2. I would guess that brokerage fees are broken in splits. If I spend $1100 to buy $300 of stock A and $700 of stock B with a $100 expense, it's likely going to allocate that $100 to each stock, leading to incorrect results. If we want to support this, I'd say the expense should be split either evenly: fee / number_of_splits_that_are_securities) or proportionally: fee / sum(cost_from_splits_that_are_securities) * cost_of_the_split_that_is_a_security So, in this example, you'd either allocate $50 to each or $30 to one and $70 to the other. This particular issue doesn't affect me. If it did, I'd probably be fine being told to split the transactions.
Still reviewing your patch. To 1. The problem is: how do you tell which stock generated the dividend? You can't without moving into territory I'm still hesitant on: namely keying off the account name*. And you mention buying some of stock A and some of stock B but only applying the dividend to stock A, what about the other $200 and/or stock B. I really think its too ambiguous a situation. To 2. This is really the same problem as above. Essentially, intent can't be gathered without some other system. Again, I think splitting the txn is the right thing to do at the moment. Making the assumption about how to apply the fee is something we just shouldn't do. Invariably it would be wrong. As has been said several times (by those with better skills/knowledge than me) buys should really be separate transactions. Then many many of these problems simply go away. * I'm not opposed to keying off account names. Even, keying off user settable account names... And there are some very powerful things that could be done that way: key off account names using the account names set for the commodity. So you could key off the account name where it's an income account that matches the account name of the commodity. If the name of the stock account is Asset:Stocks:IBM, we could look for Income:Dividends:IBM matching the two instances of IBM.... that would be nice. But that's down the road. Your patch looks to mostly implement new features in the report. So I'd like to *not* backport it to stable as I don't want to introduce new features into the stable branch. And I'd like to see what bugs percolate up from the current batch of fixes. Finally, I'm still waiting to hear on the specific issues of this bug. I still think they are fixed and am waiting to hear from Eric Bair or someone else with the same symptoms. I know this report is still a big problem, but I would like to chew on little bits at a time and keep the bug discussions discrete and well-defined. We are drifting OT to the specific issues in this bug. I hope you understand. If I don't get some feedback on the changes in r16684 in the next little while, then I'll either close this or tag it NEEDINFO. I encourage you to continue to participate in this discussion on gnucash-devel@lists.gnucash.org, directly with me, or in enhancement bug reports. Meanwhile, watch for your patch to hit SVN shortly.
patch applied r16693 with minor changes: I extended the horizontal-rule another cell and made the tooltip match, instead of conflict with, the option name ;-). thanks Richard!
I'm not dead-set against doing separate transactions. The problem, of course, is that people have existing data sets. Even if you can't cover all the cases, if you can cover the biggest ones, that greatly reduces the need to re-split transactions. I realized I missed the second part of that example, but I didn't bother with another comment, figuring it was obvious. Let me try this again: 12/01/07 Dividend 401k:Company:Stock 1 1.0000 10.0000 $100.00 401k:Me:Stock 1 1.0000 10.0000 $100.00 Income:Dividends $200.00 This is an example from my 401k plan. For tax reasons, they track my contributions separately from company matches. Thus, I have them tracked as separate accounts. My broker shows the dividends as one transaction, so that's how I entered them originally. I want the report to realize this is $100 of dividend for each stock, based on the split side, rather than taking it as a $200 dividend. Anyway, I can see why you wouldn't want to try to deal with this case. I just wanted to clarify my example. Thanks for reviewing the patch. I'll take a look at what you committed.
Well, the split buys (multiple buys in one txn) should be solved as Money In is now calculated from the Buy split of the txn instead of the "other" split, and that was the source of the problem. As to your example in comment #26: I think I see what you mean, that the report should take the dividend amount from the stock side of the split, and that's fine I guess. So you want us to, when we hit a dividend split, grab the buy split from that txn and use the buy split to calculate the dividend. Otherwise you are seeing each stock show a dividend of $200, right? This idea could just as easily be applied to brokerage fees as well, which I think is what you were getting at above. That all seems perfectly reasonable to me. Again, though, we're drifting OT to this original bug. Please start a new bug for Advanced Portfolio enhancements. Then we can talk about and collect all sorts of ideas. I'm waiting for someone to confirm, or deny, that the original issues in this bug are fixed. I still think they are... and need a testfile to show otherwise. Eric?
Rich, so that was a little tricky, though I've got it sorted. I can now calculate dividends from the buy split, but that raises a question for me. What about brokerage fees involved with a reinvested dividend? I've never seen one, but that doesn't mean they don't happen. If we use the buy side then we'll show as income the amount *after* a fee is deducted. That would be incorrect as the income is still the amount from the income split. It's better, isn't it, to calculate the proportional amount and then apply that back to the income split. thoughts?
Yes, I think applying the proportional amount of the fee is the best bet. That would help in other cases too, where you have a split buy and a brokerage fee.
Rich, thanks: see r 16774 for proportional fees and dividends. I think it works pretty well, let me know. Also, closing this bug. I think everything raised in it is fixed. If anyone disagrees, please speak up.
GnuCash bug tracking has moved to a new Bugzilla host. This bug has been copied to https://bugs.gnucash.org/show_bug.cgi?id=346062. Please update any external references or bookmarks.