AdjustedCostBase.ca provides a feature to premium subscribers allowing transactions to be uploaded in bulk from a spreadsheet into the ACB calculation engine. Accepted file formats include Microsoft Excel (both .xls and .xlsx files) as well as CSV (.csv comma-separated value files).
This feature is only available to AdjustedCostBase.ca Premium subscribers. The cost of the enhanced service is $49/year. The basic features of AdjustedCostBase.ca remain completely free for Canadian investors.
This feature can be extremely useful if you have transactions in a spreadsheet file that you’ve exported from your brokerage account or if you prefer to compile a list of transactions using Excel or other spreadsheet software. By uploading the data using this feature, you can potentially save many hours of time and avoid data entry errors.
This feature can be accessed after logging into AdjustedCostBase.ca by following the “Import Transactions from Spreadsheet” link found on most pages, including the home page and new transaction page:
After following the link you’ll be brought to this form:
From here you’ll be able to configure the format for the spreadsheet you wish to upload. The columns included in the spreadsheet as well as the column ordering can be customized in a wide variety of ways. The default column configuration includes the following 6 required columns:
| Security | The name of the security, which must match an existing security that you've already added to your account. |
| Date | The date for each transaction. When uploading an Excel spreadsheet (either .xls or .xlsx format) the date column should be formatted using any of Excel’s built-in date formats. When uploading a CSV file, the date column must be in “MM/DD/YYYY” format. |
| Transaction Type | The transaction type in each row must be set to one of the following supported values: "Buy", "Sell", "Return of Capital", "Capital Gains Dividend", "Reinvested Dividend" or "Reinvested Capital Gains Dividend". |
| Amount | The dollar amount of the transaction, assumed by default to be the total amount (as opposed to a per share amount). |
| Shares | The number of shares acquired or sold, applicable only to "Buy", "Sell" and "Reinvested Dividend" transactions. |
| Commission | The total commission amount for the transaction, applicable only for "Buy" and "Sell" transactions. |
In addition, you can specify the following optional columns:
| Total or Per Share | The value should be either "Total" or "Per Share" to specify whether a transaction amount is a total amount or per share amount (if this column isn't used the amount will be assumed to be the total amount). |
| Memo | Any free text that you wish to add as a memo for the transaction. |
| Price in Foreign Currency? | Indicates whether or not the value in the amount column is in a foreign currency, with the following acceptable values: "Yes"/"Y"/"No"/"N". |
| Exchange Rate? | The exchange rate applicable for the transaction, entered as the number of units of foreign currency equal to CAD$1.00. Alternatively, one of the following currency codes: "USD", "EUR", "JPY", "GBP", "AUD", "CHF" or "CNY". If a currency code is inputted, the exchange rate value reported by the Bank of Canada for the transaction's date will be used. |
| Commission in Foreign Currency? | Indicates whether or not the value in the commission column is in a foreign currency, with the following acceptable values: "Yes"/"Y"/"No"/"N". |
By default, transactions with the same dates for the same security will be imported in the order in which they appear in the spreadsheet (top to bottom), but you can reorder them at any time after the import.
You should check off the option “Ignore Header Row” if the spreadsheet you’re uploading includes a header row. If this option is set, the first row of the spreadsheet will be ignored during the import process.
A maximum of 500 transactions may be uploaded at a time. If you need to upload more transactions at once you’ll need to break your spreadsheet into multiple files.
To demonstrate this feature, let’s assume that you have the following Excel spreadsheet:
Once the spreadsheet is ready to be uploaded, follow the “Import Transactions from Spreadsheet” link on AdjustedCostBase.ca, and configure the form as follows to match the columns and column ordering in the spreadsheet:
Also ensure that “Ignore Header Row” is checked because the spreadsheet includes a header row. Next, hit the browse button to find the spreadsheet file on your local hard drive. Finally, hit the “Import Transactions” button. The transactions will not be added immediately, and you’ll have a chance to review all the data before committing the transactions. Review this information carefully to ensure its accuracy.
You may also see errors if there are invalid entries in the spreadsheet. The error messages should be detailed enough to help you identify and fix the errors, at which point you can try re-uploading the spreadsheet.
Once your transactions have been successfully imported, you’ll be brought to a confirmation page showing the list of imported transactions:
Why must one first create the security in the portfolio before the importing transactions? It seems like an undue burden. Also, why is it limited to 100 transactions?
Frederic,
Thank you for your suggestion. We have now added the option “Add Unknown Securities” that will add securities that do not yet exist. Please note that if the name of a security is even slightly different than one that exists already, it will be added as a new security with this feature is enabled.
The limit of 100 transactions per spreadsheet is partly in place to discourage blind uploading of large amounts of data. While this feature is intended to save time by avoiding the need to manually enter data, the data should still be carefully to ensure correctness. As noted above, a large spreadsheet can be split into multiple smaller spreadsheets and uploaded one at a time.
The Import function does not allow more than 2 decimal places.
Do you allow importing Options trades?
M Neu,
The import feature does indeed allow for a precision much greater than 2 decimal places. However, throughout AdjustedCostBase.ca monetary amounts are displayed rounded to the nearest $0.01 and share amounts are displayed rounded to the nearest 0.0001 for brevity. If you input a value with greater precision, either manually or using the import tool, the precise value you input will be used in the calculation. You can verify that the precise values have been imported properly by following the ‘Edit’ link next to an imported transaction.
Jose,
Options transactions are not supported by the import feature at this time.
I’m trying to figure this tool out for my transactions as summarized in the XLSX from my Questrade account. I can’t quite figure out the Transaction Type, especially difference between “Reinvested Dividend” or “Reinvested Capital Gains Dividend”.
I have a DRIP setup for my funds. Questrade’s XLSX has some of the transactions identified as REI (which Questrade calls “Dividend Reinvestment” — fine) and then they have Dividends, where some (mainly VAB.TO) don’t have DIV under the Action column, and others (mainly, but not consistently, VDU.TO) have DIV in the Action column. Any insight as to how I can label these properly?
Mike,
I’m unfamiliar with the specific format of the spreadsheet Questrade provides, but in the transaction reports I’ve seen from other brokerages, a dividend and its corresponding reinvestment are separated into 2 transactions (one for the dividend and another for the reinvestment). And if the DRIP is for whole shares only, then the amount of the reinvestment will likely be smaller than the dividend, with the residue being deposited as cash into your account.
Dividends do not factor into calculating ACB unless there is a return of capital or phantom distribution (also known as a reinvested capital gains distribution) component. If the dividend/reinvestment is split into 2 transactions, then you should most likely omit the dividend transaction from the spreadsheet before uploading it (and include only the reinvestment).
In your case, VAB.TO did record both return of capital and a phantom distribution for 2016 while VDU.TO had neither (although I did not check for previous years).
If the spreadsheet you’ve downloaded does indeed have complete return of capital and phantom distribution data, then you could import this data to account for these components (this seems unlikely, especially since Vanguard has just released its 2016 distribution characteristics a few days ago). Otherwise, you can use the streamlined import feature for your ETF’s to account for return of capital and reinvested distributions, which is described here:
https://www.adjustedcostbase.ca/blog/streamlined-import-of-return-of-capital-and-phantom-distributions-and-for-exchange-traded-funds-etfs-publicly-traded-mutual-funds-and-trusts/
In that case the reinvested dividends would be accounted for by the imported spreadsheet, and the return of capital/phantom distributions would be accounted for by the streamlined import feature. It is important to be careful with the ordering of the transactions in order to ensure that the reinvestment occurs after the corresponding return of capital/phantom distribution transactions.
Hopefully this will help, but please feel free to elaborate on the format of your spreadsheet if I’ve misunderstood anything.
Just uploaded my transactions and I am getting “appears to be a short transaction” message. I do not have any shorts and should not be seeing a negative share value. This is my first time with ACB.ca. Should I have created an initial fake entry at start of each new security entry to establish a starting point that would reflect my beginning share position ?
Jim,
That error indicates that a transaction has caused your share balance to fall below zero. You should check that all transactions prior to that one are complete and correct. I wouldn’t suggest putting a “fake” entry, as you should ensure that all prior transactions are inputted. If you’ve previously calculated your ACB using another method and don’t want to input all your prior transactions then you can edit each security’s initial share value and initial ACB.
Hello.
There must be a lot of people using TD Direct Investing/Webbroker, and the data we get is the “annual transaction summary” csv file from TD. Am I right about this?
How do people import this in AdjustedCostBasis?
Is everyone building their own Excel conversion solution by hand, or is there something out there already?
I have about 400 transactions and I’m a bit discouraged by the work to convert the format.
Also, am I going to have to set an exchange rate for each USD transaction?
Frederic,
I’m not familiar with TD’s format. Which particular aspects are causing you issues with importing it into AdjustedCostBase.ca? If you have a sample you could either post it hear or send it via the contact form and I can try to offer some suggestions.
We recently introduced a feature for AdjustedCostBase.ca Premium users allowing exchange rates to be easily retrieved when manually adding transactions. Would this be useful to you when importing a spreadsheet as well?
I’ve hired a person to do my calculations last month, but I’m still planning to use AdjustedCostBase premium in the next couple of weeks to also do it on my own.
When importing on your site is the Commision included in Amount?
The TD format gives me is going to require a lot of data wrangling to get it in the requested format. So I was posting to see if anyone else had already a tool to do it.
If I sold 500 shares, I might get multiple “fills” for it.
For example, below I got a 300 fill, and total amount of the transaction was 9191$, which is the true proceed and excludes the 7$ commission. (It makes sense that TD’s proceed amount includes the substraction of the comission, because they also substract any tax that was paid to the US and SEC fees. It’s the true proceed.) Then I got two entries for 100, with no commission because the had different unit prices
-300,-9191,7
-100,0,0
-100,0,0
if it’s a purchase that had 2 fills, I might have these:
300,382,7
700,875,0
this means 300 units for a total of 382$ and that includes the 7$ commission, followed by a 700 purchase with no commision.
Plus, the TD spreadsheet is sometimes inconsistent with
The actual format of the spreadsheet is (note the blank space to indicate no data)
CURR,DATE,SECURITY DESCRIPTION,BUY,SELL,PRICE,COST,PROCEEDS,COMMISSION
CAD,SEP 16 2016,”ANDREW PELLER LTD-A NV “, 100, ,0000032.23, 3230.00, , 7.00
CAD,DEC 05 2016,”ANDREW PELLER LTD-A NV “, ,- 100,0000011.45, , ,
short sells with just be expressed as a sale followed by a purchase.
With regards to exchange rate,
I have not looked at your feature yet, but in an ideal work I want these things
Just identify that a stock purchase was in USD, not specify an exchange rate at that moment
And have a toggle to chose between
1) using one set exchange rate for the year (typically the one CRA says to use)
OR
2) fetch and the exchange rates automatically from the bank of canada, for the date of transaction.
This is because you want to compare which is the optimal conversion rate to minimize your taxes.
Some people might want to use a custom rate based on when they converted their CAD to USD, but I think that’s not allowed by CRA. Anyway, it’s difficult to keep track of with multiple conversions and over time. In any case, I never want to enter an exchange rate for each US transaction.
Hi
Just wanted to thank you for allowing investors to use this tool. It is an invaluable resource for us do-it-yourselfers.
I have been DRIPing for ten years in non-registered accounts without doing regular ACB tracking, and last year decided to shelter some of these investments in a TFSA. The result is that I have a bunch of capital gains to declare, and over 800 entries for ACB calculations.
Please kill me.
Frederic,
Normally the amount should not include the commission and the commission should be specified separately. However, you can choose to exclude the commission but add it to the amount in the case of a purchase or subtract it from the amount in the case of a sale. For example, if you buy shares for $1,000 (excluding commission) with a $10 commission you could input either a) a total amount of $1,000 with a commission of $10 or b) a total amount of $1,010 with a commission of $0. The result should be the same either way. This is the case whether you input transactions manually or import them from a spreadsheet.
If your spreadsheet splits transactions into multiple parts for different fills, this should be fine as long as the total of the amounts equals the total that would apply for a single transactions, and same thing for the shares and the commission. Also, with multiple transactions you must ensure that they’re consecutive (i.e. not staggered with other transactions). Using your example, if you have the following transaction:
– Sell 500 shares for $9,191 with a $7 commission
then the resulting ACB and total capital gains should be the same as if you separate it as follows:
– Sell 300 shares for $9,191 with $7 commission
– Sell 100 shares for $0 with $0 commission
– Sell 100 shares for $0 with $0 commission
as long as these 3 transactions aren’t interspersed with other transactions. The same is true if a buy transactions is split into multiple parts with the components adding up to the total values. However, it may cause some confusion seeing these transactions separated on AdjustedCostBase.ca.
For short sales, gains or losses are typically considered to be on income account, not capital account. Therefore ACB and capital gains are not calculated for short selling.
http://www.cra-arc.gc.ca/E/pub/tp/it479r/it479r-e.html
“The gain or loss on the short sale of shares is considered to be on income account.”
I would suggest you be consistent with using either the average annual exchange rate or the daily rates from year to year, rather than toggling between the two choices based on which provides you with more of an advantage.
Thanks for your great replies. I’ve purchased Premium and I’m importing my transactions.
I think im in the same boat as frederic, also using TD Webbroker, and the format is quite different. I’ll post an example, so hopefully we can figure out the best way together.
Btw, I noticed Frederick said he wants the option to choose between average and actual exchange on transaction date, but (and im guessing here) I imagine the CRA will want us to do one or the other, not pick and choose per transaction?
I’m going to go with the logic that we should be able to choose per ACCOUNT, or maybe per SECURITY, based on what is most FAIR, rather than makes the most profit, as im sure CRA cant argue against that.
Otherwise, I think if doing it based on most profit, it should be one option or the other, to be seen as fair in CRA’s eyes, rather than per transaction.
Does that sound about right? Otherwise I just worry the CRA would be like, “Hey, thats sneeky, you’re not allowed to change the method based on profit”
OK, so heres the example format for the top few lines of the CSV output (Im assuming its the right report):
ACCOUNT,CURR,DATE,SECURITY DESCRIPTION,BUY,SELL,PRICE,COST,PROCEEDS,COMMISSION
ABCD111,CAD,JUL 13 2018,”SOME COMPANY INC “, 35, ,0000056.40, 1983.99, , 9.99
ABCD111,CAD,SEP 17 2018,”SOME COMPANY INC “, ,- 35,0000062.55, ,- 2179.26, 9.99
I can send the full CSV if that helps, but rather not post it here for privacy reasons.
Russell,
On their web site the CRA has stated that they allow an average annual rate to be used, however, they have also contradicted themselves on this matter. As a result I would recommend using the exchange rate on the date of the transaction. Furthermore, with transactions involving an actual currency exchange, this will be more beneficial as any currency spread or exchange costs will be factored into your ACB, resulting in lower capital gains.
In either case I would recommend being consistent – choose one method and sticking with it for all securities and all years.
I’m interested in the premium service for tracking cost basis on my US shares and testing out the import from spreadsheet function but I get ‘Security is Invalid” warning. Most shares are common big caps like AAPL, ABBV, etc. Do US shares need a prefix? Thank you
Dave,
By default, any securities in your spreadsheet must already exist in your account. The name must match exactly against an existing security’s name or ticker symbol.
You may also enable the “Add Unknown Securities” option to automatically add any securities that do not already exist. However, in this case you should be careful to ensure that any securities that are automatically added don’t already exist in another name.
Do I need to sort the transactions by date first before exporting to your platform? The original data are sorted first by transaction type (buy and sell), then by date.
James,
No, transactions do not need to be sorted by date in the spreadsheet. However, you may need to reorder transactions that occur on the same date after you import them to ensure they’re in the correct order.
Hello,
Does this keep track of the currency also for foreign securities, i.e., would it automatically update for example the ACB for US dollars?
Patrick,
When you input transactions that are denominated in US dollars you have 2 options:
1. Convert the amounts into Canadian dollars before uploading the spreadsheet.
2. Specify columns for “Exchange Rate”, “Price in Foreign Currency?” and “Commission in Foreign Currency?”. For the “Exchange Rate” column you can either specify a numerical value (representing the value of CAD$1 in foreign currency units) or you can specify the currency code, e.g. “USD”. In the latter case the Bank of Canada’s exchange rate on the given date will be used.
Just purchased the premium and I am having trouble importing my spreadsheet. I am with TD and the titles are not the same. Here is what TD provides:
Currency
Date
Security Description
Buy (# of shares bought)
Sell (# of shares sold)
Price (price of the security)
Cost (total price when I bought the security)
Proceeds (proceeds of the sale of the security)
Commission
I am receiving an error stating “Each value can only appear in one column”
I have shares written twice (for buy column and sell column)
I have Amount written twice (for cost and proceeds)
What is the workaround for this?
Donn,
Thank you for your inquiry. Assuming that “Currency” is column A in your spreadsheet and the remaining columns are in sequential order, you can add the following to the empty columns the the right:
The last column is required because TD uses an unusual date format.
In Excel these values can be inputted into the first row and then applied to all subsequent rows using the “Fill Down” feature.
You should then be able to use the following settings to map the columns when uploading it to AdjustedCostBase.ca:
If the spreadsheet includes any transactions in a foreign currency, then some further settings and modifications would be necessary.
Hi,
In the example above, when I copy paste your given formula, the column J gives me a “#NAME?” error in excel. Unable to get the “Buy” or “Sell” to show. The same goes with the date, it returns me a “#VALUE!” error, but it is important to note that some of the date returned just fine, but others row give the “VALUE” error. Interestingly, the column K works perfectly with no error. Also, can you be more specific about what to add in the column L “Total Per Share” in our end on excel, before importing in the website?
One more thing, can you be more specific about what settings and modifications would be necessary in the excel spreadsheet that include all transactions in foreign currency, USD, before importing? How do you include the USD exchange rate like you did in the example on the top of this page, column J? Do we need to include a formula in excel that convert all of our USD numbers to CAD numbers or are we doing this exchange rate conversion once the excel spreadsheet has been importing with the tools on the website, as shown here:
https://www.adjustedcostbase.ca/blog/calculating-adjusted-cost-base-with-foreign-currency-transactions/?
Still a little confused!
p.s. I’m not a savvy excel user, so any help is greatly appreciated
Thank you
Max
Max,
I believe the issue with column J was due to the quotation marks being mangled when the comment above was posted. I’ve updated this and it should work properly now when you copy and paste.
I’m not sure what the issue could be with the date column, however, this formula is very specific to a particular date format used by TD. If you share an example of a date in your spreadsheet then I may be able to suggest another formula to use. It’s also possible that no modifications to the date are necessary.
By default the value in the “Amount” column is interpreted as a total amount rather than a per share amount. You can add a “Total or Per Share” column to your spreadsheet to override this by specifying a value of “Total” or “Per Share”. If all values in the column of your spreadsheet are total amounts then the “Total or Per Share” column is not necessary.
If any of the amounts in the spreadsheet are in US dollars then you should add the following columns:
– Price in Foreign Currency?
– Exchange Rate
– Commission in Foreign Currency?
This “Exchange Rate” column can contain either a numerical value representing the value of CAD$1 in the foreign currency at the time of the transaction. Alternatively, you can specify “USD” in this column and the exchange rate reported by the Bank of Canada on the date for the transactions will be used. You therefore don’t need to convert any amount in your spreadsheet if they’re in USD.
Hi,
Thank you for updating the quotation marks for the column J, it is now working perfectly. I don’t know what could be the problem with the date column. As I said, some work and other rows return the “VALUE” error. The format is as follow: APR 07 2020. Sometime, there is a double space between the month and the day. I don’t know if it matters? It would have been nice if all would have worked, but I can work around this without too much hassle.
I successfully set up the exchange rate and foreign currency column, but I’ve encountered a problem though, when trying to import one of my spreadsheet.
I did some transactions on December 28 and it gave me this error: Using exchange rate for 2020-Dec-24 because the exchange rate is unavailable for 2020-Dec-28. The column with this message shows no error, it perfectly handled itself, but I have other transactions/columns with December 28th as a date and these show this message error: The exchange rate is unavailable for this date. These last columns didn’t handle themselves like the previous one as described above, so I can’t go any further.
Can you help me out with this?
Thanks again
Max
Max,
If the date has two spaces between the month and the date (e.g. “APR__07_2020”) then you would need to adjust the above formula to the following:
=DATE(MID(B2,9,4), MONTH(1&LEFT(B2,3)), MID(B2,7,2))
Thanks for reporting the issue with multiple instances of December 28, 2020 dates. Please try again and let us know if you continue to experience any problems.
I’m a premium subscriber and just wondering whether superficial loss transactions are flagged when using this feature. I hope/assume it does but haven’t examined my transactions closely to verify this.
H2,
Warnings will be displayed after adding transactions (in the case of either manually added transactions or transactions imported from a spreadsheet) in instances where AdjustedCostBase.ca can detect when the superficial loss rule may apply. Please note that there are some cases in which the superficial loss rule may apply, but a warning will not be displayed:
– Cases where applicable transactions do not appear in the same portfolio as the sale. For example, if the shares are repurchased in a different portfolio than the sale, or if the repurchase transaction isn’t inputted into AdjustedCostBase.ca because it occurs in a registered account, then the warning will not appear.
– Superficial losses that are triggered due to options transactions.
– Cases where the superficial loss rule applies between securities that are considered identical for the purposes of the superficial loss rule, for example different ETFs that track the same index.
For further information on applying the superficial loss rule, please see the following:
https://www.adjustedcostbase.ca/blog/applying-the-superficial-loss-rule-with-adjusted-cost-base-ca/
I’m assuming “Date” should be the settlement date not the trade date? This is especially important for transactions in foreign currencies.
Max,
Yes, you should input transactions into AdjustedCostBase.ca using the settlement date. Further info is available here:
https://www.adjustedcostbase.ca/blog/understanding-trade-dates-and-settlement-dates/