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: