Transactions Table

Overview

The Transactions table is the core activity table in Portfolio Slicer.

It stores buys, sells, transfers, dividends, cash events, and other portfolio activity. Many reports depend heavily on this table being complete and internally consistent.

Special Symbol: * Cash

Portfolio Slicer uses the symbol * Cash for cash-related transactions such as Deposit, Withdraw, INT (interest), BankFee, and similar entries. For these transaction types, it is important to enter the exact value * Cash in the SymbolName field (*, then a space, then Cash).

Table Columns

The darker columns on the right side are calculated columns and should not be edited. These calculated values help you understand how the data you enter affects that account.

  • Account - account assigned to this transaction. It must match a valid account from the Account table. If no match is found, the cell will normally show a red background.
  • Date - transaction date. It must be a value from MinDate up to today. The format is YYYY-MM-DD.
  • TransType - transaction type. It must be a value from the TransType table. Please carefully review the possible TransType values and how they might affect other columns in this table.
  • TransSubType - transaction subtype. This can be any value you want to use for transaction-type grouping. For example, if you enter a DivTA/BuyTA transaction pair where BuyTA represents a DRIP, you can specify DRIP for the BuyTA transaction.
  • SymbolName - symbol name for the transaction. For cash-type transactions this should be * Cash. For all other transactions, this should be either the Symbol column value from the Symbol table or a SymbolAlias value from the SymbolAlias table. If you entered a SymbolAlias and it is found, then on the far right side of the Transactions table you should see the calculated Symbol column populated with the matching value from the Symbol table.
  • Qty - number of shares affected by this transaction. Some transaction types ignore Qty. In those cases, the value may appear visually dimmed. For most transactions, enter Qty without a sign. A common exception is Split, where Qty can be positive or negative depending on whether the number of shares increased or decreased. For * Cash transactions, using Qty = 1 is usually a practical convention.
  • Price - depending on TransType, this may be either price per share, total price, or total amount. This value should be specified in the currency of the account.
    Please read more about the TransType table and how it affects the value you should specify in the Price column. In most cases, Price is entered as a positive value. An important exception is year-end adjustment transactions. For example, when you reclassify part of a dividend as return of capital, you may enter a negative DivTA adjustment and a positive ReturnOfCapital adjustment.
  • Fee - represents transaction fees when applicable. This should always be entered in the same currency as Price.
  • ExchRate - optional exchange-rate value. In some cases you may want to specify Price and Fee in a different currency than the currency assigned to the Account. In such cases you can specify ExchRate, which will convert Price and Fee to the currency of the account.
    For example, if the account is in CAD but the symbol is priced in USD, you can enter the USD price and use ExchRate to convert it into CAD.
  • Comment - free-text note for your own recordkeeping.
  • CostBasisOverride - lets you override Portfolio Slicer’s built-in cost basis calculation when needed. This is especially useful for initial holdings, SymbolTransferIn, complex partial-sale cases, or situations where you already know the correct broker cost basis.
    Always enter this as an absolute value. Portfolio Slicer will apply the sign logic based on the transaction type.
  • AccruedInterest - not used column, left for backward compatibility.
  • ExchRateRpt1Override - exchange-rate override value for Report Currency with ID = 1. When a value is specified, the exchange rate for this transaction will not be picked up from CurrencyConv.csv. Instead, this specified value will be used.
  • ExchRateRpt2Override - exchange-rate override value for Report Currency with ID = 2. When a value is specified, the exchange rate for this transaction will not be picked up from CurrencyConv.csv. Instead, this specified value will be used.
  • ExchRateRpt3Override - exchange-rate override value for Report Currency with ID = 3. When a value is specified, the exchange rate for this transaction will not be picked up from CurrencyConv.csv. Instead, this specified value will be used.
  • TotalAmnt - (calculated column) total calculated amount of this transaction. This checks whether Qty should be used or whether Price represents a total amount or a per-share amount.
  • CashImpact - (calculated column) total calculated cash impact, positive or negative.
  • CashBalance - (calculated column) account cash balance after this transaction. This works only when TrackCash is enabled in the Config table.
  • QtyChange - (calculated column) shows how this transaction changes the Symbol quantity, positive or negative.
  • QtyHeld - (calculated column) shows the total Symbol quantity held in the account after the transaction.
  • Symbol - symbol value from the Symbol table. When SymbolName was specified as a SymbolAlias, this column shows the actual Symbol.
  • TransID - unique sequential number representing the transaction ID.

