Click here to view Importing Historical Returns into Morningstar Office manual.
Historical returns are the values for securities your clients owned prior to the inception date when your firm’s data was initialized or converted into Morningstar Office. Using historical returns allows you to show return data in reports that date back farther than the transaction history you have imported into Morningstar Office. However, using historical imported return data as the sole source for client reporting is not recommended. It is strongly advised that imported historical returns are followed by all subsequent transactional data, so the full reporting capabilities of Morningstar Office are available.
You can import either daily or monthly returns. When calculating return data, you must use Time-Weighted Return methodology. You cannot use Internal Rate of Return when importing historical returns. Note: If you import monthly returns, use month-end data.
Before importing historical returns into Morningstar Office, you must prepare an Excel spreadsheet containing the data points described in the following table:
This data point... |
is... |
Account Name |
the name of the account for which you are importing the historical return data |
Account Number |
the account number of the account for which you are importing the historical return data |
Date |
the return date for each day or month; when using monthly returns, use the month-end date |
Gross Performance |
the time-weighted return percentage for the portfolio, gross of fees |
Net Performance |
the time-weighted return percentage for the portfolio, net of fees (this data point is optional) |
Management Fees |
the value of all management fees charges to the account for each day or month |
Beginning Market Value |
for monthly returns, it is the beginning market value on the first day of the month; for daily returns, it is the beginning market value at the start of the day |
Ending Market Value |
the ending market value for each day or month |
The following two data points are optional; however they will not be used on reports or for performance calculations. This is because weighted cash flows are not considered when using time-weighted return methodology. They are included for recording keeping purposes only:
This data point... |
is... |
Weighted Gross Flow |
the time-weighted cash flow for the period, including management fees and withdrawals |
Weighted Net Flow |
the time-weighted cash flow for the period, not including management fees and withdrawals |
Note: Cash flows and Beginning and Ending Market Values can be excluded from your historical import. However, if they are excluded, the performance reports run at the client aggregate level will not incorporate imported returns.
Once complete, your spreadsheet should be similar to the following image:
Note: This example shows a completed spreadsheet containing monthly returns. When importing daily returns, note that the Period Date column should be each day in the period you are importing (e.g., April 1, 2014, April 2, 2014, April 3, 2014).
Morningstar Office supports several Excel import interface types. If the Morningstar Template - Historical Account Return Series interface is not present from the Import window, you can add it using the Edit Import Interfaces procedures.
To import your spreadsheet of historical returns into Morningstar Office, do the following from the Import window:
Check the box to the left of Morningstar Template - Historical Account Return Series to select it.
Click the Import button at the top of the window. The Import dialog box opens.
Click the magnifying glass icon next to the File Name field. The Open dialog box opens.
Navigate to where you saved the spreadsheet, select the spreadsheet file, then click Open to return to the Import dialog box.
From the Date Format drop-down field, select the Date Format you used in the spreadsheet.
Enter the Start Date and End Date represented in the spreadsheet.
From the Import Interface ID drop-down field, select your custodian.
From the Return Frequency drop-down field, select Monthly or Daily.
Under the Column No. column, click inside each field, and map each column in the Excel spreadsheet to the corresponding Column Code in its row.
Click Import. The Import blotter window opens.
From the Import Blotter, review the data in each blotter to make sure that it is correct. Note: Review the New Accounts blotter. If an existing account appears here, you need to map it to the existing account.
Once ensuring the data is correct, click Post.
After you post your historical data into Morningstar Office, this data will be stored within the Account window for Client/Account you imported this data for. To view imported historical account data for an account, do the following:
From the Portfolio Management tab, select the Accounts page.
From the Accounts page, double-click the Account for which you imported historical data. The Account window opens.
From the Account window, click on the Imported Return Series page.
From the View drop-down menu, select Monthly or Daily return. Your Imported Historical Return data is displayed in the grid area.
To edit historical data after it’s imported, do the following:
From the Portfolio Management tab, select the Accounts page.
From the Accounts page, double-click the Account for which you imported historical data. The Account window opens.
From the Account window, click on the Imported Return Series page.
From the toolbar above the grid on the Imported Return Series page, click the Edit Performance button. The Imported Return Series dialog box opens.
Locate the item(s) you want to edit in the grid area.
Click inside the field(s) you want to edit, and type your changes directly into the field.
When you’re finished making changes, click Save + Close.
In order for historical return data to appear in performance reports, you must first update the account’s Account Profile page to accommodate the imported data. To update the Account Profile page, do the following:
From the Portfolio Management tab, select the Accounts page.
From the Accounts page, double-click the Account for which you imported historical data. The Account window opens.
From the Account window, click the Profile/Setting folder to expand it, then select the Account Profile page.
In the Performance Start Date field, enter the starting date of your imported return series. Note: When using imported monthly returns, enter the first day of the first month you imported.
Make sure the Use First Transaction Date box is unchecked.
From the Use Imported Returns drop-down field, select the type of returns you imported.
Click Save.
The performance reports in the following table incorporate the historical returns you have imported.
Report |
Data points that display imported historical return data |
Client Return Summary |
Imported returns are used to calculate data points in the following data tables: Account Details table:
Asset Allocation table:
Account Value Change table:
|
Performance Summary |
Imported historical returns are used to calculate the Time Weighted Return Investment Graph. Historical returns are also used to calculate the following data points within the Portfolio Summary:
Imported returns also affect the Total Portfolio Trailing Returns table. |
Performance History by Account |
Imported returns are used to calculate all data points within this report. |
Benchmark Comparison |
Imported returns are used to calculate the Total Portfolio data point. |
Account Snapshot |
Imported returns are used to calculate the Investment Activity Graph. |
Account Overview |
Imported Returns are used to calculate the following report elements under the Performance section:
|
The Report Studio components in the following table will incorporate the historical returns you import:
Component |
Data points that display imported historical return data |
Portfolio Evaluation |
Imported returns are used to calculate the following data points:
|
Portfolio Period Performance |
Imported returns are used to calculate the following data points:
|
Comparative Performance |
Imported returns are used to calculate the following data points:
|