TFPP Pipeline

Formula

=ANT.ACT.Live.TFPP_Pipeline()

Examples

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 IG, F3123, F2126, Allianz 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 Light Class 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 Master Class This class definition is used in MGUP Production meetings, similar to Major Class. TYPE: str
mgup_split_pillar	Alias: MGUP 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
S3123_class	Alias: S3123 Class Syndicate class definition used for S3123 reporting. This is a high level mapping of classes into the class groups used for S3123 & S2126 reporting. TYPE: str
guardrail_pillar	Alias: Guardrail Pillar This pillar definition is used for guardrail reporting per agreement with Blackstone. TYPE: str

PARAMETER	DESCRIPTION
mga_code	Alias: MGA Code 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. To see the mapping refer to ANT.ACT.Mappings.get_mga_mapping() TYPE: str
pillar	Alias: Pillar This pillar definition is consistent with the definition between the IG and TFP and should be used in most scenarios. 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
S3123_class	Alias: S3123 Class Syndicate class definition used for S3123 reporting. This is a high level mapping of classes into the class groups used for S3123 & S2126 reporting. TYPE: str
layer_id	Alias: Layer Id The Layer Id. TYPE: int
program_id	Alias: Program Id The Program Id. TYPE: int
layer_description	Alias: Layer Description A description of the layer. TYPE: str
program_name	Alias: Program Name The name of the program. TYPE: str
entity	Alias: Entity The Entity (FUL, FIID, FIBL). TYPE: str
broker_master_group	Alias: Broker Grouped 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: int
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 TYPE: str
domicile	Alias: Domicile The domicile of the risk. TYPE: str
zone	Alias: Zone The zone of the risk. TYPE: str
underwriter	Alias: Underwriter The underwriter of the policy. TYPE: str
master_uw_status	Alias: Master UW 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
xs_usd	Alias: Excess USD The excess (attachment point) of the layer in USD. TYPE: float
limit_usd	Alias: Limit USD The limit of the layer in USD. TYPE: float
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 UW 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: 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
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: bool
tfpp_pipeline_premium_usd	Alias: TFPP Pipeline Premium USD The pipeline premium in usd. TYPE: float
estimated_premium_usd	Alias: 100 Pct Pipeline Premium The estimated premium at 100 percent (100% pipeline premium) in USD. TYPE: float
pipeline_premium_current_and_future_months_usd	Alias: Future Pipeline Premium USD The pipeline premium for the current and future months in USD. TYPE: float
period_id	Alias: Period Id The Underwriting Year and Underwriting Month combined together as an interger in YYYYMM format. TYPE: int
quarter_id	Alias: Quarter Id The quarter id in numerical form (1, 2, 3, 4) TYPE: int
quarter	Alias: Quarter The quarter in short format (Qtr 1, Qtr 2, Qtr 3, Qtr 4). TYPE: str
month_id	Alias: Month Id The month id in numerical form (1, 2, 3, 4, 5, ..) TYPE: int
month_short	Alias: Month Short The short month description, e.g. Jan, Feb, Mar etc. TYPE: str
period_desc	Alias: Period Description The short period description, e.g. January 2025, February 2025, March 2025 etc TYPE: str
period_short_desc	Alias: Period Short Description The short period description, e.g. Jan 25, Feb 25, Mar 25 etc TYPE: str
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
current_year_flag	Alias: Current Year Flag A flag indicating the current year. These flags get updated daily with the pipeline. Use this flag to automatically get the data for the current year in your analytics / report. A value of 1 indicates the current year, 0 otherwise. 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
last_updt_ts	Alias: Last Updated Timestamp The timestamp the row was last updated by the pipeline. TYPE: datetime