1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
|
-- -*- mode: sql; sql-product: postgres; -*-
CREATE TYPE bond_strat AS ENUM('M_STR_MAV', 'M_STR_SMEZZ', 'CSO_TRANCH',
'M_CLO_BB20', 'M_CLO_AAA', 'M_CLO_BBB', 'M_MTG_IO', 'M_MTG_THRU',
'M_MTG_GOOD', 'M_MTG_B4PR', 'M_MTG_RW');
CREATE TYPE asset_class AS ENUM('CSO', 'Subprime', 'CLO', 'Tranches', 'Futures', 'Cash', 'FX', 'Cleared');
CREATE TYPE action AS ENUM('NEW', 'UPDATE', 'CANCEL');
CREATE TYPE currency AS ENUM('USD', 'CAD', 'EUR', 'YEN');
CREATE TYPE bbg_type AS ENUM('Mtge', 'Corp');
CREATE TABLE counterparties(code varchar(12) primary key,
name text,
city text,
state varchar(2),
location text,
dtc_number integer,
sales_contact text,
sales_email text,
sales_phone text,
valuation_contact1 text,
valuation_email1 text,
valuation_contact2 text,
valuation_email2 text,
valuation_contact3 text,
valuation_email3 text,
valuation_contact3 text,
valuation_email3 text,
valuation_contact4 = text,
valuation_email4 = text,
notes text);
CREATE INDEX ON counterparties(name);
CREATE TABLE bonds(id serial primary key,
deal_id varchar(28),
lastupdate timestamp,
action action,
folder bond_strat,
custodian varchar(12),
cashaccount varchar(10),
cp_code varchar(12) references counterparties(code),
trade_date date,
settle_date date,
cusip varchar(9),
isin varchar(12),
description varchar(32),
faceamount float,
price float,
accrued float,
asset_class asset_class,
ticket text,
principal_payment float,
accrued_payment float);
CREATE TABLE position(date date,
identifier varchar(12) REFERENCES position_indicative(identifier)
notional float,
coupon float,
factor float,
price float,
market_value_local float,
market_value_usd float,
accrued float,
days_accrued float,
start_accrued_date date,
factor_pay_date date,
paydown float,
writedown float,
unsettled_principal float,
unsettled_interest float,
PRIMARY KEY(identifier, date));
CREATE TABLE securities(identifier varchar(12) PRIMARY KEY,
cusip varchar(9),
isin varchar(12),
description varchar(32),
face_amount float,
maturity date,
floater boolean,
spread float,
coupon float,
frequency smallint,
first_coupon_date date,
pay_delay smallint,
currency currency default 'USD',
bbg_type bbg_type default 'Mtge',
asset_class asset_class,
paid_down date default 'Infinity')
CREATE TABLE marks(date date,
identifier varchar(12) REFERENCES securities(identifier),
price float,
PRIMARY KEY(identifier, date));
CREATE TABLE payment_history(
identifier varchar(12),
date date,
principal_paydown float,
interest float,
principal_writedown float,
recovery_percentage float,
cumloss_percentage float,
PRIMARY KEY (identifier, date));
CREATE OR REPLACE function list_marks(p_date date, include_unsettled boolean DEFAULT False)
RETURNS TABLE(identifier varchar(12), price float) AS $$
BEGIN
RETURN QUERY SELECT a.identifier, b.price FROM list_positions(p_date, Null, include_unsettled) a LEFT JOIN
(SELECT DISTINCT ON (identifier) date, marks.identifier, marks.price FROM marks
WHERE date<= p_date ORDER BY identifier, date DESC) b USING (identifier);
END;
$$ LANGUAGE plpgsql;
CREATE OR REPLACE function list_positions(p_date date,
p_class asset_class DEFAULT NULL,
include_unsettled boolean DEFAULT True)
RETURNS TABLE(identifier varchar(12), description varchar(32), strategy bond_strat,
last_settle_date date, notional float, bbg_type bbg_type) AS $$
DECLARE sqlquery text;
DECLARE asset_opt text;
DECLARE unsettled_opt text;
BEGIN
IF p_class is not NULL THEN
asset_opt := 'and position_indicative.asset_class=$2 ';
ELSE
asset_opt := '';
END IF;
IF include_unsettled THEN
unsettled_opt = 'or settle_date<=$1';
ELSE
unsettled_opt = '';
END IF;
sqlquery := 'WITH temp as (SELECT bonds.identifier, bonds.description, asset_class, settle_date, folder,
sum(faceamount*(2*buysell::int-1))
OVER (PARTITION by bonds.identifier) notional from bonds where trade_date<=$1)
SELECT DISTINCT ON (temp.identifier) temp.identifier, temp.description, folder, settle_date, notional,
securities.bbg_type
FROM temp LEFT JOIN securities USING (identifier)
WHERE (temp.notional>0 '||unsettled_opt||') and paid_down>$1 '||asset_opt
||' order by identifier, settle_date desc';
RETURN QUERY EXECUTE sqlquery USING p_date, p_class;
END;
$$ LANGUAGE plpgsql;
|
