Help for executing reports in the portfolio performance tracking template for Excel
Portfolio reports can be executed at any time by the following process:
- Enter the date of valuation in the Date of Valuation cell at the top of the Value sheet. This is defaulted to the current days date by the formula '=NOW()' but can be overwritten to allow for valuations at prior points in time.
- Enter the Current Valuations and Number of Units as at the valuation date into the value sheet. This means typing over the previous valuations. Alternatively, data can be linked from external sources such as other spreadsheets or third party market data provider add-ins. To facilitate the update of price data to multiple client portfolio files for financial advisers, a batch update module has been separately developed and is freely available here.
- If calculation is set to 'manual' in your Excel default options, press the F9 key to calculate and the reports will update automatically.
- Print out reports as required.
- Click on the 'Roll Over Portfolio' button. Essentially this program refreshes the file ready for the next reporting date. It executes the following tasks.
- Copies the values in the Current Valuation column to the Value at Start of Period column and the Value Date to Start Date in Value sheet.
- Clears out the transactions in the Trans sheet and puts previous period's transactions in chronological order into History sheet.
The 'Chart' sheet displays a breakdown of the portfolio by asset class category and allows the tracking of portfolio perfomance and valuation by snapshots.
Screenshot: Executing Reports