Quicken Canada - Ability to Convert Downloaded Foreign Security Prices to Domestic Currency
gj0608
Quicken Canada Subscription Member ✭✭
Desired Objective:
To be able to reflect downloaded security prices of foreign securities denominated in foreign currencies to your domestic currency used for reporting purposes in Quicken.
Example:
You purchase foreign securities denominated in a currency other than your domestic currency.
You record the purchases by manually determining the cost in your domestic currency.
Illustration:
A Canadian Quicken user purchases shares of a US listed security XYZ (on a US stock exchange) that is denominated in US dollars:
100 shares for $200 US ($2 US per share).
The Canadian investor records initial purchase using the Canadian dollar equivalent at the time of purchase - US-Canadian dollar exchange is $1US = $1.25 Cdn.
Therefore, the initial purchase is recorded at $250 (100 shares x $2 US per share x 1.25 exchange rate).
Note:
Recording in Quicken the subsequent purchases of the US security (for example, dividend reinvestments) is done in the same manner using the exchange rate(s) at the date(s) of purchase(s).
Whenever the stock price is downloaded to update the market value of XYZ, it is in US dollars.
Day 2:
Stock price $2.10
Exchange rate: 1.27
If Quicken downloads the stock price on day 2:
The market value Quicken would reflect: $210 US (being $2.10 US x 100 shares).
However, everything else in Quicken (cost bases of all assets - domestic or foreign sourced) are recorded in Canadian dollars and the market value of domestic securities are reflected in Canadian dollars. Therefore, there is a mixed bag of currency reporting - everything should be in your reporting currency (presumably your domestic currency).
The potential gain/loss for XYZ would be incorrectly shown as a loss of $40 (being $210 US less $250 Cdn cost base)
Instead on day 2:
The market value should be $266.70 (being $210 x 1.27 exchange rate on day 2).
The potential gain/loss on day 2 should be gain of $16.70 (being $266.70 Cdn less $250 Cdn).
This is proper reporting where the market value of the security matches the currency of the its cost base.
Suggested Enhancement
When the stock prices of the foreign securities listed on foreign exchanges are downloaded, Quicken should "behind the scenes" convert the foreign prices into the domestic currency.
How?
In the Security Detail information profile of the stock in Quicken:
Quicken knows the following information (using the above example for illustration purposes):
Name: XYZ
Symbol: XYZ (say it is XYZ)
Type: Stock
Currency: Canadian dollars (the domestic reporting currency in my example)
Exchange: U.S. Stock
Therefore, there is enough information in the Security Detail profile of the stock for Quicken to know to pull the US-Canadian dollar exchange rates on the same dates as it pulls the security prices. Quicken would then apply the exchange rates to the stock prices on those dates.
In your report, you can add a column showing the stock price in its foreign currency and domestic currency. Therefore, you could display or produce a report showing XYZ in US dollars and Canadian dollars. However, the market value and potential gain/loss of XYZ would be computed based on the equivalent Canadian stock prices and Canadian cost bases.
Alternatively, if one needs to be explicit to Quicken regarding the treatment of foreign securities, there could be an additional field in Security Detail whereby the user would state the currency of the security prices that is being downloaded and the desired currency to be reported.
For example - additional fields (using the above example for illustration purposes):
Currency of downloaded security prices: U.S. dollars
Currency to be reported: Canadian dollars (again, this may not be needed as it already exists in the current information fields).
To be able to reflect downloaded security prices of foreign securities denominated in foreign currencies to your domestic currency used for reporting purposes in Quicken.
Example:
You purchase foreign securities denominated in a currency other than your domestic currency.
You record the purchases by manually determining the cost in your domestic currency.
Illustration:
A Canadian Quicken user purchases shares of a US listed security XYZ (on a US stock exchange) that is denominated in US dollars:
100 shares for $200 US ($2 US per share).
The Canadian investor records initial purchase using the Canadian dollar equivalent at the time of purchase - US-Canadian dollar exchange is $1US = $1.25 Cdn.
Therefore, the initial purchase is recorded at $250 (100 shares x $2 US per share x 1.25 exchange rate).
Note:
Recording in Quicken the subsequent purchases of the US security (for example, dividend reinvestments) is done in the same manner using the exchange rate(s) at the date(s) of purchase(s).
Whenever the stock price is downloaded to update the market value of XYZ, it is in US dollars.
Day 2:
Stock price $2.10
Exchange rate: 1.27
If Quicken downloads the stock price on day 2:
The market value Quicken would reflect: $210 US (being $2.10 US x 100 shares).
However, everything else in Quicken (cost bases of all assets - domestic or foreign sourced) are recorded in Canadian dollars and the market value of domestic securities are reflected in Canadian dollars. Therefore, there is a mixed bag of currency reporting - everything should be in your reporting currency (presumably your domestic currency).
The potential gain/loss for XYZ would be incorrectly shown as a loss of $40 (being $210 US less $250 Cdn cost base)
Instead on day 2:
The market value should be $266.70 (being $210 x 1.27 exchange rate on day 2).
The potential gain/loss on day 2 should be gain of $16.70 (being $266.70 Cdn less $250 Cdn).
This is proper reporting where the market value of the security matches the currency of the its cost base.
Suggested Enhancement
When the stock prices of the foreign securities listed on foreign exchanges are downloaded, Quicken should "behind the scenes" convert the foreign prices into the domestic currency.
How?
In the Security Detail information profile of the stock in Quicken:
Quicken knows the following information (using the above example for illustration purposes):
Name: XYZ
Symbol: XYZ (say it is XYZ)
Type: Stock
Currency: Canadian dollars (the domestic reporting currency in my example)
Exchange: U.S. Stock
Therefore, there is enough information in the Security Detail profile of the stock for Quicken to know to pull the US-Canadian dollar exchange rates on the same dates as it pulls the security prices. Quicken would then apply the exchange rates to the stock prices on those dates.
In your report, you can add a column showing the stock price in its foreign currency and domestic currency. Therefore, you could display or produce a report showing XYZ in US dollars and Canadian dollars. However, the market value and potential gain/loss of XYZ would be computed based on the equivalent Canadian stock prices and Canadian cost bases.
Alternatively, if one needs to be explicit to Quicken regarding the treatment of foreign securities, there could be an additional field in Security Detail whereby the user would state the currency of the security prices that is being downloaded and the desired currency to be reported.
For example - additional fields (using the above example for illustration purposes):
Currency of downloaded security prices: U.S. dollars
Currency to be reported: Canadian dollars (again, this may not be needed as it already exists in the current information fields).
13
Comments
-
Please excuse the typos - not sure how it happened (possibly auto-correct). Wherever reference is made to "dominated", it should be "denominated".
Sorry.
[2 such edits made by q_lurker]0 -
Hi gj0608, I am going through this now and this would be a great idea. There is already a currency list and has historic data available. This would be useful for any person investing as not all investments will be in the currency of the country the person is in. It is a global market.
Thanks.2 -
I fully support this and would like to add the ability for an account to handle both US$ and C$. This would better align the Quicken account with the underlying Brokerage account. Also recognizes that many Canadians buy both US$ and C$ stocks, etfs etc. A Canadian brokerage firm will purchase on a US exchange but charge commission in C$ further complicating an entry in a single currency account.0
-
Are the 2 currencies transacted in the same brokerage account? If they are separate why not just set up a different investment account in a different currency? I have separate investment accounts set up by currency and ownership my name, wife's name, joint account. Mind you I don't use automatic download for investments. Cost base is separate by currency, and return calculations. All of them are converted back to home currency in reporting and Portfolio view. What am I missing here?0
-
I've been a Quicken user for over 20 years and one of the biggest shortcomings is the unrealistic expectation that a RRSP account must only hold Canadian Securities. Foreign content rules were eliminated in 2005 and I had a rather long exchange with support over a decade ago about this issue and the summary was: not happening. I figured after more than 10 years product management would be in touch with reality given how easy it is for individuals to purchase in multiple jurisdictions, especially in the US. Alas, still not there, you still have to fudge your way around it, and get incorrect net worth values because it cannot track properly.0
-
EDITED for clarityNML said:I've been a Quicken user for over 20 years and one of the biggest shortcomings is the unrealistic expectation that a RRSP account must only hold Canadian Securities. Foreign content rules were eliminated in 2005 and I had a rather long exchange with support over a decade ago about this issue and the summary was: not happening. I figured after more than 10 years product management would be in touch with reality given how easy it is for individuals to purchase in multiple jurisdictions, especially in the US. Alas, still not there, you still have to fudge your way around it, and get incorrect net worth values because it cannot track properly.
Not going to happen, it is one thing to talk about making displaying in one currency by converting using exchange rates, it is a completely different subject about Quicken allowing multiple currencies in the same account.
The first thing to realize about this is how cash is treated right now. It is what you make all the purchases in the account with. It is the "running balance". There is only "one of them" in any given account. Contrast that to a security. There can't be any running balance of securities because every single transaction could be a different security. In other words for every transaction with security you need to which security, the number of shares, and the price. With cash (in one currency) there is only one parameter, the amount. As soon as you bring multiple currencies into this you have three parameters the "amount", "currency" and exchange rate. If you expand this out to non investment account it gets even worse. Because now you have basically changed every account into an "investment account" where the you don't have just an amount, you have amounts, currency, and exchange rates to deal with.
On top of these problems there are the ones that one might not think of, like QIF exporting/importing. The QIF format doesn't have any currency in it. It is just numbers. It is the currency of the account that determines what that number is.
Signature:
This is my website: http://www.quicknperlwiz.com/0 -
On the original idea I think you "how to implement" is not correct.
The problem is in the reporting, not in the downloading. The currency exchange should be done at the time of reporting whether that is an actual report or say in the portfolio.
All transactions for the security need to stay in the same currency. If you don't do that you are mixing currencies and introducing errors. Note BTW you can't own a CAD currency in a USD account, Quicken will not allow it. All the transaction/amounts in that account have to be consistent.
Now when it comes to the reports/portfolio that is a completely different story, and I was even surprised that isn't already done. I see in the portfolio that they have "no mixing", as in if you set CAD then the only securities/cash have to be in CAD. It really should be not that hard to have show all of your accounts/securities in one currency by applying the current exchange rate to any account/security that isn't the currently selected currency.
So instead of having to do this to see all the securities:
It should be possible to say select Canadian Dollar and have it convert the prices based on that day's exchange rate.
Looking at the Investing Activity report I see that it already does this kind of conversion.
Note that in the US version they would have to fix a shortcoming before anything other than today would make sense.
The US version doesn't keep a currency exchange rate history.
Signature:
This is my website: http://www.quicknperlwiz.com/1 -
I don't understand why Quicken is able to download the latest foreign security prices but not download and apply the latest exchange rates to such prices to get a Cdn$ equivalent security price.
It should be an easy solution - there should be a capability to select the foreign currency associated with the foreign security (for example, if it is a stock listed on the US stock exchange, select US$). The currencies can be selected from a drop down menu and then when Quicken goes through downloading the latest quotes, it would also download the latest foreign currency rates applicable to the foreign securities and apply it to the US stock price to get a Cdn$ stock price.
I don't need to convert the purchases, dividend payments and reinvestment of such dividends into more foreign shares as I convert them already at the prevailing foreign exchange rates at the time of these events. However, the problem is when the net worth or the fair market value is updated for the foreign securities, the price in foreign dollars is downloaded. This creates a mixed bag of currencies being reported and an incorrect net worth amount displayed. I don't understand why Quicken cannot do this so that we can avoid any extra work to recalculate the net worth amount determined by Quicken.0 -
When updating investment accounts in Portfolio view, US Stocks in a Canadian currency account are quoted in USD. However, all the market values etc. for the US stocks are also now in USD, which in turn means that the performance data is not correct for any US stocks in a Canadian currency account.
Is there any way to have US stock quotes be converted into CAD when the portfolio accounts are updated with "One Step Update" that will allow the stock performance in the portfolio be accurate in CAD?
0 -
Q doesn't allow the combinations of multiple currencies in a single account, even an investment account. The currency associated with an account is established when the account is created.
Since you have securities that are both USD and CAD, you need two investment accounts.
Q user since February, 1990. DOS Version 4
Now running Quicken Windows Subscription, Business & Personal
Retired "Certified Information Systems Auditor" & Bank Audit VP0
