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
30
31
32
33
34
35
36
37
|
from sqlalchemy import create_engine, MetaData, Table, bindparam
import pandas as pd
import pdb
#index='HY'
#series=[10, 11, 13, 15, 17, 19, 21, 23]
index='IG'
series= [9, 11, 13, 15, 17, 19, 21, 23]
engine = create_engine('postgresql://serenitas_user:Serenitas1@debian/serenitasdb')
metadata = MetaData(bind=engine)
index_quotes= Table('index_quotes', metadata, autoload=True)
for s in series:
factors = pd.read_sql_query("select lastdate, indexfactor, cumulativeloss from index_version " \
"where index=%s and series=%s", engine, params = (index, s),
parse_dates = ['lastdate'])
factors['recovery'] = 1+factors.cumulativeloss.diff(-1)
for t in ['3yr', '5yr', '7yr', '10yr']:
df = pd.read_sql_query("select date as b_date, version, closeprice, modelprice from index_quotes " \
"where index=%s and series=%s and tenor=%s",
engine, params = (index, s, t), parse_dates =['b_date'])
df.sort_index(inplace=True)
df['adjcloseprice'] = df['closeprice']
df['adjmodelprice'] = df['modelprice']
for i in range(factors.shape[0]-1):
r = factors.ix[i].indexfactor/factors.ix[i+1].indexfactor
df.ix[df.version<=i+1,['adjcloseprice', 'adjmodelprice']] *= r
df.ix[df.version<=i+1,['adjcloseprice', 'adjmodelprice']] -= (r-1) * factors.ix[i].recovery * 100
del df['closeprice']
del df['modelprice']
stmt = index_quotes.update().where((index_quotes.c.date==bindparam('b_date')) &
(index_quotes.c.index==index) &
(index_quotes.c.series==s) &
(index_quotes.c.tenor==t))
engine.execute(stmt, df.to_dict(orient='record'))
|
