Mark-to-Market

Extract from Calypso the Net Present Value (NPV) of the newly booked front office trades from the last 5 days that haven’t been cancelled or deleted and have the following products:

Forward Exchange (FXForward)
Forward Exchange Options (FXOption)
Forward Exchange Swap (FXSwap)
Forward Contract Forward Exchange Options (FXOptionForward)
Forward Exchange TakeUp (FXTakeUp)
Interest Rate Cap and Floor (CapFloor)
Derivative Swap (Swaps)

Step 1

Load all live trades from Calypso with keyword attributes of:

Cover Trade ID
ExercisedOption
ExercisedUnder
Cover Trade ID
ExercisedOption
ExercisedUnder
SalesB2BFrom
SalesB2BTo
RolledOverFrom
RolledOverTo

View SQL

Step 2

Trade level values

Load trade mark-to-market values to production

View SQL

Step 3

Counterparty level values

Denormalise the data so each keyword has a column

View SQL

1	SELECT
2	t.trade_id,
3	b.business_unit as desk_id,
4	p.booking_date as mtm_date,
5	p.currency_code as ccy,
6	sum(
7	case
8	when p.credit_acc_id <> -1 then p.amount * -1
9	else p.amount
10	end
11	) as mtm
12	FROM
13	calypso.trade t
14	INNER JOIN calypso.bo_posting p ON t.trade_id = p.trade_id
15	INNER JOIN calypso.product_desc d ON d.product_id = p.product_id
16	INNER JOIN calypso.vw_book b ON b.book_id = p.book_id
17	INNER JOIN calypso.acc_rule r ON p.acc_rule_id = r.acc_rule_id
18	WHERE
19	r.acc_rule_type = 'FO_PNL'
20	AND p.bo_posting_type like 'MTM%'
21	AND p.description = 'NPV'
22	AND p.sent_status <> 'DELETED'
23	AND d.product_type in (
24	'FX',
25	'FXForward',
26	'FXOption',
27	'FXSwap',
28	'FXOptionForward',
29	'FXTakeUp',
30	'CapFloor',
31	'Swap'
32	)
33	AND p.posting_type = 'NEW'
34	AND t.trade_status <> 'CANCELED'
35	AND p.booking_date >= (
36	SELECT
37	dateadd(
38	day,
39	-5,
40	convert(datetime, convert(varchar(10), getdate(), 103), 103)
41	)
42	)
43	GROUP BY
44	t.trade_id,
45	b.business_unit,
46	p.booking_date,
47	p.currency_code

1	INSERT INTO
2	dbo.dw_trade_mtm (
3	trade_id,
4	mtm_date,
5	ccy,
6	mtm,
7	mtm_gbp
8	)
9	SELECT
10	trade_id,
11	mtm_date,
12	ccy,
13	mtm,
14	round(
15	case
16	when ccy = 'GBP' then mtm
17	else mtm * dbo.fnc_get_fx_rate(ccy, 'GBP', mtm_date)
18	end,
19	2
20	) as mtm_gbp
21	FROM
22	dbo.stage_calypso_trade_mtm

Mark-to-Market

Step 1

Load Calypso posting amount (trade mark-to-market)

Step 2

Load trade mark-to-market values to table dw_trade_mtm

Step 3

Load simple money market trades