diff options
Diffstat (limited to 'python')
| -rw-r--r-- | python/cusip_universe.py | 48 |
1 files changed, 30 insertions, 18 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: |