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:

8 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.

Leave a Reply

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