aboutsummaryrefslogtreecommitdiffstats
path: root/python/intex
diff options
context:
space:
mode:
Diffstat (limited to 'python/intex')
-rw-r--r--python/intex/__init__.py0
-rw-r--r--python/intex/common.py18
-rw-r--r--python/intex/intex_scenarios.py239
-rw-r--r--python/intex/load_indicative.py187
-rw-r--r--python/intex/load_intex_collateral.py149
5 files changed, 593 insertions, 0 deletions
diff --git a/python/intex/__init__.py b/python/intex/__init__.py
new file mode 100644
index 00000000..e69de29b
--- /dev/null
+++ b/python/intex/__init__.py
diff --git a/python/intex/common.py b/python/intex/common.py
new file mode 100644
index 00000000..557189a4
--- /dev/null
+++ b/python/intex/common.py
@@ -0,0 +1,18 @@
+import os
+
+if os.name =='nt':
+ root = "//WDsentinel/share/CorpCDOs"
+elif os.name == 'posix':
+ root = '/home/share/CorpCDOs'
+
+def sanitize_float(intex_float):
+ try:
+ intex_float = intex_float.replace(",", "")
+ if " " in intex_float: #case of combo notes
+ return float(intex_float.split(" ")[0])
+ if "(" in intex_float:
+ return - float(intex_float[1:-1])
+ else:
+ return float(intex_float)
+ except (AttributeError, ValueError):
+ return intex_float
diff --git a/python/intex/intex_scenarios.py b/python/intex/intex_scenarios.py
new file mode 100644
index 00000000..2650b25a
--- /dev/null
+++ b/python/intex/intex_scenarios.py
@@ -0,0 +1,239 @@
+import os
+import os.path
+import datetime
+import csv
+from csv import reader
+import json
+import re
+import psycopg2
+from common import root
+from db import query_db, with_connection
+import sys
+import yaml
+
+pattern1 = re.compile("REINVEST\[\w+::REINV_TBA(\d)\]\[DEAL,(\d+)\]=.*$")
+# reinv end date
+pattern2 = re.compile("(STANDARD_VAR\[)\w+(::#REINV_END,\d+\]=)(\d.*$)")
+# reinvprices 1
+pattern3 = re.compile("STANDARD_VAR\[\w+::#PRICE100_TBA(\d),(\d+)\]=")
+pattern5 = re.compile("STANDARD_VAR\[\w+::#REINVPCT_TBA(\d),(\d+)\]=")
+pattern7 = re.compile("KINGS3")
+pattern8 = re.compile("(#COLLATREINV_REINV_PCT_EXT\[)\w+(::\*\]\[DEAL,\d+\])=100")
+pattern9 = re.compile("(?P<a>SEVERITY\[\w+,\d+\]=)mkt\(70\)")
+
+# we use 84 so that it's both dividable by 2 and 3
+global_reinvfloatpercentage = 84
+global_reinvfixedpercentage = 16
+
+@with_connection
+def dealname_from_cusip(conn, cusips):
+ with conn.cursor() as c:
+ c.callproc("dealname_from_cusip", params = cusip)
+ dealnames = [d[0] for d in c.fetchall()]
+ return dealnames
+
+def get_reinv_assets(dealname, workdate):
+ sqlstr = 'select * from et_historicaldealinfo(%s, %s) where ReinvFlag IS TRUE'
+ reinvassets = query_db(sqlstr, params = (dealname, workdate), one = False)
+ d = {}
+ for line in reinvassets:
+ d[line[3]] = line[22]
+ return d
+
+def get_recovery(dealname, workdate, defaultrecovery = 50):
+ """ compute average price of defaulted assets
+ """
+ sqlstr = "select sum(coalesce(price, %s) * currentbalance)/sum(currentbalance) " + \
+ "from et_aggdealinfo_historical(%s, %s) where defaultedflag is True"
+ recovery = query_db(sqlstr, params = (defaultrecovery, dealname, workdate))
+
+ try:
+ recovery = recovery[0]
+ except TypeError:
+ recovery = defaultrecovery
+
+ if not recovery:
+ recovery = defaultrecovery
+ return float(recovery)
+
+def get_reinvenddate(dealname):
+ sqlstr = 'SELECT reinv_end_date from deal_indicative where dealname=%s'
+ reinvenddate = query_db(sqlstr, params = (dealname,))[0]
+ if reinvenddate:
+ reinvenddate = reinvenddate.strftime("%Y%m%d")
+ return reinvenddate
+ else:
+ raise Exception("missing reinvestment end date")
+
+def generate_scenarios(workdate, dealname):
+ prometheus = os.path.join(root, "Scenarios", "prometheus.sss")
+ n_scenarios = 100
+ basedir = os.path.join(root, "Scenarios", "Intex curves_" + workdate)
+ defaultedprice = get_recovery(dealname, workdate)
+ replace = "\g<a>{0:.3f}".format(defaultedprice)
+ try:
+ with open(os.path.join(basedir, "csv", dealname + ".config")) as fh:
+ config = yaml.load(fh)
+ except IOError:
+ print("{0}: config file doesn't exist".format(dealname))
+ return
+ reinvflag = config['reinvflag']
+ if reinvflag:
+ reinvenddate = get_reinvenddate(dealname)
+ reinv_assets = get_reinv_assets(dealname, workdate)
+ n_float_assets = len([v for v in reinv_assets.values() if v == 'FLOAT'])
+ n_fixed_assets = len([v for v in reinv_assets.values() if v == 'FIXED'])
+ rollingmat = config['rollingmat']
+ if n_fixed_assets == 0:
+ reinvfixedpercentage = 0
+ else:
+ reinvfixedpercentage = global_reinvfixedpercentage / n_fixed_assets
+ if n_float_assets > 0:
+ reinvfloatpercentage = (100 - n_fixed_assets * reinvfixedpercentage)/n_float_assets
+
+ try:
+ with open(os.path.join(basedir, "csv", dealname + "-reinvprices.csv"), "r") as fh:
+ dr = csv.DictReader(fh)
+ reinvprices = {f: [] for f in dr.fieldnames}
+ for line in dr:
+ for f in dr.fieldnames:
+ reinvprices[f].append("{0:.3f}".format(float(line[f])))
+ except IOError:
+ reinvflag = False
+
+ sssfile = os.path.join(basedir, "sss", dealname + ".sss")
+ if not os.path.exists(os.path.join(basedir, "sss")):
+ os.makedirs(os.path.join(basedir, "sss"))
+ cdrscenarios = os.path.join(basedir, "csv", dealname + "-cdr.csv")
+ recoveryscenarios = os.path.join(basedir, "csv", dealname + "-recovery.csv")
+ fhsss = open(sssfile, "w")
+ fhcdr = open(cdrscenarios, "r")
+ fhrecovery = open(recoveryscenarios, "r")
+ csvcdr = reader(fhcdr)
+ csvrecovery = reader(fhrecovery)
+ cdrline = next(csvcdr)
+ cdrline = "\t".join(["{0:.3f}".format(float(cdr)) for cdr in cdrline])
+ recoveryline = next(csvrecovery)
+ recoveryline = "\t".join(["{0:.3f}".format(float(recovery)) for recovery in recoveryline])
+
+ i=1
+ with open(prometheus) as fh:
+ for line in fh:
+ line = line.rstrip()
+
+ if "DEAL_NAME" in line:
+ newline = "DEAL_NAME=" + dealname.upper() + "\r\n"
+ fhsss.write(newline)
+ continue
+ if not reinvflag and pattern8.match(line):
+ line = re.sub(pattern8, r"\1{0}\2=0".format(dealname.upper()), line)
+ fhsss.write(line + "\r\n")
+ continue
+ if not reinvflag and "DO_REINV" in line:
+ fhsss.write("DO_REINV=0" + "\r\n")
+ continue
+ m = pattern1.match(line)
+ if reinvflag and m:
+ reinv_number, scen_number = m.groups()
+ key = "REINV_TBA" + reinv_number
+ if key in reinv_assets:
+ if reinv_assets[key] == "FLOAT":
+ coupon = 4
+ elif reinv_assets[key] == "FIXED":
+ coupon = 7
+ line = "REINVEST[{0}::REINV_TBA{1}][DEAL,{2}]=".format(dealname.upper(),
+ reinv_number,
+ scen_number)
+ line += "COUP_SPR={0}|AMORT=Bullet|USE_REINVEST_PIP=1|MAT_DATE={1}|".format(coupon,
+ rollingmat)
+ fhsss.write(line + "\r\n")
+ continue
+ if reinvflag and pattern2.match(line):
+ line = re.sub(pattern2, r"\1{0}\2{1}", line).format(dealname.upper(), reinvenddate)
+ fhsss.write(line + "\r\n")
+ continue
+ m = pattern3.match(line)
+ if reinvflag and m:
+ reinv_number, scen_number = m.groups()
+ if dealname=="litpt3" and reinv_number=="1":
+ line = "STANDARD_VAR[LITPT3::#REINVLOANP100,{0}]".format(scen_number) + \
+ "={0}".format(" ".join(reinvprices["REINV_TBA1"]))
+ fhsss.write(line + "\r\n")
+ continue
+ reinv_name = "REINV_TBA" + reinv_number
+ if reinv_name in reinvprices:
+ line = \
+ "STANDARD_VAR[{0}::#PRICE100_TBA{1},{2}]={3}".format(dealname.upper(),
+ reinv_number,
+ scen_number,
+ " ".join(reinvprices[reinv_name]))
+ fhsss.write(line + "\r\n")
+ continue
+ m = pattern5.match(line)
+ if reinvflag and m:
+ reinv_number, scen_number = m.groups()
+ reinv_name = "REINV_TBA" + reinv_number
+ if reinv_number=="1":
+ if dealname=="litpt3":
+ line = "STANDARD_VAR[LITPT3::#LOANREINVPCT,{0}]=100".format(scen_number)
+ fhsss.write(line + "\r\n")
+ continue
+ if dealname=="flags4":
+ line = "STANDARD_VAR[FLAGS4::#PCT100_TBA1,{0}]=100".format(scen_number)
+ fhsss.write(line + "\r\n")
+ continue
+ if reinv_name in reinv_assets:
+ if reinv_assets[reinv_name] == 'FIXED':
+ line = "STANDARD_VAR[{0}::#REINVPCT_TBA{1},{2}]={3}".format(dealname.upper(),
+ reinv_number,
+ scen_number,
+ reinvfixedpercentage)
+ elif reinv_assets[reinv_name] == 'FLOAT':
+ line = "STANDARD_VAR[{0}::#REINVPCT_TBA{1},{2}]={3}".format(dealname.upper(),
+ reinv_number,
+ scen_number,
+ reinvfloatpercentage)
+
+ fhsss.write(line + "\r\n")
+ continue
+ if pattern7.search(line):
+ line = re.sub(pattern7, dealname.upper(), line)
+ fhsss.write(line + "\r\n")
+ continue
+ if "LOSS_RATE[DEAL,{0}]".format(i) in line:
+ newcdrline = "LOSS_RATE[DEAL,{0}]=".format(i) + cdrline
+ fhsss.write(newcdrline + "\r\n")
+ continue
+ if "LOSS_SEVERITY[DEAL,{0}]".format(i) in line:
+ newrecoveryline = "LOSS_SEVERITY[DEAL,{0}]=".format(i) + recoveryline
+ fhsss.write(newrecoveryline + "\r\n")
+ i = i + 1
+ if i <= n_scenarios:
+ cdrline = next(csvcdr)
+ cdrline = "\t".join(["{0:.3f}".format(float(cdr)) for cdr in cdrline]) + "\r\n"
+ recoveryline = next(csvrecovery)
+ recoveryline = "\t".join(["{0:.3f}".format(float(recovery)) \
+ for recovery in recoveryline]) + "\r\n"
+ continue
+ if "LOSS_NONPERF_SEVERITY" in line:
+ line = re.sub(pattern9, replace, line)
+ fhsss.write(line + "\r\n")
+ fhsss.close()
+ fhrecovery.close()
+ fhcdr.close()
+ print("generated scenarios for: {0}".format(dealname))
+
+if __name__ == "__main__":
+ if len(sys.argv) > 1:
+ workdate = sys.argv[1]
+ else:
+ workdate = str(datetime.date.today())
+ if len(sys.argv) > 2:
+ dealnames = sys.argv[2:]
+ else:
+ dealnames = [d.split(".")[0] for d in
+ os.listdir(os.path.join(root, "Scenarios",
+ "Intex curves_" + workdate, "csv"))
+ if "RData" in d]
+ for dealname in dealnames:
+ generate_scenarios(workdate, dealname)
diff --git a/python/intex/load_indicative.py b/python/intex/load_indicative.py
new file mode 100644
index 00000000..8fd75b6e
--- /dev/null
+++ b/python/intex/load_indicative.py
@@ -0,0 +1,187 @@
+import psycopg2
+import os
+import datetime
+from datetime import date
+import csv, sys, re
+import pdb
+from common import root, sanitize_float
+from db import conn
+import logging
+
+def convertToNone(s):
+ return None if s in ["", "-", "NR"] else s
+
+def upload_cusip_data(conn, filename):
+ dealupdate = {}
+ with open( filename, "r") as fh:
+ dr = csv.DictReader(fh, dialect='excel-tab')
+ data = []
+ deals_to_update = []
+
+ for line in dr:
+ if "ISIN" not in line:
+ line['ISIN'] = None
+ sp = line["Tranche"].split(",")
+ if len(sp)==2:
+ line["dealname"], line["tranche"] = sp
+ 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:
+ with conn.cursor() as c:
+ c.execute("SELECT \"Latest Update\" FROM clo_universe " \
+ "WHERE dealname = %s ORDER BY \"Latest Update\" DESC", (dealname,))
+ dealupdate[dealname] = c.fetchone()[0]
+
+ sqlstring = "SELECT updatedate FROM latest_cusip_universe 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]
+ 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']:
+ 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:
+ logging.error(e)
+ logging.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"
+ with conn.cursor() as c:
+ c.execute(sqlstr)
+ deallist1 = dict(c)
+ sqlstr = "select dealname from deal_indicative"
+ with conn.cursor() as c:
+ c.execute(sqlstr)
+ deallist2 = [d[0] for d in c]
+ conn.commit()
+ with open( filename, "r") as fh:
+ dr = csv.DictReader(fh, dialect='excel-tab')
+ data = []
+ for line in dr:
+ if not line['Deal Name, Tranche Name'] or (line['Deal Name, Tranche Name'] == 'Unknown Security'):
+ continue
+ if not line['Latest Update']:
+ continue
+ for key in line.keys():
+ line[key] = convertToNone(line[key])
+ line["CDOpercent"] = line["CDO Pct of Assets that are Structured Finance Obligations"]
+ line["Paid Down"] = None
+ if "Paid Down" in line["Latest Update"]:
+ line["Paid Down"] = re.sub("Paid Down: ","", line["Latest Update"])
+ line["Latest Update"] = line["Paid Down"]
+ for field in ["Deal Issue Date", "Deal Termination Date", "Reinv End Date", \
+ "Latest Update", "Pay Day", "Deal First Pay Date", "Paid Down"]:
+ if line[field]:
+ try:
+ line[field] = datetime.datetime.strptime(line[field], '%b %d, %Y').date()
+ except ValueError:
+ pdb.set_trace()
+ if line["Pay Day"]:
+ line["Pay Day"] = line["Pay Day"].day
+ for key in ["Collection Account Principal Balance", "Collection Account Interest Balance",
+ "Curr Deal Bal", "Tranche Curr Bal",
+ "CDO Pct of Assets that are Structured Finance Obligations",
+ "CDO Defaulted Security Balance (Reported)"]:
+ if line[key]:
+ line[key] = sanitize_float(line[key])
+ line['Deal/Tranche ID'] = line['Deal/Tranche ID'].lower()
+ dealname = line['Deal/Tranche ID']
+ line['defaultedbal'] = line["CDO Defaulted Security Balance (Reported)"]
+ if dealname not in deallist2:
+ for key in ["Orig Deal Bal", "Tranche Orig Bal"]:
+ if line[key]:
+ line[key] = sanitize_float(line[key])
+ if line['Deal CUSIP List']:
+ line['Deal CUSIP List'] = line['Deal CUSIP List'].split(",")
+ sqlstr = "INSERT INTO deal_indicative VALUES( %(Deal/Tranche ID)s, %(Deal Name)s, " \
+ "%(Collateral Manager)s, %(Deal Issue Date)s, %(Deal Termination Date)s, " \
+ "%(Pay Day)s, %(Reinv End Date)s, %(Deal First Pay Date)s, %(Orig Deal Bal)s, " \
+ "%(Tranche Orig Bal)s, %(Deal CUSIP List)s, %(Paid Down)s)"
+ try:
+ with conn.cursor() as c:
+ c.execute(sqlstr, line)
+ except (psycopg2.DataError, KeyError) as detail:
+ logging.error(detail)
+ pdb.set_trace()
+ with conn.cursor() as c:
+ if line['Paid Down']:
+ c.execute("UPDATE deal_indicative SET paid_down=%s WHERE dealname=%s",
+ (line['Paid Down'], dealname))
+ if dealname not in deallist1 or line['Latest Update'] not in deallist1[dealname]:
+ sqlstring = \
+ "INSERT INTO clo_universe " \
+ "VALUES (%(Deal/Tranche ID)s, %(Curr Deal Bal)s, %(Tranche Curr Bal)s, " \
+ "%(Tranche Factor)s, %(Collection Account Principal Balance)s, " \
+ "%(Collection Account Interest Balance)s, %(CDOpercent)s, %(defaultedbal)s, " \
+ "%(Coupon)s, %(Latest Update)s)"
+ try:
+ with conn.cursor() as c:
+ c.execute(sqlstring, line)
+ deallist1[dealname] = [line['Latest Update']]
+ except (psycopg2.DataError, KeyError) as detail:
+ logging.error(detail)
+ pdb.set_trace()
+ conn.commit()
+
+if __name__=="__main__":
+ if len(sys.argv) > 1:
+ workdate = sys.argv[1]
+ else:
+ workdate = str(datetime.date.today())
+ files = [os.path.join(root, "data", "Indicative_" + workdate, f) for f in
+ os.listdir(os.path.join(root, "data", "Indicative_" + workdate))]
+ cusip_files = [f for f in files if "TrInfo" in f]
+ deal_files = [f for f in files if "TrInfo" not in f]
+
+ #first load deal data
+ for deal in deal_files:
+ upload_deal_data(conn, deal)
+ #then load tranche data
+ for cusip in cusip_files:
+ upload_cusip_data(conn, cusip)
+ conn.close()
diff --git a/python/intex/load_intex_collateral.py b/python/intex/load_intex_collateral.py
new file mode 100644
index 00000000..1b0a7c45
--- /dev/null
+++ b/python/intex/load_intex_collateral.py
@@ -0,0 +1,149 @@
+import psycopg2
+import os, csv, datetime
+import pdb
+from common import root, sanitize_float
+from db import conn
+import sys
+import uuid
+from load_indicative import upload_cusip_data, upload_deal_data
+import logging
+
+fields = ['Asset Name', 'Issuer', 'Contributed Balance', 'Asset Maturity Date',
+ 'Asset Subtype', 'Asset Type', 'Gross Coupon', 'Spread', \
+ 'Frequency', 'Next Paydate', 'Second Lien', 'LoanX ID', 'CUSIP',
+ 'Market Price', 'Market Price Source', 'Market Price Date', 'Fixed or Float', \
+ 'Defaulted Flag', 'Security Sub-Category', 'Structured Finance Security', \
+ 'Life Floor', 'Reinvest Collat', 'Native Currency', "Moody's Industry Name",
+ "Country"]
+
+def convertToNone(s):
+ return None if s=='' else s
+
+def windows1252_encoder(fh):
+ for line in fh:
+ yield line.decode('windows-1252').encode('utf-8')
+
+def upload_data(conn, dealnames, workdate):
+ for dealname in dealnames:
+ basedir = os.path.join(root, "data", "Collaterals_" + workdate)
+ if sys.version_info[0]==2:
+ fh = open(os.path.join(basedir, dealname.upper() + "_AD.txt"))
+ fh = windows1252_encoder(fh)
+ else:
+ fh = open(os.path.join(basedir, dealname.upper() + "_AD.txt"), encoding="windows-1252")
+
+ dr = csv.DictReader(fh, dialect = 'excel-tab')
+ missingfields = set(fields).union({'Gross Margin'}) - set(dr.fieldnames)
+ if "LoanX ID" in missingfields:
+ msg = "{0}: LoanX ID column is missing. Probably an error in exporting from intex"
+ logging.warning(msg.format(dealname))
+ data = {}
+ for line in dr:
+ for f in missingfields:
+ line[f] = None
+ for key in ['LoanX ID', 'CUSIP', 'Fixed or Float']:
+ if line[key]:
+ line[key] = line[key].upper()
+ if line['Asset Subtype']:
+ line['Asset Subtype'] = line['Asset Subtype'].replace("Reinvest ", "").replace("Reinv ","")
+ for key, l in [('LoanX ID', 8), ('CUSIP', 9), ('Asset Subtype', 10)]:
+ if line[key]:
+ if len(line[key]) > l:
+ logging.warning("dubious {0} found: {1}".format(key, line[key]))
+ line[key] = line[key][:l]
+
+ if 'Reinvest Collat' in line and line['Reinvest Collat'].upper() == 'Y':
+ # assume it's a reinvestment asset
+ line['Reinvest Collat'] = True
+ line['Issuer'] = line['ID Number']
+ if not line['Spread']:
+ line['Spread'] = line['Gross Margin']
+
+ for field in ['Spread', 'Gross Coupon', 'Market Price', 'Contributed Balance']:
+ line[field] = sanitize_float(line[field])
+ if line['Market Price'] == 0:
+ line['Market Price'] = None
+ #we store the Libor Floor in the database, so Life Floor is really Libor Floor
+ if line['Life Floor'] == "No limit":
+ line['Life Floor'] = 0
+ elif line['Life Floor']:
+ try:
+ line['Life Floor'] = float(line['Life Floor']) - float(line['Spread'])
+ except ValueError:
+ line['Life Floor'] = float('Nan')
+
+ # we take care of reinvestment asset lines
+ if not line['Asset Name']:
+ line['Asset Name'] = 'Reinv'
+ r = [convertToNone(line[field]) for field in fields]
+ #sometimes the Asset Name is not unique (we add random tag in this case)
+ if r[0] in data:
+ r[0] = r[0] + str(uuid.uuid4())[:3]
+ data[r[0]] = r[1:]
+ fh.close()
+ sqlstr1 = "select distinct(updatedate) from et_collateral where dealname= %s"
+ sqlstr2 = "select max(\"Latest Update\") from clo_universe where dealname= %s and \"Latest Update\"<=%s"
+ with conn.cursor() as c:
+ c.execute(sqlstr1, (dealname,))
+ old_update_dates = [date[0] for date in c]
+ c.execute(sqlstr2, (dealname, workdate))
+ updatedate = c.fetchone()[0]
+ conn.commit()
+ # sanity check if we already have the data
+ reinsert = False
+ if updatedate in old_update_dates:
+ sqlstr = "SELECT count(*) FROM et_collateral where dealname = %s and updatedate= %s"
+ with conn.cursor() as c:
+ c.execute(sqlstr, (dealname, updatedate))
+ currlen = c.fetchone()[0]
+ conn.commit()
+ if currlen != len(data): #then we delete and just reupload
+ logging.warning("{0} has {1} rows in the database " \
+ "and current collateral file has {2}".format(dealname, currlen, len(data)))
+ with conn.cursor() as c:
+ sqlstr = "DELETE FROM et_collateral where dealname = %s and updatedate = %s"
+ c.execute(sqlstr, (dealname, updatedate))
+ conn.commit()
+ reinsert = True
+
+ if reinsert or not old_update_dates or updatedate not in old_update_dates:
+ sql_fields = ["dealname", "updatedate", "name", "IssuerName", "CurrentBalance",
+ "Maturity", "AssetSubtype", "AssetType", "GrossCoupon",
+ "Spread", "Frequency", "NextPaydate", "SecondLien", "LoanXID",
+ "Cusip", "IntexPrice", "IntexPriceSource", "IntexPriceDate",
+ "FixedOrFloat", "DefaultedFlag", "CovLite", "isCDO",
+ "Liborfloor", "ReinvFlag", "Currency", "Industry", "Country"]
+ sqlstr = "INSERT INTO ET_COLLATERAL({0}) VALUES({1})".format(",".join(sql_fields),
+ ",".join(["%s"] * len(sql_fields)))
+ with conn.cursor() as c:
+ try:
+ c.executemany(sqlstr, [(dealname, updatedate, k) + tuple(v) for k, v in data.items()])
+ except (psycopg2.DataError, psycopg2.IntegrityError, TypeError) as detail:
+ logging.error(detail)
+ pdb.set_trace()
+ conn.commit()
+
+def intex_data(conn, workdate):
+ dealnames = [d.replace("_AD.txt", "").lower() for d in
+ os.listdir(os.path.join(root, "data", "Collaterals_" + workdate))]
+ basedir = os.path.join(root, "data", "Indicative_" + workdate)
+ files = [os.path.join(basedir, f) for f in os.listdir(basedir)]
+ cusip_files = [f for f in files if "TrInfo" in f]
+ deal_files = [f for f in files if "TrInfo" not in f]
+ #first load deal data
+ for deal_file in deal_files:
+ upload_deal_data(conn, deal_file)
+ #then load tranche data
+ for cusip_file in cusip_files:
+ upload_cusip_data(conn, cusip_file)
+
+ upload_data(conn, dealnames, workdate)
+
+if __name__ == "__main__":
+ if len(sys.argv) > 1:
+ workdate = sys.argv[1]
+ else:
+ workdate = str(datetime.date.today())
+ intex_data(conn, workdate)
+ conn.close()
+ print("done")
generated by cgit v1.2.3-70-g09d2 (git 2.52.0) at 2025-12-22 06:33:13 +0000