The Structure of Operational Templates
Although openfunds prefers the flat-table format, today a significant amount of fund information is transmitted in the narrow-table format, with particular regard to country registration information. For this reason, openfunds recognises and defines both formats.
The correct implementation of the openfunds standard ensures that the recipient system is able to recognise the format from the fields present during fully-automated data transmission.
The “flat table format”
In the flat-table format, each ISIN to be transmitted occupies an individual row. A repetition of the same ISIN in the same data file would, at best, generate an alert message; at worst, i.e. in the case of contradictory information, it would go as far as generating an error requiring immediate corrective action.
The table below shows four ISINs from three domiciles (LU, CH and DE) with distribution licences for Luxembourg and Switzerland: the information here is presented in the flat-table format. Fund A is registered for distribution in Luxembourg and Switzerland, while Fund B’s distribution licence for Luxembourg is subject to restrictions. The status of Fund C in Luxembourg is not known, while the fund is registered for distribution to all investors in Switzerland. Fund D is not registered for distribution either in Luxembourg or in Switzerland.
For more countries or information about the corresponding marketing distribution (OFST6031XX), additional columns are simply inserted in the table. Fund B (second row) presents a special case, whereby the distribution licence in Luxembourg is restricted (Res). More details about the restriction are provided in the last column headed “Country Specific Registration Type – Luxembourg” (OFST6050LU): this particular ISIN is designated as a Specialised Investment Fund (SIF).
The last two rows, containing Funds C and D, are also worth a closer look: For Fund C, there is no entry concerning registration in Luxembourg. In this case, the existing value in the recipient database will be deleted. Should it remain unchanged, one has to put in the command “[IGNORE]” instead. More Explanation can be found in the white paper “The Ambiguity of Empty Fields”.
At first glance, the last row, containing Fund D, does not appear out of the ordinary, but it demonstrates the fundamental differences compared with the narrow-table format described below. For example, a scenario may arise whereby the registration for distribution in Switzerland is cancelled: in this case, an existing “Yes” entry in the receiver database should be overwritten with “No”. Since the flat-table format essentially allows partial deliveries in terms of both fields (in this case, columns) and ISINs (in this case, rows), Fund D may well be registered for sale in other countries in addition to Luxembourg and Switzerland.
The “narrow-table format”
In the narrow-table format, each ISIN for each country is allocated its own row. Therefore, in the narrow-table format, the field “Country ISO Code (ALPHA-2)” (OFST600000) must be inserted, whereas this would cause an error message in the flat-table format. The combination of ISIN (OFST020000) and Country ISO Code (ALPHA-2) (OFST600000) therefore represents the key field in the narrow table:
In addition to the new mandatory column headed “Country ISO Code (ALPHA-2) (OFST600000), we see two red “XX”s after OFST6030 and OFST6050. While these have been replaced with the two-letter ISO country code in the flat-table format, here they stand for two things:
- They indicate that the file is in narrow-table format;
- An ISIN may take up several rows: consequently, it must necessarily include the country information (OFST600000). In the example above, Fund A is registered in both Luxembourg and Switzerland: consequently, this fund appears twice in the narrow-table format.
Fund B appears once only (correctly so) with the restricted licence for Luxembourg.
Fund C, as presented in the narrow-table format, represents a standard case. But why is Fund D missing?
In today’s master data files in narrow-table format, it is common for a missing ISIN to result in all distribution licences in the recipient database being deleted. Therefore, it must always be possible to compare data files in narrow-table format with the previous delivery, or else, before the corresponding fields are imported, these fields must be deleted for all ISINs. In practice, the second variant often causes problems, as it is unclear which ISINs are part of a complete delivery. Therefore, if the narrow-table format is to be used, the first variant is preferable, although it does require more programming.
The narrow-table format does not allow partial deliveries, as any ISIN entries omitted from the delivery would cause the ISIN entries in the recipient database to be deleted on a regular basis. It makes no difference which of the two variants mentioned above is used.
Recommendation
The remarks concerning the narrow-table format illustrate the reason why the openfunds initiative recommends the flat-table format: this format is far more flexible as it allows partial deliveries and is far less prone to errors.
Document Information
| Title: | Flat Table versus Narrow Table |
| Language: | English |
| Confidentiality: | Public |
| Authors: | Michael Partin, Charlie Duffin |
Revision History
| Version | Date | Status | Notice |
| 1.01 | 2022-03-03 | Final | Corrected typo in figure 1 and added document information section |
| 1.00 | 2015-09-07 | Final |
Implementation
If you have any questions about the new data type or difficulties with implementation please contact us at businessoffice@openfunds.org.
Joining openfunds
If your firm has a need to reliably send or receive fund data, you are more than welcome to use the openfunds fields and definitions free-of-charge. Interested parties can contact the openfunds association by sending an email to: businessoffice@openfunds.org
openfunds.org
c/o Balmer-Etienne AG
Bederstrasse 66
CH-8002 Zurich
Tel.: +41 44 286 80 20
Email: businessoffice@openfunds.org
Website: https://www.openfunds.org
If you wish to read or download this white paper as PDF, please click here.