diff options
Diffstat (limited to 'python/import_cds_quotes.py')
| -rw-r--r-- | python/import_cds_quotes.py | 74 |
1 files changed, 42 insertions, 32 deletions
diff --git a/python/import_cds_quotes.py b/python/import_cds_quotes.py index 6128c170..835f40c7 100644 --- a/python/import_cds_quotes.py +++ b/python/import_cds_quotes.py @@ -3,10 +3,9 @@ from common import root import csv import datetime from db import connmlpdb +import re, sys +from pandas.tseries.offsets import BDay import pdb -import re - -workdate= datetime.date(2014, 7, 3) def convert(x): try: @@ -14,44 +13,55 @@ def convert(x): 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'] - +def get_current_tickers(connmlpdb, workdate): + sqlstr = "SELECT markit_ticker, markit_tier, cds_curve from index_members(%s, %s)" + markit_bbg_mapping = {} + all_tickers = 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: + all_tickers.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 markit_bbg_mapping: + del markit_bbg_mapping[hykey] ## we only keep the tightest quote + markit_bbg_mapping[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)" + return (all_tickers, markit_bbg_mapping) -filelist = [f for f in os.listdir(os.path.join(root, "Tranche_data", "CDS")) if f.endswith('csv')] +def insert_cds(connmlpdb, workdate): + all_tickers, markit_bbg_mapping = get_current_tickers(connmlpdb, workdate) + filename = "cds eod {0}.csv".format(datetime.datetime.strftime(workdate, "%Y%m%d")) + 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)" -for f in filelist: - d3 = set([]) - workdate = datetime.datetime.strptime(re.match("[^\d]*(\d*)", f).groups()[0], "%Y%m%d") + tickers_found = set([]) 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: + with open(os.path.join(root, "Tranche_data", "CDS", filename)) as fh: csvreader = csv.DictReader(fh) with connmlpdb.cursor() as c: for line in csvreader: - d3.add((line['Ticker'], line['Tier'])) + tickers_found.add((line['Ticker'], line['Tier'])) k = (line['Ticker'], line['Tier'], line['Ccy'], line['DocClause'], float(line['RunningCoupon'])) - if k in d: + try: 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])]) + float(line['RunningCoupon'])*10000, float(line['RunningCoupon'])*10000, + 'MKIT', convert(line['RealRecovery'])/100) + for i, t in enumerate(markit_bbg_mapping[k])]) + except KeyError: + continue connmlpdb.commit() - print(workdate) - print(d2-d3) + print(all_tickers-tickers_found) + +if __name__=="__main__": + if len(sys.argv)>=2: + workdate = datetime.datetime.strptime(sys.argv[1], "%Y-%m-%d") + else: + workdate = datetime.datetime.today()-BDay(1) + workdate = workdate.date() + insert_cds(connmlpdb, workdate) |