Planit:Pershing Integration
From Planipedia
|
Contents |
Planit - Importing the Files
The Pershing import is performed by first uploading a pair of CSV (comma separated values) files and then having the system process and import them. One of the CSV files is the “Accounts” file and the other is the “EXHOLD” file. The upload / import function can be launched using the “Upload File - Pershing” link from the side menu of the Planit home page. See the screen shot below.
Clicking the upload link will take you to the screen shown below where you will be able to search for and select two files to be uploaded and processed.
You must browse and select each file individually. The system will do its best to make sure you select both the “Accounts” file and the “EXHOLD” file. The system is expecting the account file to have the word “account” somewhere in the file name. It is also expecting the exhold file to have “exhold” somewhere in its file name. If either of the file upload fields are left blank or if the file names in them don't correspond to an account file and an exhold file the page will refresh with an error message when you click the “upload” button.
The order in which you select the files is not important.
Once you are sure you have the correct files selected, click the “upload” button to begin the import process. The system will attempt to upload the files to a temporary holding area on our server. If this upload is successful the page will refresh and display a message indicating you are ready to import the data.
Click the “Import” button to begin actually importing the data into PlanPlus PlanIt. The account and exhold files can contain one client's data or many. The import process will import as many different clients and their accounts as it finds in the uploaded files. Once the import is complete you will be taken back to the Planit home page and be shown a set of statistics on how many clients, accounts & assets were created or updated.
Administration Setup
There is also some user administration that needs to be done before a user can begin uploading and importing Pershing files. The user must be assigned their back office rep id or planner id. This is a code that is in both the account file and exhold file. The system will ignore any record in the account or exhold file if the rep id code on the import record is not one that has been assigned to the user performing the import.
A user with access to user administration will have to edit the user in order to assign the back office id to the user. In the user administration user edit screen there is a section near the bottom for assigning back office ids to a user.
Select “Pershing” from the back office drop down list and enter the Pershing back office id in the entry field and click the “Add” button. A back office ID can only exist on a single user per back office source. In the example above the Pershing back office id of SFP is assigned to this user.
Without this mapping to the Pershing back office id, any uploaded data will be ignored.
Important Notes on Clients Created
There are some things to note about the clients that are created by the Pershing import. The Pershing data doesn't have the client's country or province in it. We need these two pieces of information in order to have a valid client in our system. These two pieces of information are therefore taken from the advisor performing the import. Each client created will be given the same country code and province code that the importing advisor has setup.
It is important that the advisor performing the import be setup correctly in user administration with a valid country and province code.
The client's language is also not in the Pershing data so that too is taken from the adviser's preferred language.
There is no gender field in the Pershing data so we try to infer the gender from the title field. If the title is “mr” or “mr.” or “sir” then the gender is set to male, otherwise the gender is set to female.
In the case where there is no first name the import will look at the last name and search for a “ “ (blank space) somewhere in the middle. If it finds the space, meaning the last name is made up of at least two distinct words then the import will take the first distinct word and make that the first name and assign the rest of the name field as the last name.
The Pershing data includes a birth date field but it might not be populated in all cases. Our system requires a birth date no matter what. In the cases where there is no birth date in the Pershing data the import will manufacture a substitute date as a place holder in order to allow the import to complete.
The system will simply generate a fake birth date which will be the date the import was being run minus 4 years. This allows the import to continue but its very obvious the date needs to be adjusted when a client with this kind of date is opened.
Accounts
The accounts that are provided from the Pershing data may or may not contain cash balance data. This could create both an asset or a liability. The import uses the lookup parameters to find the product symbol to use. This must be an already created product in the product master that the cash balance can link to.
This product must be valued at $1 and in a specific currency.
For example, if the pershing import is dealing with a USD account, it will use the following lookup:
pershing.product.master.usd.product.code
To find the product code of a $1 USD product.
It will also use the lookup:
pershing.product.master.usd.product.description
to set the default description of the asset.
Pershing Imports Creates Products
The Pershing integration will create products in the product master if it cant find a matching product code for the correct country and currency.
There are several items that are setup in configuration files in PlanPlus PlanIt that are used when creating products as part of the Pershing import. There are entries for a special company to be create, its name and type.
There are also entries for the product codes, descriptions and asset type of assets that are to be created as cash balance assets.
The user will not need to worry about these as they will have been created and configured as part of setting up the Pershing Integration.
While the Pershing import will create products in our product master there are a few pieces of information we need that aren't present in the Pershing data. We use some special values to set these when creating a new product.
There is no field in the Pershing data to tell us what kind of product it is we are dealing with. Whether its a mutual fund, a stock, bond, etc. but we need to know this when creating a new product. To get around this we assign a special product code to the Pershing generated product.
The special product type of 55 will allow the system to generate a product and be able to continue with the import process.
There is also nothing in the Pershing data to tell us what asset class the product should be assigned to. We use a field called the fund type on our end to specify the asset allocation of the product. For the Pershing generated products we are using a special fund type code of “unclassified”.
Once the import is complete, a user with access to the product master can go back in, locate the Pershing generated products, and assign them a proper product type and fund type.
