Transaction Spreadsheets - tsiemens/acb GitHub Wiki
ACB takes one or more transaction spreadsheets (in CSV format) which provide all of the information about each transaction made (buys, sells) in your portfolio.
Your spreadsheet editor of choice should be able to save simple tables as a .csv file.
Format
Each CSV should have a header and one row per transaction. The transactions can be in ANY order, and can include a mix of different securities in the same file.
| security | trade date | settlement date | action | shares | amount/share | currency | exchange rate | commission | memo |
|---|---|---|---|---|---|---|---|---|---|
| USDSTOCKA | 2016-01-05 | 2016-01-07 | Buy | 20 | 1.5 | USD | 1 | First buy! | |
| CADSTOCKB | 2017-01-04 | 2017-01-06 | Buy | 5 | 50 | 2 | |||
| USDSTOCKA | 2017-01-30 | 2017-02-01 | Sell | 5 | 1.4 | USD | 1.2 | ||
| CADSTOCKB | 2017-01-06 | 2017-01-08 | Sell | 1 | 60 |
The format will look like so if opened in a plain text editor:
security,trade date,settlement date,action,shares,amount/share,currency,exchange rate,commission,memo
USDSTOCKA,2016-01-05,2016-01-07,Buy,20,1.5,USD,,1,First buy!
CADSTOCKB,2017-01-04,2017-01-06,Buy,5,50,,,2,
USDSTOCKA,2017-01-30,2017-02-01,Sell,5,1.4,USD,1.2,,
CADSTOCKB,2017-01-06,2017-01-08,Sell,1,60,,,,
Optional Columns
affiliate- Specify a transaction to apply to a different affiliated person or registered accounts.commission currency- Specify a different currency for commissions than the trade currency.commission exchange rate- Specify a different currency for commissionssuperficial loss- Used to manually specify or override superficial losses.
Historical Format Changes
v0.22.08
Versions prior to 0.22.08 accepted only a single date column. This has since been changed to require both the trade and settlement dates, since they carry their own significance when computing your capital gains for the purposes of your tax return.
Date
Dates MUST be formatted as YYYY-MM-DD
Action
Must be either "Buy", "Sell", "RoC" (return of capital), or "SfLA" (superficial loss adjustment).
Currency
This is the currency in which ALL values for this transaction are specified (both amount/share and commission).
- If left blank, this is assumed to be CAD.
- To use the auto-rate-lookup for USD, specify "USD"
Exchange Rate
The rate at which a foreign currency is converted to CAD. If the currency is not USD or CAD, the exchange rate MUST be specified.
To use auto-rate-lookup, leave this blank (only works for USD). Auto-rate-lookup is based on the trade date, or the last available business day (the 'active' rate on the trade date).
If there is a problem looking up USD/CAD the exchange rate on the specified date, it can be specified explicitly. It can also be specified if you wish to use the year average instead of the per-day rate. You should be consistent in which rate you use, however.
Using Multiple Files
You can organise your transactions into multiple files however you wish. These can all be passed to the tool, and will be treated as one large file.