The portal allows users to import fund/investment records into their ledgers through the preparation and upload of 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 system 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
Where can I find an example of a CSV file?
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 import process requires. As you enter more records into the CSV file, keep in mind that there are some rules about date and number formats and what information you are required to provide (e.g. fund/investment type and purpose). You can find more information about the formats and required fields here and a list of fund/investment types, purposes, and methods 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?
The import process starts with you selecting and uploading a file to the portal. The portal then 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 system doesn't understand). If you see an error that you don't understand, feel free to contact us and send us your CSV file.
Once it has checked the file for errors, the system 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 examining the name of the fund/investment and the beginning of year balances. It is important that all of your funds and investments have unique names!
If the system has a record in the database that is not in the CSV or if it can't find a good match, 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 system 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 system 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 system 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 system 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 system 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 system 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 system 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.