Transactions Table

"Transactions" table is located in Portfolio Slicer workbook "srcTrans" worksheet. Through documentation this table is also called "Trans" or "srcTrans". This table contains all your investment transactions.

Transactions table has one record that should be edited before you start entering your information. This record has comment value “DO NOT DELETE.Adjust Date and Account”. Please change “Account” value to name that exists in your Account table. Please also change transaction date to minimum date that you specified when you setup web workbook. This record from transactions table should never be deleted as it contains formulas and conditional formatting rules that will be automatically transferred to other records you add to this table. This record also tells Excel data type of values for each column. As total transaction amount value for this record is 0, it does not have any impact on your actual portfolio value.

In Portfolio Slicer symbol "* Cash" (star symbol then space then word "Cash") has special meaning and must be used for all deposit/withdrawal/interest transactions. This symbol is designated to track your cash value in portfolio. All "Buy" transactions assumes that money are deducted from symbol "* Cash" and for all "Sales" type transactions cash is added back to "* Cash" symbol value.

Transactions table has following columns:

  • Account - Account that matches value in the Account table. If no match in Account table found then value will have red background.
  • Date - Transaction Date. Should be between Minimum workbook date specified in PS Web Workbook and current date.
  • TransType - Transaction Type can have one of the following values:
    BankFee - Bank fee (administration). Symbol for this transaction must be "* Cash"
    Buy - Buying shares. Need to specify number of shares bought (Qty) and price per share (Price)
    BuyTA - Buying shares. Need to specify number of shares bought (Qty) and TOTAL AMOUNT paid for all shares (Price)
    Deposit - Deposit cash into account. Symbol for this transaction must be "* Cash"
    DIV - Dividends/Distributions received. Need to specify number of shares holding (Qty) and dividend amount per share (Price)
    DivTA - Dividends/Distributions received. Need to specify total dividend amount received for ALL SHARES (Price)
    DRIP - Dividends received and full amount reinvested back into shares. Will add partial shares. Specify number of shares received (Qty) and price per share paid (Price)
    INT - Interest received. Specify total interest amount received (Price). Symbol for this transaction must be "* Cash"
    NotionalDistrib - Notional (Reinvested) Distribution. You do not receive money, but pay tax on that distribution that year. Increase Adjusted Cost Base. Specify total amount (Price)
    ReturnOfCapital - Return of Capital distribution. Decreases Adjusted Cost Base. Specify total amount (Price)
    Sell - Selling shares. Need to specify number of shares sold (Qty) and price per share received (Price)
    SellTA - Selling shares. Need to specify number of shares sold (Qty) and TOTAL AMOUNT received (Price) for all shares (exclude trans fee)
    Split - Split Up or Down shares. Need to specify Qty that changes. If received more shares, qty should be positive number, otherwise Qty should be negative
    SymbolTransferOut - Transferring symbol out of account, but not considered sale. Need to specify number of shares transfered out (Qty) and fair price on the day of the transfer
    SymbolTransferIn - Transferring symbol into account. Need to specify number of shares transfered in (Qty) and fair price on the day of the transfer. Also specify original book value in "CostBasisOverride" to track proper capital gain
    SymbolTransferOutAsSale - Same as SymbolTransferOut, but this transfer will appear in Sales reports. Use when transfer "in kind" from cash into tax (Ex 401k, RRSP) account WHTX - withholding tax paid. Need to specify total tax amount (Price)
    Withdraw - withdrawing cash from account. Symbol for this transaction must be "* Cash"
    If you will enter value not in this list, this value will have red background and you will not be able to update data in PowerPivot model and/or PowerPivot calculations will not be correct.
  • TransSubType - Short text to assign sub-type for transaction. Could be anything - not used in calculations and used to find/filter transactions. Examples:
    1. When you receive dividends and reinvest them, for stocks you will enter 2 transactions - one to mark dividend received and another to mark new shares bought. You can add TransSubType="DRIP", so you can later find these type of transactions easy.
    2. When depositing money you can further identify deposits made by your employer to match contributions by using TransSubType="Employer Deposit"
  • SymbolName - Symbol ticker or Symbol name. If you enter Symbol Name, then in "Symbol Alias" table you should have mapping for Symbol name to Symbol. For TransType = [Deposit | INT | BankFee | Withdraw] please use special Symbol "* Cash". This special symbol "* Cash" will have different formatting - italic type and light purple color.
  • Qty - Enter Quantity. Some transactions ignore quantity you entered. In such case number you entered will have different color ("dimmer"). For all "* Cash" transactions we recommend to use Qty=1. For "DivTA" transaction type we recommend to enter number of shares even if it is not used - for data audit. You can compare number of shares you have in that account for that symbol by checking field QtyHeld on the same row that shows Quantity AFTER current transaction impact is calculated. In majority of cases you should enter Qty without sign. Exception would be when you enter "Split" symbol transaction and after split you end up with less shares than before.
  • Price - Depending on TransType this could be:
    - Price/Dividend Per Share (Buy, Div, DRIP, Sell, SymbolTransferOut, SymbolTransferIn)
    - Total Amount Paid/Deposited/Withdrawn (BankFee, BuyTA, CashTransferIn, CashTransferOut, Deposit, DivTA, INT, NotionalDistrib, ReturnOfCapital, SelLTA, WHTX, Withdraw).
    - For "Split" transaction type leave this value empty.
    Info about "Price" amount sign (+ or -)
    In majority of cases Price amount will have no sign - that is amount you enter in most cases will be positive. For example, for sale transaction you would enter price 40.00$. Exception would be transactions created for adjustments. For example, lets say you hold symbol XRE.TO for which you recorded monthly dividends. At the end of the year you received "Trust Income Statement" that specified that over the year you received 50$ dividends that should be classed as "Return Of Capital". In such case you would record one adjustment transaction for last day of the year where you would specify Price="-50.00" for TransType="DivTA" and then second transaction with TransType="ReturnOfCapital" and Price=50.00.
    Currency clarifications
    Price should use following currency :
    - By default it is assumed that Price is specified using same currency as Accounts (!!) currency
    - You can use different currency, but then you must specify ExchRate column value that will be used to translate Price field to Account currency. In such case field TotalAmnt will show total amount impact in Accounts currency.
    Example. In Canadian RRSP account with CA$ I can hold Symbol MSFT that is actually US$ symbol. In such case I can specify price 40US$ and ExchRate of 1.2, so PS will translate Price as 40US$*1.2=48CA$. Total amount in such case will also include TransFee as 9.99US$*1.2=11.99CA$ because Price and TransFee are assumed to be in the same currency.
  • Fee - Fee associated with this transaction. Usually this is buy/sell transaction fee. Should be entered in the same currency as field Price. If you did not have Fee associated with this transaction, then leave value empty.
  • ExchRate - Exchange Rate that will be applied to Price and Fee to convert values to the same currency as Accounts currency. For example you have Account "D-RRSP-CAD" that was setup using currency CAD. You buy 100 shares of MSFT symbol and that symbol price is used in currency USD. You can enter such transaction in 2 ways that will produce exactly the same result (both transactions TotalAmnt field value will be the same):
    Option1: "Account: Diana-RRSP-CAD, Symbol: MSFT, TransType: Buy, Qty: 100, Price 48.00, Fee: 11.99, ExchRate: (empty)"
    Option2: "Account: Diana-RRSP-CAD, Symbol: MSFT, TransType: Buy, Qty: 100, Price 40.00, Fee: 9.99, ExchRate: 1.2"
    Math: 40.00*1.2=48, 9.99*1.2=11.99
  • Comment - Add any comment about your transaction.
  • CostBasisOverride - For Transactions that affect Book Value you can specify Book Value instead of letting Portfolio Slicer calculate it. Normally this field would be used in following cases:
    - When you do "SymbolTransferIn" transaction and want to calculate Capital Gain/Loss based on Book Value from original account. Then you specify BookValueOverride value that is equal to Book Value of that symbol in old account where you transfer it from.
    - When you believe that Portfolio Slicer incorrectly calculates Book Value due to unforeseen circumstances, you could "Sell" & "SellTA" transactions to use your own calculated Book Value.
    Always specify book override value in absolute number as Portfolio Slicer will choose + or - for this transaction based on transaction type.
  • AccruedInterest - You would use this field just with Bond buying and selling transactions.
  • TotalAmnt - Calculated field - do not edit. This is calculated total transaction amount. Formula is: ROUND( ((Qty*Price)+Fee)*ExchRate, 2) If specific TransType Qty is ignored, then Qty=1 If ExchRate is not specified, then ExchRate=1 If font color is "dimmed" - that means that TotalAmnt is the same as Price, so really no calculations was done. This calculation is shown in the currency of the account of this transaction.
  • CashImpact - Transaction impact on Cash Balance. Will have negative value if this transaction will reduce accounts cash.
  • CashBalance - If "Config" table parameter TrackCash value is set to "Yes", then this field will have calculated Account cash balance AFTER including current transaction impact. If there are multiple transactions in the same account on the same Date, then it is assumed that transactions are calculated based on TransID order. This value is used just for reference in this table and it is not used in PowerPivot model. This calculation is shown in the currency of the account of this transaction.
  • QtyChange - Transaction impact on symbol quantity. If you sold 100 shares, then this value will be -100.
  • QtyHeld - Calculated field that shows symbol Quantity Held AFTER including impact of current transaction. For all cash transactions value is 0 (displayed as empty field). Field can be used for visual audit, for example when entering Dividends received, you can double check if bank reported quantity matches quantity calculated by Portfolio Slicer. This value is used just for reference in this table and it is not used in PowerPivot model.
  • Symbol - Transaction "Symbol" or ticker. Usually represents Stock/ETF/Mutual Fund ticker. This Symbol value is calculated based on following rule - we use SymbolName column to check if we have corresponding values in Symbol Alias table (worksheet src, columns S-T). If Symbol Alias record is found, then we use Symbol specified in Symbol Alias table. Otherwise we use SymbolName column value.Symbol Alias table was introduced to simplify transaction entering process. Most banks/trading institutions will use symbol name and not ticker on their statements. In such case you can create symbol alias that would map symbol name to symbol and after that you can use Symbol Name while you entering transactions. Sometimes symbol names get changed and having multiple names pointing to the same symbol again simplifies transaction entering process. If you do not want to use Symbol Alias, you can simply always enter Symbol code into SymbolName column and that value will be used as your symbol.
  • TransID - Calculated Transaction number equal to Row number. This value is used to uniquely identify each transaction. This value will change when you re-order pivot table.

 

