Advanced Portfolio problems

classic Classic list List threaded Threaded
9 messages Options
Reply | Threaded
Open this post in threaded view
|

Advanced Portfolio problems

MonMul
In the Advanced Portfolio, when calculated in my native currency of NZ$,
the Value number of each holding not in NZ$ is calculated incorrectly. It
seems the US$ number is multiplied by the NZ$ rate rather than being
divided by it. Hence I get a lower value than I should do....For example I
have 523 shares in BBBY (Bed Bath and Beyond) they are currently trading at
US$68.80. I would expect the value of them to be around NZ$53,129 and
instead it is showing a valuation of NZ$35,982.

On a another matter. When I cut and paste the Advanced Portfolio
information into a Calc sheet in LibreOffice, the money amounts i.e.
NZ$1000 becomes text and I cannot work out how to make it a number so that
I can add up various parts of various columns. I have to go thorough the
spreadsheet and change them all manually to make them a number...there must
be a better way of doing this?

Thanks and keep up the good work!
_______________________________________________
gnucash-user mailing list
[hidden email]
https://lists.gnucash.org/mailman/listinfo/gnucash-user
-----
Please remember to CC this list on all your replies.
You can do this by using Reply-To-List or Reply-All.
Reply | Threaded
Open this post in threaded view
|

Re: Advanced Portfolio problems

Mike Alexander
--On July 15, 2015 at 8:24:08 PM +1200 dunbrokin <[hidden email]>
wrote:

> In the Advanced Portfolio, when calculated in my native currency of
> NZ$, the Value number of each holding not in NZ$ is calculated
> incorrectly. It seems the US$ number is multiplied by the NZ$ rate
> rather than being divided by it. Hence I get a lower value than I
> should do....For example I have 523 shares in BBBY (Bed Bath and
> Beyond) they are currently trading at US$68.80. I would expect the
> value of them to be around NZ$53,129 and instead it is showing a
> valuation of NZ$35,982.

According to my calculation USD68.80 is worth about NZD104.61.  How
many shares of BBBY are we talking about?  500?  This should be about
NZD52303.

Are you sure you have the correct price entered in the price data in
GnuCash?  Open the price editor from the Tools menu and look at the
entries for USD in the currency section.  They should show a security
of USD and a currency of NZD with a value between about 1.520 now and
1.314 a few weeks ago.  It sounds like you've somehow gotten the wrong
values into the price data.

I can't help you with the other problem since I've never tried pasting
the report into a spread sheet.  I know others have so it must be
possible.

             Mike
 
_______________________________________________
gnucash-user mailing list
[hidden email]
https://lists.gnucash.org/mailman/listinfo/gnucash-user
-----
Please remember to CC this list on all your replies.
You can do this by using Reply-To-List or Reply-All.
Reply | Threaded
Open this post in threaded view
|

Re: Advanced Portfolio problems

MonMul
The US$/NZ$ is 1.4932 . The price in the Price Editor of BBBY is US$68.8
(Google has it at US$69.03 today) and I have 523. So, 523*68.8*1.4932 =
NZ$53,728 or so....and the basis in Advanced Portfolio shows NZ$35,982. Now
if we multiply NZ$35,982 by 1.5 we get the correct number. Hence, my
contention that the way the Price Editor picks up the FX rate has got it
the wrong way around. How do we fix this?I have not put any FX values into
the Price Editor...it sources them independently....and it was correct up
until about 2 months ago.

On Thu, Jul 16, 2015 at 11:55 AM, Mike Alexander <[hidden email]> wrote:

> --On July 15, 2015 at 8:24:08 PM +1200 dunbrokin <[hidden email]>
> wrote:
>
>  In the Advanced Portfolio, when calculated in my native currency of
>> NZ$, the Value number of each holding not in NZ$ is calculated
>> incorrectly. It seems the US$ number is multiplied by the NZ$ rate
>> rather than being divided by it. Hence I get a lower value than I
>> should do....For example I have 523 shares in BBBY (Bed Bath and
>> Beyond) they are currently trading at US$68.80. I would expect the
>> value of them to be around NZ$53,129 and instead it is showing a
>> valuation of NZ$35,982.
>>
>
> According to my calculation USD68.80 is worth about NZD104.61.  How many
> shares of BBBY are we talking about?  500?  This should be about NZD52303.
>
> Are you sure you have the correct price entered in the price data in
> GnuCash?  Open the price editor from the Tools menu and look at the entries
> for USD in the currency section.  They should show a security of USD and a
> currency of NZD with a value between about 1.520 now and 1.314 a few weeks
> ago.  It sounds like you've somehow gotten the wrong values into the price
> data.
>
> I can't help you with the other problem since I've never tried pasting the
> report into a spread sheet.  I know others have so it must be possible.
>
>             Mike
>
>
_______________________________________________
gnucash-user mailing list
[hidden email]
https://lists.gnucash.org/mailman/listinfo/gnucash-user
-----
Please remember to CC this list on all your replies.
You can do this by using Reply-To-List or Reply-All.
Reply | Threaded
Open this post in threaded view
|

