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
28
29
|
import glob
import pandas as pd
from sqlalchemy import create_engine
import pdb
engine = create_engine("postgresql://serenitas_user@debian/serenitasdb")
index_version = pd.read_sql_table("index_version", engine, index_col='redindexcode')
for f in glob.iglob("/home/share/CorpCDOs/Tranche_data/Composite_reports/Tranche Composites*"):
df = pd.read_csv(f, skiprows=2, parse_dates=['Date'])
df.rename(columns={'Date':'quotedate',
'Index Term':'tenor',
'Attachment':'attach',
'Detachment':'detach',
'Tranche Upfront Bid': 'upfront_bid',
'Tranche Upfront Mid': 'upfront_mid',
'Tranche Upfront Ask': 'upfront_ask',
'Index Price Mid': 'index_price',
'Tranche Spread Mid': 'tranche_spread',
'Red Code':'redindexcode'}, inplace=True)
df.attach = df.attach *100
df.detach = df.detach * 100
df.tranche_spread = df.tranche_spread*10000
df.tenor = df.tenor.str.lower() + 'r'
df.set_index('redindexcode', inplace=True)
df = df.join(index_version)
df = df.filter(['basketid', 'quotedate', 'tenor', 'attach', 'detach',
'upfront_bid', 'upfront_ask', 'upfront_mid',
'tranche_spread', 'index_price'])
df.to_sql('markit_tranche_quotes', engine, if_exists='append', index=False)
|
