# USD stocks USDCAD exchange rate



## razz (Oct 21, 2014)

Hi Folks, 

I'm aware that one needs to claim gain/losses of their USD portfolio. My question is can you use an average exchange rate for the year instead of the close for the day of the transaction? I'm using a google spreadsheet to calculate my gain/loss, but my google formula is a hit/miss, not sure why google will not get every currency exchange rate for a specific date. Some dates work fine but some return: "#N/A:When evaluating GOOGLEFINANCE, the query for the symbol: 'CURRENCY:USDCAD' returned no data."

Here is the formula:
=MIN(GoogleFinance("CURRENCY:USDCAD", "close", date(YEAR(A2),MONTH(A2),DAY(A2))))

What do you guys use to track exchange rates of your portfolio?

Cheers


----------



## mordko (Jan 23, 2016)

Nope, you can't use annual average. You can get daily rates here: http://www.bankofcanada.ca/rates/exchange/10-year-lookup/


----------



## james4beach (Nov 15, 2012)

Use daily USDCAD rate (easier is just to record it in real time at the time you trade). I use google finance and plug in USDCAD.


----------



## atrp2biz (Sep 22, 2010)

I use the BoC site as well. I download the FX rates in .csv format once in a while and use the VLOOKUP function in Excel to record the appropriate exchange rate based on the trade date.


----------



## razz (Oct 21, 2014)

atrp2biz said:


> I use the BoC site as well. I download the FX rates in .csv format once in a while and use the VLOOKUP function in Excel to record the appropriate exchange rate based on the trade date.


Thanks for the idea. I'm only seeing data for the last 5 days for BoC, where do you get date for years worth going back to 2014 and onwards?

Edit: Found a Fx source here, will have to verify accuracy though. It does let you allow to download a .csv file for specified date range and currency pairs. http://www.global-view.com/forex-trading-tools/forex-history/index.html


----------



## Nerd Investor (Nov 3, 2015)

An average rate is allowed for income but not for capital gains unfortunately.


----------



## mordko (Jan 23, 2016)

raczyk said:


> Thanks for the idea. I'm only seeing data for the last 5 days for BoC, where do you get date for years worth going back to 2014 and onwards?


The link I gave above gives you 10 years worth of data.


----------



## razz (Oct 21, 2014)

mordko said:


> The link I gave above gives you 10 years worth of data.


Sorry missed it. Noticed BoC has either close of noon values, what do you guys use?

Or do you choose the lower exchange rate when you made a profit and high exchange rate when you made a loss? Or do you have to be consistent . I mean we are talking fraction of pennies here.


----------



## mordko (Jan 23, 2016)

^ it does not matter as long as you are consistent for buy and sell. Just use midday throughout.


----------



## Woz (Sep 5, 2013)

If you want to stick with google sheets you could use the following formula to pull the noon exchange rates from the Bank of Canada website:

=index(IMPORTDATA("http://www.bankofcanada.ca/stats/results//csv?rangeType=dates&lP=lookup_daily_exchange_rates.php&se=_0101&dF="&text(A1,"yyyy-mm-dd")),12,2)

With the date stored in cell A1.


----------



## My Own Advisor (Sep 24, 2012)

atrp2biz said:


> I use the BoC site as well. I download the FX rates in .csv format once in a while and use the VLOOKUP function in Excel to record the appropriate exchange rate based on the trade date.


+1

Use BoC site. 

For small business regular income or expenses, you can use annual average though - you just need to be consistent in reporting that.


----------



## razz (Oct 21, 2014)

Nerd Investor said:


> An average rate is allowed for income but not for capital gains unfortunately.


According to this site one can use an average though.. hmm? 



> "The CRA permits you to use the exchange rate from the day of the transaction date, or the average annual exchange rate if multiple transactions occurred over the course of a year. If a currency exchange was involved in the transaction, you may use the actual exchange rate you received."


http://www.adjustedcostbase.ca/blog/calculating-adjusted-cost-base-with-foreign-currency-transactions/

What is also a little confusing is due to the changing nature of the FX rates, my Adjusted Cost Base (ACB) does not go to zero when all stocks are sold. Does one need to care about that though? For stocks in CAD the ACB nicely goes to 0.


----------



## Spudd (Oct 11, 2011)

raczyk said:


