import os from common import root import csv import datetime from db import serenitasdb import re, sys from pandas.tseries.offsets import BDay import pandas as pd import numpy as np import psycopg2 from sqlalchemy import create_engine def convert(x): try: return float(x[:-1]) except ValueError: return None index_list = ['HY9', 'HY10', 'HY15', 'HY17', 'HY19', 'HY21', 'HY22', 'HY23', 'IG9', 'IG19', 'IG21', 'IG22', 'IG23', 'XO22'] def doc_clause_from_index(index, date): if index[:2] in ['HY', 'IG']: if date>=datetime.date(2014, 9, 19): return 'XR14' else: return 'XR' else: if date>=datetime.date(2014, 9, 19): return 'MM14' else: return 'MM' def get_current_tickers(database, workdate): sqlstr = "SELECT markit_ticker, markit_tier, currency, cds_curve from index_members(%s, %s)" markit_bbg_mapping = {} all_tickers = set([]) for index in index_list: spread=0.05 if index[:2] in ['XO', 'HY'] else 0.01 doc_clause = doc_clause_from_index(index, workdate) with database.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'], line['currency'], doc_clause, 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'] return (all_tickers, markit_bbg_mapping) def insert_cds(database, workdate): all_tickers, markit_bbg_mapping = get_current_tickers(database, 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)" tickers_found = set([]) with database.cursor() as c: c.execute("DELETE from cds_quotes where date=%s", (workdate,)) database.commit() with open(os.path.join(root, "Tranche_data", "CDS", filename)) as fh: csvreader = csv.DictReader(fh) with database.cursor() as c: for line in csvreader: k = (line['Ticker'], line['Tier'], line['Ccy'], line['DocClause'], float(line['RunningCoupon'])) if k in markit_bbg_mapping: 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(markit_bbg_mapping[k])]) tickers_found.add((line['Ticker'], line['Tier'])) database.commit() print(all_tickers-tickers_found) def insert_cds_single(database, workdate, bbg_tickers, mkt_tuple): 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)" with open(os.path.join(root, "Tranche_data", "CDS", filename)) as fh: csvreader = csv.DictReader(fh) with database.cursor() as c: for line in csvreader: if (line['Ticker'], line['Tier'], line['DocClause'], line['RunningCoupon'], line['Ccy']) == \ mkt_tuple: 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(bbg_tickers)]) except psycopg2.IntegrityError: database.rollback() database.commit() def insert_index(engine, workdate): basedir = os.path.join(root, 'Tranche_data', 'Composite_reports') filenames = [os.path.join(basedir, f) for f in os.listdir(basedir) if 'Indices' in f] name_mapping = {"CDXNAHY":"HY", "CDXNAIG":"IG",'iTraxx Eur': "EU", 'iTraxx Eur Xover': "XO"} cols = ['closeprice', 'closespread', 'modelprice', 'modelspread'] colmapping={'Date':'date', 'Name': 'index', 'Series': 'series', 'Version': 'version', 'Term': 'tenor', 'Composite Price': 'closeprice', 'Composite Spread': 'closespread', 'Model Price': 'modelprice', 'Model Spread': 'modelspread'} ext_cols = ['date', 'index', 'series', 'version', 'tenor'] + cols + \ ['adjcloseprice', 'adjmodelprice'] for f in filenames: if datetime.datetime.fromtimestamp(os.path.getmtime(f)).date()==(workdate+BDay(1)).date(): data = pd.read_csv(f, skiprows=2, parse_dates=[0,7]) data.rename(columns=colmapping, inplace=True) data.dropna(subset=['closeprice'], inplace=True) data[cols] = data[cols].applymap(lambda x: float(x[:-1]) if x.endswith('%') else x) data['tenor'] = data['tenor'].apply(lambda x: x.lower()+'r') data['index'] = data['index'].apply(lambda x: name_mapping[x] if x in name_mapping else np.NaN) data = data.dropna(subset=['index']) data.set_index('index', drop=False, inplace=True) data['closespread'] *= 100 data['modelspread'] *= 100 ## we renumbered the version for HY9, 10 and 11 data.loc[data.series.isin([9, 10, 11]) & (data.index=='HY'),'version'] -= 3 data['adjcloseprice'] = data['closeprice'] data['adjmodelprice'] = data['modelprice'] data[ext_cols].to_sql('index_quotes', engine, if_exists='append', index=False) 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() engine = create_engine('postgresql://serenitas_user:Serenitas1@debian/serenitasdb') insert_cds(serenitasdb, workdate) insert_index(engine, workdate) ## backpopulate single ticker # company_id = 16632863 # with serenitasdb.cursor() as c: # c.execute("select cds_curve, markit_ticker, markit_tier from cds_issuers where company_id=%s", # (company_id,)) # tup=c.fetchone() # bbg_tickers= tup[0] # for f in os.listdir(os.path.join(root, "Tranche_data", "CDS")): # if f.endswith("csv"): # workdate = datetime.datetime.strptime(f.split(" ")[2].split(".")[0], "%Y%m%d") # workdate = workdate.date() # if workdate >= datetime.date(2014, 9, 19): # mkt_tuple = (tup[1], tup[2]) + ('XR14', '0.05', 'USD') # else: # mkt_tuple = (tup[1], tup[2]) + ('XR', '0.05', 'USD') # insert_cds_single(serenitasdb, workdate, bbg_tickers, mkt_tuple) # serenitasdb.close() # for f in os.listdir(os.path.join(root, "Tranche_data", "CDS")): # if f.endswith("csv"): # workdate = datetime.datetime.strptime(f.split(" ")[2].split(".")[0], "%Y%m%d") # workdate = workdate.date() # insert_cds(serenitasdb, workdate) serenitasdb.close()