diff options
Diffstat (limited to 'python')
| -rw-r--r-- | python/backfill_cds.py | 29 | ||||
| -rw-r--r-- | python/insert_tranche_quotes_old.py | 9 |
2 files changed, 21 insertions, 17 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)) diff --git a/python/insert_tranche_quotes_old.py b/python/insert_tranche_quotes_old.py index bf99ec2a..176cfdb1 100644 --- a/python/insert_tranche_quotes_old.py +++ b/python/insert_tranche_quotes_old.py @@ -2,6 +2,7 @@ from sqlalchemy import Table, create_engine, MetaData import os
import csv
import datetime
+import pdb
engine = create_engine('postgresql://mlpdb_user:Serenitas1@debian/mlpdb')
metadata = MetaData(bind = engine)
@@ -17,17 +18,19 @@ for quotefile in quotefiles: quotedate = datetime.datetime.strptime(os.path.splitext(quotefile)[0].split("_")[-1], "%Y-%m-%d").date()
series=19 if "19" in quotefile else 21
index = os.path.splitext(quotefile)[0].split("_")
- if quotedate != datetime.date(2013, 8, 2):
+ if quotedate != datetime.date(2014, 6, 6) or series!=19:
continue
+
if quotedate <= datetime.date(2014, 5, 21):
version=1
else:
version=2
- print("pomme")
reader = csv.DictReader(fh)
data = []
+
for i, csvdict in enumerate(reader):
- d = {'quotedate' : quotedate,
+ timestamp = datetime.datetime.strptime(csvdict['bidTime'], "%m/%d/%Y %H:%M:%S %p")
+ d = {'quotedate' : timestamp,
'indexrefprice': csvdict['bidRefPrice'],
'indexrefspread': 500,
'tranchedelta': csvdict['bidDelta'],
|