Step by Step Guide: How to use Portfolio Slicer

Note: This guide assumes that you already setup Portfolio Slicer on your computer and that you are using External Data Management Scripts provided by Maxim.

Video - how to use Portfolio Slicer

Step by step guide on how to use Portfolio Slicer

Before starting to use Portfolio Slicer you need to make following decisions.

  1. Decide from when you will start track your investments (MinDate) with Portfolio Slicer. We recommend that you consider adding at least 3 full years of data into portfolio slicer. The more data you have, the more interesting Portfolio Slicer reports are. It is also recommended that you start from last business day of the year before first full year with data. So if now is mid February of 2016 and for this guide we would like to have workbook with full 3 years of data, we will choose MinDate as 2012-12-31. This way will will have data for full years 2013, 2014 and 2015.
  2. Get statements from your bank/investment institution for dates between MinDate and up to now. You will need these statements to identify what holdings you had at the MinDate and you will also need transactions from that date.
  3. Decide if you want to track cash with Portfolio Slicer. For this guide we will start using Portfolio Slicer without tracking cash and then we will add cash tracking.
  4. Decide how you will track dividends with Portfolio Slicer. You can either enter all dividend information yourself, or you can create external dividends.csv file that contains information about all dividend payments per share. For this guide we will assume that all dividend payments information is entered into Transactions table by you.
While using Portfolio Slicer, you will have to focus on 2 different areas:
  • Preparing External data files
  • Adding data about your investments into Portfolio Slicer workbook

Preparing External Data Files

