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
|
import yaml, os, csv
from db import serenitasdb
import datetime
basedir = "/home/share/CorpCDOs"
def get_index_list(basedir):
with open(os.path.join(basedir, "code", "etc", "runs.yml")) as fh:
runs = yaml.load(fh)
index_list = [[name[:2].upper(), int(name[2:]), tenor] for name, tenor
in zip(runs['name'], runs['tenor'])]
return index_list
sqlstr = "INSERT INTO risk_numbers VALUES({0},{1})".format(",".join(["%s"] * 10),
",".join(["%s::float[]"]*9))
def get_attach_from_name(index_type, series):
if index_type.lower() == "ig":
if series == 9:
attach = [0, 3, 7, 10, 15, 30, 100]
else:
attach = [0, 3, 7, 15, 100]
elif index_type.lower() == "hy":
attach = [0, 15, 25, 35, 100]
elif index_type.lower() == "xo":
attach = [0, 10, 20, 35, 100]
elif index_type.lower() == 'eu':
if series >= 21:
attach = [0, 3, 6, 12, 100]
else:
attach = [0, 3, 6, 9, 12, 22, 100]
return attach
def convert(s):
return None if s=="NA" else float(s)
def populate_risk(basedir, db):
with db.cursor() as c:
c.execute("DELETE FROM risk_numbers")
db.commit()
for index_type, series, tenor in get_index_list(basedir):
attach = get_attach_from_name(index_type, series)
with open(os.path.join(basedir, "Tranche_data", "Runs",
"{0}{1}.{2}.csv".format(index_type, series, tenor))) as fh:
csvreader = csv.DictReader(fh)
with db.cursor() as c:
toinsert = []
for line in csvreader:
line['date'] = datetime.datetime.strptime(line['date'], "%Y-%m-%d").date()
greeks = []
skew = [convert(line["{0} Corr".format(a)]) for a in attach[1:]]
for k in ["Dealer Delta", "Model Delta", "Forward Deltas", "Gamma", "Theta",
"Corr01", "Dur", "EL"]:
greeks.append([convert(line["{0}-{1} {2}".format(l, u, k)])
for l, u in zip(attach[:-1], attach[1:])])
toinsert.append((line['date'], index_type, series, tenor, line['indexprice'],
line['indexBasis'], line['indexEL'], line['indexduration'],
line['indexTheta'], attach) + (skew,) + tuple(greeks))
c.executemany(sqlstr, toinsert)
db.commit()
if __name__=="__main__":
basedir = "/home/share/CorpCDOs"
populate_risk(basedir, serenitasdb)
serenitasdb.close()
|
