Excel Source Data entry FAQ

Overview

This page collects common questions and answers about entering data into PortfolioSlicer-Source.xlsx file

FAQ

Questions:

How do I enter DRIP transactions?

The TransType table shows the supported transaction types, and for Drip and DripTA it indicates that these types are not implemented.

A Drip transaction represents a case where a dividend is paid into an account and then that exact amount is used to purchase fractional shares of the same symbol. So, in practice, this one event is actually two transactions: a dividend received and then shares bought.

In Portfolio Slicer, you would enter this as 2 transactions:

  1. The first transaction would use TransType DivTA, where in the Price column you would specify the total amount of dividends received.
  2. The second transaction would use TransType BuyTA, with optional TransSubType Drip (for grouping), Qty as the number of shares added, and Price as the amount paid, which should be the same amount as in the previous transaction.

If your investment institution for Drip transactions provides 2 separate transactions as above, you just enter these transactions as provided, just make sure that second transaction type is BuyTA or Buy.

How can I check if the transactions I entered make sense?

Before you start investigating the Transactions table, remove all filters from the table.

  1. If you see any cells with a red background, investigate those cells. For example, if in the Account column you entered a value that does not exist in the Account table, the Account column will have a red background.
  2. Go to the srcReview table, right-click inside the pivot table, and choose Refresh. Review the updated pivot table and confirm that Current Qty is correct for your holdings in each account. Any negative quantity instantly shows that you have problems with that symbol. If the quantity does not match your holdings in the account, that also means your transactions have issues.
  3. If you are tracking cash, review the CashBalance column in the Transactions table. Although it is expected to have a negative value in this column when you have multiple transactions on the same day, at the end of the day this value must be >= 0.
  4. Review the QtyHeld column values. The value in this column should be either empty or a positive number.
  5. Review the Symbol column values. Note: this is the second column from the right, not the same as the SymbolName column. Every value in this column should exist in the Symbol table.
  6. If no issues are found, go to the next step: How can I find a bad record I entered into the Transactions table?

How can I find a bad record I entered into the Transactions table?

Note: You must have backups of all source and report files before continuing with the steps below.

If you already reviewed your transaction records and found no apparent issues, your next option is to find the bad transaction by removing batches of transactions and trying to refresh again.

Start by identifying a “batch” of transactions you are going to remove. For example, you can remove the last 10 transactions you entered. Or you can remove transactions for a single month, year, symbol, or account.

Save the source workbook, run UpdatePSData.bat (full extract), and attempt to refresh the report file.

If the refresh is successful, the issue is in the last removed batch. Restore the source file backup and review those records, the ones you previously removed, even more closely. If you still cannot see any issue, remove half of the previously removed batch. Again, save, extract, and try to refresh. This way you can narrow it down to the exact record that is causing the issue.

If the refresh still fails, remove another batch of transactions, for example 10, and repeat the above steps.

Known issues that were found in the past using this method:

  • Negative cash balance
  • Buying a larger quantity of a symbol than cash allowed
  • A newly added transaction had the wrong year, thus impacting cash flows
  • An investment suddenly lost quotes in Quotes.csv