1 files changed, 57 insertions, 0 deletions
diff --git a/python/import_cds_quotes.py b/python/import_cds_quotes.py
new file mode 100644
index 00000000..6128c170
--- /dev/null
+++ b/python/import_cds_quotes.py
@@ -0,0 +1,57 @@
+import os
+from common import root
+import csv
+import datetime
+from db import connmlpdb
+import pdb
+import re
+
+workdate= datetime.date(2014, 7, 3)
+
+def convert(x):
+    try:
+        return float(x[:-1])
+    except ValueError:
+        return None
+
+sqlstr = "SELECT markit_ticker, markit_tier, cds_curve from index_members(%s, %s)"
+d = {}
+d2 = set([])
+for index in ['HY9', 'HY10', 'HY15', 'HY17', 'HY19', 'IG9', 'IG19', 'IG21']:
+    spread=0.05 if 'HY' in index else 0.01
+    with connmlpdb.cursor() as c:
+        c.execute(sqlstr, (index, workdate))
+        for line in c:
+            d2.add((line['markit_ticker'], line['markit_tier']))
+            key = (line['markit_ticker'], line['markit_tier'], 'USD', 'XR', spread)
+            hykey = key[:-1]+(0.05,)
+            if hykey in d:
+                del d[hykey] ## we only keep the tightest quote
+            d[key] = line['cds_curve']
+
+
+colnames = ['Upfront'+tenor for tenor in ['6m', '1y', '2y', '3y', '4y', '5y', '7y', '10y']]
+sqlstr = "INSERT INTO cds_quotes(date, curve_ticker, upfrontbid, upfrontask," \
+         "runningbid, runningask, source, recovery) VALUES(%s, %s, %s, %s, %s, %s, %s, %s)"
+
+filelist = [f for f in os.listdir(os.path.join(root, "Tranche_data", "CDS")) if f.endswith('csv')]
+
+for f in filelist:
+    d3 = set([])
+    workdate = datetime.datetime.strptime(re.match("[^\d]*(\d*)", f).groups()[0], "%Y%m%d")
+    with connmlpdb.cursor() as c:
+        c.execute("DELETE from cds_quotes where date=%s", (workdate,))
+    with open(os.path.join(root, "Tranche_data", "CDS", f)) as fh:
+        csvreader = csv.DictReader(fh)
+        with connmlpdb.cursor() as c:
+            for line in csvreader:
+                d3.add((line['Ticker'], line['Tier']))
+                k = (line['Ticker'], line['Tier'], line['Ccy'], line['DocClause'], float(line['RunningCoupon']))
+                if k in d:
+                    c.executemany(sqlstr,
+                                  [(workdate, t, convert(line[colnames[i]]), convert(line[colnames[i]]),
+                                    float(line['RunningCoupon'])*10000, float(line['RunningCoupon'])*10000, 'MKIT',
+                                    convert(line['RealRecovery'])/100) for i, t in enumerate(d[k])])
+        connmlpdb.commit()
+    print(workdate)
+    print(d2-d3)