TFPP Pipeline

Formula

=ANT.ACT.Live.TFPP_Pipeline()

Description

Deals in the Submission or Quoted stages of the underwriter workflow are considered for pipeline. Using this formula you can see a policy by policy breakdown of the deals which contribute to TFPP's pipeline figure.

For Non Pol Risk / Asset Backed / Energy Construction classes pipeline includes deals that bind on or after today and before the end of the month. You can get your pipeline by setting both the current_month_flag and inception_on_or_after_today_flag to 1.

For Pol Risk / Asset Backed / Energy Construction classes pipeline includes deals with an estimated month close of the current month. You can get your pipeline by setting the est_month_close to the current month.

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 Note: for premium to appear in the pipeline then the inception date must be on or after today (for non pol risk / asset backed / energy construction classes). 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`
`pipeline_uw_status`	Alias: Pipeline Underwriting Status The grouped underwriting status of the policy, this is either Submission or Quoted. TYPE: `str`
`est_month_close`	Alias: Estimated Month Close The estimated month close of the policy. This is used for pol risk, asset backed and energy construction classes. Note: for premium to be included in the pipeline for the classes above, the estimated month close needs to be the current month. TYPE: `str`
`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`
`inception_on_or_after_today_flag`	Alias: Inception On or After Today Flag A flag indicating that the policy will incept on or after the current date. To get pipeline premium for nor non pol risk, asset backed and energy construction classes this needs to be set to 1. TYPE: `int`
`tfpp_pipeline_premium_usd`	Alias: Pipeline Premium in USD The pipeline premium in usd. TYPE: `int`