1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
|
import os
from common import root
import csv
import datetime
from db import connmlpdb
import re, sys
from pandas.tseries.offsets import BDay
import pdb
def convert(x):
try:
return float(x[:-1])
except ValueError:
return None
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', 'HY21', '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']
return (all_tickers, markit_bbg_mapping)
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)"
tickers_found = set([])
with connmlpdb.cursor() as c:
c.execute("DELETE from cds_quotes where date=%s", (workdate,))
connmlpdb.commit()
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:
tickers_found.add((line['Ticker'], line['Tier']))
k = (line['Ticker'], line['Tier'], line['Ccy'], line['DocClause'], float(line['RunningCoupon']))
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(markit_bbg_mapping[k])])
except KeyError:
continue
connmlpdb.commit()
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)
|
