Technical Info about Portfolio Slicer

Portfolio Slicer was built using 100% of Microsoft technology - there are no 3rd party add-ins. All you need on your machine is Excel 2013/2016 or Excel 2010 with Microsoft PowerPivot add-in installed. Everything in Portfolio Slicer workbook is open (not encrypted, not compiled). Existing PowerPivot data model, including all DAX calculations are available for your review. If you know PowerPivot and DAX language - you can easily add your own calculations or change existing ones.

When you refresh data in Portfolio Slicer (Refresh Excel 2013 | Refresh Excel 2010), Excel loads data from source tables and external files into Portfolio Slicer data model that is built using PowerPivot technology. This data model has predefined relationships between tables and about 50 calculated columns and close to a 100 calculated measures. This is how Portfolio Slicer data model looks in PowerPivot:

Power Pivot Data Model

PowerPivot engine and Portfolio Slicer data model with these calculations and relationships what makes Portfolio Slicer so powerful. When Excel refreshes Pivot Tables/Charts, it requests data from this internal PowerPivot data model. PowerPivot engine is extremely fast and is able to answer all Excel requests on the fly.

For example, if you have a cell in your report that shows "Total Values" of your investments, PowerPivot engine makes following calculations:

  1. For every (!) symbol calculates Quantity at the end of the selected period. For that it goes through transactions table, identifies transactions that change quantity (buy/sell/split/transfer) and based on this "on the fly" calculates Quantity.
  2. For every symbol calculates Price at the end of the selected period. For that is goes through quotes table and finds latest available quote for the end of selected period.
  3. For every distinct pair of symbol currency and reporting currency goes though Currency Conversion table and calculates Exchange Rate at the end of the selected period.
  4. Multiplies "Quantity" by "Price" and by "Exchange Rate" to get total "Equity Value".
  5. Calculates "Cash Value" by going through Transactions table and identifying how each transactions impacts cash balance.
  6. Calculates "Total Value" by adding "Equity Value" and "Cash Value".
All these calculations are done just for once cell and each report can have tens or even hundred of cells. Most Pivot Tables/Charts are refreshed in under 1 second, just few of them takes 3-4 seconds to refresh.