1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
|
import pandas as pd
from utils.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')
|
