Trades | Adoro bi

FX & Loan Trades

A summary of the load process for fx and loan trades.

Step 1

Load trades from Calypso.

Criteria: Has had activity in the last 5 days and Products matching:

Loan Trades
‘SimpleMM’, ‘Cash’, ‘CallNotice’, ‘Bond’, ‘CapFloor’,’Swap’

FX Trades
‘FX’, ‘FXSwap’, ‘FXOption’, ‘FXOptionForward’, ‘FXForward’, ‘FXTakeUp’

View SQL1

View SQL2

Loan Trades

SELECT
	distinct cast(tr.trade_id as numeric) as trade_id,
	case
		when tr.bundle_id = 0 then null
		else cast(tr.bundle_id as numeric)
	end as bundle_id,
	cast(od.desk_id as numeric) as desk_id,
	cast(tr.book_id as numeric) as book_id,
	cast(tr.product_id as numeric) as product_id,
	cast(tr.cpty_id as numeric) as cpty_id,
	pd.product_type,
	tr.trade_date_time,
	tr.trade_currency as trade_ccy,
	case
		pd.product_type
		when 'SimpleMM' then smm.principal_amount
		when 'Cash' then cas.principal_amount
		when 'CallNotice' then cno.principal_amount
		when 'CapFloor' then cf.principal_amount
		when 'Bond' then pb.total_issued
	end as principal_amt,
	tr.trade_price,
	tr.neg_price_type as final_price_type,
	tr.neg_trade_price as final_price,
	tr.trade_status,
	tr.settlement_date as settle_date,
	tr.entered_user,
	str_replace(rtrim(tr.external_reference), '', null) as external_ref,
	coalesce(kos.strategy, kdp.deposit_product) as strategy,
	hr.hedge_relationship_id as hedge_id,
	hr.name as hedge_name,
	case
		when ht.hedge_relationship_id is not null then case
			when pd.product_type in ('SimpleMM', 'Cash', 'CallNotice') then 'Hedged'
			when pd.product_type = 'Swap' then 'Hedging'
		end
	end hedge_type,
	convert(
		datetime,
		convert(varchar(10), hr.effective_from, 103),
		103
	) as hedge_effective_from,
	convert(
		datetime,
		convert(varchar(10), hr.effective_to, 103),
		103
	) as hedge_effective_to,
	convert(datetime, ea.attr_value, 103) as retro_date
FROM
	calypso.trade tr
	INNER JOIN calypso.product_desc pd ON tr.product_id = pd.product_id
	LEFT JOIN calypso.product_simple_mm smm ON tr.product_id = smm.product_id
	LEFT JOIN calypso.product_call_not cno ON pd.product_id = cno.product_id
	LEFT JOIN calypso.product_cash cas ON pd.product_id = cas.product_id
	LEFT JOIN calypso.product_bond pb ON pd.product_id = pb.product_id
	LEFT JOIN calypso.product_cap_floor cf ON pd.product_id = cf.product_id
	LEFT JOIN calypso.hedge_relationship_trade ht ON tr.trade_id = ht.trade_id
	LEFT JOIN calypso.hedge_relationship hr ON ht.hedge_relationship_id = hr.hedge_relationship_id
	LEFT JOIN calypso.entity_attributes ea ON hr.hedge_relationship_id = ea.entity_id
	AND ea.attr_name = 'Retro_Start (dd/mm/yyyy)'
	LEFT JOIN (
		SELECT
			book_id,
			cast(attribute_value as numeric) as desk_id
		FROM
			calypso.book_attr_value
		WHERE
			attribute_name = 'BusinessUnit'
	) od ON tr.book_id = od.book_id
	LEFT JOIN (
		SELECT
			trade_id,
			keyword_value as strategy
		FROM
			calypso.trade_keyword
		WHERE
			keyword_name = 'FXOption_Strategy'
	) kos ON tr.trade_id = kos.trade_id
	LEFT JOIN (
		SELECT
			trade_id,
			keyword_value as deposit_product
		FROM
			calypso.trade_keyword
		WHERE
			keyword_name = 'Deposit_Product'
	) kdp ON tr.trade_id = kdp.trade_id
