Frequently Asked Questions (FAQ)

Overview

This page collects common questions and answers about Portfolio Slicer data, including source workbook tables, generated files, and how data issues usually appear in reports.

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.

When I run a .bat job, the pop-up window closes too fast. How can I make it stay longer?

You can keep the .bat window open longer by doing one of the following:

  1. Press CTRL+C. This will interrupt the run and prompt you with Terminate batch job (Y/N)?, and execution will not continue until you press Y or N.
  2. Open the .bat file in Notepad or any other text editor. Near the end of the file, you will see a line such as choice /C Y /T 10 /D Y /M "Waiting 10sec before closing". The parameter /T 10 tells the system to wait 10 seconds before closing the window.

You can change that value to a larger number, for example /T 60. This will force the window to wait 60 seconds before closing.

When I run a .bat job, I see an error message. What should I do?

First, press CTRL+C to make sure that the .bat window does not close.

Second, identify at what part of the .bat execution the failure happened. During .bat execution, you will see a list of scripts being executed. The last executed script is always Script: CheckFiles. If you see that line, it means that the .bat execution failed during CheckFiles.ps1 script execution. Otherwise, the failure happened during an earlier script.

Your next steps:

  1. If the script failed before executing the last PowerShell script, CheckFiles.ps1.
    • You will likely see an error message that specifies the script name and line number, for example: <error info> c:\PortfolioSlicer\MyPSData\PSDataExtract\Scripts\GetQuotes-Yahoo.ps1:100 char:7 <error info>. Capture this information. The numbers after the script name represent the line in the PowerShell script where the error happened.
    • First, go to the AI Help link on the website, provide that information, and ask for help. It also helps if you include the PowerShell script that reported the error.
    • If AI Help does not help resolve the issue, post this error information on PS Support.
  2. If the script failed while executing the last PowerShell script, CheckFiles.ps1.
    • The CheckFiles.ps1 script in the report files folder c:\PortfolioSlicer\PSData creates a file error.txt with information on what checks were done and what errors were found. This file is created only if errors were found.
    • Confirm that the timestamp on the file error.txt matches the time when the scripts failed. Also confirm that the other files in this folder match the time when the scripts were run.
    • Open file error.txt in Notepad and review the error messages. Address the issues reported in this file. For example, an issue such as Symbol in Quotes.csv file, but not in Symbol table means that you have to add that symbol to the Symbol table.
    • You can go to the AI Help link on the website, provide the information reported in the error.txt file, and ask for help explaining it.

When I refresh the Portfolio Slicer report workbook, I get an error. What should I do?

  1. If you have the PortfolioSlicer-Source.xlsx file open in Excel, save and close it. Sometimes, when this file is open, PowerShell scripts cannot properly read data from it.
  2. Go to the PSDataExtract folder and run the UpdatePSData.bat batch file. Make sure no errors are reported while this batch job executes. If any error messages are reported, read the previous FAQ entries to address those issues.
  3. Go to the c:\PortfolioSlicer\PSData folder. Check the timestamp for every file in this folder. All these files should have been created within the last minute, because you just ran the UpdatePSData.bat batch file in the previous step. If the timestamps do not match your last execution, open the psConfig.txt file and check the PSDataFolder parameter. In v3, you should see 17 files in this folder, and all of them should have exactly the same timestamp, or a difference within 1 minute.
  4. Check whether file c:\PortfolioSlicer\PSData\error.txt exists. If it does, open it in Notepad, review it, and address the issues reported.
  5. Open file c:\PortfolioSlicer\PSData\Symbol.csv in Notepad. The first line in this file should be a header, and after that you should see one line per symbol that exists in your Excel source file. Every column value, including the header, must be TAB-separated, not comma-separated.
  6. Open file c:\PortfolioSlicer\PSData\TransactionsInExcel.csv in Notepad or Excel. Confirm that this table has approximately the same number of records as the Transactions table in the PortfolioSlicer-Source.xlsx file.
  7. At this point, you have confirmed that the data files for Portfolio Slicer reports were prepared as expected. The issue is likely now with data that requires a more complex investigation.
  8. Back up the PortfolioSlicer-Source.xlsx file and all report files you are using. You will be changing them, and after identifying the problem, you will want to restore these backups and apply the fix you identify.
  9. Review the transactions you entered to make sure they make sense.
  10. Try refreshing the Excel PortfolioSlicer-Reports-Light.xlsx report file. This report has a simplified model, so it has a better chance of refreshing successfully. If the refresh succeeds, review all reports for data anomalies, for example: no data for a specific period, one account has a negative cash balance, or the holdings report is missing symbols.
  11. Try refreshing the Power BI PortfolioSlicer.pbix file. Because Power BI has slightly different error handling and a different refresh pattern, refreshing only the current page, you might have more success there. If the refresh succeeds, review all visible reports for data anomalies, for example: no data for a specific period, one account has a negative cash balance, or the holdings report is missing symbols. If you see any data issues, they likely point you in the direction you need to investigate. Open the PortfolioSlicer-Source.xlsx file and review the data there for the affected symbols, accounts, dates, or transactions.
  12. If the above did not help, follow the next FAQ: How can I check if the transactions I entered make sense?
  13. Another option to investigate further:
    • Make sure you have backups of your report files.
    • Open the PortfolioSlicer-Reports.xlsx file.
    • Delete all worksheets except Mthly-Symbol.
    • Go to the Mthly-Symbol worksheet, select symbol * Cash, remove filters from the Year and Account slicers, and select *Original* in the ReportCurrency slicer.
    • Refresh the report file (Data -> Refresh All -> Refresh All). This should be successful.
    • From the Symbol slicer, select one symbol and review the report. Make sure that the symbol does not disappear suddenly, for example because there is no quote.
    • Repeat the previous step for all symbols one by one.

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