def fund_performance_qz(code='163807', start_date='20150528', end_date='20190225'): # 输出单只基金的最大回撤,返回一个float数值 # 提取复权净值 sql = ''' select f3_1288 as 截止日期, f2_1288 as 复权单位净值 from wind.TB_OBJECT_1288 where f1_1288 ='S3600328' and f3_1288 >= '%(start_date)s' and f3_1288 <= '%(end_date)s' order by f3_1288 desc ''' % { 'end_date': end_date, 'start_date': start_date } fund_price = pd.DataFrame(cu.execute(sql).fetchall(), columns=['截止日期', '复权单位净值']) if (fund_price.empty): return 0 price_list = fund_price['复权单位净值'].tolist() performance = (price_list[0] - price_list[-1]) / price_list[-1] return performance
def fund_performance(code='163807', start_date='20150528', end_date='20190225'): # 输出单只基金的最大回撤,返回一个float数值 # 提取复权净值 sql = ''' select trade_dt as 截止日期, s_dq_close as 复权单位净值 from wind.chinamutualfundbenchmarkeod where s_info_windcode= '%(code)s' and trade_dt >= '%(start_date)s' and trade_dt <= '%(end_date)s' order by trade_dt desc ''' % { 'end_date': end_date, 'code': code, 'start_date': start_date } fund_price = pd.DataFrame(cu.execute(sql).fetchall(), columns=['截止日期', '复权单位净值']) if (fund_price.empty): return 0 price_list = fund_price['复权单位净值'].tolist() performance = (price_list[0] - price_list[-1]) / price_list[-1] return performance
def max_down_fund(code='163807', start_date='20150528', end_date='20190225'): # 输出单只基金的最大回撤,返回一个float数值 # 提取复权净值 sql = ''' select trade_dt as 截止日期, s_dq_close as 复权单位净值 from wind.chinamutualfundbenchmarkeod where s_info_windcode= '%(code)s' and trade_dt >= '%(start_date)s' and trade_dt <= '%(end_date)s' ''' % { 'end_date': end_date, 'code': code, 'start_date': start_date } fund_price = pd.DataFrame(cu.execute(sql).fetchall(), columns=['截止日期', '复权单位净值']) if (fund_price.empty): return 0 fund_price2 = fund_price.sort_values(by=['截止日期']).reset_index(drop=True) price_list = fund_price2['复权单位净值'].tolist() i = np.argmax((np.maximum.accumulate(price_list) - price_list) / np.maximum.accumulate(price_list)) # 结束位置 if i == 0: max_down_value = 0 else: j = np.argmax(price_list[:i]) # 开始位置 max_down_value = (price_list[j] - price_list[i]) / (price_list[j]) return -max_down_value
def zhoubodong_qz(code='163807', start_date='20190101', end_date='20190225'): sql = ''' select f3_1288 as 截止日期, f2_1288 as 复权单位净值 from wind.TB_OBJECT_1288 where f1_1288 ='S3600328' and f3_1288 >= '%(start_date)s' and f3_1288 <= '%(end_date)s' order by f3_1288 ''' % { 'end_date': end_date, 'start_date': start_date } fund_price = pd.DataFrame(cu.execute(sql).fetchall(), columns=['截止日期', '复权单位净值']) fund_price2 = fund_price.sort_values(by=['截止日期']).reset_index(drop=True) fund_price2['fund_return'] = fund_price2.复权单位净值.diff( ) / fund_price2.复权单位净值.shift(1) fund_price2.dropna(axis=0, inplace=True) fund_price2.reset_index(drop=True, inplace=True) zhou_bodong = fund_price2.fund_return.std() * (math.sqrt(250)) return zhou_bodong
def max_down_fund_zh(code='163807', start_date='20150528', end_date='20190225'): # 输出单只基金的最大回撤,返回一个float数值 # 提取复权净值 sql = ''' select t1.f2_1120 as 截止日期, t1.f8_1120 as 复权单位净值 from tb_object_1120 t1, TB_OBJECT_1090 t2 where t1.f1_1120 = t2.F2_1090 and t2.f16_1090 = '%(code)s' AND f4_1090 = 'S' and t1.f2_1120 >= '%(start_date)s' and t1.f2_1120 <= '%(end_date)s' ''' % { 'end_date': end_date, 'code': code, 'start_date': start_date } fund_price = pd.DataFrame(cu.execute(sql).fetchall(), columns=['截止日期', '复权单位净值']) if (fund_price.empty): return 0 fund_price2 = fund_price.sort_values(by=['截止日期']).reset_index(drop=True) price_list = fund_price2['复权单位净值'].tolist() i = np.argmax((np.maximum.accumulate(price_list) - price_list) / np.maximum.accumulate(price_list)) # 结束位置 if i == 0: max_down_value = 0 else: j = np.argmax(price_list[:i]) # 开始位置 max_down_value = (price_list[j] - price_list[i]) / (price_list[j]) return -max_down_value
def fund_performance_zh(code='163807', start_date='20150528', end_date='20190225'): # 输出单只基金的最大回撤,返回一个float数值 # 提取复权净值 sql = ''' select t1.f2_1120 as 截止日期, t1.f8_1120 as 复权单位净值 from tb_object_1120 t1, TB_OBJECT_1090 t2 where t1.f1_1120 = t2.F2_1090 and t2.f16_1090 = '%(code)s' AND f4_1090 = 'S' and t1.f2_1120 >= '%(start_date)s' and t1.f2_1120 <= '%(end_date)s' ORDER BY t1.f2_1120 DESC ''' % { 'end_date': end_date, 'code': code, 'start_date': start_date } fund_price = pd.DataFrame(cu.execute(sql).fetchall(), columns=['截止日期', '复权单位净值']) if (fund_price.empty): return 0 price_list = fund_price['复权单位净值'].tolist() performance = (price_list[0] - price_list[-1]) / price_list[-1] return performance
def zhoubodong_zh(code='163807', start_date='20190101', end_date='20190225'): sql = ''' select t1.f2_1120 as 截止日期, t1.f8_1120 as 复权单位净值 from tb_object_1120 t1, TB_OBJECT_1090 t2 where t1.f1_1120 = t2.F2_1090 and t2.f16_1090 = '%(code)s' AND f4_1090 = 'S' and t1.f2_1120 >= '%(start_date)s' and t1.f2_1120 <= '%(end_date)s' ''' % { 'end_date': end_date, 'code': code, 'start_date': start_date } fund_price = pd.DataFrame(cu.execute(sql).fetchall(), columns=['截止日期', '复权单位净值']) fund_price2 = fund_price.sort_values(by=['截止日期']).reset_index(drop=True) fund_price2['fund_return'] = fund_price2.复权单位净值.diff( ) / fund_price2.复权单位净值.shift(1) fund_price2.dropna(axis=0, inplace=True) fund_price2.reset_index(drop=True, inplace=True) zhou_bodong = fund_price2.fund_return.std() * (math.sqrt(250)) return zhou_bodong
def zhoubodong(code='163807', start_date='20190101', end_date='20190225'): sql = ''' select trade_dt as 截止日期, s_dq_close as 复权单位净值 from wind.chinamutualfundbenchmarkeod where s_info_windcode= '%(code)s' and trade_dt >= '%(start_date)s' and trade_dt <= '%(end_date)s' ''' % { 'end_date': end_date, 'code': code, 'start_date': start_date } fund_price = pd.DataFrame(cu.execute(sql).fetchall(), columns=['截止日期', '复权单位净值']) if (fund_price.empty): return 0 fund_price2 = fund_price.sort_values(by=['截止日期']).reset_index(drop=True) fund_price2['fund_return'] = fund_price2.复权单位净值.diff( ) / fund_price2.复权单位净值.shift(1) fund_price2.dropna(axis=0, inplace=True) fund_price2.reset_index(drop=True, inplace=True) # zhou_fund_price = pd.DataFrame(fund_price2.iloc[0,:]).T # for i in fund_price2.index: # if i>0 and i%1==0: # zhou_fund_price = zhou_fund_price.append(fund_price2.iloc[i,:]) # else: # pass zhou_bodong = fund_price2.fund_return.std() * (math.sqrt(250)) return zhou_bodong
def max_down_fund_qz(code='163807', start_date='20150528', end_date='20190225'): # 输出单只基金的最大回撤,返回一个float数值 # 提取复权净值 sql = ''' select f3_1288 as 截止日期, f2_1288 as 复权单位净值 from wind.TB_OBJECT_1288 where f1_1288 ='S3600328' and f3_1288 >= '%(start_date)s' and f3_1288 <= '%(end_date)s' order by f3_1288 ''' % { 'end_date': end_date, 'code': code, 'start_date': start_date } fund_price = pd.DataFrame(cu.execute(sql).fetchall(), columns=['截止日期', '复权单位净值']) if (fund_price.empty): return 0 fund_price2 = fund_price.sort_values(by=['截止日期']).reset_index(drop=True) price_list = fund_price2['复权单位净值'].tolist() i = np.argmax((np.maximum.accumulate(price_list) - price_list) / np.maximum.accumulate(price_list)) # 结束位置 if i == 0: max_down_value = 0 else: j = np.argmax(price_list[:i]) # 开始位置 max_down_value = (price_list[j] - price_list[i]) / (price_list[j]) return -max_down_value
def main(data_kind): update_time = (datetime.datetime.now() + datetime.timedelta(days=-1)).strftime('%Y%m%d') num = len(data_kind.index) for i in data_kind.index: try: print('第 {} 个----------总数{}'.format(i, num)) fund_code = data_kind.基金代码[i] # # if fund_code != '510070': # continue query_sql = ''' SELECT t1.f16_1090, t.f2_1272, nvl(t.f3_1272,0), nvl(t.f4_1272,0),t.F11_1272 from wind.TB_OBJECT_1272 t, wind.tb_object_1090 t1 where t.f1_1272 = t1.f2_1090 and t1.f4_1090 = 'J' and t1.f16_1090 = '%(fund_code)s' order by t.f3_1272 ''' % { 'fund_code': fund_code } fund_manager = pd.DataFrame( cu.execute(query_sql).fetchall(), columns=['fundid', 'name', 'start', 'end', 'managerid']) for j in range(len(fund_manager.values)): fund_benchmark = fund_manager.values[j][0] print("fund_benchmark", fund_benchmark) benchmark_code = fund_benchmark + 'BI.WI' begin_date = fund_manager.values[j][2] manager_enddate = fund_manager.values[j][3] end_date = manager_enddate trade_date = '0' print( 'fundcode:{} fund_manager:{} begin: {} end: {}'.format( fund_code, fund_manager.values[j][4], begin_date, end_date)) # 删除还在任职的 del_sql = '''delete from FUND_BENCHMARK_PERFORMANCE_bak t where t.fundcode= '%(fund_code)s' and t.fundmanager= '%(fundmanager)s' and trade_date ='%(trade_date)s' and t.manager_startdate = '%(manager_startdate)s' and t.update_time = '%(update_time)s' ''' % { 'fund_code': fund_benchmark, 'fundmanager': fund_manager.values[j][1], 'trade_date': trade_date, 'manager_startdate': fund_manager.values[j][2], 'update_time': update_time } cu1.execute(del_sql) fund_db_pra.commit() # 查询是否存在 record_sql = '''select manager_enddate from FUND_BENCHMARK_PERFORMANCE_bak t where t.fundcode= '%(fund_code)s' and t.fundmanager= '%(fundmanager)s' and trade_date ='%(trade_date)s' and t.manager_startdate = '%(manager_startdate)s' and t.update_time = '%(update_time)s' ''' % { 'fund_code': fund_benchmark, 'fundmanager': fund_manager.values[j][1], 'trade_date': trade_date, 'manager_startdate': fund_manager.values[j][2], 'update_time': update_time } manager_performance = pd.DataFrame( cu1.execute(record_sql).fetchall()) flag = manager_performance.empty if (not flag): print('任期已计算') else: # 日期处理 if end_date == "0": # 日期 为 0 补充为最新日期 前一天 end_date = ( datetime.datetime.now() + datetime.timedelta(days=-1)).strftime('%Y%m%d') if (data_kind.成立日[i] == begin_date): begin_date = get_tradedate(begin_date) else: tansferdate = ( datetime.datetime.strptime(begin_date, '%Y%m%d') + timedelta(days=-1)).strftime('%Y%m%d') if (get_tradedate(tansferdate) == 0): start = tansferdate else: start = get_tradedate(tansferdate) if data_kind.一级分类[i] == 'QDII': end_date = get_tradedate( (datetime.datetime.strptime(end_date, '%Y%m%d') + datetime.timedelta(days=-1)).strftime('%Y%m%d')) elif data_kind.一级分类[i] == 'QDII': end_date = get_tradedate(end_date) # 计算 result_hc = max_down_fund(benchmark_code, begin_date, end_date) result_nhbd = zhoubodong(benchmark_code, begin_date, end_date) if (math.isnan(result_nhbd)): result_nhbd = 0 result_hb = fund_performance(benchmark_code, begin_date, end_date) rec = [] rec.append(data_kind.基金代码[i]) rec.append(data_kind.基金简称[i]) rec.append(data_kind.二级分类[i]) rec.append(fund_manager.values[j][1]) rec.append(result_hc) rec.append(result_nhbd) rec.append(result_hb) rec.append(fund_manager.values[j][2]) rec.append(fund_manager.values[j][3]) rec.append(benchmark_code) rec.append(fund_manager.values[j][4]) rec.append(trade_date) rec.append(update_time) insert_sql = "INSERT INTO FUND_BENCHMARK_PERFORMANCE_bak( fundcode, fundname,fundtype, fundmanager,rrin_hc," \ "rrin_nhbd,rrin_hb,manager_startdate,manager_enddate,index_code,fundmanager_id,trade_date,update_time) VALUES(:1, :2, :3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13)" cu1.execute(insert_sql, rec) if (data_kind.二级分类[i] == '可转债'): result_hc = max_down_fund_zh('000832', begin_date, end_date) result_nhbd = zhoubodong_zh('000832', begin_date, end_date) if (math.isnan(result_nhbd)): result_nhbd = 0 result_hb = fund_performance_zh( '000832', begin_date, end_date) rec = [] rec.append(data_kind.基金代码[i]) rec.append(data_kind.基金简称[i]) rec.append(data_kind.二级分类[i]) rec.append(fund_manager.values[j][1]) rec.append(result_hc) rec.append(result_nhbd) rec.append(result_hb) rec.append(fund_manager.values[j][2]) rec.append(fund_manager.values[j][3]) rec.append('000832') rec.append(fund_manager.values[j][4]) rec.append(trade_date) rec.append(update_time) insert_sql = "INSERT INTO FUND_BENCHMARK_PERFORMANCE_bak( fundcode, fundname,fundtype, fundmanager,rrin_hc," \ "rrin_nhbd,rrin_hb,manager_startdate,manager_enddate,index_code,fundmanager_id,trade_date,update_time) VALUES(:1, :2, :3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13)" cu1.execute(insert_sql, rec) elif (data_kind.二级分类[i] == '激进债券型' or data_kind.二级分类[i] == '普通债券型' or data_kind.二级分类[i] == '纯债'): result_hc = max_down_fund_qz('CBA00203', begin_date, end_date) result_nhbd = zhoubodong_qz('CBA00203', begin_date, end_date) if (math.isnan(result_nhbd)): result_nhbd = 0 result_hb = fund_performance_qz( 'CBA00203', begin_date, end_date) rec = [] rec.append(data_kind.基金代码[i]) rec.append(data_kind.基金简称[i]) rec.append(data_kind.二级分类[i]) rec.append(fund_manager.values[j][1]) rec.append(result_hc) rec.append(result_nhbd) rec.append(result_hb) rec.append(fund_manager.values[j][2]) rec.append(fund_manager.values[j][3]) rec.append('CBA00203') rec.append(fund_manager.values[j][4]) rec.append(trade_date) rec.append(update_time) insert_sql = "INSERT INTO FUND_BENCHMARK_PERFORMANCE_bak( fundcode, fundname,fundtype, fundmanager,rrin_hc," \ "rrin_nhbd,rrin_hb,manager_startdate,manager_enddate,index_code,fundmanager_id,trade_date,update_time) VALUES(:1, :2, :3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13)" cu1.execute(insert_sql, rec) elif (data_kind.二级分类[i] == '股票型' or data_kind.二级分类[i] == '激进配置型' or data_kind.二级分类[i] == '标准配置型' or data_kind.二级分类[i] == '保守配置型' or data_kind.二级分类[i] == '灵活配置型'): code_list = ['000300', '000905', '000906', 'CBA00203'] for index in range(len(code_list)): if (code_list[index] == 'CBA00203'): result_hc = max_down_fund_qz( code_list[index], begin_date, end_date) result_nhbd = zhoubodong_qz( code_list[index], begin_date, end_date) if (math.isnan(result_nhbd)): result_nhbd = 0 result_hb = fund_performance_qz( code_list[index], begin_date, end_date) else: result_hc = max_down_fund_zh( code_list[index], begin_date, end_date) result_nhbd = zhoubodong_zh( code_list[index], begin_date, end_date) if (math.isnan(result_nhbd)): result_nhbd = 0 result_hb = fund_performance_zh( code_list[index], begin_date, end_date) rec = [] rec.append(data_kind.基金代码[i]) rec.append(data_kind.基金简称[i]) rec.append(data_kind.二级分类[i]) rec.append(fund_manager.values[j][1]) rec.append(result_hc) rec.append(result_nhbd) rec.append(result_hb) rec.append(fund_manager.values[j][2]) rec.append(fund_manager.values[j][3]) rec.append(code_list[index]) rec.append(fund_manager.values[j][4]) rec.append(trade_date) rec.append(update_time) insert_sql = "INSERT INTO FUND_BENCHMARK_PERFORMANCE_bak( fundcode, fundname,fundtype, fundmanager,rrin_hc," \ "rrin_nhbd,rrin_hb,manager_startdate,manager_enddate,index_code,fundmanager_id,trade_date,update_time) VALUES(:1, :2, :3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13)" cu1.execute(insert_sql, rec) else: result_hc = max_down_fund_zh('000300', begin_date, end_date) result_nhbd = zhoubodong_zh('000300', begin_date, end_date) if (math.isnan(result_nhbd)): result_nhbd = 0 result_hb = fund_performance_zh( '000300', begin_date, end_date) rec = [] rec.append(data_kind.基金代码[i]) rec.append(data_kind.基金简称[i]) rec.append(data_kind.二级分类[i]) rec.append(fund_manager.values[j][1]) rec.append(result_hc) rec.append(result_nhbd) rec.append(result_hb) rec.append(fund_manager.values[j][2]) rec.append(fund_manager.values[j][3]) rec.append('000300') rec.append(fund_manager.values[j][4]) rec.append(trade_date) rec.append(update_time) insert_sql = "INSERT INTO FUND_BENCHMARK_PERFORMANCE_bak( fundcode, fundname,fundtype, fundmanager,rrin_hc," \ "rrin_nhbd,rrin_hb,manager_startdate,manager_enddate,index_code,fundmanager_id,trade_date,update_time) VALUES(:1, :2, :3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13)" cu1.execute(insert_sql, rec) fund_db_pra.commit() except Exception as e: print(e) logging.exception(str(fund_code)) logging.exception(e) continue cu.close() cu1.close() fund_db_pra.close() fund_db_wind.close()
def main(df): for i in data_kind.index: try: print('第 {} 个----------总数{}'.format(i, num)) fund_code = data_kind.基金代码[i] if fund_code != '510081': continue print('fundcode:{}'.format(fund_code)) query_sql = '''SELECT t1.f16_1090, t.f2_1272, nvl(t.f3_1272,0), nvl(t.f4_1272,0),t.F11_1272 from wind.TB_OBJECT_1272 t, wind.tb_object_1090 t1 where t.f1_1272 = t1.f2_1090 and t1.f4_1090 = 'J' and t1.f16_1090 = '%(fund_code)s' order by t.f3_1272 ''' % { 'fund_code': fund_code } fund_manager = pd.DataFrame( cu.execute(query_sql).fetchall(), columns=['fundid', 'name', 'start', 'end', 'managerid']) # 日期 为 0 补充为20181231 fund_manager.loc[fund_manager['end'] == '0', 'end'] = '20181231' for j in range(len(fund_manager.values)): fund_benchmark = fund_manager.values[j][0] benchmark_code = fund_benchmark + 'BI.WI' begin_date = fund_manager.values[j][2] end_date = fund_manager.values[j][3] if len(begin_date) == 8 and len(end_date) == 8: # 计算每年 date_iter = deal_time(str(begin_date), str(end_date)) for year, date in date_iter: print('fundcode:{} fund_manager:{} year: {} {} {}'. format(fund_code, fund_manager.values[j][4], year, date[0], date[1])) # 生成每年的任期 开始日期大于20181231的不计算 begin = date[0] end = date[1] trade_date = year if begin < end: # 查看年度是否计算 record_sql = ''' select manager_enddate from FUND_BENCHMARK_PERFORMANCE_bak t where t.fundcode= '%(fund_code)s' and t.fundmanager= '%(fundmanager)s' and trade_date ='%(trade_date)s' ''' % { 'fund_code': fund_benchmark, 'fundmanager': fund_manager.values[j][1], 'trade_date': year } manager_performance = pd.DataFrame( cu1.execute(record_sql).fetchall()) flag = manager_performance.empty if flag: # 开始 结束 日期 转化为交易日 if (get_tradedate(begin) != 0): begin = str(get_tradedate(begin)) if (get_tradedate(end_date) != 0): end = str(get_tradedate(end)) # # lis = [str(x) +'1230' for x in range(2000, 2019)] # if begin in lis: # continue result_hc = max_down_fund( benchmark_code, begin, end) result_nhbd = zhoubodong( benchmark_code, begin, end) if (math.isnan(result_nhbd)): result_nhbd = 0 result_hb = fund_performance( benchmark_code, begin, end) rec = [] rec.append(data_kind.基金代码[i]) rec.append(data_kind.基金简称[i]) rec.append(data_kind.二级分类[i]) rec.append(fund_manager.values[j][1]) rec.append(result_hc) rec.append(result_nhbd) rec.append(result_hb) rec.append(fund_manager.values[j][2]) rec.append(fund_manager.values[j][3]) rec.append(benchmark_code) rec.append(fund_manager.values[j][4]) rec.append(trade_date) insert_sql = "INSERT INTO FUND_BENCHMARK_PERFORMANCE_bak( fundcode, fundname,fundtype, fundmanager,rrin_hc," \ "rrin_nhbd,rrin_hb,manager_startdate,manager_enddate,index_code,fundmanager_id,trade_date) VALUES(:1, :2, :3,:4,:5,:6,:7,:8,:9,:10,:11,:12)" cu1.execute(insert_sql, rec) if (data_kind.二级分类[i] == '可转债'): result_hc = max_down_fund_zh( '000832', begin, end) result_nhbd = zhoubodong_zh( '000832', begin, end) if (math.isnan(result_nhbd)): result_nhbd = 0 result_hb = fund_performance_zh( '000832', begin, end) rec = [] rec.append(data_kind.基金代码[i]) rec.append(data_kind.基金简称[i]) rec.append(data_kind.二级分类[i]) rec.append(fund_manager.values[j][1]) rec.append(result_hc) rec.append(result_nhbd) rec.append(result_hb) rec.append(fund_manager.values[j][2]) rec.append(fund_manager.values[j][3]) rec.append('000832') rec.append(fund_manager.values[j][4]) rec.append(trade_date) insert_sql = "INSERT INTO FUND_BENCHMARK_PERFORMANCE_bak( fundcode, fundname,fundtype, fundmanager,rrin_hc," \ "rrin_nhbd,rrin_hb,manager_startdate,manager_enddate,index_code,fundmanager_id,trade_date) VALUES(:1, :2, :3,:4,:5,:6,:7,:8,:9,:10,:11,:12)" cu1.execute(insert_sql, rec) elif (data_kind.二级分类[i] == '激进债券型' or data_kind.二级分类[i] == '普通债券型' or data_kind.二级分类[i] == '纯债'): result_hc = max_down_fund_qz( 'CBA00203', begin, end) result_nhbd = zhoubodong_qz( 'CBA00203', begin, end) if (math.isnan(result_nhbd)): result_nhbd = 0 result_hb = fund_performance_qz( 'CBA00203', begin, end) rec = [] rec.append(data_kind.基金代码[i]) rec.append(data_kind.基金简称[i]) rec.append(data_kind.二级分类[i]) rec.append(fund_manager.values[j][1]) rec.append(result_hc) rec.append(result_nhbd) rec.append(result_hb) rec.append(fund_manager.values[j][2]) rec.append(fund_manager.values[j][3]) rec.append('CBA00203') rec.append(fund_manager.values[j][4]) rec.append(trade_date) insert_sql = "INSERT INTO FUND_BENCHMARK_PERFORMANCE_bak( fundcode, fundname,fundtype, fundmanager,rrin_hc," \ "rrin_nhbd,rrin_hb,manager_startdate,manager_enddate,index_code,fundmanager_id,trade_date) VALUES(:1, :2, :3,:4,:5,:6,:7,:8,:9,:10,:11,:12)" cu1.execute(insert_sql, rec) elif (data_kind.二级分类[i] == '股票型' or data_kind.二级分类[i] == '激进配置型' or data_kind.二级分类[i] == '标准配置型' or data_kind.二级分类[i] == '保守配置型' or data_kind.二级分类[i] == '灵活配置型'): code_list = [ '000300', '000905', '000906', 'CBA00203' ] for index in range(len(code_list)): if (code_list[index] == 'CBA00203'): result_hc = max_down_fund_qz( code_list[index], begin, end) result_nhbd = zhoubodong_qz( code_list[index], begin, end) if (math.isnan(result_nhbd)): result_nhbd = 0 result_hb = fund_performance_qz( code_list[index], begin, end) else: result_hc = max_down_fund_zh( code_list[index], begin, end) result_nhbd = zhoubodong_zh( code_list[index], begin, end) if (math.isnan(result_nhbd)): result_nhbd = 0 result_hb = fund_performance_zh( code_list[index], begin, end) rec = [] rec.append(data_kind.基金代码[i]) rec.append(data_kind.基金简称[i]) rec.append(data_kind.二级分类[i]) rec.append(fund_manager.values[j][1]) rec.append(result_hc) rec.append(result_nhbd) rec.append(result_hb) rec.append(fund_manager.values[j][2]) rec.append(fund_manager.values[j][3]) rec.append(code_list[index]) rec.append(fund_manager.values[j][4]) rec.append(trade_date) insert_sql = "INSERT INTO FUND_BENCHMARK_PERFORMANCE_bak( fundcode, fundname,fundtype, fundmanager,rrin_hc," \ "rrin_nhbd,rrin_hb,manager_startdate,manager_enddate,index_code,fundmanager_id,trade_date) VALUES(:1, :2, :3,:4,:5,:6,:7,:8,:9,:10,:11,:12)" cu1.execute(insert_sql, rec) else: result_hc = max_down_fund_zh( '000300', begin, end) result_nhbd = zhoubodong_zh( '000300', begin, end) if (math.isnan(result_nhbd)): result_nhbd = 0 result_hb = fund_performance_zh( '000300', begin, end) rec = [] rec.append(data_kind.基金代码[i]) rec.append(data_kind.基金简称[i]) rec.append(data_kind.二级分类[i]) rec.append(fund_manager.values[j][1]) rec.append(result_hc) rec.append(result_nhbd) rec.append(result_hb) rec.append(fund_manager.values[j][2]) rec.append(fund_manager.values[j][3]) rec.append('000300') rec.append(fund_manager.values[j][4]) rec.append(trade_date) insert_sql = "INSERT INTO FUND_BENCHMARK_PERFORMANCE_bak( fundcode, fundname,fundtype, fundmanager,rrin_hc," \ "rrin_nhbd,rrin_hb,manager_startdate,manager_enddate,index_code,fundmanager_id,trade_date) VALUES(:1, :2, :3,:4,:5,:6,:7,:8,:9,:10,:11,:12)" cu1.execute(insert_sql, rec) fund_db_pra.commit() else: print('{}年度已计算'.format(year)) except Exception as e: print(e) continue cu.close() cu1.close() fund_db_pra.close() fund_db_wind.close()