TFPP Bound Current Year

Formula

=ANT.ACT.Live.TFPP_Bound()

Description

This formula allows you to see your bound figures for the current underwriting year.

To match with the bound YTD figure shown in TFPP set the is_this_month_or_before_flag to 1.

To see your bound deals from the last 7 days set the bound_in_last_7_days to 1.

Warning

This dataset is based on the daily Prequel snapshots and is considered the most up-to-date. The data underlying this dataset is not validated between days and can be subject to changes depending on the data being entered into Prequel. As such, this dataset should not be used for external reporting because it is not signed off / validated.

Daily movements in this dataset will not be explained by the analytics team.

Tip

The best practise for once you have used this formula is to create a table from the results and use this table within your analysis. This ensures that your data changes only when you want it to and you are not dependent on the table columns being in the same order each exercise.

1. Use the formula to get the data you need

2. Select all the data that the formula returns (including headings)

3. Go to Data >> Get Data >> From Other Sources >> From Table/Range

4. Right click on the 'Array' name and rename it to something sensible

5. Check to see if the first row has been promoted to headers, this is usually done automatically but
if not  on the "Home" tab you can select "Use the first row as headers".

6. If you are seeing errors in the layer_id or program_id columns then you need to change the data type.
To do this go to the Applied Steps on the right hand side, choose 'Changed Type' and change
{'layer_id', Int.64.Type} to {'layer_id', type text} and {'program_id', Int.64.Type} to {'program_id', type text}.

7. Click Close & Load.

This will create a table in your worksheet. If the data changes you will need to refresh the table by going to Data >> Refresh All or Data >> Queries & Connections >> right click on your table and refresh.

Outputs

PARAMETER	DESCRIPTION
snapshot_id	Alias: Snapshot ID The snapshot id TYPE: int
snapshot_date	Alias: Snapshot Date The snapshot date TYPE: date
master_carrier_grouping	Alias: Master Carrier Grouping The capacity provider. This can be the IG, F3123, Consortium, Allianz or Capital Provider. TYPE: str
business_segment	Alias: Business Segment This is the IG's mapping to business segment (INS / REIN). TYPE: str
mgup_split	Alias: MGUP Split This is the full list of MGUP master and MGUP light classes combined together separated by a hyphen. TYPE: str
mgup_split_light	Alias: MGUP Split Light This class definition is used in MGUP Production meetings, similar to Minor Class. It will split, e.g. P,C&E MGUP class into Construction, Non-Renewable, Renewables, Property and D&F. TYPE: str
mgup_split_master	Alias: MGUP Split Master This class definition is used in MGUP Production meetings, similar to Major Class. TYPE: str
mgup_split_pillar	Alias: MGUP Split Pillar This pillar definition is used in MGUP Production meetings. The main difference is the how RI classes are mapped from Speciality / Bespoke to Reinsurance and how some Reinsurance classes are mapped back to Specialty / Bespoke. Unless you want to tie up with MGUP pillar definitions, you should not use this pillar. TYPE: str
mgup_reporting	Alias: MGUP Reporting Class This is the MGUP Master class broken down into the main reporting classes. TYPE: str

