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
|
from sqlalchemy import MetaData, create_engine, Table
from db import dbconn
serenitasdb = dbconn('serenitasdb')
import pandas as pd
#series = [9, 11, 13, 15, 17, 19, 21]
series=[18]
#tenor = ['3yr', '5yr', '7yr', '10yr']
tenor = ['5yr', '7yr', '10yr']
index_type ='IG'
for s in series:
for t in tenor:
index = "{0}{1}".format(index_type.lower(), s)
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
stmt = """INSERT INTO index_quotes(date, index, series, version, tenor, closeprice, closespread,
modelprice, modelspread) VALUES(%(date)s, %(index)s, %(series)s, %(version)s, %(tenor)s,
%(closeprice)s, %(closespread)s, %(modelprice)s, %(modelspread)s)
ON CONFLICT DO NOTHING"""
with serenitasdb.cursor() as c:
c.executemany(stmt, df.to_dict('record'))
serenitasdb.commit()
serenitasdb.close()
|
