Historical Capital Call uploads can leverage FundPanel’s existing template generation tools for the data preparation process. This article will walk through exporting templates from each Fund, which in turn can be filled out & imported back into the database to add any Capital Calls that did not originate from FundPanel.
Please note: this article outlines two different methods of importing historical capital call information. If your upload data contains Capital Calls that share the same Capital Call Amounts & Dates Paid, you can disregard this longer article and refer to the following one instead:
Uploading Historical Capital Calls with Identical Capital Call Amounts & Dates Paid
Step 1. Provide the client with the Historical Capital Call Upload Template
Each fund will require its own unique upload template containing its own commitments and investment vehicles, so it is recommended to do this on a per-fund basis. A template can be generated through following these steps:
- Navigate to a particular Fund record.
- Click Capital Call button.
- Click Next.
- Enter in a random value for Capital Call Amount and Due Date.
- Click 'Export Template'.
Now that you have your unique ‘Historical Capital Call Template’ for this particular Fund, you will need to make the follow edits in a spreadsheet program:
- Add a Paid Date and Amount Paid columns.
- Create a copy of this tab (we recommend 5 tabs).
- Each tabs will represent a particular Capital Call Due Date for a particular Fund.
Step 2. Upload the Historical Capital Call Template per Due Date per Fund
Once the historical data is complete, we will start by uploading the oldest Capital Call (Due Date) first in Salesforce.
Before Salesforce can take this data, you will need to export each tab as its own individual CSV. At this time, it is also recommended to save another file per tab but this time without the Paid Date and Amount Paid columns. Ultimately, you will have two files: one will have the Paid Date and Amount Paid columns and the other file without). This is due to the fact that Salesforce cannot process the the file with the Paid Date and Amount Paid columns.
Two CSV files needed Per Tab:
1. Without the Paid Date and Amount Paid columns (will be used for the data upload).
2. With the Paid Date and Amount Paid columns (will be used in step 3 of the data update).
- Navigate to the particular Fund record
- Click Capital Call button on the Fund record.
- Click Next.
- Enter 0 for Capital Call Amount.
- Enter in the appropriate Due Date.
- Click Next.
- Click Choose Files.
- Select the appropriate Historical Capital Call file (the CSV file without the Paid Date and Amount Paid columns) provided by the client.
- Click Process CSV.
- Click Confirm & Save.
Step 3. Updating Capital Call Paid Date & Amount Paid per Due Date Per Fund
At this point in the article. There are two paths forward to importing data depending on your Capital Call information.
Follow along with Option 1 if the child Capital Calls all have the same Amount Paid and Date Paid. Use Option 2 if the child Capital Calls have different Amount Paid and Date Paid.
Option 1. Mass Mark as Paid & Mass Update
- Navigate to the newly created Parent Capital Call record.
- Select the checkbox next to Action.
- Click Mark Selected as Paid.
- Select the checkbox next to Action.
- Click Mass Update.
- Select Date Paid (aduro_DatePaid__c).
- Click Next.
- Enter the appropriate Date Paid value.
- Click Next.
Option 2. Create Historical Capital Call Salesforce Report + DataLoader
- Click Reports tab.
- Click New Report.
- Select Capital Call Report Type.
- Click Create.
Report Filters
Show: All Capital Calls.
Capital Call : Created Date = All Time.
Funds equals enter the appropriate Fund name with parenthesis (ex: "ABC Corp").
Capital Call Parent equals enter Parent Capital Call name with parenthesis.
Enter in the appropriate Capital Call Due Date.
Add the Investment Vehicle column 1.
Add Capital Call: ID column 2.
Click Run Report.
- Click Export Details.
- Click Export.
Match the Capital Call's Record ID in the Historical Cap Call Upload Template
I. From your saved Capital Call Upload Template (for that Call Date) provided by the client (with Paid Date and Amount Paid columns).
Create two new columns for A and B.
- Name Column A: 'Investment Vehicle (DNU)'.
- Name Column B: 'Capital Call: ID (DNU)'.
Copy the values from the two columns (A & B) 'Investment Vehicle' and 'Capital Call: ID' from your recently exported Salesforce Report and paste it over to the original Capital Call Upload Template provided by the client on columns A and B.
II. From your original Capital Call Upload Template provided by the client
Create a Column D and name it 'Capital Call ID'.
In column D type out the formula '=IF(ISNA(Match(C2,$A$2:$A$160,0)),"",Vlookup(C2,$A$2:$B$160,2,false))'.
Please note: the number '160' that was utilized twice in this formula represents the last row of the Investment Vehicle Name and ID columns that were pasted onto this Historical Cap Call Upload template from the Salesforce Report export (Column A & B). Ex: if column A only has 40 records then we would replace the number 160 with the number 40.
Copy the provided formula and paste it down for the rest of the Investment Vehicle rows in Column D.
III. From your original Capital Call Upload Template provided by the client
Create a Column L and name it 'Paid'.
In column L type out the formula
'=IF(NOT(ISBLANK(K2)),"True","False")'.
Copy the provided formula and paste it down for the rest of the Investment Vehicle rows in Column D.
IV. Change the Date format for column 'Paid Date' to 'mm/dd/yyyy'.
Save the file as CSV file.
DataLoader
Update Capital Call Paid Date and Amount Paid.
- Click Update.
- Select Child Capital Call.
- Choose your CSV file and click Next.
- Click Choose an Existing Map.
- Select the field mapping 'Historical Cap Call Paid Update' file provided below.
- Click Next.
- Click Finish.
Step 4. Delete Capital Call records with $0 Capital Call Amount.
When creating the historical Capital Calls, Salesforce also created Capital Calls with $0 Capital Call Amount. We will need to delete this Capital Calls with $0 Capital Call Amount.
Navigate back to your CSV file.
- Filter the 'Capital Call Amount' column to NOT show Blanks.
- Delete all the Rows in this Filter (Rows containing Capital Call Amount).
- Remove the Filter on this file.
- Your CSV file should only provide you with Capital Call records with $0 Capital Call Amount.
- Save the file as another CSV file for delete.
Navigate back to the Data Loader.
- Click Delete.
- Select Child Capital Call.
- Choose your CSV file (for delete) and click Next.
- Click Choose an Existing Map.
- Select the field mapping 'Delete Historical Cap Call $0 Amount' file provided below.
- Click Next.
- Click Finish.
