diff options
Diffstat (limited to 'python')
| -rw-r--r-- | python/backfill_cds.py | 82 |
1 files changed, 52 insertions, 30 deletions
diff --git a/python/backfill_cds.py b/python/backfill_cds.py index 90687d57..b40bf2d9 100644 --- a/python/backfill_cds.py +++ b/python/backfill_cds.py @@ -5,6 +5,7 @@ import datetime from db import connmlpdb import pdb from import_cds_quotes import get_current_tickers +import psycopg2 def convert(x): try: @@ -12,51 +13,72 @@ def convert(x): except: return None -with open(os.path.join(common.root, "Tranche_data", "bbg-markit.csv")) as fh: - csvreader = csv.DictReader(fh) - newtickermapping = {line['Markit_ticker']: (line['Bbg_ticker'], line['company_id']) for line in csvreader} - -sqlstr = "select cds_curve from cds_issuers where ticker=%s and company_id=%s" - -tm = {} +sqlstr = "select markit_ticker, cds_curve from index_members(%s, %s)" with connmlpdb.cursor() as c: - for k, v in newtickermapping.items(): - c.execute(sqlstr, v) - r = c.fetchone() - tm[k] = r['cds_curve'] + c.execute(sqlstr, ('HY17', datetime.date(2014,5,10))) + bbg_markit_mapping = {a: b[1:] for a, b in c} -rootdir = os.path.join(common.root, "Tranche_Data") -filelist = [f for f in os.listdir(rootdir) if "hy21_singlenames" in f or "hy19_singlenames" in f] +basedir = "/home/share/CorpCDOs/Scenarios/Calibration" +filelist = [f for f in os.listdir(basedir) if "hy17_singlenames" in f] sqlstr = "INSERT INTO cds_quotes(date, curve_ticker, upfrontbid, upfrontask," \ "runningbid, runningask, source, recovery) VALUES(%s, %s, %s, %s, %s, %s, %s, %s)" -sqlstr2 = "SELECT curve_ticker FROM cds_quotes where date=%s" -sqlstr3 = "DELETE from cds_quotes where date=%s and curve_ticker=%s" -tenor = ['6M']+[str(i)+'Y' for i in [1, 2, 3, 4, 5, 7, 10]] - +tenors = [str(i)+'Y' for i in [1, 2, 3, 4, 5, 7, 10]] for f in filelist: - date = datetime.datetime.strptime(f[17:27], "%Y-%m-%d").date() - with connmlpdb.cursor() as c: - c.execute(sqlstr2, (date,)) - l = set([t[0] for t in c]) print(f) - with open(os.path.join(rootdir, f)) as fh: + date = f[17:27] + with open(os.path.join(basedir, f)) as fh: csvreader = csv.DictReader(fh) + c = connmlpdb.cursor() for line in csvreader: - if line['ticker']=='': + if not line['ticker']: continue - knowntickers = [(date, t) for t in tm[line['ticker']][1:6] if t in l] - unknowntickers = [(tenor[i], t) for i, t in enumerate(tm[line['ticker']][1:6], 1)] - with connmlpdb.cursor() as c: - c.executemany(sqlstr3, knowntickers) - connmlpdb.commit() toinsert = [(date, ticker, convert(line[tenor]), convert(line[tenor]), float(line['running']), float(line['running']), 'MKIT', - float(line['recovery'])/100) for tenor, ticker in unknowntickers] - with connmlpdb.cursor() as c: + float(line['recovery'])/100) for tenor, ticker in + zip(tenors, bbg_markit_mapping[line['ticker']])] + try: c.executemany(sqlstr, toinsert) connmlpdb.commit() + except psycopg2.IntegrityError: + print("%s already in there for date %s" % (line['ticker'], date)) + connmlpdb.rollback() + continue + c.close() + +# rootdir = os.path.join(common.root, "Tranche_Data") +# filelist = [f for f in os.listdir(rootdir) if "hy21_singlenames" in f or "hy19_singlenames" in f] + +# sqlstr = "INSERT INTO cds_quotes(date, curve_ticker, upfrontbid, upfrontask," \ +# "runningbid, runningask, source, recovery) VALUES(%s, %s, %s, %s, %s, %s, %s, %s)" + +# sqlstr2 = "SELECT curve_ticker FROM cds_quotes where date=%s" +# sqlstr3 = "DELETE from cds_quotes where date=%s and curve_ticker=%s" +# tenor = ['6M']+[str(i)+'Y' for i in [1, 2, 3, 4, 5, 7, 10]] + +# for f in filelist: +# date = datetime.datetime.strptime(f[17:27], "%Y-%m-%d").date() +# with connmlpdb.cursor() as c: +# c.execute(sqlstr2, (date,)) +# l = set([t[0] for t in c]) +# print(f) +# with open(os.path.join(rootdir, f)) as fh: +# csvreader = csv.DictReader(fh) +# for line in csvreader: +# if line['ticker']=='': +# continue +# knowntickers = [(date, t) for t in tm[line['ticker']][1:6] if t in l] +# unknowntickers = [(tenor[i], t) for i, t in enumerate(tm[line['ticker']][1:6], 1)] +# with connmlpdb.cursor() as c: +# c.executemany(sqlstr3, knowntickers) +# connmlpdb.commit() +# toinsert = [(date, ticker, convert(line[tenor]), convert(line[tenor]), +# float(line['running']), float(line['running']), 'MKIT', +# float(line['recovery'])/100) for tenor, ticker in unknowntickers] +# with connmlpdb.cursor() as c: +# c.executemany(sqlstr, toinsert) +# connmlpdb.commit() # tenord = {'3Y': '3yr', '5Y': '5yr', '7Y': '7yr', '10Y': '10yr'} # sqlstr = "INSERT INTO tranche_quotes VALUES({0})".format(",".join(["%s"]*17)) |