Prerequisites

• A Windows 11 PC (required for Power BI Desktop).

• Power BI Desktop.

• Microsoft Excel (desktop or web).

• Comfortable with Microsoft Excel and IT basics.

• Optional: Microsoft OneDrive account to host the Excel workbook.

• Optional: Power BI account to deploy the report.

Important

The report retrieves FX rates from the frankfurter.dev API. The requests includes the minimum date from all commitments, transactions and valuations and the list investment and reporting currencies. 

If you are not comfortable sharing this data then an alternative source for FX rates will be required.

Step-by-step

1. Download the report source code and sample Excel workbook from GitHub.

2. Unzip the file. You should have an Excel workbook and folder containing the report source code.

3. Open the Excel workbook and edit the following tables

   • WS.Settings

     • WebURL: change the URL to your company website or similar.

     • Company Name: change to your company name.

   • WS.Currencies

     • Add currency entries to the table as required.

     • Set the "Reporting Currency" column as appropriate. It is suggested that you have no more than 2-3 currencies enabled as reporting currencies to avoid excessive refresh times.

     • Set the Currency format string and Transaction format string column as appropriate. The samples have zero decimal places for all investment metrics and two decimal places for transaction metrics.

   • WS.AssetClasses

     • Add/Remove entries as appropriate.

   • WS.Strategies

     • Add/Remove entries as appropriate.

   • WS.Regions

     • Add/Remove entries as appropriate.

   • WS.Managers

     • Clear the table and add an entry for each manager in your portfolio.

   • WS.Investments

     • Clear the table and add any entry for each investment (fund, co-investment or direct) in your portfolio. Ensure all columns have a value.

   • WS.Portfolios

     • Clear the table and add an entry for each portfolio.

   • WS.Commitments

     • Clear the table and add an entry for each commitment. Ensure all columns have a value. The Amount column should be in the investment currency as set on the WS.Investments worksheet.

   • WS.Transactions

     • Clear the table and add all the investment contributions and distributions. The following columns are required for all transactions types: Date, Portfolio, Investment, Transaction Type and Amount. All amounts should be in the investment currency as set on the WS.Investments worksheet.

     • If the contribution is a secondary purchase then select Contribution (secondary purchase) as the transaction type and enter the purchase amount in the Amount column and the amount previous contributed towards the commitment in the Prior Contribution Amount column.

     • If the distribution is recallable then set the recallable column to TRUE. If a single distribution is part recallable then create two transactions. One for the recallable part and one for the non-recallable part.

     • If the distribution is final—either due to fund liquidation or sale on the secondary market—set the Close Transaction to TRUE.

   • WS.Valuations

     • Clear the table and add all the investment valuations. All columns are required. The Amount column should be in the investment currency as set on the WS.Investments worksheet.

     • Daily valuations will be automatically created between each manual valuation by using the contributions/distributions to adjust the valuation before conversion to the reporting currencies.

     • If more than one valuation is entered on a single date for an investment within a portfolio then the average valuation will be used.

4. Save the Excel file to OneDrive or SharePoint. Take note of the URL of the file. See steps 1 & 2 in the link below for more details.

   • https://learn.microsoft.com/en-us/power-bi/connect-data/desktop-use-onedrive-business-links

   • Other 

5. Open the Portfolio Markets Portfolio Report folder and Private Markets Portfolio Report.pbip file with Power BI Desktop.

   • By default there will be no data. If you click 'Refresh now' the report will refresh with the sample data available on GitHub.

6. Click 'Transform data' on the Home tab of the ribbon bar.

7. Find the ExcelFileURL in the Parameters folder within the Queries panel. Enter the URL for the Excel file you saved to OneDrive. You will need to provide authenticated using your organisational account.

8. The permissions for all data sources must be the same. Click 'Data source settings' in the home table and click 'Edit Permissions...' and set as appropriate. As mentioned above, if you are not comfortable sharing some limited information with frankfurter.dev API then an alternative source will be required.

9. Click 'Close & Apply' to apply the changes.

10. Click 'Refresh' to pull in and process your data and FX rates.

11. Click 'Publish' to upload the report to the Power BI service. This requires a Power BI account. Talk to your organisations IT team for support.

12. To enable automatic refresh of the data, go to the Power BI service and setup a data refresh schedule.