How to use Transaction types “ReturnofCapital” and “NotionalDistribution”?

A: Let say I invested in XEI stocks. During the year I receive dividends into my account (assuming regular account, NOT RRSP or TFSA), lets say 100$/month. Around March of next year I receive “Summary of Trust Income” from my bank that now tells me that every month (actually values every month could be different) I received: 90$ of “Total Capital gains, box 21” 8$ of “Actual Amount of eligible dividends, box 49” 2$ of “Return of Capital, box 42” As I receive dividends every month I know that my “Return of Capital” for year is 24$ (12x2$). For this in portfolio slicer I create 2 transactions for the last day of the year, or if stock was sold during the year, then I enter transactions one day before last sale. Symbol: XEI, TransType: DivTA, TransSubType: DivTypeAdj, Qty: 1000, Price: -24$ Symbol: XEI, TransType: ReturnOfCapital, TransSubType: DivTypeAdj, Qty: 1000, Price: 24$ So with these 2 transactions I subtracted 24$ from general dividends paid by that symbol and then added 24$ for “Return of Capital” paid by that symbol. Total paid dividend stays the same. With these transaction ACB will be reduced and that means when I will sell that symbol, my Capital Gain will be bigger.

Some symbols have NotionalDistributions that are “Reinvested distributions”. For example symbol XEI on Dec 29, 2014 paid 0.74306$ per share as “Reinvested Distributions”. In my TD Waterhouse statement I see this as “CG DIV” transaction followed by “DRIP” transaction. Other banks might not post any transaction for such distribution. In Portfolio Slicer I enter this as one transaction: Symbol: XEI, TransType: NotionalDistrib, Qty: 1000, Price: 743.06$ This distribution is not really paid out as you do not see any change in your cash values. This transaction increases ACB by distribution amount and that means when you will sell symbol, your capital gain will be smaller. Please note that info about this distribution is not included in your “Summary of Trust Income”, so really if you did not properly identify this transaction and adjust your ACB, you will pay extra tax.

 

