Spreadsheet Manual - Motor Data Solutions
Contents
1. information is made available to Insurer A Insurer B has delegated some of the data collection and transmission to MDS Brokers and Policyholders but is using other means as well not shown e g a customer call centre and has asked for a copy of the client information and vehicle information to be sent to it for information purposes The Fleet Policyholders have chosen to operate in the following way Fleet 1 is using the MDS interactive web site to update its information on the MID Fleet 2 has chosen to ask MDS to update on its behalf by phoning or faxing in the information Fleet 3 has chosen to send a spreadsheet to MDS and thus to the MID Fleet 4 has chosen to integrate its existing Fleet Management System FMS with MDS s system supplying data to MDS and thus to the MID in a file To assist members of staff of Policyholders who have IT responsibility for the data transmission we have produced a standard spreadsheet upload interface to accept file uploads from their customers This information note refers to this spreadsheet submission method for Fleet 3 2 Gaining Access 2 1 Method To gain access to the MDS web site for sending information by spreadsheet to the MID policyholders need to apply for an upload account This will be created on request by your Broker or Insurer or if you have been directed to deal direct with MDS by emailing MDS spreadsheetrequest motordatasolutions co uk with your organisation2. 11 4 10 FRMEOPEICN Reg Istratiomindaicator sisi caia 11 4 11 nn o y AA 12 4 12 Multiple Policyholders Multiple Polici8sS ccoccoooncccnncccconcnnncononncnnnoononnconcnononncnnnnnnnos 12 4 13 Change Ory AP UE aa 12 1 Introduction In connection with the 4 EU Motor Insurance Directive Motor Data Solutions Ltd MDS have developed a vehicle data collection service for the Motor Insurance Database MID which is being rolled out to a number of Insurers and their clients from July 2002 This is a web based solution which collects data from electronic Asset Management Systems or from similar manual packages which are typically operated by Fleet Managers Transport Managers Risk Managers and others responsible for the day to day management of vehicle fleets There are various routes methods for this information to be sent to the MID The diagram below shows the routes for information submission to the MID Insurer A Insurer B N N N NS N Vehicle Data combined ae Vehicle amp Client Data with Policies N N Insurer A Vehicle amp Policy Data Insurer B Vehicle Data Interactive All Insurers have to offer their Policyholders the choice of how they operate either supplying directly to the Insurer or via the Internet to the MID Insurer A has delegated all the data collection and transmission to MDS Brokers and Policyholders Information entered by the users is sent directly to the MID Management
3. file in the correct format and has access to the Internet this data transmission method will work Q What vehicles should include A You should include all vehicles that you currently expect to drive under the Insurance Policy you have in place excluding plant and machinery If you wish to send Plant i e vehicles without Vehicle Registration Numbers you should insert the word Plant as the vehicle type These records will NOT be sent to the MID but will be included on Declarations sent by the Broker to your Insurer Q Is my data secure during transmission and after it is sent A All Internet links to MDS s system MIDAS are encrypted using 56 bit encryption based on the standard Verisign SSL method If your computer does not support this encryption please email us at support motordatasolutions co uk Q How do we remove a vehicle from MDS A There are two ways either simply omit it from the file of vehicles and the MDS system will remove it from the database by setting the Off Date to the date the file was received or send it with the Off Date filled in Q Can we set the Off Date to a future date A Yes Q When should start sending data Can test A The MDS web site is available now If you want to test your transmissions send an email to support motordatasolutions co uk with your organisation name and telephone number and we will set you up with an account Should you wish to have a d
4. s name and telephone number You will receive an email detailing The Policy number under which the policyholder is insured A World Wide Web URL to connect to e g www midasdirect com upload A user name A password gt gt See below for the process to use when you connect to this site 2 2 Downloading a template A template of the Excel spreadsheet to use is available from our website www motordatasolutions co uk template htm Mx TOR DATA SOLUTIONS Eight click the link below to download the MIDS example Excel file Please edit this fle to insert details of the vehicles on your fleet mas example xls After downloading the file it may be populated either manually or it may be created by importing a CSV file or fixed format file produced as a result of an extract from a Fleet Management System of some kind It is recommended that if vehicle details are already stored on an electronic system and include or can be made to include the essential details for sending to the MID e g Vehicle Registration and Date on Cover then Policyholders review whether it may not be better to transmit the details directly from the electronic system to MDS Please contact us for further details 3 Process 3 1 Introduction Spreadsheet files containing vehicle data records must be formatted and contain valid data as described in the section below Where this data is extracted from a Policyholder s system it is assume
5. Motor Insurance Database Phase II 4 EU Motor Insurance Directive Attended file transfer Specification Motor Insurance Database Phase Il Policyholder Client Spreadsheet Vehicle data Transmission User Manual Version 0 4 Draft August 2002 Change Control A a Sign Off 09 07 02 a Updated with FRI 18 07 02 red 03 Updated 4 8 2907 02 Jo o oo 04 5 Updated BOD DATA SOLUTIONS 2002 Motor Data Solutions Ltd Contents CO MIEN E A A cent cs Ne ees eee orake aaa 2 1 Nahi ge el Wot eana secre irae creer e een te ee eee eee 3 2 GAMING FCCC SS saaa a eM cc a hin cil eae ae ace halt tonto ene 4 AN INT OC aii 4 2 2 Downloading a template nai iaa a 4 3 PrOCOS S ornan a a Vode ash Situ Uae a a eainud eat oc ba eultgh 5 Sal jWMPOGUCOM iia 5 32 DIOS aaa oa 5 3 39 Messages YOU May TECOS imitar 7 4 Formattor Vehicle Data Upload S erena A E ee ier i elena ius 8 Ad OVENIC icc A ate E ds tai Riches INI A neice E 8 Az COMMON ASK QUESTION Susini avenue eee RIE Si sds Rade oO Sere 8 AS O O A N A ws N aN 10 ES A A A A iege eal 10 AS Malla Venice 1 VOCS uta A Wide DE O DOS NG 10 AG Make aid Model Valdano cda 11 4 7 Validation of Engine Size Derivative and SealS cooocccccncnccccnnccccnnoconnnononnnonannnnnnnnnnnnenononcnononos 11 4 8 Gross Laden Weight GVW Validation cccoonnconccccconcnnnccnonnnnnnnononncononononnnnnononancnnnonnnanenns 11 4 9 Cover omand Cover Oi tido dedunseod A
6. d that this has been suitably modified to produce the extract of the correct data items on a regular basis Where this data is held on manual or incompatible systems it is assumed that the Policyholder will put in place the regular processes and procedures to update the spreadsheet in accordance with the legislation i e on a weekly basis After transmission success failure codes are displayed by the MDS system to allow the end user to verify that data has been successfully transmitted or to address any issues with errors that are returned Note that the errors and warnings may be asynchronous and may not relate directly to the previous file transmission Your files are sent to your own folder on our site your data is only visible to you your Insurer and to the Motor Insurer Database 3 2 Steps Connect to the web site indicated in the email You will be presented with the following dialogue Enter your user name and password then click Login Enter your username Enter your password CE EA Enter the user name and the password you have been supplied with Spreadsheet Upload Logout Please specify a Spreadsheet file on your hard drive by browsing to it Select the Spreadsheet version you will be uploading failure to select the correct version will halt the loading process Excel 95 ver5 0 O Excel 97 2000 Select a Spreadsheet Upload file The screen changes to show the dialogue above Bro WSE Y
7. etails changed twice or more in the course of 24 hours only the most recent record will be extracted only one record per vehicle in each file The number of records in the file should correspond exactly to the number of vehicles on cover for the Policyholder Average transmission speeds of around 50 vehicle updates a second will be achievable in continuous transmissions You should therefore expect file transfers to take as long as can be calculated using this figure For example a 5 000 vehicle fleet will be transmitted in around 2 minutes and a 50 000 vehicle fleet in around 20 minutes Note that the connection between the spreadsheet uses Microsoft s ODBC linkage which requires that the data on the spreadsheet be held within a named area which we term vehicles and that the column headings are exactly what are specified in the sample above e g VAN Type etc Please do not therefore amend these headings or the definition of the area Name vehicles If you are experiencing difficulties loading your spreadsheet please email it to enquiries motordatasolutions co uk where we will attempt to rectify the problem for you If you wish to amend the vehicles Name definition please proceed as follows e Highlight the rows and columns that contain the data you wish to upload including the header row with the column names o VRN Type Make Model Derivative Engine Seats GVW CoverOn CoverOff FRI Click on Insert in the
8. ifferent username in future to send live data rather than test data you can always apply to us for a new location Please note that the MID itself is not live until September 2002 so your data will not be visible to the Police until then Q Pd like to be able to extract from my vehicle management system to a CSV file to then include in Excel but my software supplier is unable to make all the changes in time for me to send my data by the 20 January 2003 the date the legislation comes in to force What should do A It is a legal requirement that the data is sent by that date and then kept up to date MDS can provide you with manual work around solutions which we have made available to Brokers Please contact your broker for further information Q We don t want to amend our main system to send you the data directly but we can easily extract the information in to a spreadsheet and send it instead for the initial data load Once the information has been sent once can we then update it online A Yes Once you ve sent the data once let us know and we will then send you details of an account to use to log in to the Web Site to maintain your data manually Q Do I have to save my Excel spreadsheet as a CSV file in order to send it to the MID A No you can send your Excel spreadsheet in its entirety to the MID you can therefore use itas a handy way of storing all your insured vehicle and plant information Q What does the da
9. milar to the following should appear Logout D444 TRE Warning This Rbd exists tor another client Please ensure the WYRM Is entered correctly D444 TRE The following errors occurred Unknown Make Earth Mover FMSTHCA Warning This VRM exists for another client Please ensure the YEM is entered correctly P96FHAN The following errors occurred Unknown Type Bicycle You have amended your vehicle schedule as follows Inserted Updated Deleted 2 After sending a file you will NOT be automatically logged out from the web site but have to click on the Log Out link If you do not actively return to the site within 20 minutes you will be automatically logged out 3 3 Messages you may receive Errors are indicated to you straightaway typical errors are indicated in the screen shot above the meaning of these is e Warning This VRM exists for another client N B Warnings don t stop a vehicle being loaded Normally the Vehicle should only be registered and insured by one organisation at a time There are of course valid reasons for a vehicle to be insured twice for example if it is insured by a hirer and the hire company at the same However this is unusual so a warning is flagged The vehicle will however be added to the database e Error Unknown Make Unknown Type etc N B An error will stop a vehicle being loaded We would like to make the types make and model of vehicles as standardised as possible If
10. ou may choose from two types of Excel spreadsheet the older version Ver 5 0 or the newer version 97 2000 Click whichever radio button is most appropriate The default is Excel 97 2000 If you have other versions of Excel or are using other spreadsheet packages please contact us for details of how we can assist you Now click on the Browse button This will present you with a Microsoft Explorer like file selection screen which you can use to navigate to the folder where you have previously stored your Excel spreadsheet with the completed data Choose file Look ir oa Temp A A mds example History My Documents ERE My Computer E le name sal ad Files of type AI Files 3 z M y H oi g tk Pl After selecting the appropriate file click on the Oper button and the Web page will change to appear similar to the following file CA Templimnds_example xls Bre A very common error can occur if you are simultaneously editing the spreadsheet in Excel and have not closed the file this appears as follows Logout File Upload Error GAIMAN TAC PTA tmp An erroroccurred while attempting to upload the file specified Please ensure that the spreadsheet version was correctly specified and the file 1s currently not in use Back Go to your Excel session and Save and Close the open spreadsheet then return to your Browser and click on the Back link to try again A screen si
11. se and all spaces removed before being stored 4 5 Valid Vehicle Types Plant note that these vehicles will NOT be forwarded on to the MID but will be retained by MDS Bus Car Van Truck Trade Plate Other 4 6 Make and Model validation The list of valid make and model combinations is updated from time to time by MDS based on currently available data e g Glass s guide To reduce errors through mistyping a warning will be generated if the make or model is not known to system a blank value will be inserted in to the database in these cases MDS will be aware of any Makes and Models not defined and will by investigating the names supplied update their lists If the Make and Model have been misspelled then it is up to the Policyholder to amend their system If they have been spelled correctly but not recognised by the MDS system they should continue to be re submitted and will eventually be recognised and updated correctly If the make and model are not known by the policyholder these fields can be left blank to suppress any validation Note that only the VRM and On Date are mandatory 4 7 Validation of Engine Size Derivative and Seats These items are entirely optional and are subject to guidance from the MIIC about their values and contents They may safely be ignored at present We shall contact all soreadsheet users by email in the event of any change 4 8 Gross Laden Weight GVW Validation GVW should only be en
12. ta transmission and collection look like in overview A Please refer to the diagram below Daily at 3 00 am Daily whenever Data entered by end users as part of Excel normal fleet management Browser activities 4 3 Record Format An example of the spreadsheet file that must be submitted is given below A A A PESA A AA IP PS Y VAN Type Make Model Derivative Engine Seats GVW Covern Cover FRI 2 AAD2DEF Car Toyota Yaris 0107 02 3 AB02DEF Truck otania 1 57 010702 4 ACO2DEF Trade Plate 010702 5 ADO2DEF Other Other Other 01 07 02 El AEDZDEF Bus 01 07 02 The record format for each vehicle submitted to MDS must be structured as follows Field Name Validation Comments Chars CoverOn___ 8__ _CCYYMMDD_ Cover Off 8 _ CCYYMMDD Must be F for foreign vehicles blank for UK 4 4 Valid VRN formats The following valid VRN formats will be accepted for UK registered vehicles No more than 7 characters No characters other than letters or digits No more than 6 consecutive digits No more than 4 consecutive letters No sequence that is not letters digits letters or digits letters digits No letters only All letters are A Z or a z numbers 0 9 Detailed format characteristics such as the use of Q Z O and U for registration year letters or strict adherence to 51 02 52 etc will not be checked Spaces may be used The VRM will converted to upperca
13. tered for Trucks and should be formatted as follows 1T 3 57 17 5T 35T 40T etc Please do not use Kilograms i e 5900 Kg or any other weight measure 4 9 Cover On and Cover Off These are the dates when cover commenced and when it ended the end date can be in the future The following rules apply to these dates e They must be in the format dd mm yy e If the Or date is before the start of the current policy it will be stored within MIDAS but on transmission to the MID it will be set to be the start date of the current policy e If the Off date is after the next renewal date of the current policy it will be stored within MIDAS but on transmission to the MID it will be set to be the end date of the current policy 4 10FRI Foreign Registration Indicator This must be either F for a foreign registered vehicle or anything else for an UK registered vehicle or U if wished F will suppress the registration format checking as well as against the DVLA database 4 11 File Format The individual records specified above must be included within a standard Excel file format As many records may be transmitted as are desired No more than one transmission per day should be sent if all changes are to be registered If by accident a second file overwrites the first the information should be successfully updated but any short term intermediate changes might be lost If a particular vehicle has had ts d
14. top menu bar Move the mouse cursor down to Name in the drop down menu Click on Define in the pop up menu that appears A small window should appear entitled Define Name Type in the word vehicles Click the Add button Click the OK button Finally save the spreadsheet 4 12Multiple Policyholders Multiple Policies If the transmitting system holds details for multiple vehicle fleets these will be treated as different sources of data You should apply to your broker or to MDS if you are not using a broker for multiple login s with their own usernames and passwords If you operate on behalf of a fleet organisation that has different policies for different parts of their fleet for example if cars are insured with Insurer A and vans with Insurer B but these are all mixed together in the Policyholder s records you will need to identify these and send them separately to two separate spreadsheets 4 13 Change of VRM If a record is sent to MDS with a VRM sent in error and subsequently amended then the record will be held with the erroneous VRM To delete this record please contact the MDS helpdesk Support motordatasolutions co uk stating the Policy Number the Insurer your User Name and the Vehicle registration to be deleted
15. you receive an error as above either check the spelling or the data choose a different value or if you are certain it is correct leave it and we will update our system usually within 24 hours to accept the new data value e Inserted this is a new vehicle e Updated this is the normal case and can be new data for an existing vehicle or changed data e Deleted the vehicle has been removed from cover but will still be held on the system for historical purposes 4 Format for Vehicle Data Uploads 4 1 Overview This format is termed the Compare Amend File Format Attended and Unattended File Transfer submission method It is valid for all data uploads to the MDS system which are transmitted using spreadsheets 4 2 Commonly Asked Questions Q What is Compare and Amend A Compare and Amend is a technique recommended by the MIIC the body managing the overall Motor Insurance Database project which allows end users i e policyholders to send their complete vehicle schedule time after time without having to identify the changes This should simplify life for these users for most policyholders it will be simple to create a spreadsheet once and then maintain it as a list of vehicles on cover and send it as and when it changes to the MDS web site Q Is there a need for a digital certificate or special software to be installed on my system to send this data A No as long as your system can produce a
Download Pdf Manuals
Related Search