Re: Advanced Portfolio problems

Mike Alexander
--On July 16, 2015 at 12:43:23 PM +1200 dunbrokin <[hidden email]>
wrote:

> The US$/NZ$ is 1.4932 . The price in the Price Editor of BBBY is
> US$68.8 (Google has it at US$69.03 today) and I have 523. So,
> 523*68.8*1.4932 = NZ$53,728 or so....and the basis in Advanced
> Portfolio shows NZ$35,982. Now if we multiply NZ$35,982 by 1.5 we get
> the correct number. Hence, my contention that the way the Price
> Editor picks up the FX rate has got it the wrong way around. How do
> we fix this?I have not put any FX values into the Price Editor...it
> sources them independently....and it was correct up until about 2
> months ago.

I wasn't asking for the price of BBBY in USD,  I was asking for the
price of USD in NZD.  What is that in the price data base?  I suspect
that's what is wrong in your file.

          Mike
 
_______________________________________________
gnucash-user mailing list
[hidden email]
https://lists.gnucash.org/mailman/listinfo/gnucash-user
-----
Please remember to CC this list on all your replies.
You can do this by using Reply-To-List or Reply-All.
Reply | Threaded
Open this post in threaded view
|

Re: Advanced Portfolio problems

MonMul
The price of 1 US$ in NZ$ (according to the Price Editor...which has given
the correct price for the last 3 years and has only recently, as in the
last 2 months...I have changed nothing.... starting giving it in the
reverse way) in 1.4932 as above. Perhaps it will be clearer from the
attached screenshots.

On Thu, Jul 16, 2015 at 1:44 PM, Mike Alexander <[hidden email]> wrote:

> --On July 16, 2015 at 12:43:23 PM +1200 dunbrokin <[hidden email]>
> wrote:
>
>  The US$/NZ$ is 1.4932 . The price in the Price Editor of BBBY is
>> US$68.8 (Google has it at US$69.03 today) and I have 523. So,
>> 523*68.8*1.4932 = NZ$53,728 or so....and the basis in Advanced
>> Portfolio shows NZ$35,982. Now if we multiply NZ$35,982 by 1.5 we get
>> the correct number. Hence, my contention that the way the Price
>> Editor picks up the FX rate has got it the wrong way around. How do
>> we fix this?I have not put any FX values into the Price Editor...it
>> sources them independently....and it was correct up until about 2
>> months ago.
>>
>
> I wasn't asking for the price of BBBY in USD,  I was asking for the price
> of USD in NZD.  What is that in the price data base?  I suspect that's what
> is wrong in your file.
>
>          Mike
>
>

_______________________________________________
gnucash-user mailing list
[hidden email]
https://lists.gnucash.org/mailman/listinfo/gnucash-user
-----
Please remember to CC this list on all your replies.
You can do this by using Reply-To-List or Reply-All.

Screenshot from 2015-07-16 14:01:09.png (42K) Download Attachment
Screenshot from 2015-07-16 14:02:45.png (77K) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: Advanced Portfolio problems

Mike Alexander
--On July 16, 2015 at 2:03:49 PM +1200 dunbrokin <[hidden email]>
wrote:

> The price of 1 US$ in NZ$ (according to the Price Editor...which has
> given the correct price for the last 3 years and has only recently,
> as in the last 2 months...I have changed nothing.... starting giving
> it in the reverse way) in 1.4932 as above. Perhaps it will be clearer
> from the attached screenshots.

The basis of BBBY in the report is NZ$45,750.15, not NZ$35,982.40.
That is the current value, not the basis.

The price shown in the report is the current price, not the basis
price.  Since 523*68.80 is 35982.40 it appears that the report is using
an exchange rate of 1 for USD vs. NZD.  The report doesn't show the
purchase price or prices of BBBY that were used to calculate the basis,
but I suspect that they average around USD87.40.  The question then
becomes a matter of where did it find the bogus exchange rate of 1.
What other entries are there in the price data base for the price of
USD in NZD?  Did you somehow get an entry of 1 in there somehow?  What
is the date of the report and what price source is selected in the
report options.

If there aren't too many of them, list the purchase and sales of BBBY
that go into calculating the basis and let me know what basis
calculation method you used.

I have a test file that is essentially the same as this case and it
works fine.  I don't think there is anything fundamentally wrong with
the report.

         Mike
 
_______________________________________________
gnucash-user mailing list
[hidden email]
https://lists.gnucash.org/mailman/listinfo/gnucash-user
-----
Please remember to CC this list on all your replies.
You can do this by using Reply-To-List or Reply-All.
Reply | Threaded
Open this post in threaded view
|

Re: Advanced Portfolio problems

