Quicken & Currency

[marcharry]

Further to a number of posters input I have set about setting up Quicken to track investment transactions and do the required tracking.

I am working with 2002 deluxe edition

My immediate questions are around currrency:

Halfway through setting up the first account I changed the home currency to CANADIAN DOLLARS. 2 questions are:

I have 40 transactions from one account entered that the program assumes were in USD - that was the default currency at the time. It now refuses to allow me to change the currency of the account or of the individual entries because they originally posted in USD - I need them to be in CDN - how?

How does the software track the foreign exchange component? Can it? That is: I enter the PURCHASE i choose USD in the dropdown but nowhere does it ask for the FX RATE on the day. Of course I can put that note in the MEMO part and do it manually - but there must be a way. The software simply wants me to go to the currency list and update the rate on any given day - just to keep it current.

General questions
What is the best source of info on this stuff aside from the HELP tab - quicken for dummies? Is there an online chat group?

Is there anything worthwhile in 2005 version - I could upgrade. I know that many of you use older versions and are happy with them.

[Bylo Selhi]

I have 40 transactions from one account entered that the program assumes were in USD - that was the default currency at the time. It now refuses to allow me to change the currency of the account or of the individual entries because they originally posted in USD - I need them to be in CDN - how?

First, when you installed Q02 did you remember to check off that you wanted the CA version? (IIRC by default you get the US version, hence US$ is the default currency.)

Assuming that's not the case, I can think of two options. I haven't tested either but they're what I'd persue if in your shoes. There may be more options that I hadn't thought of.

1. Rekey the transactions but this time into a CA$ account. It's only 40 transactions. It's not that tedious.

2. Export the transactions to a file, delete the account, recreate it in CA$, import the data back in. You may have to fiddle with the export file, i.e. to change the currency code from US$ to CA$.

How does the software track the foreign exchange component? Can it? That is: I enter the PURCHASE i choose USD in the dropdown but nowhere does it ask for the FX RATE on the day. Of course I can put that note in the MEMO part and do it manually - but there must be a way. The software simply wants me to go to the currency list and update the rate on any given day - just to keep it current.

