After an evaluation, GNOME has moved from Bugzilla to GitLab. Learn more about GitLab.
No new issues can be reported in GNOME Bugzilla anymore.
To report an issue in a GNOME project, go to GNOME GitLab.
Do not go to GNOME Gitlab for: Bluefish, Doxygen, GnuCash, GStreamer, java-gnome, LDTP, NetworkManager, Tomboy.
Bug 739228 - Advanced Portfolio report: wrong calculation of Value
Advanced Portfolio report: wrong calculation of Value
Status: RESOLVED FIXED
Product: GnuCash
Classification: Other
Component: Reports
2.6.4
Other Windows
: Normal major
: ---
Assigned To: gnucash-reports-maint
gnucash-reports-maint
Depends on:
Blocks:
 
 
Reported: 2014-10-26 22:45 UTC by alex seluzhytsky
Modified: 2018-06-29 23:35 UTC
See Also:
GNOME target: ---
GNOME version: ---


Attachments
example for brokerage fees calculation (3.78 KB, application/x-gnucash)
2015-02-11 18:57 UTC, alex seluzhytsky
Details

Description alex seluzhytsky 2014-10-26 22:45:50 UTC
in the Advanced portfolio report, Basis is calculated based on currency selected in report options, but Value is taken as is, that is in the currency of a security.

Example:
stock XYZ is traded on NYSE in USD
Report is in CAD

Basis is shown as price of XYZ on the day of purchase converted to CAD, but value is shown in USD, thus making all other fields (gain, etc.) absolutely meaningless.

If I change report currency to USD, all is good, but securities that are traded in other currencies (e.g., CAD) are wrong as their basis is converted to USD, but current value is in CAD.
Comment 1 sascha.oesterle+gnome 2014-11-02 09:36:25 UTC
I want to second this. Same behavior in MacOS.
Comment 2 Edgar 2014-11-20 19:23:53 UTC
I see the same problem with multiple currencies on Win 8 64 bit and Mac - no currency exchange is calculated for any of them (GNUCash version 2.6.4) - also the currency symbol is screwed up - shows e.g. $ for AUD. Changing the report currency has no effect on the numbers - only a different (wrong) currency symbol.
I uninstalled GnuCash and went back to version 2.4.15 on Win 8 (64bit) and Mac - all works well as before with this older version.
Comment 3 Mike Alexander 2015-01-19 05:47:29 UTC
I just pushed a change that should fix this problem.  Somehow I didn't get (or missed) an EMail about this bug so I didn't know about it until today.  Sorry for the delay.
Comment 4 alex seluzhytsky 2015-02-09 22:58:48 UTC
thank you for the changes. It now uses correct currencies. But there are other issues:

1. Basis includes brokerage fees, so Basis is now equal to Money in. 

2. report uses the last available exchange rate for both Basis and Value. While it is  correct for the Value, it is not for the Basis, as shares could have been bought in the past with different exchange rates. 
The Basis should be calculated as acquisition cost x exchange rate on the day of acquisition, not the date of the report. I opened a separate bug for this one.
Comment 5 Mike Alexander 2015-02-10 05:45:17 UTC
There is an option to control how the report treats brokerage fees.  It gives you three choices: "Include in basis", "Include in gain", or "Ignore".  It sounds like you have "Include in basis" selected and want one of the other two.  If this doesn't solve point 1, let me know.

With regard to point 2, I assume you're talking about the case where the stock is traded in a currency other than the report's currency.  You're right that it should be using an exchange rate close to the purchase date for converting the foreign currency cost to the report's currency for basis calculations.  I'm going to mark this bug fixed since you opened bug 744193 for that issue.
Comment 6 alex seluzhytsky 2015-02-10 14:11:10 UTC
you are right. Sorry about that.

The new report added the option you mentioned (include brokerage fees in basis, gain or ignore), but it seems this somehow screwed up the way they are calculated.
I still can't get my head around how it works, trying to compare the report in 2.4.15 and 2.6.5.
The thing is, in 2.4.15 brokerage fees are calculated correctly, that is just the sum of all fees associated with that security. But in 2.6.5 (including the corrected report) the logic escapes me. 
For example, my brokerage fees are $9.99. My default currency is CAD, and for Canadian securities it seems that fees are calculated correctly, but for US ones something weird happens. E.g., security with 7 transactions (each time $9.99); in 2.4.15 it returns brokerage fees of $69.93 (9.99x7); in 2.6.5, this amount is 229.43. In another example with 5 transactions, in 2.4.15 it is  $49.95, in 2.6.5  - $139.43 (funny, it always ends with .43). 
This doesn't change when I use Include in basis or Include in gain.

