Show 

Importing Historical Returns

Click here to view Importing Historical Returns into Morningstar Office manual.

Overview

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.

Preparing Your Microsoft Excel Spreadsheet

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).

Importing a Historical Returns Spreadsheet

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:

  1. Check the box to the left of Morningstar Template - Historical Account Return Series to select it.

  2. Click the Import button at the top of the window. The Import dialog box opens.

  3. Click the magnifying glass icon next to the File Name field. The Open dialog box opens.

  4. Navigate to where you saved the spreadsheet, select the spreadsheet file, then click Open to return to the Import dialog box.

  5. From the Date Format drop-down field, select the Date Format you used in the spreadsheet.

  6. Enter the Start Date and End Date represented in the spreadsheet.

  7. From the Import Interface ID drop-down field, select your custodian.

  8. From the Return Frequency drop-down field, select Monthly or Daily.

  9. 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.

  10. Click Import. The Import blotter window opens.

  11. 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.

  12. Once ensuring the data is correct, click Post.

Using Imported Historical Return Data

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:

  1. From the Portfolio Management tab, select the Accounts page.

  2. From the Accounts page, double-click the Account for which you imported historical data. The Account window opens.

  3. From the Account window, click on the Imported Return Series page.

  4. From the View drop-down menu, select Monthly or Daily return. Your Imported Historical Return data is displayed in the grid area.

How do I edit historical data after it is imported?

To edit historical data after it’s imported, do the following:

  1. From the Portfolio Management tab, select the Accounts page.

  2. From the Accounts page, double-click the Account for which you imported historical data. The Account window opens.

  3. From the Account window, click on the Imported Return Series page.

  4. From the toolbar above the grid on the Imported Return Series page, click the Edit Performance button. The Imported Return Series dialog box opens.

  5. Locate the item(s) you want to edit in the grid area.

  6. Click inside the field(s) you want to edit, and type your changes directly into the field.

  7. When you’re finished making changes, click Save + Close.

What do I need to update in an account’s profile before I run performance reports?

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:

  1. From the Portfolio Management tab, select the Accounts page.

  2. From the Accounts page, double-click the Account for which you imported historical data. The Account window opens.

  3. From the Account window, click the Profile/Setting folder to expand it, then select the Account Profile page.

  4. 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.

  5. Make sure the Use First Transaction Date box is unchecked.

  6. From the Use Imported Returns drop-down field, select the type of returns you imported.

  7. Click Save.

Using Historical Returns on Performance Reports

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:

  • Beginning Market Value

  • Ending Market Value

  • Net Investment

Asset Allocation table:

  • Ending Market Value

  • Ending Asset %

Account Value Change table:

  • Beginning Market Value

  • Net Investment

  • Management Fees

  • Period Investment Gain/Loss

  • Ending Market Value

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:

  • Beginning Market Value

  • Ending Market Value

  • Period Investment Gain/Loss

  • Ending Market Value

  • Money Weighted Return %, and

  • Average Annualized Return %

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:

  • Trailing Returns table

  • Best/Worst Time Periods table, and

  • Investment Activity Graph

Which Report Studio components incorporate my imported historical returns?

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:

  • Ending Market Value, and

  • Percent Asset

Portfolio Period Performance

Imported returns are used to calculate the following data points:

  • Beginning Market Value

  • Net Investment

  • Management Fee

  • Period Investment gain/loss, and

  • Ending Market Value

Comparative Performance

Imported returns are used to calculate the following data points:

  • Beginning Market Value

  • Management Fee

  • Total Gain after Management Fees

  • Total Gain before Management Fees, and

  • Ending Market Value