PARAMETER	DESCRIPTION
mga_code	Alias: MGA Mapping This is a high level mapping of MGAs / Third Party binders based on their Layer Id. If the value is Fidelis this means that the business is Fidelis sourced, an example of a MGA would be Navium. This mapping gets updated monthly and the mapping is owned by the DA team within Actuarial. To see the mapping refer to ANT.ACT.Mappings.get_mga_mapping() TYPE: str
pillar	Alias: Pillar The pillar of the policy. TYPE: str
class_id	Alias: Class Id The 3 letter class code. TYPE: str
major_class	Alias: Major Class This is consistent with the IG definition of major classes. TYPE: str
minor_class	Alias: Minor Class This is consistent with the IG definition of minor classes. TYPE: str
layer_id	Alias: Layer Id The Layer Id. TYPE: int
program_id	Alias: Program Id The Program Id. TYPE: int
entity	Alias: Entity The Entity (FUL, FIID, FIBL). TYPE: str
broker_master_group	Alias: Broker This is a high level mapping of brokers. Brokers have been grouped together for reporting purposes from their lowest granularity. For example, all GC and Marsh broker names have been grouped together into GC Marsh. To see the mapping refer to ANT.ACT.Mappings.get_broker_mapping() TYPE: str
is_pinewalk_flag	Alias: Is Pinewalk Flag You can use this flag to separate Pine Walk business from the rest of TFP. TYPE: bool
inception	Alias: Inception Date The inception date of the policy TYPE: date
expiry	Alias: Expiry Date The expiry date of the policy TYPE: date
master_placing_basis	Alias: Master Placing Basis This is the high level grouping of placing basis into Open Market, Binder, Lineslip, Consortium, Declaration and Capital Provider TYPE: str
uw_year	Alias: Underwriting Year The Underwriting Year of the policy. TYPE: int
uw_month	Alias: Underwriting Month The Underwriting Month of the policy. TYPE: int
business_type	Alias: Business Type Whether the policy is new or renewal. TYPE: str
parent_client	Alias: Parent Client The high level client grouping TYPE: str
assured	Alias: Assured The name of the assured TYPE: str
uw_status	Alias: Underwriting Status The underwriting status of the policy as defined in Prequel. TYPE: str
master_uw_status	Alias: Master Underwriting Status This is a high level grouping of the underwriting status. The statuses have been grouped into Declined, Submission, Quoted, NTU, Bound and Bound (Expired). TYPE: str
tfpp_gross_gg_signed_premium_usd	Alias: Gross Signed Premium in USD Gross of reinsurance, gross of acquisition costs signed premium in USD consistent with how it is showed in the weekly TFPP reporting. This column calculates the premium on Open Market, Binder, Lineslip and Consortium business. It uses the master binder / lineslip EPI premium and does not include individual declarations (unless Open Market Consortium business). TYPE: int
counts_date	Alias: Counts Date The date which the policy first counted as bound in Prequel. TYPE: int
quarter	Alias: Quarter The quarter in short format (Qtr 1, Qtr 2, Qtr 3, Qtr 4). TYPE: str
month_short	Alias: Month Short The short month description, e.g. Jan, Feb, Mar etc. TYPE: str
bound_in_last_7_days	Alias: Bound in last 7 days Flag A flag indicating if the policy was bound in the last 7 days. TYPE: int
current_month_flag	Alias: Current Month Flag A flag indicating the current month. These flags get updated daily with the pipeline. Use this flag to automatically get the data for the current month in your analytics / report. A value of 1 indicates the current month, 0 otherwise. Note: This flag only is 1 for the current month in the current year only. If you need the current month in previous years you can use the Current Month Last Year Flag or Current Month All Year Flag. TYPE: int
current_quarter_flag	Alias: Current Quarter Flag A flag indicating the current quarter. These flags get updated daily with the pipeline. Use this flag to automatically get the data for the current quarter in your analytics / report. A value of 1 indicates the current quarter, 0 otherwise. Note: This flag only is 1 for the current quarter in the current year only. If you need the current quarter in previous years you can use the Current Quarter Last Year Flag or Current Quarter All Year Flag. TYPE: int
last_month_flag	Alias: Last Month Flag A flag indicating the previous month. These flags get updated daily with the pipeline. Use this flag to automatically get the data for the previous month in your analytics / report. A value of 1 indicates the previous month, 0 otherwise. Note: This flag only is 1 for the previous month in the current year only. If you need the previous month in previous years you can use the Last Month All Year Flag. TYPE: int
last_quarter_flag	Alias: Last Quarter Flag A flag indicating the last quarter. These flags get updated daily with the pipeline. Use this flag to automatically get the data for the last quarter in your analytics / report. A value of 1 indicates the last quarter, 0 otherwise. Note: This flag only is 1 for the last quarter in the current year only. If you need the last quarter in previous years you can use the Last Quarter All Year Flag. TYPE: int
is_this_month_or_before_flag	Alias: Is this month or before Flag A flag indicating that the policy incepted in, or before the current month. This calculates YTD policies incepting up to the current month. This flag is used in the TFP production meetings for YTD. These flags get updated daily with the pipeline. Use this flag to automatically get the YTD data in your analytics / report. A value of 1 indicates data up to and including this month, 0 otherwise. TYPE: int