Portfolio Slicer - Introduction

Portfolio Slicer is a free Excel workbook that lets you track your investments (stocks, ETFs, mutual funds) your way. Portfolio Slicer comes with over 35 prebuilt reports, but these can be added to. If you have the ability to create Excel Pivot tables and Pivot charts, you can build an unlimited number of your own reports or arrange your own dashboards.

To use Portfolio Slicer you need to have Microsoft Excel 2010 and you will need to download the free Excel add-in from Microsoft: "Microsoft SQL Server 2012 PowerPivot for Microsoft Excel 2010". This add-in provides ground-breaking technology that allows for the fast manipulation of large data sets. Portfolio Slicer uses this technology to pre-build a database with calculations that can be easily used in Excel PivotTables and PivotCharts.

Portfolio Slicer uses three main data sources:

• A list of investment transactions (cash deposits, security buys, dividends, distributions, security sells, stock splits, etc). This information has to be entered by the user in a separate spreadsheet.
• Historical stock price quotes. Portfolio Slicer comes with a free script that will download historical and current (delayed by up to twenty minutes) stock quotes from the Yahoo Financial website and will prepare that data to be loaded into Portfolio Slicer.
• Currency exchange rates (optional). Portfolio slicer comes with free script that will download daily and monthly US$ <-> CA$ exchange rates from Bank of Canada website and will prepare that data to be loaded into Portfolio Slicer.

When this information is loaded into the Portfolio Slicer workbook, it can calculate, with amazing speed, the stocks in your portfolio at any point in time, the cash value of your portfolio, changes in your portfolio value daily/monthly/yearly, and many other metrics. All of the calculations in the Portfolio slicer workbook are done on the fly and the majority of PivotTables take about 0.5 - 5 seconds to recalculate. Here is what will happen if you, for example, will request your total Portfolio value for 2012-05 month (just one cell in one report):

1. Portfolio Slicer will loop through each Symbol and calculate what is total quantity of that symbol in your portfolio. For that Portfolio Slicer will go through your source buy/sell/split transactions and will add or subtract quantity for that symbol.
2. Portfolio Slicer will query quote table and find what was price of that symbol at the end of 2012-05 month.
3. Portfolio Slicer will check if your reporting currency matches your symbol currency. If no, Portfolio Slicer will query currency coversion table and will find what was exchange rate between your symbol currency and reporting currency.
4. For each symbol Portfolio Slicer will multiple quantity with price and then with exchange rate to get total Equity value in your portfolio for 2012-05 month.
5. Portfolio Slicer will go through all your source transactions and will identify how this transaction affects portfolio cash value. When you deposit money or get dividends - your portfolio cash value increases. When you buy stocks, your portfolio cash value decreases. Portfolio Slicer will sum all these cash impacts to calculate your portfolio cash value at the end of 2012-05 month.
6. Portfolio Slicer will add Equity value and Cash value to show Total value.

As you can see Portfolio Slicer does huge number of calculations on the fly, yet technology used allows to do all that with very reasonable performance. Our test machine with around 3000 investment transactions over 80 symbols over 6 years takes about 30 seconds to recalculate all included reports.

Portfolio slicer is delivered in 5 main files:

• File "GetQuotes-Yahoo.ps1". A free script that lets you download daily stock/ETF/mutual fund quotes from the Yahoo Finance website and store them locally. This script is not compiled, the source cood can be seen an edited. Minimal computer skills are required to run this script. This script is written in Microsoft's PowerShell language.
• File "PortfolioSlicer-src.xlsx". An Excel workbook ("Source") with predefined tables where you will have to enter information about your investments (some info about equities, your transactions, etc)
• File "PortfolioSlicer.xlsx". An Excel workbook ("Portfolio Slicer") with a PowerPivot data model and predefined calculations and reports. This workbook will read data from the quotes file and the source file, load it into the PowerPivot data model and provide all of the calculations for your report. All of the calculations (calculated columns and measures) are written in PowerPivot's DAX language. These calculations are open. You can review them, adjust them to meet your needs and create your own calculations.
• File "GetCurrency-CAD-USD.ps1". A free PowerShell script that lets you download daily/monthly CA$ <-> US$ exchange rates from Bank of Canada website. This script is not compiled, the source cood can be seen an edited. Minimal computer skills are required to run this script. This script is written in Microsoft's PowerShell language.
• File "CreateDateTable.ps1". A free PowerShell script that creates flat file with dates. This script will check your quotes and currency exchange file to get minimum and maximum dates that will be used in your analysis and based on that it will create flat file with one record per day.  

Portfolio Slicer was created by VidasSoft Systems Inc., a business intelligence consulting company located in Toronto, Canada. The primary Software Architect for Portfolio Slicer is Vidas Matelis. He has over fifteen years experience in this field. He has received the Microsft MVP award every year since 2008 for his contribution to SQL Server community. In 2012 he achieved "SSAS Maestro" certification, the highest certification available on Microsoft SSAS technology (very closely related to PowerPivot technology).

How Can you Help

Portfolio Slicer is a small Business Intelligence application that is free of charge for personal use. If you like Portfolio Slicer - you can help us. Please help us spread information about it by sharing this with your friends! We very much would appreciate any tweet/facebook Like/ g+1 about Portfolio Slicer. We would love if you would mentioned us in any blog post/article. Thank you very much in advance for that!

We are planning many more enhancements to Porfolio Slicer, so please check back often.

Screenshots

Below are a few screenshots from pre-built reports that show what type of reports can be generated with Portfolio Slicer.