By standardising fund data, openfunds itself is based on rules and conventions when it comes to the description and definition of data fields. The advantages of the standard’s uniform design are that it fosters the understanding of the fields and their proper handling while making them simple to work with. This white paper looks at the conventions within the openfunds standard and explains what some commonly used terms mean and how to handle them:
Terms related to …
- Field Names
- Data Types Definition
- OFST999990 Non-openfunds Field
- OFST999999 Field To Ignore
- Flat File format
- Narrow File format
- Delta File
The field names of openfunds are in English, the first letter of each word in the name is always capitalised, and in general, there are no special characters used e.g. “Fund Administrator Name”. The designation of the fields should always be meaningful and as short as possible.
Data Types Definition
The possible data types for openfunds fields are; boolean, date, time, double, integer and string. The following list specifies the data types. Please note that openfunds usually designates values in lower case letters.
boolean: A boolean value within the openfunds standard is used to indicate the values “yes” or “no”. Although openfunds shows in the description that the values of boolean fields are either “yes” or “no” and that the fields are not nullable, we recommend implementing fields as nullable as openfunds in general accepts blank fields. For more information about blank fields please refer to the white paper Ambiguity of Empty Fields.
The boolean data type is used for fields implying a question e.g. “OFST001890 Has Collateral Manager”. It also applies to so called “Reasonable Has” fields, which are normally linked to another field where the actual value is provided. If a “Reasonable Has” field is populated with the value “no”, the linked field(s) must be left empty. If the value of such fields is “yes”, than the linked field(s) should contain the respective value. Let us explain this using the following table as an example:
Table 1: Example of a “Reasonable Has” field and the related field
The first column represents the “Reasonable Has” field, in this case “OFST451419 Has Redemption Fee” with the possible values “yes” or “no”. The second column shows the linked field to the “Reasonable Has” field. In the first row, the example value is “no”. That means that the linked field should not be populated with a value, but rather left empty.
However, if “OFST451419 Has Redemption Fee” is “yes”, the linked fields are expected to be populated with a value as well. Please also consider the explanation in the table.
date: In the openfunds standard a date is represented in the format YYYY-MM-DD e.g. “2018-02-26”.
time: In the openfunds standard a time is represented in the format hh:mm (24 hours) e.g.”13:00”.
double: The double value is a decimal figure, rounded to maximum seven digits after the decimal point. Furthermore, the double value also covers percentage values. For instance, in order to indicate “0.5%” openfunds expects “0.005” to be delivered instead. This value can be either positive or negative e.g. “1.027” or “-0.0132”.
integer: The integer value is a number without a decimal point e.g. “7”. The minimum value is “0”.
string: The string value represents a sequence of characters. Within the data type string, letters, numbers or special characters are allowed e.g. “The Field “OFST001000 Fund Group Name” contains the overall brand name of the fund company!” The openfunds standard does not define the length of a string value.
OFST999990 Non-openfunds Field
This field can be used to transmit data that is not defined in the openfunds standard. Please note that on the recipient side it is recommended to use the sender’s field name in the second line of the data table for identification. We therefore recommend not leaving the field name empty.
OFST999999 Field To Ignore
This field tells the recipient to ignore the values in this column.
In general, blank fields are interpreted as “unknown” by openfunds. In some cases, however, a distinction should be made between different meanings of an empty field. For more information, please refer to the white paper The Ambiguity of Empty Fields.
Flat File format
A flat file is considered as a normal flat table as illustrated in table 2. Thus, there is one ISIN per row. Exceptions are ISINs with several listings and stock exchanges i.e. ETFs. The advantages are that flat files are easy to validate and intuitive to read. The disadvantage is that such files can be very broad. For more information regarding flat files, please refer to the white paper Flat Table versus Narrow Table.
Table 2: Example of a Flat File format
Narrow File format
A typical characteristic of narrow files is that the same ISIN has different fields. i.e., OFST6030XX Country Legal Registration. As illustrated in table 3, Luxembourg in one row has the same ISIN as Switzerland in another row. The advantages here are that it is possible to transmit a vast amount of data and there is almost no limit regarding the number of fields. The disadvantage is that the narrow files are difficult to read. For more information regarding narrow files, please consider our white paper Flat Table versus Narrow Table.
Table 3: Exmample of a Narrow File format
Imagine you create a Full Scope File with 100 rows and 800 columns. This means you create 80’000 data points. Because of the huge amount of data, a large amount of data storage is required, and processing is time consuming. In order to not have to produce the Full Scope File for each change, openfunds recommends using Delta Files. The processing with the Delta File works as follows:
The initial created Full Scope File (T0) is compared with the last generated file (T-1). The changes are captured in the Delta File. For each ISIN which has changed, all 800 columns will be updated. All the ISINs which have not changed will remain the same. Thanks to this method, processing can be accelerated.
Without this method a full scope file would be produced each time a change is made, even for small changes. The Delta File lists only those datasets which actually changed. As a result, the Delta File is much smaller than a full scope file and can be processed faster.
openfunds follows the principle of mirrored databases. This means that if the sender exports all data from its database and the recipient imports it, the two databases should contain identical data.
Please note that an empty field means “unknown” as a rule within openfunds. If you send a flat file with a value in a certain field and later send an update where the same field is empty, the initial value will be deleted. That means an empty field leads to a deletion of a value. To avoid any deletion of a value in the receiving database, openfunds recommends using the “[IGNORE]” command instead of an empty field (including square brackets and all capital letters).
If a fund house does not need a certain field and therefore no values are inserted and will not be inserted in the future, there is the possibility to enter “[N/A]” in this field. The “[N/A]” indicates that the field is not applicable, and no values are supplied. Please consider the following example scenarios and the behaviour of a new report.
Table 4: Possible [N/A] scenarios
[IGNORE] vs. [N/A]
The “[IGNORE]” command is used for data that may be required but is not always known, or the data has already been submitted once and therefore does not need to be re-transmitted again. In order to not delete the initially submitted value, please use the “[IGNORE]” command.
The “[N/A]” command, on the other hand, is used for fields which are not applicable and therefore no action with regards to these values is necessary.
The Adapter forms a comprehensive standard based on best practice, i.e. the adapter translates files in the openfunds format into the most important formats as illustrated in figure 1. The difference between EMT, EPT, CEPT and openfunds is that openfunds is a more comprehensive framework that does not only take regulatory aspects into account.
Note: To avoid confusion of terms, a translator from a proprietary format to the openfunds format is called a Transformer.
Figure 1: What an Transformer / Adapter does