Importing Transactions Into AdjustedCostBase.ca from a Spreadsheet

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:

Import Transactions from Spreadsheet Link

After following the link you’ll be brought to this form:

Import Transactions from a Spreadsheet 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.
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 100 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:

14 thoughts on “Importing Transactions Into AdjustedCostBase.ca from a Spreadsheet

  1. frederic

    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?

  2. AdjustedCostBase.ca Post author

    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.

  3. AdjustedCostBase.ca Post author

    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.

  4. Mike K

    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?

  5. AdjustedCostBase.ca Post author

    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.

  6. Jim McCloy

    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 ?

  7. AdjustedCostBase.ca Post author

    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.

  8. frederic

    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?

  9. AdjustedCostBase.ca Post author

    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?

  10. frederic

    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.

  11. Conrad Blackish

    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.

Leave a Reply

Your email address will not be published. Required fields are marked *