Distribution type ReturnOfCapital

Assume symbol XEI pays monthly cash distributions into a regular taxable account (not an RRSP or TFSA), for example $100 per month.

Around March of the following year, the broker may issue a Summary of Trust Income showing that each monthly distribution was actually made up of different tax components. For example:

90$ of Total Capital gains, box 21
8$ of Actual Amount of eligible dividends, box 49
2$ of Return of Capital, box 42

If this happened every month, then the total Return of Capital for the year would be $24 (12 x 2$).

In Portfolio Slicer, this can be entered as 2 transactions dated on the last day of the year. If the symbol was sold during the year, then these transactions should be entered one day before the last sale.

Symbol: XEI, TransType: DivTA, TransSubType: DivTypeAdj, Qty: 1000, Price: -24$
Symbol: XEI, TransType: ReturnOfCapital, TransSubType: DivTypeAdj, Qty: 1000, Price: 24$

With these 2 transactions, $24 is subtracted from the general dividends paid by that symbol and $24 is added as ReturnOfCapital.

The total cash distribution stays the same. However, because ReturnOfCapital reduces ACB, the capital gain will be higher when the symbol is eventually sold.

Distribution type NotionalDistrib

Some symbols have NotionalDistributions, which are reinvested distributions. For example, symbol XEI on Dec 29, 2014 paid $0.74306 per share as a reinvested distribution.

A broker statement may show this as a CG DIV transaction followed by a DRIP transaction. Other banks may not show any transaction for such a distribution.

In Portfolio Slicer, this can be entered as one transaction:

Symbol: XEI, TransType: NotionalDistrib, Qty: 1000, Price: 743.06$

This distribution is not actually paid out, so there is no visible change in cash balance. Instead, this transaction increases ACB by the distribution amount, which means that when the symbol is sold, the capital gain will be smaller.

Please note that information about this distribution may not be included in the Summary of Trust Income, so if this transaction is not identified correctly and ACB is not adjusted, extra tax may be paid.

Norbert’s Gambit

First of all, here are 2 great links explaining more about Norbert’s Gambit: here and here.

Note: Before adding transactions you must enter the symbol(s) that you will use into your Symbol table.

Example 1: Buy and sell are done in the same account, but with different currency

Usually when you enter Norbert’s Gambit transactions into Portfolio Slicer you will end up with 3 transactions. Below is an example of such entries.

  1. The first entry is a Buy transaction for your symbol and, because this is a Canadian-currency account and you are buying a Canadian symbol, there is no ExchRate.
  2. The second transaction is a Sell transaction. Please note that although normally an equity sold on a US exchange would have a different symbol (DLR-U.TO vs DLR.TO), you must specify the same symbol as in the first transaction. For this Sell transaction you must also specify ExchRate, because now you are selling a US-currency symbol from a Canadian-currency account. Most importantly, you must make sure that TotalAmnt matches your bank statement exactly.
  3. The third transaction shows how you would use the US currency to buy some other US symbol. The example here shows buying symbol IDV, but the same rules apply if you are just parking your US currency in the account. As you can see, we again use the same ExchRate value as in the previous transaction. Again, it is very important that TotalAmnt matches your bank statement exactly.

