Bookmark this page Print this page

Bordereaux, Excel Templates, Example for Premium Credit

If Premium Credit haven't already sent you a copy of the Excel template called "Premium Credit Template.xltm" then you should request one from them or Durell (in the latter case via a support ticket at http://support.durell.co.uk). Before proceeding ensure you've copied this file into your \Imw-Data\Template folder.

Then open your Document Viewer, select the Premium Credit Template (see below) for "Policies, all records" and select the worksheet called "Data" from the dropdown at the bottom of the pop-up. 

However at this point you won't yet have a Data Field Set called "Premium Credit", so click the "Setup" button...

...then click "New" and name your new empty data set as "Premium Credit".

Next clear the tick from the box at the top centre re "If ticked list fields by..." so that you can select the fields you require in the same order as the columns in the Excel template.

Then select your fields in the order shown below from the correct tables...

TABLEFIELD
  
User licence detailsAddress: Line1
User licence detailsAddress: Line3
Policy - Special infoSpecial - Policy status
Policy - Details 1InsurerName
Policy - Details 1InceptionDate
Policy - Details 1Reference
Policy - Details 1PolDets_PayMethod
Policy - Details 1MainTypeName
Policy - Details 1PolDets_GrossPremium
Policy - Details 1PolDets_Deposit
Policy - Details 1PolDets_Invoice
Policy - Details 1PolDets_Nett
Policy - Details 1LeadName
Client detailsDate of Birth
Client detailsPostCode1
Client detailsOccupation

Make sure you click "Save" to save the new data set, then "Close to return to your "Link to Microsoft Excel" pop-up, where the Data Field Set should now either appear or be selectable.

With everything setup correctly on your "Link to Microsoft Excel" pop-up as shown at the start of this section you should be able to click "OK" to export the data from Durell to its worksheet called "Data".

As you do so the resulting Excel spreadsheet should appear automatically (though possibly it may be minimised at the bottom of your Windows Desktop). It will open on its first worksheet, called "Control" on which you'll find a button to "Run Reports". Just click this to pass your raw Durell data from the "Data" worksheet to the "Reports"one (shown below), where it'll be correctly formatted for Premium Credit.

Click "Yes" to save this as a "macro-free workbook" and ignore the following irrelevant error message. Then save the spreadsheet in whatever location you prefer, and return a copy to Premium Credit.

Note that you may wish to refine the policy selection (e.g. to just include Commercial policies with On Risk dates in the current year) for which you'd need to do a Mailshot selection before you start (see previous section re "Mail Shots / Bulk Selection of Policies") and then set the "Select Records" dropdown on your "Link to Microsoft Excel" pop-up to "Policies - Mailshot = Y", as shown below.