Import Microsoft Excel - Transactions Dialog Box

Although the principal purpose of this interface is to import transactions, it can also be used to import client, security, and price data. Note that client and security data imports only apply to new clients or securities. Existing clients or securities cannot be updated with new information using the Excel interface.

Note: If this is not your first import, click the Reset button to insure that previous file paths and mappings are cleared.

1. Use the dropdown menus to select the Security ID Type and the Date Format corresponding with the data formats contained in the spreadsheets.

2. Select the type of data to be imported. Place a checkmark in the box next to the Client, Transaction, Security or Price Information types located on the left-hand side of the screen and then click the title to open an associated data screen. Additional information on each page is listed below.

3. In the File Name box, click the magnifying glass icon to browse to the Microsoft Excel file you want to import. Select the file and click Open. The path is displayed in the File Name box.

4. If the spreadsheet contains data in the first row, the system automatically selects the File Contains Header Line option. The column names are then shown as selections when mapping the file for import. Note: If the file does not contain column headings, the first row should be blank (contains no data).

5. In the Column Order area, map each data type to the corresponding column in the spreadsheet.

6. Click the Reset button to clear any selections and return each area to the default setting.

7. Click the Import button to start extracting the data into the import blotters where you can review prior to posting to the client accounts.

8. Click Cancel to exit the screen without making changes.

Note: Data from Client Information and Security Information imports will only apply to new clients or securities, respectively. Existing clients or securities cannot be updated with new information using the Excel import interface

Client Information Page

To import client data contained in an Excel spreadsheet into Morningstar Office, you must be sure that the spreadsheet is formatted correctly. The following data points are recognized for importing client information:

• Client Name
• Address 1
• Address 2
• Address 3
• Cell Phone
• City
• Client Type (Individual/Institutional)
• Country/Region
• Date of Birth
• E-mail
• Fax
• Gender (F/M)
• Home Phone
• Member Name
• Middle Name
• Relationship (Required if member chosen)
• SSN/TIN
• State/Province
• Work Phone
• Work Phone Extension
• Zip Code

To import transactional data contained in an Excel spreadsheet, you must be sure that the spreadsheet is formatted correctly. Morningstar Office reads the following data points within a Microsoft Excel transactions spreadsheet:

• Account Number
• Account Name
• Client Name
• Commission
• Security ID
• Net Amount
• Gross Amount
• Price per Share
• Security Name
• Security Type
• Shares
• Ticker
• Transaction Date
• Transaction Type
• Original Date
• Other Fee
• Cost Basis per Share
• Settlement Date

While most data points are optional, the following are required for valid transactions:

• Transaction Date
• Transaction Type
• At least one of the following three security identifiers:
• • Security ID
  • Ticker
  • Security Name
• Depending on the transaction type, usually two out of the following three:
• • Share Quantity
  • Price
  • Gross Amount or Net Amount
• Select the import Custodian from the drop-down list. This will allow your accounts to come in as existing if they’ve been imported from the Custodian in the past.
• Select Yes or No for the Add cash flows option. The spreadsheet should not include offsetting cash transactions, and most advisors choose to have cash offsets generated automatically for internal cash flows. Select Yes, and then use the magnifying glass icon to browse for the Default Cash security where the offsetting transaction will be created. If No is selected, the transactions will be offset with an external cash flow (i.e. contributions to or from an IRA account).

Note: If the desired cash security has not yet been created, you may wish to close the import window, create the cash security within the Definition Master, and then start the import over.

• If you have used codes to signify transaction types that do not exactly match Morningstar transaction type codes on the list above, you will need to map them to Morningstar transaction types.

Note: This mapping is only necessary if the spreadsheet data differs from the Morningstar standard.

• Use the drop-down list under the Transaction Type column to select the Morningstar name. Then, in the corresponding row under the Transaction Code(s) in Excel column, type the name as it is listed in the spreadsheet.

Security Information

• To import security information contained in an Excel spreadsheet into Morningstar Office, you must be sure that the spreadsheet is formatted correctly. The following data points are recognized for importing:

• • Security Name

  • Ticker

  • Security ID

  • Security Type

  • Maturity Date

  • Interest Rate

  • Base Currency

  • Payment Frequency

  • First Coupon

  • SP Rating

  • Moody Rating

  • Day Type

  • Issue Date

  • Issue Price

• Additional mapping is available for Security Type, Payment Frequency, and Day Type. Click one of the choices from header bar to load the grid with selections specific to that data type.

• Use the drop-down list under the Security Type column to select the Morningstar name. Then, in the corresponding row under the Security Code(s) in Excel column, type the name as it is listed in the spreadsheet.

• Use the drop-down list under the Payment Frequency column to select the Morningstar name. Then, in the corresponding row under the Payment Frequency Code(s) in Excel column, type the name as it is listed in the spreadsheet.

• Use the drop-down list under the Day Type column to select the Morningstar name. Then, in the corresponding row under the Day Type Code(s) in Excel column, type the name as it is listed in the spreadsheet.

Note: After importing, you can access user-defined securities in the Definition Master.

Price Information

• To import price information contained in an Excel spreadsheet into Morningstar Office, you must be sure that the spreadsheet is formatted correctly. The following data points are recognized for importing:

• • Security Name

  • Ticker

  • Security ID

  • As of Date

  • Price

  • Price Factor

See Also

Importing from Microsoft Excel