There are three CSV files attached to the end of this article: one for the MS-9, one for the MS-10, and one for the MS-10 Brokerage Fees and Expenses. They each contain a single row with the Field Names (Headers) described below and can be used as a template when preparing your own spreadsheet or CSV file with your data. You can also create a CSV file from the MS-9 or MS-10 Report dropdown buttons.

CSV File Structure

The CSV files must follow these conventions:

  • The file must be a UTF-8 encoded text file with the extension “.csv” or “.txt”
  • The first line of the file contains the field names
  • Each additional line constitutes one record
  • Each line is terminated by either a line feed (LF) or line feed/carriage return pair (CRLF)
  • Each field is separated by a comma character (,)
  • Text fields must be enclosed with double quote characters (")
  • If a text field contains embedded double-quote characters, they must be replaced with a pair of double-quote characters ("").

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.
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.
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).
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.
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.
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.
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.
PrincipalNewFunds

Yes

Decimal (18,2)

This field is present for compatibility reasons but not used by the MS-10.
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:
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

Yes

Decimal (18,2)

The principal-only beginning-of-year fair value of the investment.
PrincipalOnlyUnrealizedGains

Yes

Decimal (18,2)

The principal-only unrealized gains during the year.
PrincipalOnlyEOYFairValue

No

Decimal (18,2)

The principal-only end of year fair value of the investment. 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).
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.