The portal allows users to import fund/investment records into their ledgers using a CSV file.
What is a CSV file?
A CSV file is a text file of "comma-separated values". It is a text file that works much like a spreadsheet and can be opened, edited, and saved using spreadsheet software like Microsoft Excel. The CSV file will have a row of column headers followed by rows of fund or investment records. Each row is a separate record. The column headers tell the portal what each of the fields in the rows below it contain. This is what a CSV file looks like in a text editor (left) and in Microsoft Excel (right):
What should the file contain?
The CSV files need to have a specific structure, depending on what type of data you are trying to import. There are two main types of CSV that the portal accepts:
- The Report of Trust and Capital Reserve Funds (MS-9)
- The Report of Common Trust Fund Investments (MS-10)
You can find the full details of what should be in the CSV file here: Import File Specification. The specification is a little technical so we encourage you to contact us if you get stuck. We realize that this is all fairly technical so please you get stuck contact us if you get stuck.
Where should I start?
The best place to start is by exporting a CSV file from the portal and it often helps to have a reference fund or investment record so that you can see what the contents will look like:
- Log in to the Portal
- If you already have some fund/investment records in your MS-9 or MS-10, skip to Step 8
- Click on the blue "MS-9 Ledger" or "MS-10 Ledger" button
- At the bottom of the ledger, click "Add Fund" (on the MS-9) or "Add Investment" (on the MS-10)
- Provide the information about one of your fund/investment records then click "Save"
- Fill in the annual financial details about the fund/investment then click "Save"
- Click on "Home" in the left menu to return to your Home Page
- Click on "MS-9 Report" or "MS-10 Report" and then select "CSV"
Your browser should download a CSV file with the records from the selected ledger. This CSV file is in the exact format that the portal's import process wants. As you enter more records into the CSV file, keep in mind that you will need to provide valid values for fund types, fund purposes, and so forth. A list of fund/investment types, purposes, and methods is available here.
Where do I upload the file?
Once you have a CSV file put together, you can upload it by clicking "Import File" on the MS-9 panel (1) or MS-10 panel (2) on your home page:
How does the import process work?
When you upload a CSV file the portal goes through a few steps.
First, it checks the file to see if it is missing any required information or if there are problems with the contents (e.g., bad date formats or other contents the portal doesn't understand). If you see an error that you're not able to fix, feel free to send us your CSV file.
Once it has checked the file for errors, the portal will try to match the contents of your CSV file against any fund/investment records that are already in the database. It does this by comparing the name of the fund/investment in the CSV file to what it already has in the database. Therefore, it is important that all of your funds and investments have unique names! If there are duplicates, the portal will have trouble matching the records for you.
If the portal has a record in the database that is not in the CSV or if it can't find a good match on its own, you'll see the unmatched records at the top of the page and you will have the ability to select a matching fund from the dropdown or to tell the portal that the record was omitted from your CSV because the fund/investment was sold/closed or because you want to manually updated it later. In this screenshot, the top fund had no match in the database and the bottom fund was successfully matched:
If you tell the portal that any unmatched records were "Sold/Closed", you'll provide the reason for the sale/closure and some comments on the next page. Finally, the portal will bring you to a summary screen that will provide you with an overview of what will occur when you complete the upload/import process. In the screenshot below, the portal is telling me that when I click "Complete Import", it will result in one new fund record being added to the database, ten records being updated, and one fund record being marked as sold/closed (which will remove it from the MS-9 ledger):
A note about "Matched Records"
When the portal matches records it will update the information in the database with the information from the file. It is important to note that imports aren't cumulative - the portal doesn't add the values in the CSV to the values in the database. Whatever is in your CSV file will replace the information in the database.
How can I get help with errors?
Errors can happen for a number of reasons. The portal will try to communicate to you what correction needs to be made (e.g. a Fund Type or Purpose doesn't match the accepted values). If you're not sure what the error indicates or you run into an error at the end of the import process, we can help! Feel free to send us your CSV files (via a new ticket or an email to firstname.lastname@example.org) for testing and/or if you receive error messages when attempting to import.