Bordereau Converter

From Wiki
Jump to: navigation, search

See the NOVUS Walkthrough Guide for general information about the system.


The CHALIS (Coverholder At Lloyd's Insurance Software) bordereau converter allows you to map columns within various similar bordereau spreadsheets to fewer styles or a single style for bordereau processing with the INBORD program.


Quick Start Guide

Foreword

To make explanation clearer, ‘Acme Comm’ is used as the example bordereau.


Opening Bordereau Converter

To open the ordereau converter program click the ‘CHALIS’ tab in the ribbon at the top of NOVUS, then click the bordereau converter icon. The NOVUS shortcut BORDCONV also invokes this program.


Bordereau converter icon.png


3 Stages of the Conversion

In order to convert a bordereau to another destination format such as the Lloyd's premium reporting template, the following steps must be followed.


1. Create Style Source and Destination
A bordereau style needs to be created. In doing so, a list of column headings and data types are collected. The style is essentially a blueprint for that particular bordereau. The ‘Acme Comm’ will be imported into the style creator, a title and a type will be given.The type given decides the direction of the data. If the Bordereau contains source data that needs to be exported into the ‘Lloyd's premium’ style, it will be marked as a ‘source’ type.


2. Create a mapping
Once a source and destination Style exist within the system, a mapping needs to be created. The mapping directs data from a source column into a destination column during the conversion process. There are a number of different column mapping types, each of which performs a different task and are outlined later in the document.


3. Upload bordereau for conversion
Finally, the source bordereau can be converted to the destination format and saved as a CSV file. If multiple mappings exist between a source style and destination style, these will be available during the conversion process.


The image below shows an example of the bordereau converter. Two columns in the source bordereau have been combined into one column during the conversion process. The style mapping has copied data from the source bordereau’s ‘premium’ column and copied to the ‘gross premium paid this time’ column in the ‘Lloyd's premium’ style.


Bordereau completed mapping.jpg

Stage 1 - Creating a style

1. Open ‘new style’ by: a. Clicking ‘create style’ on the bordereau converter home screen


Bordereau new style.PNG


b. Clicking ‘new style’ in the ‘home’ and ‘styles’ tab of the ribbon at the top of the bordereau converter

Bordereau create style icon.jpg



2. Click the ‘browse’ button to locate a source bordereau spreadsheet in ‘.XLS’ or ‘.XLSX’ format

Bordereau new style 2.PNG

3. Click the ‘import’ button to load the spreadsheet into the program

4. Select the worksheet to create a style for in the ‘worksheet’ dropdown (refer to errors section of this document for errors)

5. Select a style type from the ‘type’ dropdown menu. This is used to define whether data comes from or is converted to this style and will be either ‘source’ or ‘destination’:

6. Populate the ‘style title’ box, this refers to the style during the mapping process. Clicking on the spreadsheet title, next to the ‘Import’ button will copy that name into the ‘style title’ box

7. The most important part of this process is locating the header row(s). To ensure that the header row(s) were selected correctly, click the ‘show workings’ button. A copy of the original spreadsheet will open, the header row should be selected. It is possible that there will be two rows and only one is selected. To resolve this issue, select both rows by clicking in the top of the header rows and drag the mouse downwards. Upon releasing the mouse, both the rows will be selected.

8. Click the ‘save selection’ button, to return to the ‘new style’ screen. The screen will look as below.


Create Style


9. Click the ‘column properties’ button or Tab

Bdx conv create style4.png


10. The ‘data format’, ‘CSV alias’ and ‘required’ options are used only by destination styles.
a. Data format
i. If this is selected, any data that is copied into this column must be of the same format. For example, if Number is selected and during the conversion process, the is text, the data row will be discarded.
b. CSV alias
i. This is used by Morning Data’s internal processing
c. Required
i. If selected, during the conversion the source column that is being copied into that column must be populated else the data row will be discarded.

11. Click ‘save style’ button to save the style ready for use in a mapping

Stage 2 - Creating a Mapping

Once there is at least one source and one destination style a mapping can be created. Choose the two styles to map to each other.

To add or edit a mapping use the edit mapping screen to “add column mapping” and select the source column. The same source columns can be used multiple times for mapping purposes.

Now select the destination column from the destination sheet, CHALIS highlights the columns that have already been mapped as a destination column can only be used once.

Existing mappings are shown with highlighted rows:


Mapping enquiry screen.PNG


The list of mapped columns is shown:


Creating a Mapping


Advanced mapping can be achieved which will offer further functionality on top of the basic columns to columns ‘standard column mapping’. The three other options available include ‘calculation column mapping’; ‘custom column mapping’; ‘multi-column mapping’ and ‘cell mapping’


Add column mapping type.PNG


‘Calculation Column Mapping

The transformation would place the product of commission percentage and total premium in the source spreadsheet in to the commission amount field in the destination row:


Calculation columns.PNG


‘Multi- Column Mapping'

The transformation would show the concatenation of values from two source columns to give a single value.


Multi column mappings.PNG


‘Custom- column mapping’ Allows the user to specify a default value for the destination column for all rows.


Custom column mappings.PNG

Cell Mapping:

Allows a specified cell that is updated on a monthly basis to be referenced. I.e. If in a cell the date is updated, then it will update appropriately in all rows of a specified column.


Creating a Mapping

Stage 3 - Upload bordereau for conversion

Finally once the above stages are complete, the bordereau conversion can be made by following the steps as indicated below:


Bordereau conversion steps.PNG


Save on local hard drive:


The output is produced in excel for based on the input spreadsheet. In the simple 5 column mapping from the received bordereau into the Lloyd's premium bordereau, any unmapped columns are left blank.


Stage3a


N.B. Please take note of where the chosen export directory is located. The conversion bordereau will be stored in NOVUS if files have been uploaded from NOVUS. Therefore ensure that the file is saved on the local drive if that is where the file is to be used


Error Messages

Style Creator

When selecting a worksheet from the ‘worksheets’ dropdown, if the selected worksheet does not have 10 heading columns the worksheet will be rejected.


Error


When selecting a worksheet from the ‘worksheets’ dropdown, if the selected worksheet has already been saved as a style it will be rejected. Use the ‘destination styles’ or ‘source styles’ options in the ‘style’ section of the ribbon at the top of the bordereau converter.


Error


Version History

v 5.1.0 – Updated to latest version