diff options
| -rw-r--r-- | python/cusip_universe.py | 48 | ||||
| -rw-r--r-- | sql/et_tables.sql | 32 |
2 files changed, 56 insertions, 24 deletions
diff --git a/python/cusip_universe.py b/python/cusip_universe.py index 982e00a5..8e665e14 100644 --- a/python/cusip_universe.py +++ b/python/cusip_universe.py @@ -20,29 +20,30 @@ def sanitize_float(intex_float): return intex_float def upload_data(workdate, conn, cursor): - data = [] + dealupdate = {} for cusip_universe_file in os.listdir(os.path.join(common.root, "data", "Trinfo_" + workdate)): with open( os.path.join(common.root, "data", "Trinfo_" + workdate, cusip_universe_file), "r") as fh: dr = csv.DictReader(fh, dialect='excel-tab') data = [] + deals_to_update = [] for line in dr: if "," in line['Tranche']: line["dealname"], line["tranche"] = line["Tranche"].split(",") else: continue line["dealname"] = line["dealname"].lower() - sqlstring = "SELECT * FROM cusip_universe WHERE cusip = %s" - cursor.execute(sqlstring, (line['CUSIP'],)) + dealname = line['dealname'] line = {k: convertToNone(v) for k, v in line.iteritems()} - if cursor.fetchone(): - for key in ['Curr Balance', 'Curr Attachment Point', 'Curr Detachment Point', 'Factor']: - if line[key]: - line[key] = sanitize_float(line[key]) - line[key] = convertToNone(line[key]) - sqlstring = "UPDATE cusip_universe SET Curr_Balance = %(Curr Balance)s, " \ - "Factor = %(Factor)s, Curr_moody = %(Curr Moody)s, " \ - "Curr_attach = %(Curr Attachment Point)s WHERE cusip = %(CUSIP)s" - else: + if dealname not in dealupdate: + cursor.execute("SELECT \"Latest Update\" FROM clo_universe " \ + "WHERE dealname = %s ORDER BY \"Latest Update\" DESC", (dealname,)) + dealupdate[dealname] = cursor.fetchone()[0] + sqlstring = "SELECT updatedate FROM latest_cusip_universe WHERE cusip = %s" + cursor.execute(sqlstring, (line['CUSIP'],)) + curr_date = cursor.fetchone() + if not curr_date or curr_date[0] < dealupdate[dealname]: + deals_to_update.append(dealname) + line['updatedate'] = dealupdate[dealname] for key in ['Curr Balance', 'Orig Balance', 'Orig Attachment Point', 'Curr Attachment Point', 'Orig Detachment Point', 'Curr Detachment Point', 'Factor', 'Coupon']: if line[key]: @@ -50,17 +51,28 @@ def upload_data(workdate, conn, cursor): line[key] = convertToNone(line[key]) if "Curr Moody" not in line: line['Curr Moody'] = line['Orig Moody'] - sqlstring = "INSERT INTO cusip_universe(cusip, ISIN, dealname, tranche," \ - "coupon, Orig_Balance, Curr_Balance, Factor, Orig_moody, Curr_moody, " \ - "Orig_attach, Orig_detach, Curr_attach, Curr_detach, Floater_Index," \ - "Spread) " \ + sqlstring = "INSERT INTO cusip_universe(Cusip, ISIN, 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, %(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)s)" - cursor.execute(sqlstring, line) + "%(Curr Detachment Point)s, %(Floater Index)s, %(Floater Spread)s, " \ + "%(updatedate)s)" + cursor.execute(sqlstring, line) print "uploaded: {0}".format(line['CUSIP']) conn.commit() + for dealname in deals_to_update: + cursor.execute("SELECT p_cusip, p_curr_subordination, "\ + "p_curr_thickness from et_deal_subordination(%s)", + (dealname,)) + data = cursor.fetchall() + data = [ (t[1], t[2], t[0], dealupdate[dealname]) for t in data] + cursor.executemany("UPDATE cusip_universe SET subordination = %s, " + "thickness = %s WHERE cusip = %s AND " + "updatedate = %s", data) + conn.commit() if __name__=="__main__": if len(sys.argv) > 1: diff --git a/sql/et_tables.sql b/sql/et_tables.sql index eff848f4..a26ad542 100644 --- a/sql/et_tables.sql +++ b/sql/et_tables.sql @@ -260,7 +260,10 @@ CREATE TABLE cusip_universe ( Curr_Detach float, Floater_Index text, Spread float, - PRIMARY KEY(Cusip) + subordination float, + thickness float, + updatedate date, + PRIMARY KEY(Cusip, updatedate) ); GRANT ALL ON cusip_universe TO et_user; @@ -273,6 +276,13 @@ CREATE 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; + +GRANT ALL ON latest_cusip_universe TO et_user; CREATE VIEW dealcusipmapping AS SELECT dealname, unnest("Deal Cusip List") AS Cusip from latest_clo_universe; @@ -547,11 +557,13 @@ CREATE TABLE et_deal_model_numbers( ); CREATE TABLE et_cusip_model_numbers( - Cusip varchar(9) references cusip_universe(Cusip), - dealname varchar(10), - subordination float, - thickness float, - PRIMARY KEY(Cusip) + Cusip varchar(9), + price float, + wal float, + duration float, + delta float, + updatedate date, + PRIMARY KEY(Cusip, updatedate) ); GRANT ALL ON et_deal_model_numbers TO et_user; @@ -565,3 +577,11 @@ CREATE VIEW latest_deal_model_numbers AS ORDER by dealname asc; GRANT ALL ON latest_deal_model_numbers TO et_user; + +CREATE VIEW latest_cusip_model_numbers AS + SELECT b.* + FROM (SELECT MAX(updatedate) AS latestdate, cusip FROM et_cusip_model_numbers GROUP BY cusip) a + JOIN et_cusip_model_numbers b ON a.cusip = b.cusip AND a.latestdate = b.updatedate + ORDER by cusip asc; + +GRANT ALL ON latest_cusip_model_numbers TO et_user; |