1 files changed, 39 insertions, 0 deletions
diff --git a/python/thetas-durations.py b/python/thetas-durations.py
new file mode 100644
index 00000000..75f889ff
--- /dev/null
+++ b/python/thetas-durations.py
@@ -0,0 +1,39 @@
+import pandas as pd
+from analytics import Index
+from db import dbengine
+
+serenitas_engine = dbengine('serenitasdb')
+tenors = {"IG": ("3yr", "5yr", "7yr", "10yr"),
+          "HY": ("3yr", "5yr", "7yr"),
+          "EU": ("3yr", "5yr", "7yr", "10yr"),
+          "XO": ("3yr", "5yr", "7yr", "10yr")}
+
+sql_str = "INSERT INTO index_risk VALUES(%s, %s, %s)"
+
+conn = serenitas_engine.raw_connection()
+for index in ["IG", "HY", "EU", "XO"]:
+    for series in range(18, 31):
+        if index in ["EU", "XO"] and series == 30:
+            continue
+        indices = {t: Index.from_name(index, series, t) for t in tenors[index]}
+        index_quotes = pd.read_sql_query(
+            "SELECT id, date, tenor, close_price FROM index_quotes_pre "
+            "LEFT JOIN index_risk USING (id) "
+            "WHERE index=%s AND series=%s "
+            "AND source='MKIT' AND duration is NULL AND tenor IN %s",
+            serenitas_engine,
+            params=(index, series, tenors[index]),
+            parse_dates=['date'],
+            index_col='id')
+        if index_quotes.empty:
+            continue
+        with conn.cursor() as c:
+            for k, v in index_quotes.groupby('date'):
+                for id, tenor, close_price in v[['tenor', 'close_price']].itertuples():
+                    indices[tenor].value_date = k
+                    indices[tenor].price = close_price
+                    duration = indices[tenor].risky_annuity
+                    theta = - indices[tenor].theta / indices[tenor].notional
+                    c.execute(sql_str, (id, theta, duration))
+        conn.commit()
+conn.close()