WHERE
	pd.product_type in (
		'SimpleMM',
		'Cash',
		'CallNotice',
		'Bond',
		'CapFloor',
		'Swap'
	)
	and tr.trade_id in (
		SELECT
			distinct entity_id
		FROM
			calypso.bo_audit
		WHERE
			entity_class_name = 'Trade'

FX Trades

SELECT
	distinct cast(tr.trade_id as numeric) as trade_id,
	case
		when tr.bundle_id = 0 then null
		else cast(tr.bundle_id as numeric)
	end as bundle_id,
	cast(od.desk_id as numeric) as desk_id,
	cast(tr.book_id as numeric) as book_id,
	cast(tr.product_id as numeric) as product_id,
	cast(tr.cpty_id as numeric) as cpty_id,
	case
		when pd.product_type = 'FX' then 'FXSpot'
		else pd.product_type
	end as product_type,
	tr.trade_date_time,
	tr.trade_currency as trade_ccy,
	case
		fo.option_side
		when 'BUY' then 'Buy'
		when 'SELL' then 'Sell'
	end as principal_dir,
	case
		when pd.product_sub_type <> 'DIGITAL' then tr.quantity
	end as principal_amt,
	case
		fo.option_side
		when 'BUY' then 'Sell'
		when 'SELL' then 'Buy'
	end as quoting_dir,
	case
		when pd.product_type = 'FXOption' then case
			when pd.product_sub_type <> 'DIGITAL' then round(tr.quantity * fo.option_strike, 2)
		end
		else tr.accrual
	end as quoting_amt,
	tr.trade_price,
	tr.neg_price_type as final_price_type,
	tr.neg_trade_price as final_price,
	pm.trade_premium_ccy,
	pm.trade_premium_amt,
	tr.trade_status,
	tr.settlement_date as settle_date,
	tr.entered_user,
	str_replace(rtrim(tr.external_reference), '', null) as external_ref,
	tr.comments as trade_comment,
	coalesce(om.off_mkt_rate_y, 'N') as off_mkt_rate_yn,
	coalesce(im.imkt_y, 'N') as imkt_yn,
	kos.strategy
FROM
	calypso.trade tr
	INNER JOIN calypso.product_desc pd ON tr.product_id = pd.product_id
	LEFT JOIN calypso.product_fx_option fo ON tr.product_id = fo.product_id
	LEFT JOIN (
		SELECT
			book_id,
			cast(attribute_value as numeric) as desk_id
		FROM
			calypso.book_attr_value
		WHERE
			attribute_name = 'BusinessUnit'
	) od ON tr.book_id = od.book_id
	LEFT JOIN (
		SELECT
			trade_id,
			'Y' as imkt_y
		FROM
			calypso.trade_keyword
		WHERE
			keyword_value = 'IMKT'
	) im ON tr.trade_id = im.trade_id
	LEFT JOIN (
		SELECT
			trade_id,
			'Y' as off_mkt_rate_y
		FROM
			calypso.trade_keyword
		WHERE
			rtrim(keyword_name) = 'Off_Market_Rate'
	) om ON tr.trade_id = om.trade_id
	LEFT JOIN (
		SELECT
			trade_id,
			keyword_value as strategy
		FROM
			calypso.trade_keyword
		WHERE
			keyword_name = 'FXOption_Strategy'
	) kos ON tr.trade_id = kos.trade_id
	LEFT JOIN (
		SELECT
			trade_id,
			currency_code as trade_premium_ccy,
			sum(amount) as trade_premium_amt
		FROM
			calypso.trade_fee
		WHERE
			fee_type = 'PREMIUM'
		GROUP BY
			trade_id,
			currency_code
	) pm ON tr.trade_id = pm.trade_id
WHERE
	pd.product_type in (
		'FX',
		'FXSwap',
		'FXOption',
		'FXOptionForward',
		'FXForward',
		'FXTakeUp'
	)
	and tr.trade_id in (
		SELECT
			distinct entity_id
		FROM
			calypso.bo_audit
		WHERE
			entity_class_name = 'Trade'
			AND convert(datetime, convert(varchar(10), modif_date, 103), 103) >= convert(
				datetime,
				convert(varchar(10), dateadd(day, -5, getdate()), 103),
				103
			)
	)

Step 2

Load all trades to a temporary table and replace the trade date time with that of the non-exercised trade leg if it is earlier.

View SQL

Load trades into temporary table and adjust trade date

CREATE TABLE #tmp_adj_trade_date
(
	trade_id numeric(20) NOT NULL,
	product_id numeric(20) NULL,
	desk_id numeric(20) NULL,
	product_type varchar(32) NULL,
	principal_amt float NULL,
	quoting_amt float NULL,
	trade_date_adj datetime NULL
)
INSERT INTO
	#tmp_adj_trade_date
	(
		trade_id,
		product_id,
		desk_id,
		product_type,
		principal_amt,
		quoting_amt,
		trade_date_adj
	)
SELECT
	tr.trade_id,
	tr.product_id,
	tr.desk_id,
	tr.product_type,
	tr.principal_amt,
	tr.quoting_amt,
	min(
		case
			when tr.trade_date_time < coalesce(ltr.trade_date_time, getdate()) then tr.trade_date_time
			else ltr.trade_date_time
		end
	) as trade_date_adj
FROM
	dbo.stage_calypso_trades tr
	INNER JOIN dbo.dw_control_desks_included_in_dw cd ON tr.desk_id = cd.desk_id
	LEFT JOIN dbo.dw_trade_legs tl ON tr.trade_id = tl.trade_id
	AND tr.desk_id = tl.trade_desk_id
	LEFT JOIN dbo.vw_stage_load_trade_legs_trades ltr ON tl.leg_trade_id = ltr.trade_id
	AND tl.leg_trade_desk_id = ltr.desk_id
	AND ltr.trade_status <> 'EXERCISED'
GROUP BY
	tr.trade_id,
	tr.product_id,
	tr.desk_id,
	tr.product_type,
	tr.principal_amt,
	tr.quoting_amt 
    
    create index idx_tmp_adj_trade_date_ids on #tmp_adj_trade_date (trade_id, desk_id)
	create index idx_tmp_adj_trade_date_prod_id on #tmp_adj_trade_date (product_id)
	create index idx_tmp_adj_trade_date_prod_typ on #tmp_adj_trade_date (product_type)

Step 3

Calculate the profit and loss for each trade.

FXOption, FXForward, FXSpot, FXSwap, FXTakeUp, SimpleMM, Cash:

P&L amount from Calypso converted to GBP.

FXOptionForward:

Principal amount converted to GBP + Quoting amount converted to GBP.

View SQL

Calculate the profit and loss amounts

-- P&L calculation for FX and Cash
CREATE TABLE #tmp_pnl_amts
(
	trade_id numeric(20) NOT NULL,
	desk_id numeric(20) NULL,
	pnl_component_ccy char(3) NULL,
	pnl_component_amt float NULL,
	pnl_component_amt_gbp float NULL
)
INSERT INTO
	#tmp_pnl_amts
	(
		trade_id,
		desk_id,
		pnl_component_ccy,
		pnl_component_amt,
		pnl_component_amt_gbp
	)
SELECT
	pc.trade_id,
	st.desk_id,
	null as pnl_component_ccy,
	null as pnl_component_amt,
	round(
		sum(
			pnl_component_amt * dbo.fnc_get_fx_rate(pc.pnl_component_ccy, 'GBP', st.trade_date_adj)
		),
		2
	) as pnl_component_amt_gbp
FROM
	dbo.stage_calypso_trade_pnl_component pc
	INNER JOIN #tmp_adj_trade_date st ON pc.trade_id=st.trade_id
WHERE
	st.product_type in (
		'FXForward',
		'FXSpot',
		'FXSwap',
		'FXTakeUp',
        'SimpleMM',
        'Cash'
	)
GROUP BY
	pc.trade_id,
	st.desk_id create index idx_tmp_pnl_amts_ids on #tmp_pnl_amts (trade_id, desk_id)

UNION

SELECT
	tr.trade_id,
	tr.desk_id,
	null as pnl_component_ccy,
	null as pnl_component_amt,
	sum(
		round(
			(
				dbo.fnc_get_fx_rate(
					pd.principal_ccy,
					'GBP',
					tr.trade_date_adj
				) * tr.principal_amt
			) + (
				dbo.fnc_get_fx_rate(
					pd.quoting_ccy,
					'GBP',
					tr.trade_date_adj
				) * tr.quoting_amt
			),
			2
		)
	) as pnl_component_amt_gbp
FROM
	#tmp_adj_trade_date tr
	INNER JOIN dbo.stage_calypso_trade_products pd ON tr.product_id = pd.product_id
WHERE
	tr.product_type = 'FXOptionForward'
GROUP BY
	tr.trade_id,
	tr.desk_id

Step 4

Load the main trades table

View SQL

Load the main trade table from staging with adjusted trade date and profit and loss.

INSERT INTO
	dbo.dw_trades (
		trade_id,
		bundle_id,
		desk_id,
		book_id,
		product_id,
		cpty_id,
		cpty_ext_yn,
		product_type,
		product_sub_type,
		trade_date_time,
		trade_ccy,
		trade_ccy_tr_date_gbp_fxrate,
		has_exercised_option_yn,
		option_side,
		principal_dir,
		principal_ccy,
		principal_amt,
		prin_ccy_tr_date_gbp_fxrate,
		quoting_dir,
		quoting_ccy,
		quoting_amt,
		quot_ccy_tr_date_gbp_fxrate,
		trade_price,
		final_price_type,
		final_price,
		trade_premium_ccy,
		trade_premium_amt,
		trade_status,
		settle_date,
		entered_user,
		external_ref,
		trade_comment,
		off_mkt_rate_yn,
		imkt_yn,
		strategy,
		hedge_id,
		hedge_name,
		hedge_type,
		hedge_effective_from,
		hedge_effective_to,
		retro_date,
		pro_user,
		pnl_component_ccy,
		pnl_component_amt,
		pnl_component_amt_gbp,
		pnl_comp_ccy_tr_date_gbp_fxrate,
		excl_from_rep_yn
	)
SELECT
	tr.trade_id,
	tr.bundle_id,
	tr.desk_id,
	tr.book_id,
	tr.product_id,
	tr.cpty_id,
	c.cpty_ext_yn,
	tr.product_type,
	pd.product_sub_type,
	tr.trade_date_time,
	tr.trade_ccy,
	dbo.fnc_get_fx_rate(trade_ccy, 'GBP', ta.trade_date_adj) as trade_ccy_tr_date_gbp_fxrate,
	coalesce(ex_opt.has_ex_option_yn, 'N') as has_exercised_option_yn,
	pd.option_side,
	case
		when tr.product_type = 'FXOption' then pd.option_type
		when tr.product_type in (
			'SimpleMM',
			'Cash',
			'CallNotice',
			'Bond',
			'CapFloor'
		) then tr.principal_dir
		when tr.product_type not in ('SimpleMM', 'Cash', 'CallNotice', 'Bond')
		AND pd.ccy_pair_dir_positive_yn = 'Y' then tr.principal_dir
		else tr.quoting_dir
	end as principal_dir,
	case
		when tr.product_type in (
			'SimpleMM',
			'Cash',
			'CallNotice',
			'Bond',
			'CapFloor'
		) then pd.principal_ccy
		when tr.product_type not in ('SimpleMM', 'Cash', 'CallNotice', 'Bond')
		AND pd.ccy_pair_dir_positive_yn = 'Y' then pd.principal_ccy
		else pd.quoting_ccy
	end as principal_ccy,
	case
		when tr.product_type in (
			'SimpleMM',
			'Cash',
			'CallNotice',
			'Bond',
			'CapFloor'
		) then tr.principal_amt
		when tr.product_type not in (
			'SimpleMM',
			'Cash',
			'CallNotice',
			'Bond',
			'CapFloor'
		)
		AND pd.ccy_pair_dir_positive_yn = 'Y' then tr.principal_amt
		else tr.quoting_amt
	end as principal_amt,
	case
		when pd.product_sub_type <> 'DIGITAL' then dbo.fnc_get_fx_rate(
			case
				when tr.product_type in ('SimpleMM', 'Cash', 'CallNotice', 'Bond') then pd.principal_ccy
				when tr.product_type not in ('SimpleMM', 'Cash', 'CallNotice')
				AND pd.ccy_pair_dir_positive_yn = 'Y' then pd.principal_ccy
				else pd.quoting_ccy
			end,
			'GBP',
			ta.trade_date_adj
		)
	end as prin_ccy_tr_date_gbp_fxrate,
	case
		when tr.product_type = 'FXOption' then case
			pd.option_type
			when 'Put' then 'Call'
			when 'Call' then 'Put'
		end
		when tr.product_type in (
			'SimpleMM',
			'Cash',
			'CallNotice',
			'Bond',
			'CapFloor'
		) then null
		when tr.product_type not in (
			'SimpleMM',
			'Cash',
			'CallNotice',
			'Bond',
			'CapFloor'
		)
		AND pd.ccy_pair_dir_positive_yn = 'Y' then tr.quoting_dir
		else tr.principal_dir
	end as quoting_dir,
	case
		when tr.product_type in (
			'SimpleMM',
			'Cash',
			'CallNotice',
			'Bond',
			'CapFloor'
		) then null
		when tr.product_type not in (
			'SimpleMM',
			'Cash',
			'CallNotice',
			'Bond',
			'CapFloor'
		)
		AND pd.ccy_pair_dir_positive_yn = 'Y' then pd.quoting_ccy
		else pd.principal_ccy
	end as quoting_ccy,
	case
		when tr.product_type in (
			'SimpleMM',
			'Cash',
			'CallNotice',
			'Bond',
			'CapFloor'
		) then null
		when tr.product_type not in (
			'SimpleMM',
			'Cash',
			'CallNotice',
			'Bond',
			'CapFloor'
		)
		AND pd.ccy_pair_dir_positive_yn = 'Y' then tr.quoting_amt
		else tr.principal_amt
	end as quoting_amt,
	case
		when pd.product_sub_type <> 'DIGITAL' then dbo.fnc_get_fx_rate(
			case
				when tr.product_type in ('SimpleMM', 'Cash', 'CallNotice', 'Bond') then null
				when tr.product_type not in ('SimpleMM', 'Cash', 'CallNotice', 'Bond')
				AND pd.ccy_pair_dir_positive_yn = 'Y' then pd.quoting_ccy
				else pd.principal_ccy
			end,
			'GBP',
			ta.trade_date_adj
		)
	end as quot_ccy_tr_date_gbp_fxrate,
	case
		when tr.product_type in (
			'SimpleMM',
			'Cash',
			'CallNotice',
			'Bond',
			'CapFloor'
		) then null
		else tr.trade_price
	end as trade_price,
	case
		when tr.product_type in (
			'SimpleMM',
			'Cash',
			'CallNotice',
			'Bond',
			'CapFloor'
		) then null
		else tr.final_price_type
	end as final_price_type,
	case
		when tr.product_type in (
			'SimpleMM',
			'Cash',
			'CallNotice',
			'Bond',
			'CapFloor'
		) then null
		else tr.final_price
	end as final_price,
	trade_premium_ccy,
	abs(trade_premium_amt) as trade_premium_amt,
	tr.trade_status,
	tr.settle_date,
	tr.entered_user,
	tr.external_ref,
	case
		when len(rtrim(tr.trade_comment)) = 0 then null
		else tr.trade_comment
	end as trade_comment,
	tr.off_mkt_rate_yn,
	tr.imkt_yn,
	tr.strategy,
	tr.hedge_id,
	tr.hedge_name,
	tr.hedge_type,
	tr.hedge_effective_from,
	tr.hedge_effective_to,
	tr.retro_date,
	rtrim(c.pro_user) as pro_user,
	pa.pnl_component_ccy,
	pa.pnl_component_amt,
	case
		when tr.product_type <> 'FXTakeup' then pa.pnl_component_amt_gbp
	end as pnl_component_amt_gbp,
	case
		when pa.pnl_component_ccy is not null then dbo.fnc_get_fx_rate(pa.pnl_component_ccy, 'GBP', ta.trade_date_adj)
	end as pnl_comp_ccy_tr_date_gbp_fxrate,
	'N' as excl_from_rep_yn
FROM
	dbo.stage_calypso_trades tr
	INNER JOIN dbo.stage_calypso_trade_products pd ON tr.product_id = pd.product_id
	INNER JOIN dbo.dw_control_desks_included_in_dw d ON tr.desk_id = d.desk_id
	LEFT JOIN dbo.stage_counterparties c ON cast(tr.cpty_id as char) = c.cpty_id
	AND c.system_name = 'calypso'
	LEFT JOIN #tmp_adj_trade_date ta ON tr.trade_id=ta.trade_id
	LEFT JOIN #tmp_pnl_amts pa ON tr.trade_id=pa.trade_id AND tr.desk_id=pa.desk_id
	LEFT JOIN (
		select
			distinct trade_id,
			'Y' as has_ex_option_yn
		FROM
			dbo.dw_trade_legs_pivot
		WHERE
			xopt_trade_id is not null
		GROUP BY
			trade_id
	) ex_opt ON tr.trade_id = ex_opt.trade_id