> What is also a little confusing is due to the changing nature of the FX rates, my Adjusted Cost Base (ACB) does not go to zero when all stocks are sold. Does one need to care about that though? For stocks in CAD the ACB nicely goes to 0.


I'm confused, why would this be? If you buy 100 shares at $10/sh USD which is $15/sh CAD, your ACB is $15/sh. Then later you sell all 100 shares for $20/sh USD which is $35/sh CAD since exchange rate changed, but you still sold all your shares, why wouldn't your ACB be zero at the end? Are you saying that adjustedcostbase.ca isn't reflecting it properly as zero when you enter the exchange rates?


----------



## Eclectic12 (Oct 20, 2010)

Spudd said:


> raczyk said:
> 
> 
> > ... What is also a little confusing is due to the changing nature of the FX rates, my Adjusted Cost Base (ACB) does not go to zero when all stocks are sold. Does one need to care about that though? For stocks in CAD the ACB nicely goes to 0.
> ...


I'm confused as why an investor would want to zero out their ACB, when selling the last of their shares.
One has to adjust one's per share ACB in several situations, which will have currency conversion effects but not for a sale, that I can recall.

Examples where the investor has to adjust the per share ACB is where more shares are bought [ add more costs], cash paid was return of capital (RoC) [subtract from ACB] or reinvested amounts where the number of units was held constant [add to ACB].


