GNOME Bugzilla – Bug 776300
Advanced Portfolio Income (maybe Brokerage Fees too) incorrect in switch and multiple investments
Last modified: 2018-06-29 23:52:46 UTC
First of all, note that I've put all my investments operations (fees, capital gains, dividends, ...) into Income accounts (Income:Finance:...), so I can have the net finance balance directly. Therefore in advanced Portfolio I have not-zero values only in Income column, while Brokerage Fees column has all zero values. So I couldn't check Brokerage Fees column values in Advanced Portfolio, but I think it would be affected by the same problem. I've found two situations causes incorrect Income values in Advanced Portfolio Report: 1) Switch Investments Switching e.g. from FUND 1 (sell) to FUND 2 (buy) (without involving your Checking account) generally you have to pay fees (e.g. 5,00 Euro) charged to FUND 2, while FUND 1 doesn't have any fee (sell is for free). In this case Advanced Portfolio income column charge 5,00 Euro both to FUND 1 and FUND 2. 2) Multiple Investments With just one operation (one transfer from your Checking Account Bank ABC) you buy two funds FUND 3 and FUND 4, paying 5,00 Euro fee each (total fee 10,00 Euro). In this case Advanced Portfolio income column charge 10,00 Euro both to FUND 1 and FUND 2. Please note that all totals in account tree (mainly Income:Finance:...) are always correct.
It sounds like you are putting multiple splits for different stock (or mutual fund) accounts in the 1 transaction. Advanced Portfolio Report is not designed to handle that. It would be very complicated to handle that sort of transaction. Put them in separate transactions even if they didn't happen like that. For item 1 you'll have to do a dummy in/out of the bank or brokerage account (or some other account). Please let us know if the commission is still a problem after you do that. Note that if you enter splits for fees (or commissions) you will have to manually calculate capital gains rather than using lots for the automatic calculation and creation of capital gain transactions. This is more work but perfectly valid. If your jurisdiction allows you to deduct fees and you wish to use automatic calculation of capital gains, you must enter net prices (less fees) on the stock splits and must not enter splits for the fees. I am currently documenting this. This means it is not easy to track fees in specific accounts but this is how it currently is. In future, you might like to discuss this sort of thing in the gnucash-user email group before you go to the trouble of raising a bug.
Let me check gnucash-user email group first, as you suggested me. I'm sorry, thanks
I forgot to say, interesting idea making your fees income accounts. I assume the report will not recognize any brokerage as it expects them to be expenses.
I'm thinking about creating a dummy account: Assets:Current Assets:Checking Account:DUMMY BANK (which total must be always zero) to use as intermediate step between: 1) To manage switch investment operations: Assets:Investments:Broker DEF:Mutual Fund:FUND 1 Assets:Current Assets:Checking Account:DUMMY BANK Assets:Investments:Broker DEF:Mutual Fund:FUND 2 Assets:Investments:Broker DEF:Mutual Fund:FUND 3 Assets:Current Assets:Checking Account:DUMMY BANK Assets:Investments:Broker DEF:Mutual Fund:FUND 4 2) To manage multiple investment operations: Assets:Current Assets:Checking Account:BANK ABC Assets:Current Assets:Checking Account:DUMMY BANK Assets:Investments:Broker DEF:Mutual Fund:FUND 1 Assets:Investments:Broker DEF:Mutual Fund:FUND 2 Assets:Investments:Broker DEF:Mutual Fund:FUND 3 Assets:Investments:Broker DEF:Mutual Fund:FUND 4 This should solve the problem in a handy, strong and clear way. (I know, this should be a gnucash-user email group discussion, just to know your opinion)
Hi Alberto, Your new suggestion still has multiple splits for different stock (or mutual fund) accounts in the 1 transaction. You need to do for your example 1: Transaction 1: Sell Fund 1 Assets:Investments:Broker DEF:Mutual Fund:FUND 1 Credit Assets:Current Assets:Checking Account:DUMMY BANK Debit Transaction 2: Buy Fund 2 Assets:Investments:Broker DEF:Mutual Fund:FUND 2 Debit Assets:Current Assets:Checking Account:DUMMY BANK Credit etc You can use the real bank account or brokerage account so long as you remember these transactions do not actually affect the bank account. Please see https://www.gnucash.org/docs/v2.6/C/gnucash-guide/invest-sell1.html#invest-sellLots for info on how to use lots for automatic calculation of gain.
Hi Chris, with dummy transaction all works fine: switch and multiple investment operations. You must avoid split transactions at all, in order to have correct Advanced Portfolio values. Moreover, assumed that after a total sell (0 shares owned): Money Out - Money In + Income - Brokerage Fees = Total Return I realized the problem is with reinvested dividends too: if you follow the user manual "Dividends Re-Invested", the transaction seems to be a split transaction for Advanced Portfolio, with incorrect Money In column values. To solve the problem, again I used a dummy transaction: first pay the dividend, then invest it. Maybe user manuals should be improved for not-so-experienced users like me. In a short time, I'm going to study and practice about Automatic capital gain calculation and Training Account values meaning. Thanks for your kindness, best regards Alberto
Hi Alberto, Sorry about the long delay since my last response which is due to my holidays. I guess when you are talking about split transactions, you mean where there are 2 splits for different stocks in the same transaction. As there are at least 2 splits in every transaction, it is not possible to avoid split transactions. I'm afraid I don't understand where or how exactly you think the manual can be improved. If you can give specific example(s) of where it is wrong and what it should be, I'll see if I can make it better. Regards, Chris Good
Created attachment 343326 [details] testcase
Hi Chris, I'm sorry for the long delay due to end year holidays. Please have a look at attached files (screenshots are of the Advanced Portfolio reports). You can compare results using split transactions (wrong report results) and no-split transactions (with DUMMY BANK right report results, as account tree values) in the following cases: 1) Switch Investments (FUND 1 and 2) 2) Multiple Investments (FUND 3 and 4) 3) Reinvested dividends (FUND 5) I think user manuals should emphasize that split transactions must be avoided in order to have right Advanced Report results (for not-so-experienced users as me). I've lost many time to separate lot of split investment transactions. Hope this helps. Thanks, best regards Alberto
Hi Chris, please note that I encountered these situations in the real life (I think I'm not so creative). Split transactions respect real transactions, while separate transactions using Dummy Bank is a trick. Split and separate transactions give the same values on the account register, but Advanced Investment report gives correct results just with separate transactions. So, I think, the right way to proceed should be to make Advanced Investment report working fine with split (real) transactions too. Modify manuals is a trick again. This is my opinion. Thanks, best regards Alberto Dante
This comment should be before comment 10: Hi Alberto, OK, that sounds like a useful addition to the manual. You're not the first to get into trouble from being too creative. Regards, Chris Good
Even though what you did mirrors your real life events (usually a good practice), there is nothing in the manuals that says you can put splits for different stocks in 1 transaction. I intend to add that you should NOT do that if you wish to use the Advanced Portfolio Report (APR). Yes it is a trick to use a dummy intermediate bank transaction to change a switch from 1 fund to another in 1 transaction to a sell in 1 transaction and a buy in another transaction, but to be realistic, to make the APR handle multiple different stock accounts in 1 transaction would, IMHO, be a huge amount of work and the user setup required to make it work, would make it much more ungainly to use. As there is a simple work-around, it is unlikely a developer would ever decide this was a good use of their time. Of course if you could do this yourself, or were prepared to pay for some-one else to do it, that may be a different story. The reason I think this would a huge amount of work, is that the APR (AFAIK) makes assumptions about what the other splits in a transactions are, depending on what type of account they are (Income or Expense etc). If you have multiple stock accounts in a transaction, how would it know how to apportion the fees, income etc to what stock? I admit I haven’t thought long about this as it is not a priority for me. If you’re a programmer and have any ideas about how to simply implement your required functionality (and still be useful to other users), please share them. Just to be clear: Note GnuCash reports are written in Scheme and I’m only a Scheme beginner, and I’m not offering to do this work. Regards, Chris Good
Hi Alberto, Re your comment: Please note that FUND 5 dividend reinvest translation involves only one stock : You seem to be saying that ‘Money In’ should include cost of stock acquired as part of the DRP but that is not what the APR does. As per https://www.gnucash.org/docs/v2.6/C/gnucash-help/report-classes.html#report-assets : Money In: Sum of the cost of stock purchased, excluding stock acquired as part of a Dividend Reinvestment Plan. I.e. External money used to purchase shares. In this case, you SHOULD to do the dividend and the addition of the stock in the 1 transaction so that APR knows the addition was from the DRP. If you do it as 2 transactions, APR doesn’t know that the stock was acquired as part of the DRP, although maybe this is what you really want. Re your suggestion that the APR can use the fact that the Stock Account is one level of the full fees/dividends/capital gains account to determine which stock the split should be applied to: That seems a reasonable way to do it. The code would have to cater for the fact that different people have different chart set ups. For instance, your example uses Income:Finance:Investments:Broker DEF:Mutual Fund:FUND 5:Dividend But mine is Income:Dividend:FUND 5:Franked:Chris
GnuCash bug tracking has moved to a new Bugzilla host. The new URL for this bug is https://bugs.gnucash.org/show_bug.cgi?id=776300. Please continue processing the bug there and please update any external references or bookmarks.