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
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
|
import pandas as pd
from db import dbengine
import matplotlib
import numpy as np
import matplotlib.pyplot as plt
import statsmodels.api as sm
from statsmodels.formula.api import gls
df = (pd.read_sql_table('external_marks_mapped',dbengine('dawndb'),
parse_dates=['date']).
sort_values('date'))
df1 = pd.read_sql_query("select trade_date, cusip as identifier, principal_payment, price from bonds where buysell = 'f'"
,dbengine('dawndb'),
parse_dates=[
'trade_date'
])
df1 = df1.sort_values('trade_date')
d = {}
for source, g in df.groupby('source'):
d[source] = pd.merge_asof(df1, g, left_on='trade_date', right_on= 'date', by='identifier')
df2 = pd.concat(d, join='inner')
df3 = df2.dropna(axis=0, subset=['mark'])
df3 = df3.reset_index(level=1, drop=True)
df4 = df3.set_index(['trade_date','identifier', 'source', 'principal_payment'])
df4 = df4.groupby(level=['trade_date', 'identifier','source', 'principal_payment']).mean()
df4 = df4.set_index('price', append=True)
df5 = df4.unstack(-3)
df5.columns = df5.columns.droplevel()
#df6 = df5.reset_index(col_level=1, drop=True)
df6 = df5.sortlevel(0, ascending = False)
df6 = df6.reset_index()
r2 = {}
stderr = {}
parameters = {}
#for g in df5['mark']:
for col in df6.columns[4:]:
regmodel = gls(formula= "df6['price'] ~df6[col]", data = df6).fit()
r2[col] = regmodel.rsquared
stderr[col] = regmodel.bse
parameters[col] = regmodel.params
r2 = pd.Series(r2, name= 'R2')
r2.index.name = 'Source'
r2.reset_index
r2 = pd.DataFrame(r2)
stderr = pd.DataFrame(stderr)
parameters = pd.DataFrame(parameters)
df7 = df6
for col in df7.columns[4:]:
df7[col] = (df7.price - df7[col])/df7[col]
filter = 2
begindate = '2015-01-01'
enddate = '2017-01-01'
df8 = df7[(df7['trade_date']>begindate) & (df7['trade_date']<enddate)]
difference= pd.DataFrame(df8[df8<filter].mean(), columns=['Average'])
difference['Std Dev'] = df8[df8<filter].std()
difference.reset_index()
writer = pd.ExcelWriter('/home/serenitas/edwin/Subprime_ModelandBacktest_outputonly.xlsx')
df6.to_excel(writer, sheet_name = 'BackTesting', header = True, na_rep = '', startrow= 1, startcol= 1)
r2.to_excel(writer, sheet_name = 'regression', header = True)
parameters.to_excel(writer, sheet_name = 'regression', header = True, startrow= 0, startcol= 5)
difference[2:].to_excel(writer, sheet_name = 'Diff', header = True, startrow= 1, startcol= 1)
df7.to_excel(writer, sheet_name = 'Diff', header = True, startrow= 15, startcol= 1)
writer.save()
#fig = plt.plot(figsize=(12,8))
#fig = sm.graphics.plot_partregress_grid(regmodel,fig=fig)
#df6 = pd.DataFrame()
#for g in df5['mark']:
# d[g] = (df5['price'] - df5['mark'][g])/df5['mark'][g]
#df6.from_dict(d)
|
