Instructions on handling Excel forms
Excel-type forms are freely available from the SNB for use in its surveys. The institutions required to report data can use them to complete their reports and then submit the Excel files to the SNB for processing.
The Excel files generally contain a delivery note, one or more SNB forms and, in some cases, additional tables containing information. 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 validation tests, and error or warning messages are produced when necessary. The errors and warnings are transferred to the delivery note.
Please always send complete Excel files.
The names of the sheet tabs in the Excel files may not be changed.
Manual data entry
The institution required to report data is given an identification code by the SNB, which is entered on the delivery note. The ID code will then be transferred to all subsequent SNB forms.
In general, the reference date to which the report refers is the final date of the month. This date will be transferred to all subsequent SNB forms.
In the case of surveys that contain several Excel files, there may be links between the files. In such cases, the names of the files may not be changed. Once they have been completed, all the files will need to be updated. If problems with the links occur, please refer to the recommendations in the sheet entitled Information on reporting under ‘Obtaining survey documents’.
Once the forms have been filled in, the completed delivery note must be printed out, signed and enclosed with the delivery if the reports are to be delivered on a electronic data carrier (CDs or disks) or in the form of a paper print-out. Reports submitted by e-mail do not need to be signed.
Automation using Excel forms
It is also possible to use the Excel forms in an automation procedure. However, the SNB recommends that you use the XML format for this purpose (for further information, cf. firstname.lastname@example.org). The forms are subject to changes which are made available to interested parties (in the form of PDF files) at the earliest possible date (-> cf latest updates). The Excel formats are delivered later, thereby delaying program adjustments. However, changes to the XML specifications are rare and there is more time to make adjustments to reporting.
Please note the following:
- The structure and layout of the Excel forms may not be changed. All labels and observations must be allocated to the original excel coordinates. This means that no rows or columns may be inserted. In particular, you should not change the formating of the form fields (the format templates contain the name 'Beobachtung' (observations)). (When copying data, copy without formating.)
- Field protection in forms may be removed.
- Forms may contain references to external data sources. When the files are processed at the SNB, links are not updated.
- You may insert further tables for interim calculations. This does not affect the processing.
- In most cases, the zero values are not shown in the tables. The SNB does not process zero values in Excel that are not visible. Apart from some exceptions, zero values may not be supplied. When automating procedures with the aid of links you should ensure that no zero values are displayed.
- It is not advisable to remove the validation tests included in the forms. If the SNB identifies validation faults, the institution required to report data may be required to submit a repeat delivery (cf. faulty deliveries / repeat deliveries).