Portfolio Slicer - design
Portfolio Slicer is a free Excel workbook that lets you track your ivestments (stocks, ETFs, cash, Mutual Funds) your way. Portfolio Slicer comes with over 35 prebuilt reports but these can be extended. If you are able to create Excel Pivot tables and Pivot charts, you can build an unlimited number of your own reports or arrange your own dashboards. Portfolio Slicer is built using PowerPivot, a free Excel addin from Microsoft. PowerPivot provides ground-breaking technology that allows for the fast manipulation of large data sets. Portfolio Slicer uses this technology to create a database structure with calculations that can be easily used in Excel PivotTables and PivotCharts.
Portfolio Slicer uses two main data sources:
- List of investment transactions (cash deposits, security buys, dividends, distributions, security sells, stock splits, etc). This information has to be entered by user in separate spreadsheet.
- Historical stock price quotes. Portfolio Slicer comes with free script that will download historical and current (up to 20 min delayed) stock quotes from Yahoo Financial website and will prepare that data to be loaded into Portfolio Slicer.
When this information is loaded into Portfolio Slicer workbook, it can calculate with amazing speed the stocks held in your portfolio at any point in time, the cash value of your portfolio, changes in portfolio value, and so on. Again, all calculations in the Portfolio Slicer workbook are done on the fly and the majority of PivotTables take 0.5 - 2 seconds to recalculate.
Portfolio Slicer database structure
Portoflio Slicer loads data from the source workbook and flat file with quotes into its own internal database. The following listing depicts the structure of the tables in this database.
The first set of main tables are shown above.
The second set of tables, mostly used to group calculated measures or in internal calculations, are shown above.