Source data

The source data about your investments must be entered into the PortfolioSlicer-src.xlsx Excel workbook. This workbook has multiple sheets and each sheet contains data for one table (or subject area). The main tables are "Date", "Trans", "Portfolio", "TransType" and "Symbol". Other tables are used just for specific, non-critical reports or are tables used to group calculated measures. Below you can find an explanation for each table and column.

Note: When entering source data into Excel workbooks, you have to make sure that each worksheet does not have empty rows or columns. After you empty cell data, excel might still treat that row/column as "used" and load it into PowerPivot. This is most common error that was reported by Portfolio Slicer user. To see what columns/rows are used in your worksheet you need to press  "CTRL+END" - your cursor should jump to cell in the last row and last column. Delete empty rows and columns and press "CTRL+END" again - cursor should go to last used cell.

Table "Portfolio"

Note: You must populate this table with values specific to your environment.

This table contains a list of portfolios for your investments. It is up to the user as to what defines a portfolio. Often, this is an account in your bank or with your broker.

 This table has the following columns:

Table “Trans”

Note: You must populate this table with values specific to your environment.

The “Trans” table is a primary table for Portfolio Slicer that contains all of the transactions for your selected tracking period. Ideally, this table should have every record that is in your portfolio's bank statement. But you can start by entering just major transactions and check reports generated from that data. Later add dividends and distributions and see a complete picture of your investments. It is understood that entering all of the transactions takes time, but at this point there is no automated way of generating dividend payment information.

 This table has the following columns:

Table “TransType”

Note: You might need to change/add values to this table.

This table already comes with set of predefined transaction types that most likely do not need to change. You can modify the names of existing transaction types, but you have to make sure that every transaction type used in the Trans table, has a matching record here. The Transaction Type records in this table has a set of [*Sign] and [*Flag] attributes that specifies how each transaction type affects the portfolio's cash value and/or symbol quantity and book value.

The "TransType" table is a cricital table that defines what happens for each transaction type:

• Transaction might change the quantity on hand for specific symbol (Columns IgnoreQtyFlag, QtySign)
• Transaction might increase or decrease the cash amount in portfolio (Column CashTransSign).
• Transaction might change the book value for specific symbol (column BookValueSign)
• Transaction might be defined as beeing a "dividend" payment (column DividendFlag)
• Transaction might be defined as beeing a "deposit" into portfolio (column DepositFlag)

This table has the following columns:

Table “Symbol”

Note: You must populate values in this table.

This table lists all of the symbols and their attributes used in your investment portfolios. Most of the attributes are used to group symbols one way or another (by Allocation, by style, type, etc). All of these attribute values are not critical and can be defined any way you want.

This table should have one special Symbol "* Cash". This symbol is used to track the cash amount in your portfolio. 

This table has the following columns:

Table “luSymbolAlias”

Note: You might need to populate values in this table.

The data from this table is not loaded into the PowerPivot database. It is just used to lookup the Symbol in the Trans table based on a symbol alias or name. This is done so that in many cases when you enter data into Trans table, you copy it from bank statements that lists names and not symbols. This extra lookup helps speed up the transaction-entering process by resolving names to proper symbols.

This table has following columns:

Table “SymbolSector”

Note: You will need to populate this table if you want to report your investment holding detail splits by sector.

If the investment for this Symbol is assigned to one sector, then you just need to enter one record per symbol with Percent=100%. However, if one symbol's investments are split between different sectors, then you should enter the percentage for each sector. It is very important that the sum of the Percent field for each symbol is exactly 100%.

This table has the following columns:

Table “luSectorSensitivity”

Note: You might want to change data in this table if the existing sensitivity for sector relationship does not work for you or if you want to add more sectors.

Data from this table is not loaded into the PowerPivot workbook, but it is used by the table “SymbolSector” to calculate sensitivity for a specific sector.

This table has following columns:

Table “SymbolCountry”

Note: If you want to be able to see your investment allocation by country, then you need to maintain data in this table. Otherwise you can ignore this table and delete one pie chart report from existing dashboards

The data from this table is used to split symbol allocation by location. The majority of symbols have investments located in one country. For these symbols you would have just one record in this table specifying it's country/region and 100% in the Percent field. For symbols that have investments in multiple countries (for example: VEA), you would have to specify the investment percentage in each country. It is very important that the sum of the Percent fields for each symbol is 100%

This table has following columns:

Table “Currency”

Note: You might need to change this table, if you trade in currencies other than USD or CAD.

The list of currencies used in your investment portfolios. The values in this table should match the Currency values in the Symbol table.

This table has following columns:

Table “ReportCurrency”

Note: You might need to change this table.

This table list reporting currencies. This table should have at least one value - "*Original*" that means that your report numbers will be calculated in ORIGINAL transaction currency. This table can also have other currencies that you want to use in your reporting. Portfolio Slicer sample workbook comes with values:

• CAD = Canadian $, daily exchange rate
• USD = US $, daily exchange rate
• CAD-M = Canadian $, monthly exchange rate
• USD-M = US $, monthly exchange rate

Table “ReportHold”

Note: You do not need to change this table.

This is an empty table that is used to group calculated measures related to the current investment's Holdings report.

Table “ReportSales”

Note: You do not need to change this table.

This is an empty table that is used just to group calculated measures related to Sales reporting.

Table “ReportSymbol”

Note: You do not need to change this table.

This is an empty table that is used just to group calculated measures related to Symbol reporting.

Table “XIRR”

Note: You do not need to change/add any data to this table.

This table is used for the annualized Rate of Return calculation. Only users with a very advanced understanding of the PowerPivot DAX language could consider changing records in this table (for example to increase Rate of Return calculation precision). This table is also used to group calculated measures related to Rate of Return calculations.

You can also watch the video below on how to enter source data into the Portfolio Slicer workbook