diff options
Diffstat (limited to 'python/cusip_universe.py')
| -rw-r--r-- | python/cusip_universe.py | 100 |
1 files changed, 0 insertions, 100 deletions
diff --git a/python/cusip_universe.py b/python/cusip_universe.py deleted file mode 100644 index 15102f2b..00000000 --- a/python/cusip_universe.py +++ /dev/null @@ -1,100 +0,0 @@ -import psycopg2 -import os -import os.path -import datetime -from datetime import date -import csv -import pdb -import sys -import common -from common import query_db - -def convertToNone(s): - return None if s=="" else s - -def sanitize_float(intex_float): - if intex_float.count(".") > 1: - raise ValueError - intex_float = intex_float.replace(",", "") - if "(" in intex_float: - intex_float = - float(intex_float[1:-1]) - else: - intex_float = float(intex_float) - return intex_float - -def upload_data(workdate, conn): - 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 = [] - c = conn.cursor() - for line in dr: - if "ISIN" not in line: - line['ISIN'] = None - if "," in line['Tranche']: - line["dealname"], line["tranche"] = line["Tranche"].split(",") - else: - continue - line["dealname"] = line["dealname"].lower() - dealname = line['dealname'] - line = {k: convertToNone(v) for k, v in line.items()} - if dealname not in dealupdate: - dealupdate[dealname] = query_db("SELECT \"Latest Update\" FROM clo_universe " \ - "WHERE dealname = %s ORDER BY \"Latest Update\" DESC", - params = (dealname,))[0] - - sqlstring = "SELECT updatedate FROM latest_cusip_universe WHERE cusip = %s" - curr_date = query_db(sqlstring, params = (line['CUSIP'],)) - 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] - try: - for key in ['Curr Balance', 'Orig Balance', 'Orig Attachment Point', - 'Curr Attachment Point', 'Orig Detachment Point', - 'Curr Detachment Point', 'Factor', 'Coupon', 'Floater Spread']: - if line[key]: - line[key] = sanitize_float(line[key]) - line[key] = convertToNone(line[key]) - except ValueError: - continue - - 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, 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, " \ - "%(updatedate)s)" - try: - c.execute(sqlstring, line) - except psycopg2.DataError: - pdb.set_trace() - print("uploaded: {0}".format(line['CUSIP'])) - conn.commit() - - for dealname in deals_to_update: - data = query_db("SELECT p_cusip, p_curr_subordination, "\ - "p_curr_thickness from et_deal_subordination(%s)", - params = (dealname,), - one = False) - data = [ (t[1], t[2], t[0], dealupdate[dealname]) for t in data] - c.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: - workdate = sys.argv[1] - else: - workdate = str(datetime.date.today()) - - upload_data(workdate, common.conn) - common.conn.close() |