1 files changed, 67 insertions, 0 deletions
diff --git a/python/load_ms_data.py b/python/load_ms_data.py
new file mode 100644
index 00000000..856a418c
--- /dev/null
+++ b/python/load_ms_data.py
@@ -0,0 +1,67 @@
+from analytics import CreditIndex
+from utils.db import serenitas_pool
+import numpy as np
+import pandas as pd
+
+def load_sheet(index, series):
+    df = pd.read_excel("/home/serenitas/CorpCDOs/Tranche_data/USTrancheClientFile11072019.XLS",
+                       sheet_name=f'5Y {index.upper()}{series}', skiprows=[0, 1, 2, 3, 4])
+
+    sql_str = ("INSERT INTO tranche_quotes("
+               "quotedate, index, series, version, tenor, attach, detach, "
+               "trancheupfrontmid, trancherunningmid, indexrefprice, "
+               "tranchedelta, corratdetachment, quotesource) "
+               f"VALUES({','.join(['%s'] * 13)})")
+
+    df = df.set_index("Date")
+    if index == "HY":
+        cols = [0., 0.15, 0.25, 0.35]
+    else:
+        cols = [0., 0.03, 0.07, 0.15]
+    if index == "HY":
+        df_upfront = df[['0% - 15%', '15% - 25%', '25% - 35%', '35% - 100%']]
+    else:
+        df_upfront = df[['0-3%', '3-7%', '7-15%', '15-100%']]
+    df_upfront.columns = cols
+    df_upfront = df_upfront.stack()
+    df_upfront.name = 'upfront'
+    df_delta = df[['Delta', 'Delta.1', 'Delta.2', 'Delta.3']]
+    df_delta.columns = cols
+    df_delta = df_delta.stack()
+    df_delta.name = 'delta'
+    df_corr = df[['Correlation', 'Correlation.1', 'Correlation.2']]
+    df_corr.columns = cols[:-1]
+    df_corr = df_corr.stack()
+    df_corr.name = 'correlation'
+    df_detach = pd.DataFrame(np.repeat([cols[1:] + [1.]], len(df.index), 0),
+                             index=df.index, columns=cols).stack()
+    df_detach.name='detach'
+    df_merged = pd.concat([df_upfront, df_delta, df_corr, df_detach], axis=1)
+    df_merged.index.names = ['date', 'attach']
+    if index == "HY":
+        df_merged['price'] = 100. * (1 - df_merged.upfront)
+    else:
+        df_merged['price'] = 100 * df_merged.upfront
+    df_merged = df_merged.reset_index("attach")
+    df_final = df_merged.join(df['Spread (bp)'])
+    df_final = df_final.rename(columns={'Spread (bp)': 'indexspread'})
+
+    conn = serenitas_pool.getconn()
+    credit_index = CreditIndex(index, series, "5yr")
+    with conn.cursor() as c:
+        for t in df_final.itertuples():
+            credit_index.value_date = t.Index.date()
+            credit_index.spread = t.indexspread
+            c.execute(sql_str, (t.Index + pd.DateOffset(hours=17), index, series,
+                                credit_index.version, "5yr",
+                                int(t.attach * 100),
+                                int(t.detach*100), t.price, 500, credit_index.price, t.delta, t.correlation, "MSre"))
+    conn.commit()
+    serenitas_pool.putconn(conn)
+
+if __name__ == "__main__":
+    for index in ("IG", "HY"):
+        for series in (25, 27, 29, 31, 33):
+            if index == "IG" and series <= 29:
+                continue
+            load_sheet(index, series)