4.4 Excel Import Templates - Using Your Own Templates

Whilst DPM Authority makes it easy to generate and import data from its own templates, it is also possible to use your own import templates. This would make sense if you already have a system capable of generating Excel version of the templates and you want to avoid a process of linking or transposing data from your templates to the DPM Authority generated Excel templates.

There are two approaches you can adopt to achieve this.

1. Manually map each data point in DPM Authority to a cell in your import template using any of the 3 Excel data adaptors which are described in detail in the External Data Connections section starting at this article.
2. Modifying your Excel Import templates to include the required worksheet names and Excel named ranges to automate the mapping process.

We recommend the second approach if possible as it would not be necessary to manually map each data point as it would be required in the first approach.  The rest of this article will explain how to modify your Excel Import template in order for DPM Authority to automate the mappings.

Modifying your Excel Import Template

Sheet Names

In order to automate your mappings, your sheet names must be the same as the sheet names in the DPM Authority generated Excel import templates.  Usually, this is just the sheet code, such as "C 00.01".  However some sheets are dimensional and may include sheet dimension labels, such as "C 07.00.a(001)", "C 07.00.a(002)" & "C 09.01.a(All countries)".  Furthermore, the length of a sheet name is limited by Excel to a maximum of 31 characters.  The sheet dimension label may have to be truncated, such as "C 09.01.a(SOUTH GEORGIA AND TH)".

If there is any doubt with what a sheet name should be, generate an Excel Import Template from DPM Authority containing the sheet and refer to its name within.

Named Ranges

To use Report Authorrity's automatic mapping functionality, each data point in fixed row and column tables should contain a worksheet scoped named range using the naming convention "_rNNNcNNN", such as _r010c010 (or _rR0010cC0010 in the case of EIOPA Solvency II).

It is important that these names are worksheet scoped, because there will be many data points with the same name in many different tables (such as _r010c010).  Excel does not allow duplicate workbook-scoped named ranges.

Automatically adding worksheet scoped named ranges

Worksheet-scoped named ranges could be added automatically by the system generating the Excel Import templates, or via a VBA script after they have been created. For assistance with this, please contact support@authoritysoftware.co.uk.

Manually add worksheet scoped named range

Please note that the most common way to add a named range to a sheet is to select the cell, then type the name in the name box to the left of the formula bar:

This method cannot be used for automatic linking purposes as it adds a workbook scoped named range.  To add a worksheet scoped named range, first select the cell, then click the Formulas - Defined Names - Name Manager:

This will open the following form:

Click New:

The name should follow the prescribed naming convention and the Scope should be set to the appropriate sheet name.

Adding a Connection

Once you have assigned the name ranges, you can connect your Excel template to DPM Authority by clicking on the Manage Connections button:

This will prompt the Data Connections form:

1. Name - Provide a name for the connection 
2. Workbook - Browse for the workbook and select the relevant path:
   1. Absolute path - An absolute path will be a fully qualified path to the file.  If the file or any of its parent folders are renamed or moved, the path of the connection will have to be updated.  
   2. Location relative to current document - A relative path can be used if the DPM Authority document has been saved and the workbook exists in either the same folder as the DPM Authority document or any folders within.  This way, the connection will not need to be updated as long as the workbook’s position in relation to the DPM Authority document has not been changed.  Moving the two files together will not result in the need to update the connection’s path.
3. Password - if the workbook is password protected, provide the password. 
4. Click the add button and then Close. 

TIP:  If you need to connect to multiple Excel workbooks, you can drag them from Windows Explorer and drop them onto this form.

Auto Link

The auto-link functionality automatically sets the data adaptor of each data point to use the Excel Named Range adaptor with the name that conforms with the expected naming convention. 

Click the Auto-Link to Custom Workbook button:

The Auto-Link to Existing Template form will appear:

1. Select the tables that you want to link to DPM Authority.
2. Select the connection to your custom Excel workbook.
3. Click Link.

This will automatically connect each data point to your Excel workbook.

• Preserve calculations - This option is checked by default and will ensure your custom formulae and calculations are not removed in the auto-link process.