Portfolio Slicer expects 4 external files “Dates.csv“ ,”CurrencyConv.csv“ ,”Quotes.csv“ and “Dividends.csv” that will be imported into Excel workbook. You can use External Data Management Scripts to create these files.

  1. Edit configuration file “psConfig.txt” and specify parameters “MinDate” value. MinDate is very important value and if you will decide to change it later - you will have to delete all existing data from Quotes and CurrExch folder and then re-run scripts.
  2. Run batch file “UpdatePSData.bat” after you updated psConfig.txt MinDate parameter value and BEFORE you you add currency or Symbol information. As default psConfig.txt file list 2 index symbols, when you will run scripts, you will attempt to download quotes for these 2 indexes. At the end of the run you should see green message like this:
    If you see green message at the end of script execution, then PowerShell scripts are working OK. If you see any red messages, that means you have to fix issues reported by error messages. For example, if during PowerShell script execution you will get error message “Method Invocation failed because [System.Object[]] doesn’t contain a method named ‘IndexOf’”, then you will need to upgrade PowerShell to version 3.0 or later.
  3. Check \Quotes\ subfolder and use notepad to open existing files there. Familiarize yourself with how data is stored in these files. Each symbol should have 2 files - one file that is used by Portfolio Slicer with monthly/daily values and then archive file. For example symbol “^GSPC” will have files “_ _ GSPC.txt and “ _ GCPC_ _ Archive.txt”. Inside each file you should see comma separated values without header, for example:
    2014-12-31,2058.899902,^GSPC
    2015-01-30,1994.989990,^GSPC
    2015-02-27,2104.500000,^GSPC
  4. Check \PSData\Quotes.csv file and familiarize yourself how data looks in this file. You should see TAB separated values with header, like this:
    Date Close Symbol
    2014-12-31 1.0000000001 * Cash
    2014-12-31 2058.899902 ^GSPC
    2015-01-30 1994.989990 ^GSPC
    First line in this file will be header, followed with quote for “* Cash” symbol - ignore this first record as it is placeholder record.

  5. Update psConfig.txt file “Currency” Section. If all your symbols are in one currency and your country currency is the same, then you will have just one value in this section. For example, if you live in USA and invest just in USA listed symbols, then you would list “USD” in your currency section. But if you live, for example in Canada, and invest in USA and Canadian symbols, then you would list USD and CAD values in Currency section:
    <Currency>
    USD
    CAD
    </Currency>
    Re-run UpdatePSData.bat script. Check content of CurrExch folder. If you have just one currency, then this folder will be empty. If you have more than one currency, you will have files there with exchange rates between currency pairs, example “CAD_USD.txt” and “USD_CAD.txt”. This files will have COMMA separated values with exchange rates for each business day, example:
    2016-01-29,0.7102,CAD,USD
    2016-01-28,0.7106,CAD,USD
    2016-01-27,0.7121,CAD,USD
    Check file “\PSData\CurrencyConv.csv” - this file will have TAB separated values with header, example:
    Date ExchRate CurrencyFrom CurrencyTo
    2006-12-27 1.0000000001 N/A N/A
    2016-04-08 0.6747 CAD EUR
    2016-04-07 0.6672 CAD EUR
    First line in this file will always be header and then first record will have MinDate value from your configuration and CurrencyFrom/CurrencyTo values as N/A. This is placeholder record that could be ignored.

  6. Choose 1 symbol that you will start working with. Choose quote source for that symbol:
    • Visit Yahoo Finance website http://finance.yahoo.com and enter name/ticker in search bar, for example “Apple” and hit search. You should see page with summary information about that symbol. Take a note of symbol in url, for Apple this will be AAPL: https://finance.yahoo.com/quote/AAPL?p=AAPL , but for XEI this will be XEI.TO: https://finance.yahoo.com/quote/XEI.TO?p=XEI.TO. This is a symbol that you will have to specify in Yahoo section of psConfig.txt file. Click on “Historical Data” link from Symmary page (just below quote on the right side) - if you see historical data and “Download Data” link then you should add this symbol to Yahoo section in psConfig.txt and use Yahoo as quote source.
    • Visit Google finance website https://finance.google.com and enter name/ticker in search bar, for example “Apple” and hit search. You should see page with summary information about that symbol. Take a note of symbol in url, for Apple this will be AAPL: https://finance.google.com/finance?q=AAPL&ei=gRe4Wbmasdfasd, but for XEI this will be TSE:XEI: https://finance.google.com/finance?q=TSE%3AXEI&ei=gRe4Wbmasdfasd. In XEI link you see value %3A between TSE and XEI and that is code for “:“. But you can see same symbol at the top of the page just after the name. This is a symbol that you will have to specify in Google or GoogleWeb section of psConfig.txt file.
      Use url: https://www.google.com/finance/historical?q=AAPL&num=30&start=0&startdate=Sep+1%2C+2017 and replace AAPL with your symbol (example TSE:XEI) and visit that page. If on the right side you see “Export” section with link “Download to spreadsheet”, then you can use that symbol in “Google” section of psConfig.txt file. If you do not see “Export” section, but still see historical quotes in web table, then you can use that symbol in “GoogleWeb” section of psConfig.txt. If you do not see historical quotes, that means you cannot use Google as source for quotes for that symbol!!!!!
    After adding symbol to Yahoo,Google or GoogleWeb section of psConfig.txt file run UpdatePSData.bat script. Confirm that script runs successfully (green message) and then check files in Quotes folder - make sure there are quotes for dates you need in that symbol quotes files.
    Repeat this step for few more symbols and make sure to check if quotes are successfully extracted to Quotes folder after adding each symbol. At the start do not add all symbols you are planning to work with, but rather choose 1-3 symbols. Make sure Portfolio Slicer works with just a few symbols and just then keep adding more symbols and testing results. To continue with examples, this guide will use currency CAD and USD and symbols AAPL,VTI and XEI.TO in Yahoo section and symbol MUTF_CA:TDB900 in GoogleWeb sections.

  7. If you are using Portfolio Slicer “Generated Quotes”, specify them in “GeneratedQuotes” parameter.
  8. At this point your psConfig.txt is updated for your environment. For this guide file psConfig.txt will have following values:

  9. At this point your external data files are ready (yes, it was that easy - thanks to users MaximT scripts). You will need to run batch file “UpdatePSData.bat” every time when you want to get updated quote information - this could be done daily or even multiple times a day if you want to get intraday quotes.
  10. At this point you have a choice of changing where Excel will be loading external files from. Default folder is “c:\PortfolioSlicer\PSData\“. You can change this location by following instructions on this page: Excel 2010 | Excel 2013. For this guide we will assume that we will keep all external files in the default folder.
  11. In psConfig.txt there is section “SymbolRename” that could be used to change symbol string in Quotes.csv and Dividends.csv files. For example, you defined symbol TSE:XEI in GoogleWeb section in psConfig.txt file. But in Excel workbook you would prefer to refer to this symbol as XEI. In such case in “SymbolRename” section you would add line “TSE:XEI,XEI” and when Quotes.csv and Dividends.csv file is created, symbol string “TSE:XEI” will be replaced with “XEI”.

