def portfolioVolatility(self):
td = TradingDay()
tradedays = td.getDuration(self.startdate, self.enddate)
df_pcntg = pd.DataFrame()
df_pcntg['TRADE_DT'] = tradedays
df_pcntg['portfolio'] = float(0)
# print(df_pcntg)
oc = OracleConnector()
oracle_connection = oc.getConn()
ms = MySQLConnector()
mysql_connection = ms.getConn()
for stock in self.stocklist:
if stock[-3:] == '.SH' or stock[-3:] == '.SZ':
sqls = """select TRADE_DT, S_DQ_PCTCHANGE "%s" from wind.AShareEODPrices where S_INFO_WINDCODE = '%s'and TRADE_DT >= '%s'and TRADE_DT <= '%s' order by TRADE_DT""" \
% (stock, stock, self.startdate, self.enddate)
df_stock = pd.read_sql(sql=sqls, con=oracle_connection)
# print(df_stock)
else:
sqls = """select L_DATE as TRADE_DT, round((EN_LAST_PRICE - EN_YESTERDAY_CLOSE_PRICE)/EN_YESTERDAY_CLOSE_PRICE, 4) as "%s"
from O32_THISSTOCKINFO where VC_REPORT_CODE = "%s" and L_DATE >= '%s' and L_DATE <= '%s' order by L_DATE""" \
% (stock, stock, self.startdate, self.enddate)
df_stock = pd.read_sql(sql=sqls, con=mysql_connection)
df_stock['TRADE_DT'] = df_stock['TRADE_DT'].astype(str)
df_stock[stock] = df_stock[stock] * 100
# print(df_stock)
weight = self.df_input.loc[self.df_input['VC_SCDM'] == stock,
'EN_SZZJZ'].values
# print(weight)
df_stock[stock] = df_stock[stock].astype(float) * weight / 100
# print(type(df_stock['TRADE_DT'][0]))
df_pcntg = pd.merge(df_pcntg, df_stock, how='outer', on='TRADE_DT')
df_pcntg = df_pcntg.fillna(0)
df_pcntg['portfolio'] += df_pcntg[stock]
# print(df_pcntg)
oc.closeConn()
ms.closeConn()
# print(list(df_pcntg['portfolio']))
# writer = pd.ExcelWriter("D:\PerformanceAnalysis/output.xlsx")
# print(df_pcntg)
# df_pcntg.to_excel(writer, 'Sheet2')
# writer.save()
vol = df_pcntg['portfolio'].std() * np.sqrt(20)
print("Stock Portfolio Volatility: %.4f" % vol)
return vol
def portfolioVolatility(self):
td = TradingDay()
tradedays = td.getDuration(self.startdate, self.enddate)
df_pcntg = pd.DataFrame()
df_pcntg['TRADE_DT'] = tradedays
df_pcntg['portfolio'] = float(0)
# print(df_pcntg)
oc = OracleConnector()
oracle_connection = oc.getConn()
ms = MySQLConnector()
mysql_connection = ms.getConn()
for pb in self.purebondlist:
sqls = """select TRADE_DT, b_anal_net_cnbd "%s" from wind.cbondanalysiscnbd where S_INFO_WINDCODE = '%s'and TRADE_DT >= '%s'and TRADE_DT <= '%s' order by TRADE_DT""" \
% (pb, pb, self.startdate, self.enddate)
sqls_minus1day = """select TRADE_DT, b_anal_net_cnbd "%s" from wind.cbondanalysiscnbd where S_INFO_WINDCODE = '%s'and TRADE_DT >= '%s'and TRADE_DT <= '%s' order by TRADE_DT""" \
% (pb, pb, self.startdate_minus1day, self.enddate_minus1day)
# print(sqls)
df_pb = pd.read_sql(sql=sqls, con=oracle_connection)
df_pb_minus1day = pd.read_sql(sql=sqls_minus1day,
con=oracle_connection)
# print(df_pb, df_pb_minus1day)
df_pb[pb] = (df_pb[pb] / df_pb_minus1day[pb] - 1) * 100
# print(df_pb)
weight = self.df_input.loc[self.df_input['VC_SCDM'] == pb,
'EN_SZZJZ'].values
# print(weight)
df_pb[pb] = df_pb[pb].astype(float) * weight / 100
# print(type(df_pb['TRADE_DT'][0]))
df_pcntg = pd.merge(df_pcntg, df_pb, how='outer', on='TRADE_DT')
# df_pcntg = df_pcntg.fillna(0)
df_pcntg['portfolio'] += df_pcntg[pb]
# print(df_pcntg)
oc.closeConn()
ms.closeConn()
# print(list(df_pcntg['portfolio']))
# writer = pd.ExcelWriter("D:\PerformanceAnalysis/output.xlsx")
# df_pcntg.to_excel(writer, 'Sheet2')
# writer.save()
vol = df_pcntg['portfolio'].std() * np.sqrt(20)
print("PB Portfolio Volatility: %.4f" % vol)
return round(vol, 4)
def portfolioVolatility(self):
td = TradingDay()
tradedays = td.getDuration(self.startdate, self.enddate)
df_pcntg = pd.DataFrame()
df_pcntg['TRADE_DT'] = tradedays
df_pcntg['portfolio'] = float(0)
# print(df_pcntg)
oc = OracleConnector()
oracle_connection = oc.getConn()
ms = MySQLConnector()
mysql_connection = ms.getConn()
for cb in self.cblist:
sqls = """select TRADE_DT, S_DQ_PCTCHANGE "%s" from wind.CBondEODPrices where S_INFO_WINDCODE = '%s'and TRADE_DT >= '%s'and TRADE_DT <= '%s' order by TRADE_DT""" \
% (cb, cb, self.startdate, self.enddate)
# print(sqls)
df_cb = pd.read_sql(sql=sqls, con=oracle_connection)
weight = self.df_input.loc[self.df_input['VC_SCDM'] == cb,
'EN_SZZJZ'].values
# print(weight)
df_cb[cb] = df_cb[cb].astype(float) * weight / 100
# print(type(df_cb['TRADE_DT'][0]))
df_pcntg = pd.merge(df_pcntg, df_cb, how='outer', on='TRADE_DT')
df_pcntg = df_pcntg.fillna(0)
df_pcntg['portfolio'] += df_pcntg[cb]
# print(df_pcntg)
oc.closeConn()
ms.closeConn()
# print(df_pcntg)
# print(list(df_pcntg['portfolio']))
# writer = pd.ExcelWriter("D:\PerformanceAnalysis/output.xlsx")
# df_pcntg.to_excel(writer, 'Sheet2')
# writer.save()
vol = df_pcntg['portfolio'].std() * np.sqrt(20)
print("CB Portfolio Volatility: %.4f" % vol)
return round(vol, 4)