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
|
select sum(currentbalance* coalesce(b.bid,c.price))/sum(currentbalance) as wap, sum(currentbalance) as pricedbalance from et_collateral a
left join latest_markit_prices b on a.loanxid=b.loanxid
left join bloomberg_prices c on a.cusip=c.cusip
where a.dealname='octagon8' and coalesce(b.bid,c.price) is not Null;
select issuername, sum(currentbalance) AS currentbalance, a.maturity, fixedorfloat,
avg(coalesce((b.bid+b.offer)/2, c.price)) as price, avg(grosscoupon) as grosscoupon,
avg(a.spread) as grossmargin, a.frequency, min(a.nextpaydate) AS nextpaydate,
a.loanxid, a.cusip, (max(a.covlite)='Cov_Lite' or (max(a.covlite)='COV_LITE' or
bool_or(c.covlite))) as covlite, (bool_or(a.secondlien) OR
bool_or(c.secondlien)) AS secondlien, bool_or(a.iscdo) AS iscdo, bool_or(defaultedflag) as defaultedflag
from et_collateral a left join latest_markit_prices b on a.loanxid=b.loanxid left join bloomberg_prices c
on a.cusip=c.cusip where a.dealname='babs062'
group by issuername, a.maturity, a.loanxid, fixedorfloat, a.frequency, a.cusip order by issuername;
select issuername, sum(currentbalance) AS currentbalance, a.maturity, fixedorfloat, a.dealname,
avg(coalesce((b.bid+b.offer)/2, c.price)) as price, avg(grosscoupon) as grosscoupon,
avg(a.spread) as grossmargin, a.frequency, min(a.nextpaydate) AS nextpaydate,
a.loanxid, a.cusip, (max(a.covlite)='Cov_Lite' or (max(a.covlite)='COV_LITE' or
bool_or(c.covlite))) as covlite, (bool_or(a.secondlien) OR
bool_or(c.secondlien)) AS secondlien, bool_or(a.iscdo) AS iscdo, bool_or(defaultedflag) as defaultedflag
from et_collateral a left join latest_markit_prices b on a.loanxid=b.loanxid left join bloomberg_corp c
on a.cusip=c.cusip
group by issuername, a.dealname, a.maturity, a.loanxid, fixedorfloat, a.frequency, a.cusip order by dealname;
select * from latest_markit_prices;
select * from bloomberg_prices where cusip='038521AG5';
select distinct cusip from et_collateral;
select count(a.currentbalance) from et_collateral a where dealname='octagon8';
select dealname from clo_universe;
--query latest prices from markit_prices
select b.loanxid,b.issuer,b.dealname,b.facility,b.industry,b.sp, b.moodys,b.amount,b.maturity,b.spread,b.bid,b.offer,b.depth,b.stm,a.latestdate from (select max(pricingdate) as latestdate, loanxid from markit_prices group by loanxid) a join markit_prices b
on a.loanxid = b.loanxid and a.latestdate= b.pricingdate;
select * from dealcusipmapping;
select * from et_aggdealinfo('octagon8');
select * from et_ReadMapped('abcde',2016-4-12,3);
insert into et_user_mapping (issuername, maturity, grosscoupon, spread, loanxid) VALUES ('Aramark','1/24/2014',2.34,1.88,'LX063469');
select * from cusip_user_mapping;
select * from et_collateral where;
select * from latest_markit_prices where issuer like '%Acxiom%';
insert into loanx_user_mapping (issuername, maturity, spread, loanxid) VALUES ('RH Donnelley','10/24/2014','4.25','LX113368');
insert into loanx_user_mapping (issuername, maturity, spread, loanxid) VALUES ('RH Donnelley','10/24/2014','5.25','LX213368');
update loanx_user_mapping SET issuername = 'RH Donnelley', maturity = '10/24/2014', spread = '4.25', loanxid = 'LX113368' WHERE issuername = 'RH Donnelley' and maturity = '10/24/2014'and spread = '4.25'and loanxid = 'LX113368'
select * from loanx_user_mapping;
delete from loanx_user_mapping;
select distinct loanxid,cusip from et_collateral where loanxid is not NULL and cusip is not NULL;
select * from bloomberg_corp;
select * from et_latestdealinfo('abrlf','8/24/2012');
UPDATE et_collateral a SET et_loanxid = 'LX050789' WHERE a.dealname = 'abrlf' and a.name = 'AVIO S.p.A - Term Loan B2' and a.maturity = '2014-12-13';
select * from et_collateral where dealname = 'abrlf' and et_loanxid= 'LX050789';
select * from et_collateral where dealname = 'abrlf';
Drop function et_aggdealinfo_historical (varchar(10), date);
select count(*) from loanx_user_mapping a where a.issuername = 'RH Donnelley' and a.maturity = '10/24/2014' and a.spread = '4.25'
count(loanxid) from loanx_user_mapping a where a.issuername = 'RH Donnelley' and a.maturity = '10/24/2014' and a.spread = '4.25'
CREATE OR REPLACE FUNCTION et_aggdealinfo_historical (p_dealname varchar(10), p_date date)
RETURNS TABLE(issuername text, currentbalance float, maturity date,
fixedorfloat intex_fixedorfloat, assettype text, price float, grosscoupon float, spread float,
frequency varchar(1), nextpaydate date, loanxid varchar(10), cusip varchar(9),
covlite boolean, secondlien boolean, iscdo boolean, defaultedflag boolean, et_loanxid varchar(10), et_cusip varchar(9)) AS $$
BEGIN
RETURN QUERY SELECT a.issuername, sum(a.currentbalance) AS
currentbalance, a.maturity, a.fixedorfloat, max(a.assettype),
avg(coalesce((b.bid+b.offer)/2, c.price)) AS price,
avg(a.grosscoupon) AS grosscoupon, avg(a.spread) AS
spread, a.frequency, min(a.nextpaydate) AS nextpaydate,
a.loanxid, a.cusip, (max(a.covlite)='Cov_Lite' or (max(a.covlite)='COV_LITE' or
bool_or(c.covlite))) AS covlite, (bool_or(a.secondlien) OR
bool_or(c.secondlien)) AS secondlien, bool_or(a.iscdo) AS iscdo,
bool_or(a.defaultedflag) AS defaultedflag, a.et_loanxid, a.et_cusip from et_latestdealinfo(p_dealname, p_date) a
left join historical_markit_prices(p_date) b on b.loanxid=Coalesce(a.loanxid,a.et_loanxid) left
join historical_bloomberg_corp(p_date) c on a.cusip=c.cusip where a.dealname = p_dealname
group by a.issuername, a.maturity, a.loanxid, a.fixedorfloat,
a.frequency, a.cusip, a.et_loanxid, a.et_cusip ORDER BY issuername;
END;
$$ LANGUAGE plpgsql;
select * from et_aggdealinfo_historical('abrlf', '8/27/2012');
CREATE TABLE loanx_user_mapping (
IssuerName text,
Maturity date,
Spread float default NULL,
LoanxID varchar(8) default NULL
);
Drop TABLE loanx_user_mapping;
|
