The Swiss National Bank uses two different data models for its surveys - the coordinates-based model and the subject-specific model. The data model determines the structure of the delivery formats for transmitting the statistical data to the SNB. It is important to note that data for a given survey can only be delivered using the specified data model, and there is no choice with respect to the delivery format.

The coordinates-based data model

The coordinates-based model is based on forms, rows and columns. The keys used for this reflect a figure's position in a given form.

For instance, in the comprehensive monthly balance sheet (valid to October 2015), the survey item 'Assets, Liquid assets, Domestic, CHF' is located in form M011, row 01, column 01. It is assigned the key M011(Z01,K01); in XML format it is assigned <x/> and <y/>.

The delivery formats Excel and XML are set up to be able to support these keys.

Subject-specific data model

The subject-specific data model depicts the content of a survey in a hierarchical structure, using codes combined to form a key as its structural elements.

For instance, Balance sheet, Assets, Liquid assets, Domestic, CHF: In the subject-specific data model, the item in the example above is identified using the key BIL.AKT.FMI{I,CHF}. Each part of the code is given a title in order to make clear which item it refers to: BIL=Balance sheet, AKT=Assets, FMI=Liquid assets, I=Domestic, CHF=Swiss francs. This key is used in more than one survey (e.g. MONA_U, MONA_B, JAHR_U, JAHR_B, JAHR_K).

The delivery formats Excel and XML, which support subject-specific keys, are therefore different from the formats in the coordinates-based surveys.

The subject-specific data model is also used for web tables and the associated CSV format.

Excel is the main format available for the completion and delivery of surveys that use the coordinates-based data model. For automated reporting, there is also a DTD (Document Type Definition) available, which can be used to generate XML reports.

Coordinates-based survey documents in Excel format

The SNB provides forms in Excel format (xls 2003 or xlsx 2010) for a number of surveys. The reporting institutions can use them to complete their reports and then submit the Excel files to the SNB for processing.

The Excel files generally contain a 'Start' worksheet, one or more SNB forms (one form per table) and, in some cases, additional tables containing information or consistency checks. They do not contain any macros.

The tables with the forms are protected so that entries can only be made in designated data entry fields. Entries cannot be made in fields such as totals that are calculated by the system. Some tables may also include consistency checks, which generate errors or warnings when the checks are failed. The total number of errors and warnings on the individual forms is shown in the table on the 'Start' sheet.

Please note the following:

• You must always send complete Excel files.
• The structure and layout of the Excel forms may not be altered. All reporting items must always be allocated to the original Excel coordinates. Consequently, no rows or columns may be added.
• The names of the sheet tabs in the Excel files may not be changed.
• Worksheet protection may be removed.
• Cells with formulas or references to external data sources can lead to problems during processing. The SNB therefore recommends avoiding such constructs when completing reports.
• Entering additional tables for intermediate calculations is permitted and does not affect processing.
• It is not advisable to remove the consistency checks included in the forms. If the SNB identifies errors, the reporting institution may be required to submit a corrected report.
• If, during the processing of a report, any unauthorised modifications are identified or any errors due to formulas or references are encountered, the system will be unable to process the report and it will be rejected.

Coordinates-based survey documents in XML format

The SNB provides an XML definition (DTD) for coordinates-based surveys. The definition describes how to prepare statistical reports using XML and submit them to the SNB. It can be used for all coordinates-based surveys. This solution makes sense when reporting institutions use IT tools to process their reporting data.

An XML file may contain reports on different surveys for a given reference date. An XML file contains a report for each form in a survey. The reports contain the version number of the form.

The delivery formats that are available for subject-specific surveys depend on whether the survey in question uses web tables or Excel forms to record data. Surveys that do not use web tables for data collection can be submitted in either Excel or XML format. For those that do use web tables, data can be entered directly in the web table itself or be submitted in CSV or XML format.

Subject-specific survey documents in Excel format

The Excel format used for the subject-specific survey documents requires at least Microsoft Office 2007.

The row numbers and column names in the forms (alphanumeric) correspond to the Excel coordinates and are purely designed to simplify communications with the SNB in the event of queries. The link between Excel coordinate and data point is dynamic and may therefore change as a result of future adjustments to the Excel form.

For technical reasons, the xlsx forms do not include any calculated items, and consistency checks are filed outside the data entry field, in the form of consistency rules. Moreover, all the consistency rules are compiled in a separate table together with additional information.

The xlsx survey documents are supplied together with an XML schema, which contains the subject-specific keys. The SNB uses the specified XML schema to process the file. Thus, an xlsx file submission to the SNB must include the relevant XML schema. The following documents provide further information on the use of xlsx survey documents:

Subject-specific survey documents in XML format

In the subject-specific model, a separate XML schema is required for each survey. These XML schemata are supplied by the SNB along with the other survey documents.

Together with the subject-specific keys, the XML schema also contains titles in the various languages to ensure that the keys can be understood in connection with the surveys.

An XML file may contain no more than one report per survey for each reference date. The content of the report is defined by the XML schema, which bears a version number.

In each case, the XML schemata are also embedded in the relevant Excel survey documents. This means that XML reports can also be displayed in Excel, using Excel's Import function. This may make sense if the contents of an XML file need to be displayed and validated in tabular form. The following documents provide further information on the XML format for subject-specific surveys:

The subject-specific XML schema is also used for surveys based on web tables. An XML file created using this schema can be uploaded to the corresponding web table, where it can be viewed and subsequently checked.

Web tables for subject-specific surveys

Web tables are based on the subject-specific data model and are integrated into eSurvey. If a survey is provided in the form of web tables, the Excel option is no longer available. The CSV format can be used in addition to web tables. The XML schema - which can also be used with web tables - corresponds to the one used for the subject-specific data model.

CSV format for subject-specific surveys

The CSV delivery format is an additional format that can be used with web tables. The CSV file can be opened in Excel and linked with internal Excel sources. This allows reports to be prepared either locally or offline. Reports generated in this way can be subsequently displayed in the web tables, further processed if necessary, checked for consistency and ultimately submitted.