Pre-Upload (Spreadsheet Processing)
Before proceeding with the data uploads, these tips will help you to process the data correctly at each step:
- Place an apostrophe ( ' ) before leading zeroes so that they do not get removed during import (common with Tax IDs)
- External IDs are exact copies of the primary column's value. An Investment Vehicle's "External ID - Entity" is just an exact copy and paste of the Investment Vehicle into the external ID column so that you have an easy way of mapping to this record in the future without having to export the FundPanel IDs of the object's records.
- Record Types and Record IDs are unique to each org and will require you to obtain them from the FundPanel org for each org's data imports. This is particularly important for the Capital Commitments upload as the Fund ID is a required data field.
- Prior to making changes to a client's data spreadsheets, make sure they get uploaded to the 'Client Implementations' folder on Box. Create a new folder for the Fund (with the same name as the Fund), and within that folder create a 'Provided by Client folder'. Place all original spreadsheets received from the client within this folder. Additionally, create a 'Fundpanel Workpapers' folder within this Fund folder and upload and place all CSVs you prepare within this folder.
- Prior to logging into the client's org through the Data Loader, you will need to add your IP Address in the 'Network Access' section of the Salesforce org. This is so you are not asked for a security token upon login.
De-Dupe: Investment Vehicles
- Click Data tab in your Excel sheet.
- Click Advanced icon.
- Select Filter the list, in-place.
- Make sure the List range is on the "Investment Vehicle Name" column.
- Select Unique records only.
- Click OK.
Your excel sheet should now present you with only unique Investment Vehicle records.
- Create another column and name it 'Status'.
- Add a 'X' in all of the cells in the Status column.
- Click on the Status column and click Filter.
- Select (Blanks) as the filter.
- Click Delete Row to delete all of the Investment Vehicles in the 'Blank' filter. Please note: these Investment Vehicles are duplicate Investment Vehicles.
- Remove the 'Status' column.
- Click the Filter icon.
- Click on the Investment Vehicle Name column.
- Click Remove Duplicates.
- Make sure Excel says 'No duplicates found'.
You have successfully de-duped Investment Vehicle records.
De-Dupe: Contacts
In your Excel sheet.
- Click Data tab.
- Click Sort icon.
- Select My List has headers.
- Select Email under Column.
- Select Values under Sort On.
- Select A to Z under Order.
- Click OK.
Go through your Excel and delete any duplicate emails that has duplicate Investment Vehicle associated with it. (Ex: Delete one of the row that contains 'swheelwright@hbs.edu' email address associated with 'Weber River Assets, LLC' because there is already a duplicate email address associated to another duplicate Investment Vehicle.
- Select Delete Row on the duplicate records.
- Click Data tab in your Excel sheet.
- Click Advanced icon.
- Select Filter the list, in-place.
- Make sure the List range is on the "Email" column.
- Select Unique records only.
- Click OK.
You should now only be presented with unique Contact records. Save this Excel sheet in a separate file in CSV. format and name it 'Contact Unique'. You now have a CSV file with unique Contact records.
Navigate back to the original Contact excel sheet.
- Make sure you are still in the 'Unique' filter.
- Delete all the rows in this 'Unique' filter.
- Click Data tab.
- Click Clear icon.
You should now only be presented with duplicate Contact records associated to different Investment Vehicles. Save this Excel sheet in a separate file in CSV. format and name it 'Contact Duplicate'. You now have a CSV file with duplicate Contact records that are associated with different Investment Vehicles.
Bank Information
- The Bank Account # is going to be a twelve digit number.
- The Bank Routing Number (ABA Number in the database) will be a nine digit code.
- SWIFT codes are not required and only pertain to clients conducting business internationally.
- If there are seemingly duplicate Investment Vehicles, use the Tax ID to differentiate between different Investment Vehicles. If the Tax ID is not provided and the Investment Vehicles share the same name but they have different Investor Accounts, the data was likely prepared in error and the duplicates can be deleted.
- The 'Last Name' field is what is used to map names as it is required within FundPanel. Only this field is used to save time and eliminate the need to split columns into multiple name fields - further, clients do not always provide clarity around the distinction between first, middle & last names.
- Clients will occasionally include multiple email addresses within a single cell. In these instances, assume that the additional rows need to be created for these contacts.
GP Commitments
A GP Commitment will need to be added for each Fund. GP Commitments, when provided in the template spreadsheets, will include the name of the client in the Investment Vehicle. To create the GP Commitment, refer to these instructions: https://fundpanel.freshdesk.com/support/solutions/articles/35000054694-creating-a-gp-commitment
If a GP Commitment is not provided by the client, reach out to them to acquire the information necessary to adding it to the database.
Post-Upload
Once all of the steps have been completed and your upload has been processed, use the Contact Roles report to check your work and verify that all Investment Vehicles have a Contact. If they do not have a Contact, the client will not receive and Fundpanel communications via our application - in these cases go back and review the records for any errors that may have occurred in the upload process.
This report can be found in the package installed by FundPanel, called 'Aduro Reports'.