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
|
-- -*- 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,
PRIMARY KEY(identifier, date));
|
