1 files changed, 82 insertions, 0 deletions
diff --git a/python/backfill_cds.py b/python/backfill_cds.py
new file mode 100644
index 00000000..0975629e
--- /dev/null
+++ b/python/backfill_cds.py
@@ -0,0 +1,82 @@
+import os
+import common
+import csv
+import datetime
+from mlpdb import conn
+import pdb
+
+
+def convert(x):
+    try:
+        return float(x)
+    except:
+        return None
+
+with open(os.path.join(common.root, "Scenarios", "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:
+    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]
+
+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[16:26], "%Y-%m-%d").date()
+    with conn.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:] if t in l]
+            unknowntickers = [(tenor[i], t) for i, t in enumerate(tm[line['ticker']][1:], 1)]
+            with conn.cursor() as c:
+                c.executemany(sqlstr3, knowntickers)
+                conn.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:
+                c.executemany(sqlstr, toinsert)
+                conn.commit()
+
+# tenord = {'3Y': '3yr', '5Y': '5yr', '7Y': '7yr', '10Y': '10yr'}
+# sqlstr = "INSERT INTO tranche_quotes VALUES({0})".format(",".join(["%s"]*17))
+
+# # with open("../../CDXNAIG Tranches.csv") as fh:
+# #     csvreader=csv.DictReader(fh)
+# #     toinsert = [(datetime.datetime.strptime(line['Date'], "%m/%d/%Y"), 'IG', line['Index Series'],
+# #                  line['Index Version'], tenord[line['Index Term']], int(float(line['Attachment'])*100),
+# #                  int(float(line['Detachment'])*100), convert(line['Tranche Upfront Mid']),
+# #                  convert(line['Tranche Spread Mid']),
+# #                  0, float(line['IndexRefSpread'])*10000, None, None, None, None, None, 'MKIT')
+# #                 for line in csvreader if line['Date']!='7/5/2010']
+# #     with conn.cursor() as c:
+# #         c.executemany(sqlstr, toinsert)
+# #         conn.commit()
+
+
+
+# with conn.cursor() as c:
+#     c.execute("SELECT * FROM quotes where index='IG'")
+#     toinsert = [tuple(r[:7])+(r['upfront'],r['running'])+(None,r['refbasketprice'])+tuple(r[10:]) for r in c]
+# with conn.cursor() as c:
+#     c.executemany(sqlstr, toinsert)
+#     conn.commit()