Note that the MS-9 will be changing for all filers as of July 1, 2023 (see this article). If you are preparing an MS-9 CSV for the first time ahead of that date, it is recommended that you opt in to the new MS-9 version via a support ticket so that you can take advantage of the new CSV format.
This article is fairly technical and is intended for financial managers, financial institutions, and users that have experience working with Microsoft Excel, spreadsheets, and text files. If you have difficulty with the contents below, we recommend reading the Import Overview help article, first. |
CSV File Structure
The CSV files must follow these conventions:
- The file must be a UTF-8 encoded text file with the extension “.csv"
- The first line of the file must contain the field names only
- Each additional line in the file constitutes one record
- Each line is terminated by either a line feed (LF) or line feed/carriage return pair (CRLF)
- Each field on a line and the field names on the first line are separated by a comma character (,)
- Text fields that contain commas must be enclosed with double quote characters (")
Note: Microsoft Excel and other spreadsheet applications usually do this automatically for you
Field Order and Field Names
The NHTTF import process is field order agnostic and is not case sensitive; if the imported file uses the correct field names, they may appear in any order within the file. In addition, the file may contain extra fields not included in the schema so long as the field names do not coincide with those in the schema and do not contain special characters (the import process will ignore the extra fields).
Data Type
The contents of the fields in each record must match the data type specified in the schemas. Certain data types have restrictions on the character set that may be stored.
| Data Type | Allowable Characters | Sample |
| Text | All (See: CSV File Structure for caveats) | This is sample text. |
| Decimal | Numbers (0-9), decimal/period (.), and minus (-) | -123.45 |
| Integer | Numbers (0-9) | 1 |
Data Width
The contents of the fields in each record must match the data type and field width specified in the schemas. Field width refers to the maximum number of characters for the field (if text), the maximum precision and scale (if decimal), or the maximum number of digits (if integer). For example: a text field of width “100” allows a maximum of 100 characters; a decimal with “18,2” specified indicates that it supports an 18-digit number with two decimal places.
Reference Values
Certain fields (e.g. DeletedReason, FundType) can only contain specific values (See: Reference Values). While the application import process is not case-sensitive, if a value other than the ones given is used, the user will have to manually correct the imported record in the NHTTF.
Schemas
Report of Trust and Capital Reserve Funds (MS-9)
| Field Name | Required | Type (Width) | Description/Notes |
| ID | No | Text (100) | The ID of the Fund used by the system or application generating the text file. This value is not currently used by the application and is included here only for compatibility purposes. |
| FundName | Yes | Text (100) | The name of the fund. This is the primary field that the portal uses to match CSV records against its own database. For this reason, all records should have unique values. |
| Type | Yes | Text (100) | The type of fund (See: MS-9 Fund Type). |
| Purpose | Yes | Text (100) | The purpose of the fund (See: MS-9 Fund Purpose). |
| HowInvested | Yes | Text (100) | The method of investment (See: MS-9 Fund Investment Method). |
| DateOfCreation | Yes | Text (10) | The date the fund was created, in the following format: MM/DD/YYYY. If this value is not known, use “01/01/1900”. If only the year is known, use January 1 of that year or just the four digit year (YYYY). |
| PrincipalBOYBalance | Yes | Decimal (18,2) | The beginning-of-year principal balance of the fund. |
| PrincipalNewFunds | Yes | Decimal (18,2) | The amount of new funds added during the year. |
| PrincipalUnrealizedGains | Yes | Decimal (18,2) | The amount of unrealized gains during the year. |
| PrincipalRealizedGains | Yes | Decimal (18,2) | The amount of realized gains during the year. |
| PrincipalCashCapGains | Yes | Decimal (18,2) | The amount of cash capital gains during the year. |
| PrincipalWithdrawals | Yes | Decimal (18,2) | The amount of withdrawals during the year. Report this as a positive number. |
| PrincipalEOYFairValue | No | Decimal (18,2) | The principal end-of-year fair value for the fund. The application will recalculate this value after the import completes using the following equation: PrincipalBOYBalance + PrincipalNewFunds + PrincipalUnrealizedGains + PrincipalRealizedGains + PrincipalCashCapGains - PrincipalWithdrawals |
| IncomeBOYBalance | Yes | Decimal (18,2) | The beginning-of-year income balance for the fund. |
| IncomeIncome | Yes | Decimal (18,2) | The amount of income during the year. |
| IncomeExpended | Yes | Decimal (18,2) | The amount of expenditures during the year. Report this as a positive number. |
| IncomeEOYBalance | No | Decimal (18,2) | The end-of-year income balance for the fund. The application will recalculate this value after the import completes using the following equation: IncomeBOYBalance + IncomeIncome - IncomeExpended |
| Note | No | Text (1000) | Optional comment field. |
| Deleted | No | Integer (1) | Indicates that the fund was removed from the ledger during the year (0 = False, 1 = True). If this value is not specified, the system will assume that the fund was not removed (0). |
| DeletedOn | No | Text (10) | The date that the fund was removed from the ledger (MM/DD/YYYY). Field is required if the fund was specified as deleted (Deleted = 1). This does not have to be the date that the actual fund was sold or closed - it is just a date that is tracked internally in the database. |
| DeletedReason | No | Text (100) | The reason that the fund was removed from the ledger (See: MS-9 Fund Deletion Reason). Field is required if the fund was specified as deleted (Deleted = 1). |
| DeletedNote | No | Text (1000) | A note or comment related to the reason for deletion or removal of the fund from the ledger. |
Report of Common Trust Fund Investments (MS-10)
| Field Name | Required | Type (Width) | Description/Notes |
| ID | No | Text (100) | The ID of the investment used by the system or application generating the text file. This is the primary field that the portal uses to match CSV records against its own database. For this reason, all records should have unique values. |
| InvestmentName | Yes | Text (100) | The name of the investment. |
| Type | Yes | Text (100) | The type of investment (See: MS-10 Investment Type). |
| Shares | Yes | Decimal (18,2) | The number of shares owned. If this does not apply (e.g., bank accounts) it can be left as zero. |
| PrincipalBOYBalance | Yes | Decimal (18,2) | The beginning-of-year principal balance of the investment. |
| PrincipalPurchases | Yes | Decimal (18,2) | The purchases made during the year (or new funds added to the account). |
| PrincipalCashCapGains | Yes | Decimal (18,2) | The cash capital gains during the year. |
| PrincipalSaleProceeds | Yes | Decimal (18,2) | The amount of proceeds made during the year for the investment. This should be reported as a negative value, as it is being subtracted from the end of year balance. |
| PrincipalSaleGainLoss | Yes | Decimal (18,2) | The amount of gain (positive) or loss (negative) during the year on the principal. |
| PrincipalEOYBalance | No | Decimal (18,2) | The end-of-year balance of the principal. The application will recalculate this value after the import completes using the following equation (note that PrincipalSaleProceeds is negative here): PrincipalBOYBalance + PrincipalPurchases - PrincipalCashCapGains + PrincipalSaleProceeds + PrincipalSaleGainLoss + PrincipalNewFunds |
| IncomeBOYBalance | Yes | Decimal (18,2) | The beginning-of-year income balance for the investment. |
| IncomeIncome | Yes | Decimal (18,2) | The amount of income during the year. |
| IncomeExpended | Yes | Decimal (18,2) | The amount of expenditures during the year. Report this as a positive number. |
| IncomeEOYBalance | No | Decimal (18,2) | The end-of-year income balance for the investment. The application will recalculate this value after the import completes using the following equation: IncomeBOYBalance + IncomeIncome - IncomeExpended |
| PrincipalOnlyBOYFairValue | No | Decimal (18,2) | The principal-only beginning-of-year fair value of the investment. This field is optional - not all entities submit Principal-Only values. |
| PrincipalOnlyUnrealizedGains | No | Decimal (18,2) | The principal-only unrealized gains during the year. This field is optional - not all entities submit Principal-Only values. |
| PrincipalOnlyEOYFairValue | No | Decimal (18,2) | The principal-only end of year fair value of the investment. This field is optional - not all entities submit Principal-Only values. The application will recalculate this value after the import completes using the following equation: PrincipalOnlyBOYFairValue + PrincipalOnlyUnrealizedGains |
| Note | No | Text (1000) | Optional comment field. |
| Deleted | No | Integer (1) | Indicates that the investment was removed from the ledger during the year (0 = False, 1 = True). If this value is not specified, the system will assume that the investment was not removed (0). |
| DeletedOn | No | Text (10) | The date that the investment was removed from the ledger (MM/DD/YYYY). Field is required if the investment was specified as deleted (Deleted = 1). This does not have to be the date that the actual fund was sold or closed - it is just a date that is tracked internally in the database. |
| DeletedReason | No | Text (100) | The reason that the investment was removed from the ledger (See: MS-10 Investment Deletion Reason). Field is required if the investment was specified as deleted (Deleted = 1). |
| DeletedNote | No | Text (1000) | A note or comment related to the reason for deletion or removal of the fund from the ledger. |
MS-10 Brokerage Fees & Expenses
| Field Name | Required | Type (Width) | Description/Notes |
| ID | No | Text (100) | The ID of the record used by the system or application generating the text file. |
| Name | Yes | Text (100) | The name of the brokerage firm/organization. |
| FeesPaid | Yes | Decimal (18,2) | The amount of fees paid during the year. |
| ExpensesPaid | Yes | Decimal (18,2) | The amount of expenses paid during the year. |
| Comment | No | Text (1000) | Optional comment field. |