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
|
-- -*- 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);
CREATE TABLE position(date date,
isin varchar(12),
cusip varchar(9),
identifier varchar(12),
description varchar(32),
notional float,
face_amount float,
coupon float,
currency currency,
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,
bbg_type bbg_type,
strategy bond_strat,
asset_class asset_class,
presettle_principal float,
presettle_interest float,
settle_date date,
PRIMARY KEY(identifier, date));
CREATE OR REPLACE function list_positions(p_date date, p_class asset_class DEFAULT NULL)
RETURNS TABLE(identifier varchar(12), description varchar(32), strategy bond_strat,
last_settle_date date, notional float) AS $$
DECLARE sqlquery text;
BEGIN
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) from bonds where trade_date<=$1)
SELECT DISTINCT ON (temp.identifier) temp.identifier, temp.description, folder, settle_date, sum AS notional
FROM temp
WHERE (sum>0 or settle_date>=$1)';
IF p_class is not NULL THEN
sqlquery := sqlquery || 'and asset_class=$2';
END IF;
sqlquery := sqlquery || 'order by identifier, settle_date';
RETURN QUERY EXECUTE sqlquery
USING p_date, p_class;
END;
$$ LANGUAGE plpgsql;
|