Any ideas what is going on?
Comment 7 Mike Alexander 2015-02-11 05:00:32 UTC
I have no idea what's going on.  Can you attach a file that demonstrates this problem to this bug report?  Or perhaps to bug 744193 since I suspect this is a related problem since it only seems to happen when the currency used by the security is not the same as the report currency.  Let's use that bug for problems related to that.
Comment 8 alex seluzhytsky 2015-02-11 18:54:09 UTC
(In reply to Mike Alexander from comment #7)
> I have no idea what's going on.  Can you attach a file that demonstrates
> this problem to this bug report?  Or perhaps to bug 744193 since I suspect
> this is a related problem since it only seems to happen when the currency
> used by the security is not the same as the report currency.  Let's use that
> bug for problems related to that.

Hi Mike,
I think I figured it out. It has nothing to do with currencies in this case.

When you enter dividends with taxes in the same transaction (withdrawal tax in US)the report somehow picks them up and adds to brokerage fees.
This issue exists in 2.6.5 (and in the corrected report in Git), but the report  works correctly in 2.4.15.
I attached the test file to see how it works (compare results in 2.4.15 and 2.6.5)

Do I need to open another report for this?
Comment 9 alex seluzhytsky 2015-02-11 18:57:06 UTC
Created attachment 296631 [details]
example for brokerage fees calculation

when dividends are entered as a split transaction with taxes withdrawn, advanced portfolio report in 2.6.5 adds these taxes to brokerage fees.

it works correctly in 2.4.15, that is only expenses associated with buying or selling securities are added to brokerage fees.
Comment 10 Mike Alexander 2015-02-15 07:01:21 UTC
Yes, it assumes that any split to an expense account that is part of a purchase or sale transaction represents brokerage fees.  How do you suggest that it should determine which of these are not brokerage fees?  Perhaps "brokerage fees" is a bit of a misnomer.  It's really expenses of all sorts related to the holding, including taxes and other expenses.

If it worked in 2.4 it's only by coincidence.  There were so many bugs in that version of the report that it's hard to tell why it worked that way.
Comment 11 alex seluzhytsky 2015-02-16 16:39:03 UTC
agree that we should include all expenses related to holding of a security. The only problem with taxes is that they are not final. The withdrawal tax is often taken automatically as a fixed percentage and is needed for tax tracking purposes. The actual tax maybe higher or lower depending on the final tax rate (which is not known until later and even then it is hard to apply it to individual transactions). 

There are both advantages and disadvantages to include taxes into "brokerage fees". So maybe the best way would be to add this choice into report options?
Comment 12 Mike Alexander 2015-02-17 00:18:18 UTC
Fine, but how does the report figure out which expense accounts are for brokerage fees and which are for taxes?  I still don't know a good algorithm for that.

Another way to deal with this is to use a liability account for prepaid taxes.  Then when you file your tax return and know how much the actual tax is, you pay all or part of it from that liability account.  Anything left over should be a refund.  Once years ago I read that this is a good way to handle this and it's what I've been doing for the last 7 or 8 years.  You can also use an asset account to record the refunds due which haven't yet been received.  If you do it this way the report won't consider these prepaid taxes to be an expense.
Comment 13 alex seluzhytsky 2015-02-17 01:54:28 UTC
This is a really neat way to handle taxes. Thanks for the tip! Will try to set up accounts for this.

Regarding the algorithm, I don't know what may be the best way. If most people use parent expense account named "Taxes", then, if it's possible, excluding all transactions related to this parent expense account and its sub-accounts may be a solution.
Comment 14 John Ralls 2018-06-29 23:35:34 UTC
GnuCash bug tracking has moved to a new Bugzilla host. This bug has been copied to https://bugs.gnucash.org/show_bug.cgi?id=739228. Please update any external references or bookmarks.