Adding data about your investments into Portfolio Slicer workbook

  1. In "src" worksheet edit "Config" table to have values like this (changed MinDate and TrackCash):
  2. In "src" worksheet edit "Account" table to have values like this:
    Please note that we will be using 2 accounts with different currencies: CAD and USD.
  3. In "src" worksheet edit "Allocation" table to have values like this:
    For this simple scenario we will target 80% of our investments into USA market and 20% of our investments into Canadian market.
  4. We will make "CAD" as our primary currency by assigning it to CurrencyID=1 record in src worksheet table "Report Currency":
  5. In "srcSymbol" worksheet we will add information about Symbols. It is important that all symbols that have records in Quotes.csv and Dividends.csv files are defined in Symbol table! First of all we have to confirm format of symbols in Quotes.csv external table - as Symbol in "Symbol" table should match column "Symbol" in Quotes.csv table. In our case symbols we have quotes for have following format: "^GSPC","^GSPTSE","AAPL","VTI","XEI.TO" and "TDB900.TO". We will add information about these symbols with following data:
    Important fields in this table: Symbol, SymbolName, Currency (Must match value in "Report Currency" table), MER and WHTPercent (it is good idea for number values always enter data - if value is not known or not applicable, then enter 0).
  7. At this point we will not add any data into "Symbol Sector" table and will leave it as it was:
  8. We will not change anything in "Symbol Alias" table and we will leave it as it was:
  9. In "srcTrans" worksheet first we have to edit first record to replace "Account" with one of the accounts from our table and then replace "Date" with MinDate for our setup. Then we will enter transactions that will establish holdings in our accounts for MinDate - that is 2012-12-31. We choose to use transaction type "SymbolTransferIn", but "Buy" would work almost in the same way (except if tracking cash). For all transactions we use date as 2012-12-31, but amounts are as of date of symbol purchase. For all initial transactions it is highly recommended to populate column "CostBasisOverride" with original cost basis specified in the currency of the account (not symbol!). For this example take a look at 2 different transactions for AAPL symbol - they were bought at same time and for the same price, but placed in accounts with different currencies:
    We also added 2 last transactions to mark AAPL symbol stock split. This was 1 to 7 split, where for each share we got 6 additional shares (1 share become 7 shares). As we had 10 shares in each account, we added 60 more shares at the date of the split.
  10. Now lets refresh data in our reports - to do so please follow instructions: Excel 2010 Refresh | Excel 2013 Refresh.
  11. First report that you should look at after refresh is "Holdings" report. Make sure you first change "ReportCurrency" slicer to "*Original*" to remove currency conversion. Start by checking "Qty Held" column for each symbol to confirm that value is what you would expect:
    If you have same symbol in multiple Accounts, you can click on the "Account" slicer to show this report for that specific Account. If you do not see symbol in this report, that most likely means that you do not have quotes for this symbol. This report is filtered to show just symbols with "Total Value">0. If you do not have quote for symbol or if after currency conversion value is 0 (no currency conversion rates from symbol to report currency provided) then then symbol will not appear in this report. If quantity is OK, then look at "Cost Basis" and "Total Value" columns to make sure they match your expectations. If you see any issues there, then change "Report Currency" slicer value to "*Original" - by doing so you will disable currency conversion. If at this point numbers look good, you can switch between currencies to confirm that currency conversion works properly (that is - you provided right currency conversion data).
  12. At this point you can review other reports available in Portfolio Slicer.
  13. Now lets edit "Config" table and change "TrackCash" parameter from "No" to "Yes":
  14. In Transactions table now we can add deposit transactions for 2012-12-31, as we know that one account had 1000$ and another account had 2000$ in cash (in account currency!):
  15. Again, lets refresh data in our reports - to do so please follow instructions: Excel 2010 Refresh | Excel 2013 Refresh.
  16. And now if we will look at "Holdings" report, we will see that cash is now part of "Total Value" calculation:
  17. At this point we will add records about dividend received. We choose to manually enter dividends received into Transactions table. In such case make sure that for symbols that you enter dividends information manually, you do not have records in Dividends.csv file. From our statements we found that VTI paid dividends: 2013-1.673, 2014-1.869, 2015-2.067 per share. We choose to enter one transaction per year, we choose middle of the year date. Also, in our account bank withholds 20% tax on any dividends received, so we record that too:
  18. Lets do refresh on our reports one more time:: Excel 2010 Refresh | Excel 2013 Refresh.
    This time as we know that we changed data just in one table Transactions, and we are sure that other tables did not change, we could do "fast" refresh. In Excel 2010 we can simply open PowerPivot window - this will trigger Transation table update in data model. Then we can right away close PowerPivot window and do right mouse click in any Pivot Table and choose "Refresh". In Excel 2013 we can do one table refresh as described in Excel 2013 Refresh page "Selective data refresh" part.
  19. And now if we will look at "Holdings" report, we will see that dividends for VTI are in the report and included in Profit calculation:
    We could continue adding dividend information for other symbols...