aboutsummaryrefslogtreecommitdiffstats
diff options
context:
space:
mode:
Diffstat
-rw-r--r--python/intex/load_indicative.py98
-rw-r--r--sql/et_tables.sql53
2 files changed, 94 insertions, 57 deletions
diff --git a/python/intex/load_indicative.py b/python/intex/load_indicative.py
index ae0faf9f..50e29557 100644
--- a/python/intex/load_indicative.py
+++ b/python/intex/load_indicative.py
@@ -13,6 +13,26 @@ logger = logging.getLogger(__name__)
def convertToNone(s):
return None if s in ["", "-", "NR"] else s
+def insert_new_cusip(conn, line):
+ line['Pari-Passu Tranche'] = line['Pari-Passu Tranche'].split(",")
+ to_insert = (line['CUSIP'], line['ISIN'], line['Bloomberg Ticker'], dealname,
+ tranche, line['Pari-Passu Tranche'])
+ for key in ['Orig Balance', 'Orig Attachment Point','Orig Detachment Point',
+ 'Floater Spread/Margin']:
+ if line[key]:
+ line[key] = sanitize_float(line[key])
+ line[key] = convertToNone(line[key])
+ to_insert += (line['Orig Balance'], line.get('Orig Moody'),
+ line['Orig Attachment Point'], line['Orig Detachment Point'],
+ line['Floater Index'], line['Floater Spread/Margin'])
+ sqlstr = "INSERT INTO cusip_ref VALUES({0})".format(",".join(["%s"]*12))
+ with conn.cursor() as c:
+ try:
+ c.execute(sqlstr, to_insert)
+ except psycopg2.DataError as e:
+ logger.error(e)
+ conn.commit()
+
def upload_cusip_data(conn, filename):
dealupdate = {}
with open( filename, "r") as fh:
@@ -33,67 +53,49 @@ def upload_cusip_data(conn, filename):
line = {k: convertToNone(v) for k, v in line.items()}
if dealname not in dealupdate:
with conn.cursor() as c:
- c.execute("SELECT \"Latest Update\" FROM clo_universe " \
- "WHERE dealname = %s ORDER BY \"Latest Update\" DESC", (dealname,))
+ c.execute("SELECT max(\"Latest Update\") FROM clo_universe " \
+ "WHERE dealname = %s", (dealname,))
try:
- dealupdate[dealname] = c.fetchone()[0]
+ dealupdate[dealname], = c.fetchone()
except TypeError:
logging.error('deal:{0} not in database'.format(dealname))
continue
-
- sqlstring = "SELECT updatedate FROM latest_cusip_universe WHERE cusip = %s"
+ sqlstring = "SELECT cusip FROM cusip_ref WHERE cusip=%s"
with conn.cursor() as c:
c.execute(sqlstring, (line['CUSIP'],))
- curr_date = c.fetchone()
- conn.commit()
- if not curr_date or curr_date[0] < dealupdate[dealname]:
- if dealname not in deals_to_update:
- deals_to_update.append(dealname)
- line['updatedate'] = dealupdate[dealname]
+ if c is None:
+ try:
+ insert_new_cusip(conn, line)
+ except ValueError:
+ continue
+ sqlstring = "SELECT max(updatedate) FROM cusip_update WHERE cusip = %s"
+ with conn.cursor() as c:
+ c.execute(sqlstring, (line['CUSIP'],))
+ try:
+ curr_date, = c.fetchone()
+ except TypeError:
+ curr_date = dealupdate[dealname] - 1
+ if curr_date < dealupdate[dealname]:
try:
- for key in ['Curr Balance', 'Orig Balance', 'Orig Attachment Point',
- 'Curr Attachment Point (def at MV)', 'Orig Detachment Point',
- 'Curr Detachment Point (def at MV)', 'Factor', 'Coupon',
- 'Floater Spread/Margin']:
+ for key in ['Curr Balance', 'Curr Attachment Point (def at MV)',
+ 'Curr Detachment Point (def at MV)', 'Factor', 'Coupon']:
if line[key]:
line[key] = sanitize_float(line[key])
line[key] = convertToNone(line[key])
except ValueError:
continue
- line['Curr Attachment Point'] = line['Curr Attachment Point (def at MV)']
- line['Curr Detachment Point'] = line['Curr Detachment Point (def at MV)']
- if "Curr Moody" not in line:
- if 'Orig Moody' in line:
- line['Curr Moody'] = line['Orig Moody']
- else:
- line['Curr Moody'] = None
- line['Orig Moody'] = None
- sqlstring = "INSERT INTO cusip_universe(Cusip, ISIN, \"Bloomberg Ticker\", dealname, tranche, " \
- "Coupon, Orig_Balance, Curr_Balance, Factor, Orig_Moody, Curr_Moody, " \
- "Orig_Attach, Orig_Detach, Curr_Attach, Curr_Detach, Floater_Index, " \
- "Spread, updatedate) " \
- "VALUES(%(CUSIP)s, %(ISIN)s, %(Bloomberg Ticker)s, %(dealname)s, %(tranche)s, %(Coupon)s, " \
- "%(Orig Balance)s, %(Curr Balance)s, %(Factor)s, %(Orig Moody)s, %(Curr Moody)s, " \
- "%(Orig Attachment Point)s, %(Orig Detachment Point)s, "\
- "%(Curr Attachment Point)s, %(Curr Detachment Point)s, " \
- "%(Floater Index)s, %(Floater Spread/Margin)s, %(updatedate)s)"
- try:
- with conn.cursor() as c:
- c.execute(sqlstring, line)
- except psycopg2.DataError as e:
- logger.error(e)
- logger.debug("uploaded: {0}".format(line['CUSIP']))
+ line['Curr Moody'] = line.get('Curr Moody') or line.get('Orig Moody')
+ sqlstring = "INSERT INTO cusip_update VALUES({0})".format(",".join(["%s"] * 8))
+ to_insert = (line['CUSIP'], line['Curr Balance'], line['Factor'], line['Coupon'],
+ line['Curr Moody'], line['Curr Attachment Point (def at MV)'],
+ line['Curr Detachment Point (def at MV)'], dealupdate[dealname])
+ with conn.cursor() as c:
+ try:
+ c.execute(sqlstring, to_insert)
+ except psycopg2.DataError as e:
+ logger.error(e)
+ logger.debug("uploaded: {0}".format(line['CUSIP']))
conn.commit()
- # for dealname in deals_to_update:
- # with conn.cursor() as c:
- # c.execute("SELECT p_cusip, p_curr_subordination, "\
- # "p_curr_thickness from et_deal_subordination(%s)",
- # (dealname,))
- # data = [ (t[1], t[2], t[0], dealupdate[dealname]) for t in c]
- # c.executemany("UPDATE cusip_universe SET subordination = %s, "
- # "thickness = %s WHERE cusip = %s AND "
- # "updatedate = %s", data)
- # conn.commit()
def upload_deal_data(conn, filename):
sqlstr = "select dealname, array_agg(\"Latest Update\") from clo_universe group by dealname"
diff --git a/sql/et_tables.sql b/sql/et_tables.sql
index edf1551b..7460da1e 100644
--- a/sql/et_tables.sql
+++ b/sql/et_tables.sql
@@ -310,7 +310,8 @@ CREATE TABLE clo_universe (
GRANT ALL ON clo_universe TO et_user;
-CREATE TABLE cusip_universe (
+CREATE TABLE cusip_universe_old (
+--deprecated
Cusip varchar(9),
ISIN varchar(12),
dealname varchar(10),
@@ -336,6 +337,36 @@ CREATE TABLE cusip_universe (
GRANT ALL ON cusip_universe TO et_user;
+CREATE TABLE cusip_ref(
+ Cusip varchar(9) PRIMARY KEY,
+ ISIN varchar(12),
+ bloomberg_ticker text,
+ dealname varchar(10),
+ tranche text,
+ pari-passu_tranches text[],
+ Orig_Balance float,
+ Orig_Moody text,
+ Orig_Attach float,
+ Orig_Detach float,
+ Floater_Index text,
+ Spread float);
+
+CREATE TABLE cusip_update(
+ Cusip varchar(9) REFERENCES cusip_ref,
+ curr_balance float,
+ factor float,
+ coupon float,
+ Curr_Moody text,
+ Curr_Attach float,
+ Curr_Detach float,
+ updatedate date,
+ PRIMARY KEY(cusip, updatedate));
+
+CREATE OR REPLACE VIEW cusip_universe AS
+ SELECT a.*, b.curr_balance, b.coupon, b.curr_moody, b.curr_attach, b.curr_detach, b.subordination,
+ b.thickness, b.updatedate FROM cusip_ref a
+ JOIN cusip_update b USING (cusip);
+
CREATE OR REPLACE VIEW latest_clo_universe AS
SELECT b.dealname, "Deal Name", Manager, orig_deal_bal, b."Curr Deal Bal", orig_collat_bal,
b."Curr Collat Bal", b."Tranche Factor", b."Principal Bal", b."Interest Bal", b."CDO Percentage",
@@ -348,11 +379,13 @@ CREATE OR REPLACE VIEW latest_clo_universe AS
GRANT ALL ON latest_clo_universe TO et_user;
-CREATE VIEW latest_cusip_universe AS
- SELECT b.*
- FROM (SELECT MAX(updatedate) AS latestdate, cusip FROM cusip_universe GROUP BY cusip) a
- JOIN cusip_universe b ON a.cusip = b.cusip AND a.latestdate= b.updatedate
- ORDER by cusip asc;
+CREATE OR REPLACE VIEW latest_cusip_universe AS
+ WITH latest_cusip_update AS
+ (SELECT DISTINCT ON (cusip) * FROM cusip_update
+ ORDER BY cusip, updatedate DESC)
+ SELECT a.*, b.curr_balance, b.coupon, b.curr_moody, b.curr_attach,
+ b.curr_detach, b.updatedate FROM cusip_ref a
+ JOIN latest_cusip_update b USING (cusip);
GRANT ALL ON latest_cusip_universe TO et_user;
@@ -394,8 +427,8 @@ CREATE OR REPLACE FUNCTION historical_dealname_universe(p_dealname varchar(10),
END;
$$ LANGUAGE plpgsql;
-CREATE VIEW dealcusipmapping AS
- SELECT dealname, cusip from latest_cusip_universe;
+CREATE OR REPLACE VIEW dealcusipmapping AS
+ SELECT dealname, cusip from cusip_ref;
GRANT ALL ON dealcusipmapping TO et_user;
@@ -541,12 +574,14 @@ CREATE OR REPLACE FUNCTION et_cusip_details(p_cusips VARIADIC varchar(10)[])
reinvenddate date, maturity date, stale_percentage float,
cdo_percentage float, wap_basis float, portfolio_spread_5y float,
portfolio_spread float, subordination float, thickness float,
+ mvoc float, mvcoverage float,
empty3 unknown, pricingdate date, delta float, duration float, wal float, price float) AS $$
BEGIN
RETURN QUERY SELECT a.Orig_Moody, a.Curr_Moody, NULL, b."Deal Name", b.manager, NULL,
a.Spread/100, a.Coupon/100, b.deal_issue_date, b.reinv_end_date, b.Maturity,
e.stalepercentage, greatest(b."CDO Percentage"/100, e.cdopercentage), e.wapbasis,
- e.dealspread5y, e.dealspread, a.curr_attach/100, (a.curr_detach-a.curr_attach)/100, NULL, d.updatedate,
+ e.dealspread5y, e.dealspread, a.curr_attach/100, (a.curr_detach-a.curr_attach)/100, d.mvoc, d.mvcoverage,
+ NULL, d.updatedate,
d.delta, d.duration, d.wal, d.price
FROM latest_cusip_universe a LEFT JOIN latest_clo_universe b
ON a.dealname = b.dealname
generated by cgit v1.2.3-70-g09d2 (git 2.52.0) at 2025-12-28 04:42:03 +0000