diff options
Diffstat (limited to 'python/backfill_cds.py')
| -rw-r--r-- | python/backfill_cds.py | 29 |
1 files changed, 15 insertions, 14 deletions
diff --git a/python/backfill_cds.py b/python/backfill_cds.py index 0975629e..90687d57 100644 --- a/python/backfill_cds.py +++ b/python/backfill_cds.py @@ -2,9 +2,9 @@ import os import common import csv import datetime -from mlpdb import conn +from db import connmlpdb import pdb - +from import_cds_quotes import get_current_tickers def convert(x): try: @@ -12,21 +12,21 @@ def convert(x): except: return None -with open(os.path.join(common.root, "Scenarios", "bbg-markit.csv")) as fh: +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 = {} -with conn.cursor() as c: +with connmlpdb.cursor() as c: for k, v in newtickermapping.items(): c.execute(sqlstr, v) r = c.fetchone() tm[k] = r['cds_curve'] -rootdir = os.path.join(common.root, "Scenarios", "Calibration") -filelist = [f for f in os.listdir(rootdir) if "igs_singlenames" in f] +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)" @@ -34,9 +34,10 @@ sqlstr = "INSERT INTO cds_quotes(date, curve_ticker, upfrontbid, upfrontask," \ 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[16:26], "%Y-%m-%d").date() - with conn.cursor() as c: + 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) @@ -45,17 +46,17 @@ for f in filelist: for line in csvreader: if line['ticker']=='': continue - knowntickers = [(date, t) for t in tm[line['ticker']][1:] if t in l] - unknowntickers = [(tenor[i], t) for i, t in enumerate(tm[line['ticker']][1:], 1)] - with conn.cursor() as c: + 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) - conn.commit() + 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 conn.cursor() as c: + with connmlpdb.cursor() as c: c.executemany(sqlstr, toinsert) - conn.commit() + connmlpdb.commit() # tenord = {'3Y': '3yr', '5Y': '5yr', '7Y': '7yr', '10Y': '10yr'} # sqlstr = "INSERT INTO tranche_quotes VALUES({0})".format(",".join(["%s"]*17)) |