Currency Conversion for Foreign Securities-Quicken Deluxe 2018. (Windows)

Unknown

I have read various issues about this problem but most of them related to Quicken on Mac.  I am running Quicken Deluxe 2018  Version R12.8  Build 27.1.12.8   Canada Edition. 


The problem is this.  I have a Investing TFSA account, this account is in Canada and is therefore set to Canadian Dollar Currency.  Now this TFSA has several holdings, some of these holdings are on the NYSE.  Because I want to link them to my Canadian Dollar TFSA and because my brokerage buys shares in Canadian Dollars for all securities, the currency settings on these securities had to be set to Canadian  Dollar. 

Now when I update my quotes for these securities the Gain/Loss amounts and percentages for the NYSE funds are incorrect and causes Quicken to show big losses on the investments which is not the case.  This is happening because the quotes are being shown and calculated in US dollars and I purchased the funds in Canadian dollars.  Example the price I bought shares for a particular security was at 81.59 CAD$ per share, but the US$ quote will show $63.05, so now that's a -22% loss when in fact its a Currency problem. 


This is a huge show stopper for me and according to Quicken support I am Out of Luck as Multi-Currency is not supported in the Canadian Version, only the US version of the product, which makes the whole product useless for me.  Is this true? there is no way I am going to stay doing manual currency conversions on each quote.  

There must be a way Canadian Investors using the product have found a solution other then uninstalling the product and going with something else.  As to be honest this is ridiculous.. 


  

Arctic Hare

@Paul: For starters, it is the Canadian version of Quicken that has strong multi-currency support, not the US version. I regularly use the multi-currency features of QC2018.

There are a couple possible explanations for the problem that you are encountering. Can you please name one of the securities involved in this problem?

Also, which brokerage are you using?

Unknown

Hello and thank you for your response.  Okay a couple of the of the securities associated with my TFSA are: IEFA

(iShares Core MSCI EAFE ETF) and  
VTI (Vanguard Total Stock Market Index Fund ETF

Shares). 







These trade on US Stock markets .


The brokerage is "ShareOwner" but the accounts are through WealthSimple. Neither of which are in the list of Quicken, so the Financial Institution was setup manually.  The Account is set to Canadian Dollar.  


I have also attached an example of one of the US security settings and the price history showing quotes coming in US Dollars which is the source of my problem since I purchased the shares in Canadian Dollars.

Arctic Hare

@Paul: I happen to own both of those securities. You didn't really purchase either in CAD. Those securities are only sold in USD; someone performed the foreign exchange transaction for you.

The only way to handle this in Quicken is to set up two brokerage accounts, one denominated in CAD and one in USD. To represent your purchase in Q, you'll need to do a cash transfer of that amount from the CAD denominated brokerage account in Q into the USD denominated account and then show the purchase in the USD denominated account, considering the correct number of units and price in USD. 