Example 2: Buy is done in one account (for example CAD$) and sell is done in another currency account (USD$)

  1. First enter the Buy transaction in the CAD$ portfolio. Make sure that the symbol specified is in the same currency as the portfolio. In this example we use symbol TD.TO.
  2. For the same portfolio add a SymbolTransferOut transaction. You are moving that symbol out of this portfolio into another portfolio. Please note that now Price for that symbol also includes the transaction fee paid in the previous transaction. Price = ((Qty * Price) + Fee) / Qty.
  3. In another USD$ portfolio add a SymbolTransferIn transaction. This transaction uses a slightly different symbol, TD. It is the same company as TD.TO, but that symbol is traded on the US stock exchange. Enter the same quantity as in the previous transaction. To calculate Price, use the official exchange rate from the Bank of Canada website. This should be the rate that you would use when filing your taxes for this investment transaction. For this transaction: Price = TotalAmnt(previous transaction) * BankExchRate / Qty and CostBasisOverride = TotalAmnt(previous transaction) * BankExchRate
  4. Finally, enter the Sell transaction for the USD$ portfolio and specify the actual sales price and transaction fee.

Your bank statement will very likely show very similar transactions. Symbol transfer transactions might be called journal entries. After these transactions, you have converted CAD$ into USD$.

The goal of these transactions is:

  • For the CAD account, your Buy and SymbolTransferOut TotalAmnt values should be exactly the same. So there is really no capital gain for this transaction.
  • For the USD account, your SymbolTransferIn value for TotalAmnt will be in USD currency, and it should be equal to the CAD account book value converted to USD currency using that day’s exchange rate. If you are selling this symbol on the same day, you can then use the sales price adjusted for transaction fees.

Practical Advice

  • enter transactions in small batches
  • validate symbols and accounts carefully
  • use * Cash correctly for cash events
  • populate CostBasisOverride for initial holdings when needed
  • when in doubt, make sure TotalAmnt matches the bank or broker statement exactly

Complicated splits

Sometimes companies announce complicated splits and Portfolio Slicer can handle them through multiple transactions.

Example 1

Company ABC announced that stock holders on 2020-01-20 for each ABC share will receive 0.5 of company ABC shares and 2 of company DEF shares. Value split between these 2 companies will be 60% ABC and 40% DEF.

Lets say you had 100 shares of company ABC on that date, and your cost basis for these shares was 7500$.

Lets say that at the end of the day before the split (2020-01-19) symbol ABC price was 80$. That means that total value at the end of 2020-01-19 for this holding was 100 * 80$ = 8000$.

To record such split in Portfolio Slicer, you will need to enter one SymbolTransferOut transaction, one DivTA transaction and 2 SymbolTransferIn transactions. For these transactions you want to make sure that:

  • Total Amount that is in SymbolTransferOut transaction (Qty * Price = 100 * 80$ = 8000$) matches sum of both SymbolTransferIn total amount transactions (Qty * Price). So for ABC SymbolTransferIn transaction Total Amount should be 8000$ * 60% = 4800$. For DEF SymbolTransferIn transaction the Total Amount should be 8000$ * 40% = 3200$.
  • Cost Basis of SymbolTransferOut transaction (7500$) matches sum of both SymbolTransferIn column CostBasisOverride. So for ABC SymbolTransferIn transaction CostBasisOverride should be 7500$ * 60% = 4500$. For DEF SymbolTransferIn transaction the CostBasisOverride should be 7500$ * 40% = 3000$.

To implement this split we will end up with 3 transactions:

  • Date: 2020-01-20, TransType: SymbolTransferOut, Symbol: ABC, Qty: 100, Price: 80$
  • Date: 2020-01-20, TransType: SymbolTransferIn , Symbol: ABC, Qty: 50, Price: 96$ (Total amount: 50*96$=4800), CostBasisOverride: 4500$
  • Date: 2020-01-20, TransType: SymbolTransferIn , Symbol: DEF, Qty: 200, Price: 16$ (Total amount: 200*16$=3200), CostBasisOverride: 3000$