MonMul
Sorry, yes I got confused. The basis is NZ$45k as you say and the value is
the value in US$ and not NZ$ as I had wrongly assumed (given that there was
a NZ$ in front of the number). Indeed the conversion rate seems to be 1. I
do not know where the 1 came from. There are only 4 or 5 entries in the
Price Editor for the NZ$ (I did do a get rid of old quotes a few months
ago....so that may have a bearing!). You have seen the latest fx price and
attached is the earliest price...none of the fx prices in the price editor
show 1. The date of the report is the same date as I showed in the previous
screenshot 17 July 2015. The currency conversion comes from wherever the
default currency is chosen from. Could the time difference be a problem? NZ
is often on a different day to the US?

Thanks for your help....much appreciated.

On Thu, Jul 16, 2015 at 2:50 PM, Mike Alexander <[hidden email]> wrote:

> --On July 16, 2015 at 2:03:49 PM +1200 dunbrokin <[hidden email]>
> wrote:
>
>  The price of 1 US$ in NZ$ (according to the Price Editor...which has
>> given the correct price for the last 3 years and has only recently,
>> as in the last 2 months...I have changed nothing.... starting giving
>> it in the reverse way) in 1.4932 as above. Perhaps it will be clearer
>> from the attached screenshots.
>>
>
> The basis of BBBY in the report is NZ$45,750.15, not NZ$35,982.40. That is
> the current value, not the basis.
>
> The price shown in the report is the current price, not the basis price.
> Since 523*68.80 is 35982.40 it appears that the report is using an exchange
> rate of 1 for USD vs. NZD.  The report doesn't show the purchase price or
> prices of BBBY that were used to calculate the basis, but I suspect that
> they average around USD87.40.  The question then becomes a matter of where
> did it find the bogus exchange rate of 1. What other entries are there in
> the price data base for the price of USD in NZD?  Did you somehow get an
> entry of 1 in there somehow?  What is the date of the report and what price
> source is selected in the report options.
>
> If there aren't too many of them, list the purchase and sales of BBBY that
> go into calculating the basis and let me know what basis calculation method
> you used.
>
> I have a test file that is essentially the same as this case and it works
> fine.  I don't think there is anything fundamentally wrong with the report.
>
>         Mike
>
>

_______________________________________________
gnucash-user mailing list
[hidden email]
https://lists.gnucash.org/mailman/listinfo/gnucash-user
-----
Please remember to CC this list on all your replies.
You can do this by using Reply-To-List or Reply-All.

Screenshot from 2015-07-16 14:58:40.png (148K) Download Attachment
Screenshot from 2015-07-16 15:09:48.png (44K) Download Attachment
Screenshot from 2015-07-16 15:10:44.png (10K) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: Advanced Portfolio problems

Wm...
In reply to this post by MonMul
Wed, 15 Jul 2015 20:24:08
<CAF1oafdw1hvQF=n1VbhbV7w7Kicx=[hidden email]>
dunbrokin <[hidden email]> wrote...

>On a another matter. When I cut and paste the Advanced Portfolio
>information into a Calc sheet in LibreOffice, the money amounts i.e.
>NZ$1000 becomes text and I cannot work out how to make it a number so that
>I can add up various parts of various columns. I have to go thorough the
>spreadsheet and change them all manually to make them a number...there must
>be a better way of doing this?

Works fine here with a pound symbol.

Does your LO know what you would like it to think is a currency symbol?
<-- beyond this list's scope, I suggest

--
Wm...

_______________________________________________
gnucash-user mailing list
[hidden email]
https://lists.gnucash.org/mailman/listinfo/gnucash-user
-----
Please remember to CC this list on all your replies.
You can do this by using Reply-To-List or Reply-All.
Reply | Threaded
Open this post in threaded view
|

Re: Advanced Portfolio problems

John Ralls-2

> On Jul 19, 2015, at 12:18 PM, Wm... <[hidden email]> wrote:
>
> Wed, 15 Jul 2015 20:24:08 <CAF1oafdw1hvQF=n1VbhbV7w7Kicx=[hidden email]>
> dunbrokin <[hidden email]> wrote...
>
>> On a another matter. When I cut and paste the Advanced Portfolio
>> information into a Calc sheet in LibreOffice, the money amounts i.e.
>> NZ$1000 becomes text and I cannot work out how to make it a number so that
>> I can add up various parts of various columns. I have to go thorough the
>> spreadsheet and change them all manually to make them a number...there must
>> be a better way of doing this?
>
> Works fine here with a pound symbol.
>
> Does your LO know what you would like it to think is a currency symbol? <-- beyond this list's scope, I suggest

Not necessarily. One can customize currency symbols in the security editor. The catch with multi-currency is coming up with a scheme that the spreadsheet can understand while keeping the human from getting confused.

Regards,
John Ralls


_______________________________________________
gnucash-user mailing list
[hidden email]
https://lists.gnucash.org/mailman/listinfo/gnucash-user
-----
Please remember to CC this list on all your replies.
You can do this by using Reply-To-List or Reply-All.