diff options
Diffstat (limited to 'python')
| -rw-r--r-- | python/intex/load_indicative.py | 98 |
1 files changed, 50 insertions, 48 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" |