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
|
from sqlalchemy import MetaData, create_engine, Table
engine = create_engine('postgresql://serenitas_user@debian/serenitasdb')
import pandas as pd
metadata = MetaData(bind = engine)
index_quotes = Table('index_quotes', metadata, autoload=True)
#series = [9, 11, 13, 15, 17, 19, 21]
series=[9]
#tenor = ['3yr', '5yr', '7yr', '10yr']
tenor = ['3yr', '5yr', '7yr']
index_type ='HY'
for s in series:
for t in tenor:
index = "{0}{1}".format(index_type.lower(), s)
stmt = index_quotes.delete().where((index_quotes.c.index==index_type) &
(index_quotes.c.series==s) &
(index_quotes.c.tenor==t))
engine.execute(stmt)
df = pd.read_excel('/home/share/CorpCDOs/index historical.xlsx', "{0} {1}".format(index, t))
df.rename(columns={k: k.lower() for k in df.columns.tolist()}, inplace=True)
df['index']= index_type
df['series']=s
df['tenor']=t
df.to_sql('index_quotes', engine, if_exists='append', index=False)
|