I don't understand why you have to explicitly choose US$ and/or explicitly specify the FX rate. I use the CA version of Q02 Deluxe. When I enter a transaction in a US$ account, before it's saved Q02 tells me the exchange rate it will use (which it downloaded off the Internet during a portfolio update.) I can override this but never do. (It's close enough.)

What is the best source of info on this stuff aside from the HELP tab - quicken for dummies? Is there an online chat group?

You might be able to get help here http://quikiwiki.com/tiki-index.php although it's a US-based site. (I found it just now when I tried to load the now defunct Unofficial Quicken Web Page.)

Is there anything worthwhile in 2005 version - I could upgrade. I know that many of you use older versions and are happy with them.

Newer versions of Quicken have mostly cosmetic improvements plus a product activation "feature" that prevents you from installing on more than one PC. I'm sticking with Q02 until the banks stop supporting it for transaction downloads.

[Shakespeare]

I'm sticking with Q02 until the banks stop supporting it for transaction downloads.

Not being and never intending to be a Quicken user: would it be possible to kludge a fix via .csv file and an Excel spreadsheet if the banks stopped supporting the Quicken version you were using?

[Bylo Selhi]

Not being and never intending to be a Quicken user: would it be possible to kludge a fix via .csv file and an Excel spreadsheet if the banks stopped supporting the Quicken version you were using?

It would indeed. You'd lose some functionality, mostly reports and such.

But as an official Quicken beta tester I get free copies of the newer versions. (Beta testing isn't all that onerous. It's sooo easy to find bugs in their software ) It's just that, apart from the beta versions, I've never actually installed the retail versions of Q03, Q04 or Q05 on any of my PCs.

[marcharry]

I don't understand why you have to explicitly choose US$ and/or explicitly specify the FX rate. I use the CA version of Q02 Deluxe. When I enter a transaction in a US$ account, before it's saved Q02 tells me the exchange rate it will use (which it downloaded off the Internet during a portfolio update.) I can override this but never do. (It's close enough.)

For example: I hold a US dollar ETF in a canadian account (I realize that it should be held in the US side of the account - but it wasn't - my bad)
I think I need to keep track of the currency influence separate from the cap gain/loss. So I need to track the rate on the day of purchase and sale. Is this not so?

Now, my first admission is, I do not (yet) download any rates or quotes or balances - not ready to go there yet. but I can easily manually enter the FX rate.

It seems that the thing to do would be to enter the canadian cost, in the CDN dollar account and note the FX rate as a memo - that's basically what TDW does. Is this wonky?

I probably did neglect to set it up as the CA version on install - I have just been translating - 401K to RRSP etc. (I cannot find the disks from the install - my bad again)

[Shakespeare]

I hold a US dollar ETF in a canadian account (I realize that it should be held in the US side of the account - but it wasn't - my bad)

Ask your broker if it can be journaled. There should be no cost or tax penalties.

[marcharry]

Thanks Shakes, I talked about that one in an earlier post. I have to break down the porfolio and re-buy it But jounaling would be an option in many cases.

Hence my need to track the buy/sells for tax and tracking - the need to learn Quicken.

[couldawouldashoulda]

I use Quicken XG04 and I have always used the converted dollar amount shown on my account as the basis for my transactions. It seems to make more sense than trying to convert the individual transactions. Whenever I want to see what my US shares are worth, I use the conversion factor of the day and apply it to the total value of my US holdings. Although the program will allow me to set up each transaction with a conversion factor if I want, I prefer not to use that feature.

[Bylo Selhi]

Although the program will allow me to set up each transaction with a conversion factor if I want, I prefer not to use that feature.

Be careful. That may be your preference, but it's not the way that CRA wants you to account your US$ transactions for capital gain/loss purposes. You have to assign the FX rate in effect at the time for each transaction. CRA is flexible about "at the time." It could be at the instant of the transaction, the daily, weekly, monthly or even the annual average, but you have to be consistent. Quicken (at least the 2002 version) gives you the ability to specify the FX for each transaction and then to apply that when creating cap gain/loss reports. This is the way CRA expects it to be done. BTW, it behooves those who bought US securities when the loonie was drowning to do it this way because they paid a lot more in CA$ terms than today's rate, so their CGs are substantially lower (or negative) if they do the accounting correctly than if they simplistically apply today's rate for all transactions. It's bad enough to make an error but it's worse when it costs you additional tax too.

As for looking at portfolio value or asset allocation, then of course you'd use the current FX rate.

[couldawouldashoulda]

By entering the transaction cost in Canadian dollars, I've already converted the stock to Canadian dollars without effectively entering the conversion rate. Surely the CRA would see this as being correct? The dollar amount coming out of my brokerage account is Canadian, not US.

[Bylo Selhi]

By entering the transaction cost in Canadian dollars, I've already converted the stock to Canadian dollars without effectively entering the conversion rate. Surely the CRA would see this as being correct? The dollar amount coming out of my brokerage account is Canadian, not US.

In that case, then yes. The discussion upthread was about using Quicken's multi-currency feature to track US$ transactions in US$ accounts.

[marcharry]

I am trying to record the JOURNAL of EAFE shares from the canadian side of a brokerage account to the US side.

The canadian account is set up in canadian currency. I dont think you can set up an account in multiple currencies. So, the software thinks that EFA is $C security.

Software does not allow me to TRANSFER to $US account because currency is mismatched. Will not allow me to "Remove shares" for some reason and "add" them in US acct. Tried selling them for $0...no way to add them back in to US acct at $0 [not the best idea anyway because it messes up cap gains - which is the main reason for tracking all this]

Anyone have a suggestion on how to enter this transaction?

[Bylo Selhi]

The only way I know to do this is to have two securities for EFA, one in each currency, e.g. EFA$U and EFA$C. Then you can journal shares by ShrsOut, say 100 shares of EFA$C at CA$1,200 and ShrsIn 100 shares of EFA$U at US$1,000. Make sure you have the correct ACB and use the correct FX rate to make everything kosher taxwise.

(BTW, I do this with interlisted shares like RY when I convert CA$ to US$ using Norbert's gambit.)

[marcharry]

Thanks, bear with me a little- I improvised the same solution. BUT on the SharesIn(AddShrs) if I enter a "price paid" then software will calc a cost paid and presumably debit cash. I tried to zero the net cost - no way.

IF i leave "price paid" blank it will enter the transaction, the cost base is noted as a memo...but then software will never know the cost base and will not track cap gains/loss etc.

How do you fiddle it so that on the SharesIn the cash impact is zero, but the cost base is in the system?

[Bylo Selhi]

on the SharesIn(AddShrs) if I enter a "price paid" then software will calc a cost paid and presumably debit cash. I tried to zero the net cost - no way... How do you fiddle it so that on the SharesIn the cash impact is zero, but the cost base is in the system?

Which version of Quicken are you using? I'm still on 2002 Deluxe. (I have 2006 but it's not currently installed.)

When I do the ShrsIn, I specify number of shares and use ACB as the transaction amount. Q02 fills in the price and also plugs in the same ACB value into Comm/Fee so that the net effect is zero cash. As far as I can see, this tracks the ACB correctly.

[marcharry]

you are right - i was just retrying it as you were posting. It rings up the value but not the cash that way.

It seems that is the solution

As always - thx

[AltaRed]

Bylo, your rationale for upgrading to 2006? Autodownloading of transactions? Would like to hear your opinion of it when you have installed and played with it for awhile. I have been holding off any upgrade from 2002 Deluxe version.

[Bylo Selhi]

Bylo, your rationale for upgrading to 2006?

I've been a loyal beta tester since 2004. In exchange for playing around with each new release, reporting bugs and making suggestions, I get a free copy of the final software. Being a Quicken beta tester isn't an onerous task. It's not hard to find bugs or to come up with suggestions for imporovement. And the best :?: part is that most of what I find or suggest in one year is equally applicable the next

I have been holding off any upgrade from 2002 Deluxe version.

Me too. But they've announced the end of support for Q02 in the US, so Canada can't be far behind. At that point I'll be forced to use Q06 for productive work

[Peculiar_Investor]

The only way I know to do this is to have two securities for EFA, one in each currency, e.g. EFA$U and EFA$C. Then you can journal shares by ShrsOut, say 100 shares of EFA$C at CA$1,200 and ShrsIn 100 shares of EFA$U at US$1,000. Make sure you have the correct ACB and use the correct FX rate to make everything kosher taxwise.

(BTW, I do this with interlisted shares like RY when I convert CA$ to US$ using Norbert's gambit.)

This advise is 6 years old, but I've taken my time on making use of the USD side of my account at TDW. For many years I just bought/sold US stocks in my CAD account, it made my life simpler -- and made my broker some coin along the way on FX. When I started investing, Quicken didn't have multi-currency support.

I'm finally getting with the program to journal the US stocks into the USD account and buy/sell them in their native currency. I'm trying to figure out the exact mechanics to use in Quicken 2010 to record what I've done. I've never used the multiple currency feature of Quicken, and I don't use Quicken to update stock prices (I've got my own mechanism, as I only store month end data in Quicken).

I want to make sure that I get the ACB correct and that I'm setup to update stock prices properly going forward. I follow the gist of Bylo's example, but I'm struggling to apply it in my situation. The situation might be a bit more complicated as I hold the US stock in both non-registered and registered (C$ is the only option) accounts.

Say on 21-Jun-2001 I bought 100 shares of JNJ in a C$ account, price was US$ 53.11, exchange rate was 1.5310 , commission was $101.671. In Quicken I recorded this as 100 Johnson & Johnson (Symbol JNJ) @ $81.31, commission $155.66, total $8286.66. Over the years I gladly collected dividends that were converted to C$. Finally on 1--Feb-2011, I did a journal of the shares into the USD side of the account at TDW.

So in Quicken, in the Cash - C$ account, I believe I do a ShrsOut on 2/10/2011 for Johnson & Johnson, price C$81.31 (plus commission as their is no field for this in Quicken). In the Cash - US$ account, I do a ShrsIn on 2/10/2011. My questions are a) what do I enter as the stock name? b) what do I enter as the symbol? and c) what do enter as the price? Going forward, how do I update stock prices so that the same security in the registered C$ account and the non-registered US$ account reflect accurate prices?

For those who've read this far and chose to guide me forward, I say thanks in advance.

1 "full-service" broker and before pre-FWF days, I've since learned that costs matter.

Bump: and paging Bylo.

[Bylo Selhi]

paging Bylo.

I'm heading out later this morning on an Internet-free overseas trip so I don't much time right now to experiment with Quicken. Keep in mind that what I do for Norbert's Gambit's, which is a one-time transaction, isn't quite the same as what you want to do with a series of related transactions over an extended time period. Also because these stocks are interlisted (by definition) I can specify on which exchange and by implication in which currency they trade. I'm not sure that applies to something like JNJ which trades only in NY.

[adrian2]

My questions are a) what do I enter as the stock name? b) what do I enter as the symbol? and c) what do enter as the price? Going forward, how do I update stock prices so that the same security in the registered C$ account and the non-registered US$ account reflect accurate prices?

If you're going to manually update stock prices on a monthly basis, I'd say update them all in C$ and forget about Quicken currency support. Just call the account "TDW US$" and use the CA$ prices, which is what you'll need to track ACB et al. Then you have the same stock name and symbol for all accounts. BTW, this is what I do.

[Bylo Selhi]

paging Bylo.

I'm heading out later this morning on an Internet-free overseas trip...

Damn car battery was dead so we almost didn't get out of the garage. Anyway we're now at YYZ with the car parked at Park 'n Fly's valet lot, i.e. so that it's their problem to deal with a possibly dead battery when we get back