How in Portfolio Slicer enter “Norbert’s Gambit” transactions

First of all here are 2 great links explaining more about Norbert's Gambit: (here and here).

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

Example1: When buy and sell is done in the same account but with different currency

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

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

 Example 2: When buy is done in one account (example CAD$) and sell is done in another currency account (USD$).

  1. First enter buy transaction in CAD $ portfolio. Make sure that symbol specified is the same currency as portfolio. In our example we use symbol TD.TO.
  2. For the same portfolio add "SymbolTransferOut" transaction - you are moving that symbol out of this portfolio into another portfolio. Please note that now TransPrice for that symbol also inlcudes transaction fee paid in previous transaction Trans Price = ((TransQty*TransPrice) + TransFee) / TransQty.
  3. In another USD $ portfolio add "SymbolTransferIn" transaction. This transaction uses slightly different symbol "TD" - it is the same company as TD.TO, but that symbol is traded in US stock exchange. Enter the same quantity as in previous transaction. To calculate TransPrice use official exchange rate from Bank of Canada website - this should be rate that you would use when filing your taxes for this investment transaction. So for this transaction TransPrice=TransTotalAmnt(PrevTransaction)*BankExchRate/TransQty and TBookValueOverride=TotalTransAmnt(PrevTransaction)*BankExchRate.
  4. Finally enter sell transaction for USD $ portfolio and specify actual sales price and transaction fee.

Your bank statement very likely will have very similar transactions (symbol transfer transactions might be called "journal entries"). After these transactions now you have converted CAD$ into USD$.

The goal of these transactions really is:
- For CAD account your buy and Transfer out "TotalAmount" should be exactly the same. So there is really no capital gain for this transaction.
- For USD account your Transfer In value for "TotalAmount" will be in USD currency and it should be equal to CAD account book value converted to USD currency using that days exchange rate. If you are selling this symbol same day, you can then use sales price (adjusted for transaction fees).