Step by Step Guide: How to use Portfolio Slicer
Note: This guide assumes that you already setup Portfolio Slicer on your computer.
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.
- 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.
- 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.
- 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.
- 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
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.
- Edit configuration file “psConfig.txt”. Specify parameters “MinDate” values. Check parameters “PSDataFolder” value - that is location where you will place external data files. If you are using Portfolio Slicer “Generated Quotes”, specify them in “GeneratedQuotes” parameter. List currencies and symbols used in parameter areas. For this guide file psConfig.txt will have following values:
- Run batch file “UpdatePSData.bat”. 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 script find no issues with data file formatting. If you would see red message, you should go to folder where these files are located and review file error.txt with details on issues found.
In the current folder this batch job will create sub-folders “CurrExch”, “Dividends”, “Quotes” and “QuotesIntraDay” and place files with data into these folders. It will also create 4 required files in “C:\PortfolioSlicer\PSData” folder. Review “Quotes” subfolder and make sure that there are files for each symbol (for this guide there should be 6 files) - each of this file will have quotes that will be stored on your PC. Review subfolder “CurrExch” - make sure there is a file for currency pair (for this guide there should be 2 files).
- 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.
- 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.
- In “src” worksheet edit “Config” table to have values like this (changed MinDate and TrackCash):
- 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.
- 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.
- We will make “CAD” as our primary currency by assigning it to CurrencyID=1 record in src worksheet table “Report Currency”:
- In “srcSymbol” worksheet we will add information about Symbols. 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).
- At this point we will not add any data into “Symbol Sector” table and will leave it as it was:
- We will not change anything in “Symbol Alias” table and we will leave it as it was:
- 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.
- Now lets refresh data in our reports - to do so please follow instructions: Excel 2010 Refresh | Excel 2013 Refresh.
- 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).
- At this point you can review other reports available in Portfolio Slicer.
- Now lets edit “Config” table and change “TrackCash” parameter from “No” to “Yes”:
- 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!):
- Again, lets refresh data in our reports - to do so please follow instructions: Excel 2010 Refresh | Excel 2013 Refresh.
- And now if we will look at “Holdings” report, we will see that cash is now part of “Total Value” calculation:
- 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:
- 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.
- 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…