The following spreadsheets serve as examples for how you should format your data for import. Note that you may import holdings for multiple portfolio dates in a single spreadsheet.
The Definition Master allows for the creation of user-defined securities.
The Import Blotters are six spreadsheets that allow you to preview and edit imported data before you use it to generate clients, accounts, transactions, securities, or prices in your Practice.
Buttons in the import blotters:
� New Transaction: allows you to create a new transaction for any account from within the blotters
� Delete: allows you to delete the items that are checked
� Save: saves any changes you make to the blotters
� Save and Close: saves any changes and closes the blotter window. You can bring back the saved blotter by double-clicking the import source or by checking the source and clicking View Blotters
� Export: exports selected rows to Microsoft Excel
� Print: prints the selected rows
Functionality in import blotters
Ranking by columns: You can double-click many columns to rank the data. For instance, you can double-click the account name column in the transactions blotter so that you can see all transactions ordered by account name or number.
View transactions for a holding: By right-clicking an item in the reconcile blotter, you can view the transactions for that holding. This can help you determine the cause of the position being off balance. The transactions displayed are those that have already been posted to the accounts. If you want to view the unposted transactions for the holding, click Pending Transactions in the left navigation pane.
Positions
This blotter shows the position balances of the holdings as of the end date selected during the import. For instance, if you choose to import data from 9/1/06 to 9/30/2006, the position blotter will show you the balances as of 9/30/06. When importing Transaction Accounts, Position data is used for reconciliation purposes and reference only. It is not used to overwrite the positions that are calculated as the sum of the transactions. The position blotter is not available during the initialization step of the import process because reconciliation is unnecessary at this stage.
When importing Quick Accounts, the Position Blotter is used to create the allocation of the holdings that are posted to accounts.
Transactions
The transactions blotter contains all the transactions within the files in the date range selected. Most custodians provide a single transaction file for each date on which transactions occurred in your practice. So if you import the files from 10/01/06 � 10/31/06, you will be importing the transactions that took place on those days. In some cases, however, such as Fidelity Advisor Channel, transactions for multiple dates can appear in a single transaction file. That is, you might have a file created on 10/01/06 that includes transactions for 01/01/06 through 10/01/06. In that case, you can choose to import transactions based on their File Date or according to the Transaction Date within the file. This Date Type feature allows you the flexibility to ensure that you don�t import transactions for the same date multiple times.
In the transactions blotter you can edit the following information:
� Transaction date
� Transaction type
� Amount
� Number of shares
� Price
� Sales charge
� Commission amount
There is a Status column that notifies you if there is an error in the transaction. You can scroll to the right to the �Error Message� column to see the message for any transaction that has an exclamation in the Status field. You can also rank by double clicking on any column heading.
Prices
This blotter shows the prices of the securities for the range of dates selected. After posting, these prices are used to update the current market value of account holdings. You can edit the following information in the price blotter:
� Price
� Price factor
� Principal factor
� Price date
Securities
This blotter contains security information like name, symbol and security type. If the symbol or CUSIP in the import file is different from Morningstar�s, so that the holding is not recognized, you can map securities to Morningstar securities. To do so, click the �Morningstar Mapped Holding� magnifying glass.. The mapping process is especially important for variable annuity subaccounts since subaccounts don�t have standardized symbols. If a security is not part of the Morningstar universe, such as a bond or a cash holding, the security will be added to your Definition Master as a user-defined security. Be sure to correctly identify the security type in this blotter because the range of valid transaction types for a holding is defined according to its security type.
Reconciliation
This blotter is available when importing transactional data from custodians. When you click into this blotter, it compares the share balances in the custodian position file with the position balance calculated by Office Edition based on the transactions that occur. The custodian position file used is based on the date range selected during the import. For instance if the end date selected was 9/30/2006 and the last day of the month was 9/29/2006, it would use the 9/29/2006 position file for the reconciliation. The reconciliation blotter is unavailable when initializing positions because reconciliation is unnecessary until you have multiple dates of transactions to verify.
Holdings that are not in balance appear in red and blue. If items are in red, the number of shares shown in the custodian position file does not match the positions calculated by Office Edition. If items are in blue, the custodian does not show this holding in the position file or has a zero balance but Office Edition does have some shares in this holding. This is common for CASH holdings.
Note: You cannot edit any information in the reconciliation blotter.
Explanation of reconciliation blotter columns:
|
Column |
Description |
|
+/- |
This column tells us whether Workstation Office shows a higher or lower number of shares compare to the custodian position file. A �+� means Workstation has calculated a higher number of shares than the custodian; a ��� means Workstation calculates a lower number of shares. |
|
Difference |
This column tells us the actual difference in number of shares between the custodian position file and the calculated position balance in Workstation Office |
|
Current Position |
This column shows the current number of shares that exist in this holding based on the transactions that have already been posted to the accounts |
|
Transactions to Post |
This column shows the number of shares to be posted based on the transactions shown in the Transactions Blotter. This can be a positive or negative number of shares |
|
Total Shares |
This column is the sum of Current Position and Transactions to Post. This number is compared with the custodian position balance to determine if the shares are in or out of balance |
|
Custodian Position |
This column shows the number of shares in the custodian position file as of the end date |
|
As of Date |
This is the date as of which Office Edition compares the positions to determine whether they are in or out of balance |