Advanced Portfolio Income (maybe Brokerage Fees too) incorrect in switch and multiple investments transactions
Alberto Dante
alberto.dante at gmail.com
Wed Jan 11 14:35:26 EST 2017
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
On 31/12/2016 01:14, Chris Good wrote:
>> -----Original Message-----
>> From: Alberto Dante [mailto:alberto.dante at gmail.com]
>> Sent: Thursday, 22 December 2016 4:32 AM
>> To: Chris Good <chris.good at ozemail.com.au>; gnucash-user at gnucash.org
>> Subject: Re: Advanced Portfolio Income (maybe Brokerage Fees too)
>> incorrect in switch and multiple investments transactions
>>
>> 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#in
>>> vest-sellLots
>>>
>>> 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
> 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
>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: Test-Advanced Portfolio-No Split transactions-Right report.gnucash
Type: application/x-gnucash
Size: 8642 bytes
Desc: not available
URL: <http://lists.gnucash.org/pipermail/gnucash-user/attachments/20170111/58b962e6/attachment-0002.bin>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: Test-Advanced Portfolio-Split transactions-Wrong report.gnucash
Type: application/x-gnucash
Size: 8124 bytes
Desc: not available
URL: <http://lists.gnucash.org/pipermail/gnucash-user/attachments/20170111/58b962e6/attachment-0003.bin>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: Screen Shot 2017-01-11 at 20.04.49.png
Type: image/png
Size: 161334 bytes
Desc: not available
URL: <http://lists.gnucash.org/pipermail/gnucash-user/attachments/20170111/58b962e6/attachment-0002.png>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: Screen Shot 2017-01-11 at 20.05.02.png
Type: image/png
Size: 163440 bytes
Desc: not available
URL: <http://lists.gnucash.org/pipermail/gnucash-user/attachments/20170111/58b962e6/attachment-0003.png>
More information about the gnucash-user
mailing list