diff options
| -rw-r--r-- | python/populate_risk_numbers.py | 43 | ||||
| -rw-r--r-- | sql/serenitasdb.sql | 24 |
2 files changed, 67 insertions, 0 deletions
diff --git a/python/populate_risk_numbers.py b/python/populate_risk_numbers.py new file mode 100644 index 00000000..1aede16e --- /dev/null +++ b/python/populate_risk_numbers.py @@ -0,0 +1,43 @@ +import yaml, os, csv +from db import serenitasdb +import datetime + +basedir = "/home/share/CorpCDOs" +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'])] + +sqlstr1 = "SELECT * from nameToBasketID(%s, %s)" +sqlstr2 = "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] + return attach + +def convert(s): + return None if s=="NA" else float(s) + +for index_type, series, tenor in index_list: + 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) + for line in csvreader: + line['date'] = datetime.datetime.strptime(line['date'], "%Y-%m-%d").date() + with serenitasdb.cursor() as c: + 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:])]) + c.execute(sqlstr2, (line['date'], index_type, series, tenor, line['indexprice'], + line['indexBasis'], line['indexEL'], line['indexduration'], + line['indexTheta'], attach) + (skew,) + tuple(greeks)) diff --git a/sql/serenitasdb.sql b/sql/serenitasdb.sql index 2f6d2471..fe48cecf 100644 --- a/sql/serenitasdb.sql +++ b/sql/serenitasdb.sql @@ -292,3 +292,27 @@ CREATE OR REPLACE FUNCTION get_tranche_quotes(pg_index_type text, pg_series inte END LOOP;
END;
$$ language plpgsql;
+
+CREATE TABLE risk_numbers(
+ date date,
+ index index_type,
+ series integer,
+ tenor tenor,
+ indexprice float,
+ indexbasis float,
+ indexEL float,
+ indexduration float,
+ indextheta float,
+ attach integer[],
+ Skew float[],
+ "Dealer Deltas" float[],
+ "Model Deltas" float[],
+ "Forward Deltas" float[],
+ Gammas float[],
+ Thetas float[],
+ Corr01 float[],
+ Durations float[],
+ EL float[],
+ PRIMARY KEY(date, index, series, tenor));
+
+GRANT ALL ON risk_numbers to serenitas_user;
|