Preparing to Import Data into Morningstar Office Manual
Introduction to Importing Transactional Data Video
This topic provides an overview of what you need to do before you begin importing data into Morningstar Office, as well as an overview of the ongoing import process in Morningstar Office.
You will learn about the following topics:
What is an import blotter?
Blotters are accounting spreadsheets where you can manipulate the data you imported, before you post it to your clients’ accounts. The purpose of the blotters is to search for and correct discrepancies that occurred while importing, before posting data to your practice. The import blotters include import categories, which vary depending on the import interface sources you chose.
This section describes the following topics:
Importing is the process of bringing transactional data from your custodian into Morningstar Office, in the form of data files. The import process in Morningstar Office consists of the following steps:
Loading the import blotters with data from a custodian.
Reviewing the data for errors.
Reconciling data so information from your custodian matches that in Morningstar Office.
Posting transactions to your clients’ accounts.
What kind of data can be imported?
The following data can be imported into Morningstar Office:
client name
contact information
security prices, and
transaction records.
Note: If you are importing from Microsoft Excel, you can also bring in other data, such as Research lists, members and positional accounts.
After the import process is complete, your clients and accounts will be available within Morningstar Office. Once the data is available, you will be able to conduct portfolio reporting, portfolio accounting and billing.
What settings do I need to address before importing?
Before you begin importing transactional data into Morningstar Office, it is crucial that you create and apply the following settings:
These settings should be updated prior to initialization (or your existing database being converted into Morningstar Office). Initialization establishes an initial position for each of your clients before importing begins. The initialization process is done only once, and creates a starting position as of the date of your choosing.
The currency and exchange filters, and the price settings, also need to be set only once, as these apply to all custodians you use. The tax lot method and manual transaction settings, however, must be created for each custodian you use in your practice.
Before you import transactional data from a custodian, its important to create Tax Lot Methods settings for that data. Tax lot methods determine how your clients’ cost basis is calculated, and becomes important in the import process when using the Tax Lot Reconciliation tool (currently available for TD Ameritrade, Schwab, Pershing and Fidelity).
Note: To learn more about tax lot reconciliation, please refer to the Importing and Reconciling Tax Lot Data in Morningstar Office topic.
The Tax Lot Methods settings you create in Morningstar Office must match your custodian’s tax lot methods. If the methods do not align, your cost basis will be mismatched and your tax lots will not be reconciled.
What tax lot method values can be set in Morningstar Office?
The following table describes the tax lot method values you can choose from in Morningstar Office:
|
This tax lot method... |
Assumes |
|
Average Cost |
the value of a pool of assets or expenses is assumed to be equal to the average cost of the assets or expenses in the pool. |
|
FIFO |
the assets acquired first are sold first. |
|
Specified |
the investor will specify the shares that are sold. |
|
LIFO |
the assets acquired last are sold first. |
|
High Cost |
the highest cost shares are sold first. |
|
Low Cost |
the lowest cost shares are sold first. |
How do I create a Tax Lot Methods setting in Morningstar Office?
If you import from multiple custodians, you may need to create a Tax Lot Methods setting for each custodian, if the custodians use different tax lot methods. If the tax lot methods are identical for all security types at your custodians, then you will not need to create multiple Tax Lot Methods settings in Morningstar Office.
If your custodians do use different tax lot methods, not only do you need to create separate Tax Lot Methods settings, but before you initialize data for each custodian, you must change your User Default Settings to the appropriate Tax Lot Methods setting for the custodian. This will ensure that all new accounts added during the initialization process will apply the Tax Lot Methods setting that matches the custodian.
Click here 
for more information on how to create a Tax Lot Methods setting in Morningstar Office.
How do I change the default Tax Lot Methods setting?
Once a Tax Lot Method has been created, it is available for selection when setting the User Defaults.
From the Home tab, select Tools…Settings...Set User Default Settings from the toolbar at the top of the screen.
From the Tax Lot Methods drop-down field, select the tax lot method you want to use as the default setting for transactions that are about to be initialized or imported.
Click OK. When the confirmation message appears, click OK again.
This section describes how to create and apply exchange and currency filters in Morningstar Office. The following topics are covered:
Why do I need to create and apply exchange and currency filters?
During the import process, Morningstar Office identifies securities contained in your files based on those tracked by Morningstar’s Research team. By mapping your clients’ holdings to Morningstar’s databases, you can incorporate Morningstar’s research and analysis into your client reports.
Since many securities are traded in multiple currencies and on various exchanges throughout the world, the mapping process could occasionally yield multiple matches for the same CUSIP or ticker. In order to eliminate confusion as to which security is being imported, it is recommended that you create a filter to guide Morningstar in mapping a security to the correct match.
By creating exchange and currency filters you ensure that, in the case where Morningstar’s database has more than one version of a security (with the same name/ ticker, but on a different exchange or in a different currency), Morningstar Office will map to the security traded on the exchange (or in the currency) you have specified.
By setting these filters, it’s not that Morningstar Office will exclude imported securities traded on an exchange that is not included in a filter. Rather, the program simply uses the filter as a guide for finding the correct match when multiple matches are found. Securities falling outside of an exchange and currency filter are flagged with a warning in the import blotter.
How do I apply a currency or exchange filter?
Morningstar tracks securities globally. In many cases, the same security is traded on many exchanges throughout the world. Therefore, occasionally you will receive a “Multiple Securities Found” message in the Securities blotter. To minimize the occurrence of this message, you can configure your security matching process by manually selecting the countries where your securities may be trading through the currency and exchange filters setting.
Click here for more information on applying a currency or exchange filter.
This section describes how to assign a price setting to be used when you generate performance reports for imported securities. The following topics are covered:
Why do I need to assign a price setting?
The import-related files from your custodian(s) should include a price file (or price data), which contains updated price information for every security you import. When this price data is imported, Morningstar Office stores it within the Definition Master. When you later generate performance reports in Morningstar Office, this imported price history is used to analyze the performance of all securities not tracked by Morningstar, such as fixed income securities or options.
When it comes to calculating the performance history of Morningstar-tracked securities, however, you have the option to use either Morningstar’s prices, or the imported prices from your custodian(s). When generating performance reports, if you apply your custodian’s prices for Morningstar-tracked securities, rather than Morningstar’s prices, you will eliminate any price discrepancies that could otherwise appear between custodian-generated reports and reports you generate using Morningstar Office. This setting should be applied prior to importing securities. Keep in mind, though, that if price data is missing from your custodian files, this information may not be as accurate as Morningstar’s prices.
How do I assign a price source for imported transactions?
To assign a price source for imported transactions, do the following:
From the Home tab, select Tools…Settings...Set User Default Settings from the toolbar at the top of the screen.
If you want to use your custodian’s prices for Morningstar-tracked securities, uncheck the box marked Use Morningstar Price. To use Morningstar’s price for these securities, leave the box checked.
To save the setting, click OK.
This section describes how to set up manual transaction settings in Morningstar Office.
The following topics are covered:
Why do I need to create manual transactions settings?
Morningstar recommends that you create a setting for manual transactions to mimic your custodian’s transaction format. This way, if you ever have to manually create transactions for an imported account, this setting will apply the appropriate cash offset transaction to those transactions.
Part of creating a transactions setting involves choosing a cash source. The following table shows the cash holding names for many of the custodians from which you can import transactions into Morningstar Office.
Note: If you use more than one custodian with the same cash holding name, you need only one instance of that name; you do not need to duplicate it.
|
If you use this custodian… |
You need a cash holding with this name… |
|
ByAllAccounts |
Cash Balance |
|
LPL |
CASH |
|
Fidelity |
CASH |
|
First Clearing |
CASH |
|
FolioFN |
US Dollars |
|
Pershing |
CASH SWEEP |
|
Raymond James |
CASH |
|
Royal Bank of Canada |
MSCash |
|
Schwab |
CASH |
|
Scottrade |
CASH |
|
Southwest Securities |
CASH |
|
TD Ameritrade |
CASH |
How do I create a transactions setting?
If you import for multiple custodians, you need to create a manual transactions setting for each custodian. Before you initialize for each custodian, you must change the User Default Setting to the appropriate Manual Transaction Setting. This will ensure that all new accounts added during the initialization process will apply the correct Manual Transaction Setting for each custodian.
Click here for more information on creating transaction settings.
Before you can begin importing into Morningstar Office, you must select the import interfaces you plan to use in your practice.
Click here for more information on selecting your import sources.
Not every Morningstar Office user needs to initialize positions before importing clients, accounts and transactions.
This section explains the following:
Morningstar highly recommends that you begin importing with the help of a Morningstar Import Specialist. Call the Support team at 1-866-215-2503 for more details if you have never imported transactional data before.
Why do I need to initialize positions?
For advisors not undergoing a database conversion, it is usually necessary to establish an initial position for each of your clients before you can begin importing transactional data. The initialization process is done only once, and creates a starting position as of the date of your choosing. The initial positions generated in Morningstar Office will be the ending share quantities and market values as of the initialization date.
After you initialize, your data will appear on a series of import blotters. These blotters contain information related to accounts, positions, transactions, prices and securities for your clients. You must review the data on your import blotters for errors before posting to your practice.
During initialization, you should ensure that your clients do not appear to have a negative holding in their account, should they sell out of a security before you import transactions.
How do I initialize my clients’ positions?
To initialize your clients’ positions, do the following:
From the Home tab, select Import from the toolbar at the top of the screen. The Import window opens.
Check the box next to the Interface Name of your choosing.
From the Action menu above the spreadsheet area, select Initialize Positions. The Import dialog box opens.
To select the folder where your custodian files are stored, click the magnifying glass icon next to the File Name field. The Browse For Folder dialog box opens.
Once you select a folder, the Initialize Date field automatically populates with the earliest date for which a position file is available. If you wish to select a later date, you can edit the Initialize Date field.
Note: The file types included will differ, depending on the custodian for which you are initializing.
Click Initialize. The Initialization blotter opens. This blotter summarizes all information that will be posted to Morningstar Office from your files. Review the Accounts, Transactions, and Securities pages of the blotter window to ensure that the information to be posted is correct.
Note: You might encounter some transactions or securities that are in warning status, requiring extra attention. Some warnings (e.g., cost basis missing) can be safely ignored before posting data to your practice, while others (e.g., price missing) require immediate attention. If you encounter a warning symbol in the Status column next to any item, refer to the Handling Warnings in the Import Blotter topic.
From the toolbar above the spreadsheet area, click Post. This uploads all information contained within the blotter to your Morningstar Office database.
When the confirmation message appears, click OK. The client information, account numbers, and initial positions for each account have now successfully posted to Morningstar Office.
After you have initialized your positions, you are ready to begin the ongoing import process.
This section explains the following:
How often should I import data?
It is strongly recommended that you export from your custodian, and import to Morningstar Office, on a daily basis. This ensures that fixed income prices (or other prices not supplied by Morningstar) will be imported, that all transactions are captured, and that reconciliation is easier and less time consuming. If you are unable to import daily, avoid importing more than a week’s worth of data at a time, so as to maximize the ease and efficiency of reconciling data.
How do I load information into the blotter?
To load the import blotter, do the following:
From the Home tab, select Import from the toolbar at the top of the screen. The Import window opens.
Check the box next to the Interface Name of your choosing.
On the Import window toolbar, click Import. The Import dialog box opens.
Confirm that the Start Date field is correct. The Start Date should automatically display the date following that of your last imported file. For example, if you initialized as of July 10th, 2013 and are importing for the first time, the Start Date should be July 11, 2013. By tracking which files have already been imported, Morningstar ensures that you do not accidentally import the same file twice.
The End Date field automatically displays the current date. If necessary, you can change this date.
Click the Import button at the bottom of the dialog box. The Import Blotter window opens. These blotters summarize the data that must be reconciled before you post the data to Morningstar Office.
How do I review the imported data?
For each page in the Blotter window, ensure that the information is correct. If you encounter a warning symbol in the Status column next any item, refer to the Handling Warnings in the Import Blotter topic.
How do I reconcile import items that are out-of-balance?
After confirming that the information contained in the blotter is correct, run the reconciliation process by clicking the Reconcile icon in the upper right-hand corner of the screen.
In this process, the position of each individual holding within your accounts is compared to the positions contained in the position file provided by your custodian. (The positions for your accounts in Morningstar Office are calculated based on the transaction history recorded within Morningstar Office.) Any holdings for which Morningstar’s calculated position does not equal the position in your custodian file will appear within the Failed Reconciliation section of the blotter.
If you see out-of-balance items appear during reconciliation, please refer to the Reconciliation Troubleshooting topic.
How do I reconcile cost basis data in the Tax Lots blotter?
For certain custodians, cost basis reconciliation is available within the Import window. This feature allows you to reconcile lot and cost basis data from your custodian with the transactional data currently available within Morningstar Office.
Cost Basis reconciliation occurs after you successfully reconcile and post your client account data to Morningstar Office. For instructions on how to complete tax lot cost basis Reconciliation, refer to the Importing and Reconciling Tax Lot Data topic.
If you close the Import window while importing, your blotters are automatically saved. When you are ready to finish importing, you can easily return to your saved blotter. This section explains the following:
How do I return to my saved blotter?
To return to a saved blotter, do the following:
From the Home tab, select Import from the toolbar at the top of the screen. The Import window opens.
Check the box next to the Interface Name of your choosing.
From the Action menu above the spreadsheet area, select View Blotter. You can now complete your import session.
Note: Once you complete the reconciliation process and post the data to your practice, the blotter is cleared. You can then begin another import with any interface of your choosing.
How do I delete a blotter that I do not wish to post?
In some cases, you may want to delete a blotter before you post the data to Morningstar Office. To remove a blotter you do not wish to post to Morningstar Office, do the following:
From the Home tab, select Import from the toolbar at the top of the screen. The Import window opens.
Check the box next to the Interface Name of your choosing.
From the Action menu above the spreadsheet area, select Refresh Local Database. This action clears your blotter. You can now begin a new import session.
Morningstar keeps a record of your post history for each custodian and interface type from which you import. You can view information about the transactions you posted, and you can remove transactions you have posted as well, but only for one import session at a time. Also, if you choose to remove transactions you have posted, you will have to redo the importing procedure in order to repost your transactions.
This section covers the following topics:
What should I know about viewing or removing past transactions?
You can only un-post transactions in the order in which you imported them. Only the last 90 days/posts can be removed. If more history needs removed, you would need to find an alternate method of deletion.
Only transactions un-post. Securities, Prices, Clients, and Accounts will remain as part of your practice. For example, if you post time-stamped files, un-posting transactions will not fix all of the extra user-defined securities, clients, and accounts that were added.
If you edit a transaction after it’s been posted, that transaction is still part of the original group and will un-post.
When you unpost transactions, the Import Start Date on the Import dialog box will not update. You will have to keep track of the data that you un-post and adjust the start date accordingly.
If a transaction doesn’t post because of a warning, such as “Duplicate transaction,” it will still be included in the Number of Transactions Posted column in the Transaction Post History dialog box, when in reality, it did not post. To see the actual transactions that posted, do the following:
In the Transaction Post History dialog box, select the check box for a row.
Click the View Post icon. The Transactions dialog box opens.
How do I view transactions I already posted to Morningstar Office?
To view transactions you have posted to Morningstar Office, complete the following steps:
From the Home tab, select Import from the toolbar at the top of the screen. The Import window opens.
From the toolbar on the Import window, click the View Post History icon. The Transaction Post History window opens.
Check the box next to the import session you would like to view.
From the tool bar above the spreadsheet area, click View Post. The Transactions dialog box opens.
How can I remove transactions I’ve posted to Morningstar Office?
If necessary, you can remove the most recent set of transactions you posted to Morningstar Office by removing an import session. Note the following important information about this procedure:
Before you remove transactions you have posted, make a note of the date range you are removing. The next time you import (or repost), set the Start Date and End Date fields on the import dialog box to match the date range you removed; these fields will not update automatically.
Only transactions will unpost; user-defined securities, clients, accounts and prices will not be removed from your practice. Also, you must remove posts one at a time, in reverse chronological order of when they were added.
The system displays only a certain number of recent posts at a time, but as you un-post, more sessions are displayed.
It takes the same amount of time to un-post transactions as it does to post them.
To remove transactions from Morningstar Office, complete the following steps:
From the Home tab, select Import from the toolbar at the top of the screen. The Import window opens.
From the toolbar on the Import window, click the View Post History icon. The Transaction Post History window opens.
Check the box next to the most recent import session you have posted.
Click Remove Post. When the un-post is complete, you will receive an e-mail.
See Also
Handling Warnings in the Import Blotter
Importing and Reconciling Tax Lot Data in Morningstar Office
Troubleshooting Guide for Common Import and Reconciliation Issues