1 files changed, 27 insertions, 0 deletions
diff --git a/python/markit_tranches.py b/python/markit_tranches.py
new file mode 100644
index 00000000..e91f00a9
--- /dev/null
+++ b/python/markit_tranches.py
@@ -0,0 +1,27 @@
+import pandas as pd
+from db import dbengine
+
+engine = dbengine("serenitasdb")
+df = {}
+sheets = ["AskClose", "Close", "BidClose"]
+df= pd.read_excel("/home/share/CorpCDOs/NewSpreadsheet/Markit tranches history.xlsx",
+                  sheets, parse_dates=['Date'], index_col='Date')
+for sheet in sheets:
+    df[sheet].columns = ['0-3', '3-7', '7-15', '15-100']
+df = pd.concat(df)
+df = df.unstack(level=0).stack(level=0).reset_index(level=1)
+df[['attach','detach']] = df.level_1.str.split("-", expand=True).astype("int")
+del df['level_1']
+df.index = df.index.normalize()
+index_quotes = pd.read_sql_query("SELECT date, closeprice FROM index_quotes WHERE INDEX='IG' " \
+                                 "AND series=21 AND tenor='5yr'",
+                                 engine, parse_dates=["date"], index_col='date')
+
+tranche_quotes  = df.join(index_quotes)
+tranche_quotes.columns = ['upfront_ask', 'upfront_bid', 'upfront_mid', 'attach', 'detach', 'index_price']
+tranche_quotes['tranche_spread'] = tranche_quotes.attach.replace({0:500, 3:100, 7:100, 15:25})
+tranche_quotes.index_price /= 100
+tranche_quotes['basketid']=162
+tranche_quotes['tenor']='5yr'
+tranche_quotes.ix[:'2014-07-01'].to_sql("markit_tranche_quotes", engine, if_exists='append',
+                                        index_label='quotedate')