What comes to mind is where the investor is using *total ACB (i.e. per share ACB x # of shares)* then does a partial sell. They need to reduce that previous ACB number by the number of shares sold. Applying the currency conversion to this adjustment is wrong AFAICT. 

Selling did not change the price paid for the share so there's no reason to apply a second currency conversion before the total ACB number is reduced.


For example, for illustration purposes, I will simply the ACB calculation by ignoring commissions. Say the investor buys 200 shares at $10 USD with a currency exchange of $1.50 (Spudd's example). Six months later, the investor sells 100 shares. The sale does not change the price one paid ... it is still $10 USD x $1.50 x 200 (or $3K). With 100 shares remaining, the total ACB no longer $3K but half of that (i.e. $1.5K). Either way, the per share ACB is still $15 CAD.

The currency for the proceeds will come into play for the capital gain or capital loss calculation but won't change the ACB per share.


Cheers


----------



## Spudd (Oct 11, 2011)

Eclectic12 said:


> I'm confused as why an investor would want to zero out their ACB, when selling the last of their shares.
> One has to adjust one's per share ACB in several situations, which will have currency conversion effects but not for a sale, that I can recall.
> 
> Examples where the investor has to adjust the per share ACB is where more shares are bought [ add more costs], cash paid was return of capital (RoC) [subtract from ACB] or reinvested amounts where the number of units was held constant [add to ACB].
> ...


Agreed, I think what he meant though, is that his ACB for his remaining shares is <>0 even though his number of remaining shares is zero. But it doesn't make sense.


----------



## Eclectic12 (Oct 20, 2010)

^^^^^

If you look closely at the blog entries calculations:
number one "initial ACB",
number two "The sale results in a decrease in ACB equal to" 
and calculation number four "The new ACB is therefore", the blog is badly written. (Or maybe they are trying to drum up business by confusing people :wink.


Calculation number four says the remaining sixty shares will have a value of CAD $2,617.49 (or CAD $43.62 per share). I believe this is correct as the per share ACB matches up with calculation number one.

IMO, the source of the problem is that calculation number two is not what is it labelled (i.e. the ACB amount of the forty sold shares). It has numbers for the selling price of the shares as well as the USD sales commission and the currency exchange. This looks like the "Proceeds of Disposition" with the sell commission thrown in for some reason.

If one takes "decrease in ACB" for calculation number two to mean this is the amount to reduce the initial ACB to remove the forty shares sold and leave the new ACB value for the remaining sixty shares, it will be wrong IMO. The math ends up with CAD $4,362.48 - CAD $1,923.83 = CAD 2438.65 for sixty shares ( or CAD $40.54 per share). 

Selling shares should not be changing the cost per share of the remaining shares.


Is it really surprising to end up with a non-zero ACB where the math is understating what's left in the bucket?


Cheers


----------



## Eclectic12 (Oct 20, 2010)

Maybe the OP can confirm whether the numbers used to figure out the amount to reduce the ACB use the selling share price?

That would help confirm if the blog's misleading label is causing the issue.
Another way to figure it out would be if a couple of sample "buy, sell" with the calculations to determine how much to decrease the ACB were posted.


Otherwise - it is a working theory without confirmation.


Cheers


----------



## razz (Oct 21, 2014)

mordko said:


> The link I gave above gives you 10 years worth of data.


Thank you. The date is indeed a slightly different then the other source I've tried. Will use BoC data instead. But the funny thing is the BoC data has a bunch of "Bank holiday" where that is the data and no exchange is listed. Funny as trading of our currency goes on, weather there is hoiday in Canada or not. The other source has USD/CAD exchange rates for "Bank holiday" though. What exchange rates do you guys use on "Bank holiday"?



Woz said:


> If you want to stick with google sheets you could use the following formula to pull the noon exchange rates from the Bank of Canada website:
> 
> =index(IMPORTDATA("http://www.bankofcanada.ca/stats/results//csv?rangeType=dates&lP=lookup_daily_exchange_rates.php&se=_0101&dF="&text(A1,"yyyy-mm-dd")),12,2)
> 
> With the date stored in cell A1.


Tried the formula, but I only get "#maintenance a{color:#3366CC}" as a result. Guess I'll have to import the data first, which is not a big deal as it only has to be done once a year. That's tax time.


----------



## Woz (Sep 5, 2013)

raczyk said:


> Tried the formula, but I only get "#maintenance a{color:#3366CC}" as a result. Guess I'll have to import the data first, which is not a big deal as it only has to be done once a year. That's tax time.


No spaces in the formula. For whatever reason it seems to add a space whenever you copy it.


----------



## razz (Oct 21, 2014)

Spudd said:


> I'm confused, why would this be? If you buy 100 shares at $10/sh USD which is $15/sh CAD, your ACB is $15/sh. Then later you sell all 100 shares for $20/sh USD which is $35/sh CAD since exchange rate changed, but you still sold all your shares, why wouldn't your ACB be zero at the end? Are you saying that adjustedcostbase.ca isn't reflecting it properly as zero when you enter the exchange rates?


In my spread sheet I simply multiplied the USD New-ACB and Change-in-ACB by the USD/CAD exchange rate, to get the numbers in CAD. Believe the issue is when calculating the New-ACB, which is calculated by adding the Change-in-ACB added to the New-ACB from the previous transaction. But the transactions are based on different exchange rates. 

Edit: Okay taking a closer look at the spreadsheet, believe I was just getting confused by looking at the wrong columns. Thanks for pointing that out Sudd. This spread sheet for calculating ACB, can get quite confusing..


----------



## Eclectic12 (Oct 20, 2010)

Re: This SS for calculating ACB can be quite confusing.

Is the confusion from a badly laid out SS or is it using the "total ACB" where one needs to re-adjust the total for the shares sold?


I much prefer to calculate the ACB as a total number then have the SS calculate the "ACB per share". 

From what I recall, I have had as many as seven sell transactions but as there weren't any transactions that changed the ACB - for each of the sells, all I needed was (number of shares sold x ACB per share ). Your would be a bit different as there would also be the currency exchange but simplifying the ACB part might help.


Where one has transactions that modify the ACB (more buys, RoC etc.), then the SS has to handle it but the most common setup, the ACB affecting transactions are mostly buys and that's it.


Cheers


----------



## DavidW (May 27, 2016)

This is a sample of how I track ACB. ROC is the only way I can figure ACB getting to zero.

View attachment ACB_Example.pdf


----------



## Eclectic12 (Oct 20, 2010)

^^^^

The comment from the OP was that with the currency conversions as per the blog description, when the last batch of shares was sold, the ACB didn't zero out.

As I've indicated - the blog write up can lead to mistakenly using the selling price, the USD sell commission and the currency conversion instead of what was paid when the shares were bought.


Cheers


----------



## razz (Oct 21, 2014)

What USD/CAD exchange rate do you guys use when BoC has many dates listed as "Bank holiday"?


----------



## atrp2biz (Sep 22, 2010)

Yeah, this is definitely a drawback of the BoC data. I use the rate from the previous day.


----------



## razz (Oct 21, 2014)

atrp2biz said:


> Yeah, this is definitely a drawback of the BoC data. I use the rate from the previous day.


It's kind of silly.. its not like the world stops because BoC has a holiday.


----------

