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[['upfront_ask', 'upfront_bid', 'upfront_mid', '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')