Ratebook data specification
Introduction
This document outlines the rate and vehicle data specification for the Gensen whole life cost (“WLC”) calculators and salary sacrifice (“SS”) quotation platform, and sets out the requirements for ratebooks, contract profiles and images.
Ratebooks
Ratebooks, or contract profiles, are supplied as CSV files that conform with RFC 4180. To summarise and clarify:
-
Files must include a header line that denotes the field headings as defined below
-
The character encoding used is UTF-8
-
Separator character: ,
-
Quote character: ”
It is assumed that each contract profile supplied will contain rates for the same range of vehicles. For example, if a contract profile 3-0-33 / 20K miles contains vehicles A, B, C, then all contract profiles should contain vehicles A, B, C.
Rates can be broken into separate CSV files, for example a rate book for 3-0-33, 20,000 miles per annum, or stored on one large CSV file, for example a contract profile for 3-0-33 inclusive of all annual mileage parameters, as is most convenient for you to export.
Each row in each CSV file after the header line defines a rate for one derivative/vehicle.
Vehicles should be the latest model-year to avoid duplication. Some information might not necessarily be included in a ratebook or contract profile, for example, vehicle length or weight, and may need to be sourced separately from CAP.
Default rate book profiles can be set at a client level, with the standard default rate book profile being 3-0-33 / 20K miles for the WLC calculators, and 1-0-35 / 10K miles for the SS quotation platform.
Many columns are marked as optional but are recommended for the intended context (salary sacrifice, cars, vans, and so on). Optional fields are marked inline in the table below. Residual value and purchase price requirements are described in note 8.
Column reference
Field heading 9 | Data type | Length / size | Description | Example(s) |
|---|---|---|---|---|
| Vehicle specific | ||||
accelerationoptional | number | empty | Time in seconds to reach 62 mph. Defined by CAP as "0 to 62 mph (secs)". | 8.8 | |
all_electric_range 5 | number | empty | The all-electric range expressed in miles of a PHEV in standard configuration. This is the total distance that can be driven electrically, before the engine turns on for the first time. Defined by CAP as "WLTP - AER (miles) - Comb". | 31.5 | |
all_electric_range_max 5optional | number | empty | The all-electric range expressed in miles of a PHEV carrying maximum equipment. This is the total distance that can be driven electrically, before the engine turns on for the first time. Defined by CAP as "WLTP - AER (miles) - Comb - TEH". | 31.1 | |
all_electric_range_min 5 | number | empty | The all-electric range expressed in miles of a PHEV carrying minimum equipment. This is the total distance that can be driven electrically, before the engine turns on for the first time. Defined by CAP as "WLTP - AER (miles) - Comb - TEL". | 31.9 | |
battery_typeoptional | string | empty | The battery type included within a BEV or PHEV, e.g. lithium-ion. Defined by CAP as "Battery Type". | "Lithium-ion" | |
bhp | number | Power output in BHP. | 120 | |
bodytype | string | Vehicle body type. | "Hatchback" | |
cap_code | string | 35 | Alphanumeric CAP code. | "ANY514 3WXYZ" "ABA914CM83HPTM" |
cap_id | string | 35 | Numeric CAP ID. | "46895" |
co2_comb 2 | integer | empty | Carbon dioxide emissions in g/km for an ICE car (inc self charging and mild hybrid) in standard configuration. Defined by CAP as "WLTP - CO2 (g/km) - Comb". | 122 | |
co2_comb_max 2optional | integer | empty | Carbon dioxide emissions in g/km for an ICE car (inc self charging and mild hybrid) in its least efficient configuration. Defined by CAP as "WLTP - CO2 (g/km) - Comb - TEH". | 120 | |
co2_comb_min 2 | integer | empty | Carbon dioxide emissions in g/km for an ICE car (inc self charging and mild hybrid) in its most efficient configuration. Defined by CAP as "WLTP - CO2 (g/km) - Comb - TEL". | 122 | |
co2_weighted_comb 2optional | integer | empty | Carbon dioxide emissions in g/km for a PHEV in standard configuration based on regular usage of both electrical and combustion elements. Defined by CAP as "WLTP - CO2 Weighted (g/km) - Comb". | 42 | |
co2_weighted_comb_max 2optional | integer | empty | Carbon dioxide emissions in g/km for a PHEV vehicle in its least efficient configuration based on regular usage of both electrical and combustion elements. Defined by CAP as "WLTP - CO2 Weighted (g/km) - Comb - TEH". | 42 | |
co2_weighted_comb_min 2optional | integer | empty | Carbon dioxide emissions in g/km for a PHEV vehicle in its most efficient configuration based on regular usage of both electrical and combustion elements. Defined by CAP as "WLTP - CO2 Weighted (g/km) - Comb - TEL". | 42 | |
combined_mpg_wltp 3 4 | number | empty | Fuel economy in miles per gallon for an ICE car (inc self charging and mild hybrid) in standard configuration. Defined by CAP as "WLTP - MPG - Comb". | 55.1 | |
combined_mpg_wltp_max 3 4optional | number | empty | Fuel economy in miles per gallon for an ICE car (inc self charging and mild hybrid) in its least efficient configuration. Defined by CAP as "WLTP - MPG - Comb - TEH". | 52.1 | |
combined_mpg_wltp_min 3 4 | number | empty | Fuel economy in miles per gallon for an ICE car (inc self charging and mild hybrid) in its most efficient configuration. Defined by CAP as "WLTP - MPG - Comb - TEL". | 57.2 | |
delivery_dateoptional | date | The proposed delivery date for specials in yyyy-mm format. | 2023-02 | |
derivative 1 | string | 100 | Derivative identifier. | "2.0 EcoBoost Titanium 5dr Powershift" |
dim_height | integer | empty | Height in mm. | 1709 | |
dim_length | integer | empty | Length in mm. | 4819 | |
dim_width | integer | empty | Width in mm. | 1884 | |
engine_size | number | empty | Engine size in millilitres. | 1984 | |
equivalent_all_electric_range 5 | number | empty | The equivalent all-electric range expressed in miles of a PHEV in standard configuration. This refers to the portion of the specific test cycle driven using only the battery power and which is the primary measure for tax purposes. Defined by CAP as "WLTP - Equivalent AER (miles) - Comb". | 30.5 | |
equivalent_all_electric_range_max 5optional | number | empty | The equivalent all-electric range expressed in miles of a PHEV carrying maximum equipment. This refers to the portion of the specific test cycle driven using only the battery power and which is the primary measure for tax purposes. Defined by CAP as "WLTP - Equivalent AER (miles) - Comb - TEH". | 30.1 | |
equivalent_all_electric_range_min 5 | number | empty | The equivalent all-electric range expressed in miles of a PHEV carrying minimum equipment. This refers to the portion of the specific test cycle driven using only the battery power and which is the primary measure for tax purposes. Defined by CAP as "WLTP - Equivalent AER (miles) - Comb - TEL". | 30.9 | |
fuel_type | "Diesel" | "Petrol" | "HybridDieselElectric" | "HybridPetrolElectric" | "HybridDieselPlugin" | "HybridPetrolPlugin" | "Electric" | "ElectricExtendedRange" | "FlexiblePetrolGas" | "Lpg" | Engine fuel type. CAP changed some fuel type descriptions in January 2024, so your mappings may need to change to match our specification. Parallel and Series PHEV descriptions should be mapped as described in our addendum. | "Petrol" | |
grade | string | empty | Manufacturer's model grade. | "Titanium" | |
insurance_groupoptional | integer | empty | The overall insurance rating for a car. Defined by CAP as "Insurance Group 1 - 50 Effective January 07". | 50 | |
make 1 | string | Manufacturer name. | "Ford" | |
model 1 | string | Car name. | "Focus" | |
model_year | string | Model year. Fractional years represented using a decimal point. | "2021.5" | |
ncap_ratingoptional | integer | empty | The overall safety rating for a car. Defined by CAP as "NCAP Overall Rating - Effective February 09". Value should be empty if no rating available. | 4 | |
num_doorsoptional | integer | Number of doors. | 5 | |
num_seatsoptional | integer | empty | Number of seats. | 4 | |
p11d_value | number | List price. | 21840.99 | |
pure_electric_range 5 | number | empty | The maximum range expressed in miles of a BEV in standard configuration. Defined by CAP as "WLTP - Pure Electric Range (miles) Comb". | 225.5 | |
pure_electric_range_max 5optional | number | empty | The maximum range expressed in miles of a BEV carrying maximum equipment. Defined by CAP as "WLTP - Pure Electric Range (miles) - Comb - TEH". | 225.1 | |
pure_electric_range_min 5optional | number | empty | The maximum range expressed in miles of a BEV carrying minimum equipment. Defined by CAP as "WLTP - Pure Electric Range (miles) - Comb - TEL". | 225.9 | |
rankoptional | number | empty | Numerical ranking to prioritise stock cars and special offers, where lower numbers denote higher priority. | 1 | |
rde_certification_level 7 | "RDE 1" | "RDE 2" | empty | Confirmation of the Real Driving Emissions test certification, which will determine whether the diesel supplement is applied to a diesel car. Defined by CAP as "RDE Certification Level". | "RDE 2" | |
sales_categoryoptional | string | empty "Stock" | "Pipeline" | "Special" | empty | The proposed sales category used to identify stock cars and/or special offers. Can be left empty on non-salary sacrifice platforms. | "Stock" | |
standard_emissionsoptional | string | empty | The official EU emissions standard under which the vehicle was type approved. The value will be in the format "Euro [number][sub-designation]". This column is required to apply PHEV easement correctly, if omitted we will assume no easement applies. | "Euro 6d-ISC-FCM" "Euro 6e" "Euro 6e-bis" | |
tag_lineoptional | string | empty | 255 | A short promotional message to be posted with each stock car or special offer. We may display where appropriate and as space allows. Although we accept up to 255 characters, we recommend keeping this under 35 characters for optimal display. | "In stock" "Available now" "Hot deal" |
top_speedoptional | integer | empty | Maximum speed for a car in miles per hour. Defined by CAP as "Top Speed". | 70 | |
transmission | "Automatic" | "Manual" | Type of transmission. | "Automatic" | |
vehicle_homologationoptional | string | empty | EU vehicle homologation class, such as M1, M2, M3, N1, N2. Defaults to "M1" (passenger car) if not provided. | "M1" | |
vehicle_id | string | 255 | LeaseCo's own vehicle identifier. | "ANY12345" |
weightoptional | integer | empty | Gross vehicle weight in kilograms. | 2450 | |
weighted_mpg_wltp 3 4optional | number | empty | Weighted average fuel consumption in miles per gallon for a PHEV in standard configuration based on regular usage of both electrical and combustion elements. Defined by CAP as "WLTP - FC Weighted (mpg) - Comb". | 55.0 | |
weighted_mpg_wltp_max 3 4optional | number | empty | Weighted average fuel consumption in miles per gallon for a PHEV in its least efficient configuration based on regular usage of both electrical and combustion elements. Defined by CAP as "WLTP - FC Weighted (mpg) - Comb - TEH". | 52.1 | |
weighted_mpg_wltp_min 3 4optional | number | empty | Weighted average fuel consumption in miles per gallon for a PHEV in its most efficient configuration based on regular usage of both electrical and combustion elements. Defined by CAP as "WLTP - FC Weighted (mpg) - Comb - TEL". | 57.2 | |
wltp_ec_comb 6optional | number | empty | The electrical consumption, expressed in miles per kWh, for a BEV or PHEV with a specified configuration. Defined by CAP as "WLTP - EC (miles/kWh) - Comb". | 3.4 | |
wltp_ec_comb_max 6optional | number | empty | The electrical consumption, expressed in miles per kWh, for a BEV or PHEV carrying maximum equipment. Defined by CAP as "WLTP - EC (miles/kWh) - Comb - TEH". | 3.8 | |
wltp_ec_comb_min 6optional | number | empty | The electrical consumption, expressed in miles per kWh, for a BEV or PHEV carrying minimum equipment. Defined by CAP as "WLTP - EC (miles/kWh) - Comb - TEL". | 3.6 | |
| Ratebook specific | ||||
advance_payment | integer | Number of months’ payments to be paid up front | 3 | |
contract_mileage_total | integer | Contract mileage over whole lease cycle | 60000 | |
monthly_payments | integer | Number of consecutive monthly payments to be made after the advance payment | 33 | |
periodic_finance_payment | number | Monthly rental payment in GBP (ex VAT) | 325.50 | |
periodic_service_payment | number | Monthly maintenance payment in GBP (ex VAT) | 18.21 | |
purchase_price 8 | number | empty | LeaseCo’s VAT inclusive purchase price for vehicle in GBP. Purchase price must not include first year VED or registration fee but should include delivery costs, factory and dealer options. The purchase price should be provided net of any plug-in grant for eligible vehicles | 19850.99 | |
rate_id | string | LeaseCo's own rate identifier, used for traceability | "ABCDE12345" | |
residual_value 8 | number | empty | Estimated VAT exclusive residual value or sale proceeds for vehicle in GBP (used for lease or buy comparisons) | 12385.99 | |
term_months | integer | The number of months in the contract | 36 | |
Car-specific columns
alternative_fuel_qualifying, battery_capacity, connector_type, max_charge_rate
Van-specific columns
load_height, load_length, load_space, load_width, max_payload_weight, max_towing_weight, min_kerb_weight, relief_category_code, relief_category_desc, wheelbase
Salary sacrifice-specific columns
delivery_date, rank, sales_category, tag_line
Additional notes
1. Make, model and derivative must be unique for each
vehicle_id. For example, this is not allowed:vehicle_id make model derivative 100 Ford Focus Titanium XYZ 101 Ford Focus Titanium XYZ These vehicles might differ in some option or attribute but we don’t handle importing this situation. However, this would be allowed:
vehicle_id make model derivative 100 Ford Focus Titanium XYZ Red 101 Ford Focus Titanium XYZ Blue This caution also applies to model year variations. Although it is possible to include multiple model year variations of a car, this should be reflected in the vehicle_id and derivative columns.
2. For a car powered by an internal combustion engine, we will use the following fields in order of preference:
co2_comb_max(only for salary sacrifice quotation platform)co2_combco2_comb_minco2_comb_max
The order of preference for a plug-in hybrid electric vehicle (“PHEV”) is:
co2_weighted_comb_max(only for salary sacrifice quotation platform)co2_weighted_combco2_weighted_comb_minco2_weighted_comb_max
3. For a car powered by an internal combustion engine, we will use the following fields in order of preference:
combined_mpg_wltp_max(only for salary sacrifice quotation platform)combined_mpg_wltpcombined_mpg_wltp_mincombined_mpg_wltp_max
The order of preference for a plug-in hybrid electric vehicle (“PHEV”) is:
weighted_mpg_max(only for salary sacrifice quotation platform)weighted_mpgweighted_mpg_minweighted_mpg_max
4. If all mpg values are missing then the vehicle will be rejected, unless it is a battery electric vehicle. In some cases, applications may not use mpg or fuel cost calculations, in which case this validation may be switched off entirely.
5. The order of preference for a plug-in hybrid electric vehicle (“PHEV”) is:
equivalent_all_electric_range_max(only for salary sacrifice quotation platform)equivalent_all_electric_rangeequivalent_all_electric_range_minequivalent_all_electric_range_maxall_electric_range_max(only for salary sacrifice quotation platform)all_electric_rangeall_electric_range_minall_electric_range_max
The order of preference for a battery electric vehicle (“BEV”) is:
pure_electric_range_max(only for salary sacrifice quotation platform)pure_electric_rangepure_electric_range_minpure_electric_range_max
6. In order of preference, we will use:
wltp_ec_comb_max(only for salary sacrifice quotation platform)wltp_ec_combwltp_ec_comb_minwltp_ec_comb_max
7. Diesel supplement and alternative fuel qualifying
Following consultation with HMRC, the BVRLA published updated guidance on 18 February 2020 confirming that once a mild hybrid diesel has been reclassified as alternative fuel qualifying, it will not be subject to the 4% diesel supplement, even if it is not RDE 2 qualifying. Accordingly, within Gensen the 4% diesel supplement will not be applied if a diesel car is deemed to be either:
- RDE 2 qualifying; and/or
- alternative fuel qualifying
8. Residual value and purchase price
Residual values and purchase prices are only required for the WLC calculators; if the rate book is provided by a broker these fields may be left blank and we will apply the residual value and purchase price when importing the rate book.
For the SS quotation platform this data is not required but the blank fields should be retained within the rate book.
9. If the export method for your ratebook excessively limits the number of characters you can use for column names, see the following reference for a workaround: