Advanced Portfolio Income (maybe Brokerage Fees too) incorrect in switch and multiple investments transactions

Alberto Dante alberto.dante at gmail.com
Wed Dec 21 12:31:55 EST 2016



On 21/12/2016 02:28, Chris Good wrote:
>> Message: 12
>> Date: Tue, 20 Dec 2016 11:45:32 +0100
>> From: Alberto Dante <alberto.dante at gmail.com>
>> To: gnucash-user at gnucash.org
>> Subject: Advanced Portfolio Income (maybe Brokerage Fees too)
>> 	incorrect in switch and multiple investments transactions
>> Message-ID: <691053c2-2ebc-b2de-9cdd-f0c5ed915872 at gmail.com>
>> Content-Type: text/plain; charset=utf-8; format=flowed
>>
>> First of all, note that I've put all my investment operations (fee,
> capital
>> gain,
>> dividend, ...) into Income accounts (Income:Finance:...), so I can have
> the
>> net
>> finance balance directly, and I can compare different types of Income
> (Job,
>> Finance, ...) in a glance.
>> Simply, my account tree is something like that:
>>
>> Assets
>>           Current Assets
>>                   Cash
>>                   Checking
>>                           Bank ABC
>>           Investment
>>                   Broker DEF
>>                           Mutual Fund
>>                                   FUND 1
>>                                   FUND 2
>>                                   FUND 3
>>                                   FUND 4
>> Income
>>           Job
>>                   ........
>>           Finance
>>                   Investment
>>                           Broker DEF
>>                                   Mutual Fund
>>                                           FUND 1
>>                                                   Capital gain
>>                                                   Dividend
>>                                                   Fee / Commission
>>                                           FUND 2
>>                                                   ......
>>                                           FUND 3
>>                                                   ......
>>                                           FUND 4
>>                                                   ......
>>
>> Therefore, in Advanced Portfolio report 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 column values in
> Advanced
>> Portfolio Report:
>>
>> 1) Switch Investments
>>
>> When switching e.g. from FUND 1 (sell) to FUND 2 (buy) (without involving
>> Bank ABC 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);
>> total fee = 5,00 Euro.
>> In this case Advanced Portfolio income column charges 5,00 Euro both to
>> FUND 1 and FUND 2; total fee = 10,00 Euro.
>>
>> 2) Multiple Investments
>>
>> With just one operation (one transfer from Bank ABC account) you buy two
>> funds FUND 3 and FUND 4, paying 8,00 Euro fee each; total fee = 16,00
> Euro.
>> In this case Advanced Portfolio income column charges 16,00 Euro both to
>> FUND 1 and FUND 2; total fee = 32,00 Euro.
>>
>> Please note that all totals in account tree (mainly Income:Finance:...)
> are
>> always correct; other column values in Advanced Portfolio report seems to
>> be correct too.
>>
>> Moreover:
>> - Basis column how is calculated (when, after multiple buy operations, you
>> have a total sell and a new buy again)? Does Advanced Portfolio recognize
>> the total sell before the last buy?
>> - I calculate capital gain manually; how is it possible to find it
>> automatically?
>> Realized gain column does it already or uses my manually calculated
> values?
>> Thanks, Alberto
>>
> Hi Alberto,
>
> Just so people know what has already been suggested in bug 776300 [1]:
>
> Comment 1 from Chris Good:
>
> 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.
>
> Comment 3 from Chris Good:
>
> 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.
>
> Comment 4 from Alberto Dante:
>
> 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)
>
> Comment 5 from Chris Good:
>
> 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
>
> You can use the real bank account or brokerage account so long as you
> remember
> these transactions will not appear on your bank statement.
>
> Please see [2] for info on how to use lots for automatic calculation of
> gain.
>
> [1] https://bugzilla.gnome.org/show_bug.cgi?id=776300
> [2]
> https://www.gnucash.org/docs/v2.6/C/gnucash-guide/invest-sell1.html#invest-s
> ellLots
>
> Regards, Chris Good
Hi Chris,

with dummy transactions all works fine: both 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" chapter, 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.

Thanks for your kindness, best regards Alberto


More information about the gnucash-user mailing list