I deal with a couple discount brokerage firms, but my main firm is RBC Direct Investing. With RBC DI, one can have a CAD account and USD account that share a common account number; I have this arrangement for my RRSP account with RBC DI. I have corresponding CAD and USD denominated accounts within Q setup (i.e. one account number with RBC DI for the pair of CAD and USD accounts, but two separate accounts in Q.

In my case, when I purchase a security traded on a US exchange, I do see the USD cost directly, but that is because I purchase my USD denominated securities using USD cash. RBC does permit me to use CAD cash, but I avoid doing so because the F/X rate they charge is not competitive; i.e. RBC is taking too big of a cut for performing the F/X transaction.

As an aside, you might want to check out how to perform Norbert's Gambit as it would probably save you a bunch on the F/X costs. I use Norbert's Gambit to move cash between the CAD and USD sides of my RBC DI account. Not all brokerages allow you to use Norbert's Gambit, but it works slickly with RBC DI.

Unknown

Thanks Arctic. So if I do a Cash Transfer does quicken automatically do a currency conversion for me? 

Arctic Hare

Paul Cachia said:

Thanks Arctic. So if I do a Cash Transfer does quicken automatically do a currency conversion for me? 

It does, but you'll have to adjust the f/x rate to match the rate that actually applied to your transaction. Your brokerage's rate will be different (more costly) than the trading rate that Quicken chooses. 

Unknown

Paul Cachia said:

Thanks Arctic. So if I do a Cash Transfer does quicken automatically do a currency conversion for me? 

Is this what I would use for the f/x rate correction? 

Unknown

Paul Cachia said:

Thanks Arctic. So if I do a Cash Transfer does quicken automatically do a currency conversion for me? 

So to confirm, I am going to create two accounts (in my case they are TFSA's) One in CAD the other in USD, I am then going to link the US Exchange Securities to the US TFSA and the CAD to the CAD TFSA, I will then transfer cash investments from my CAD TFSA to my US TFSA and set the correct currency conversion?    The only problem I see with this is that now I lost my ability to manage my TFSA account as a whole, being split up like this makes it harder to track, I am also going to have to figure out what the exchange rate was being used at the time of the the funds being invested every month.. 

Arctic Hare

Paul Cachia said:

Thanks Arctic. So if I do a Cash Transfer does quicken automatically do a currency conversion for me? 

Yes @Paul, you've understood the setup correctly. You can still view your account as a whole, essentially, in quicken by selecting the accounts that are included in views/reports. For example, if you want to consider asset allocation, you simply select the two accounts that make up the complete TFSA.

Yes, that is where you would set the exchange rate, however, depending on what data you receive from your broker, you might find it easier to enter the amount of CAD leaving one Q account and the amount of USD entering the other account and let Q calculate the resultant rate.

Again, I suggest that you consider Norbert's Gambit. It will save you money on the F/X conversion. You'd have to confirm that your broker allows the Gambit and, if so, if there are any special instructions you need to follow. Using the Gambit also makes the F/X transaction straight forward.

You might also consider switching to a broker that handles the CAD + USD like RBC DI; it is quite convenient.

Unknown

Paul Cachia said:

Thanks Arctic. So if I do a Cash Transfer does quicken automatically do a currency conversion for me? 

Thanks Arctic for your help, I will try this out next week, I doubt I will be able to use Gambit as I am using a roboadvisor and I am not doing the investing myself, I was able to find the statements showing the shares purchased in US dollar equivalents, I should be able to calculate the exchange rate from that..  

Arctic Hare

Paul Cachia said:

Thanks Arctic. So if I do a Cash Transfer does quicken automatically do a currency conversion for me? 

Good luck. Please *Like* the comments that you found helpful !

Unknown

Paul Cachia said:

Thanks Arctic. So if I do a Cash Transfer does quicken automatically do a currency conversion for me? 

Hello Arctic, so I setup the funds as you suggested and so far so good.  Now I have hopefully the last piece of the puzzle, how do I see the total value of the two TFSA's I created in a single currency?  Basically I want to know the total balance of both accounts in CAD$ and not have to stay doing currency conversions to find this out.  Is there anyway I can do this in reporting? 

UKR

Paul Cachia said:

Thanks Arctic. So if I do a Cash Transfer does quicken automatically do a currency conversion for me? 

Paul,
in the Investing tab, Portfolio view customize a view to include the accounts you wish to see. (At least in the US version which I use) the Portfolio view has a field in the header portion of the view, "Currency:". Set the currency to C$. Also be sure to set the view to "Group by: Account"

Unknown

Paul Cachia said:

Thanks Arctic. So if I do a Cash Transfer does quicken automatically do a currency conversion for me? 

Okay so I setup this view, and now I discovered another issue related to currency.  I have my view set to show me the Amount Invested and the Market Value as well as the Gain/Loss. It of course subtracts the two amounts to caculate your gains or losses.  The problem is that it is applies the daily currency rate to convert the US funds back to CAD currency to give you total's in currency, that's not a problem for the Current Market Value amounts, but its a problem for the Amount Invested because its changing based on today's value, of course this throws off the Gain/Loss calculation and it shows wrong results.  The amount invested should not be changing based on today's currency, it should only be calculated based on the rate at the day of investment.   If I change the US$ Currency setting (CTRL+Q) to so the rate at the time of investment now my Amount invested is accurate but my Market Value is off, its a catch 22...   going nuts here..  need a life line badly!! 

Arctic Hare

Paul Cachia said:

Thanks Arctic. So if I do a Cash Transfer does quicken automatically do a currency conversion for me? 

@Paul: Click on the Gear icon to Customize the Report. At the bottom of the Display tab, beside where you can select Currency, you'll see an option to us the "Transaction Exchange Rate". Click that box. Note: this will force the cost basis to be calculated based on the transaction rate recorded in the corresponding transaction; it doesn't look up the market rate for that day in the currency table, it used the rate recorded in the actual purchase transaction. So, you need to ensure that, when you record transactions, you use confirm the actual exchange rate is correct. With that selected, you should get your cost basis converted to CAD at the transaction f/x rate and the market value calculated based at the f/x rate defined by the currency table.



Is that the lifeline you were seeking?

Unknown

Paul Cachia said:

Thanks Arctic. So if I do a Cash Transfer does quicken automatically do a currency conversion for me? 

Thanks Arctic, I will test this tonight. So is this option only available in the settings of the Canned Reports? Is it not available in the default Investing view to allow the correct calculations of the Amount Invested & Market Value Columns.

Arctic Hare

Paul Cachia said:

Thanks Arctic. So if I do a Cash Transfer does quicken automatically do a currency conversion for me? 

@Paul: I'm not exactly certain what you mean by "default Investing view". Perhaps you can take some screen shots this evening if you have further questions? The answer to your questions is, probably, "correct; you need to run a report and set custom options"... but, you can always save the customized report, so I don't see that as a problem, per se.

BTW, I have never used the USA version of Q, but, to the best of my understanding, this feature is unique to the CAD version. This is one case where the CAD version is more feature rich... thankfully... because some of us need this feature !

Unknown

Paul Cachia said:

Thanks Arctic. So if I do a Cash Transfer does quicken automatically do a currency conversion for me? 

Artic, it worked!!  good stuff and thanks so much for the tips and help and everybody else.  I will work on some custom Graph's..  now