def hand_turn_over(model): # 能力分析 - 持股集中度、换手率 # fof_fund_stock_porfolio if not del_data('fof_fund_stock_porfolio'): raise Error("删除历史数据失败,任务结束") fundSymbols = getData_fundSymbols(['股票型', '混合型']) btParms = { 'symbol': fundSymbols, 'startDate': '20020101', 'endDate': datetime.strftime(datetime.today(), '%Y%m%d') } self = fundTurnOver(btParms) output = self.output for i in output: val = output[i] for k, v in val.items(): dt = k # 时间 value = v # 值 symbo = i # 基金 id_ = uuid_util.gen_uuid() ls = [] ls.append(id_) ls.append(symbo) ls.append(dt._short_repr.replace("-", "")) ls.append(transformFloatIfAvaliable3(value)) ls.append("sys") ls.append(datetime.now()) ls.append("sys") ls.append(datetime.now()) sql = "INSERT INTO `fof`.`fof_fund_stock_porfolio`(`OBJECT_ID`, `J_WINDCODE`, `TRADE_DT`, `CHANGE_RATE`, `CREATE_USER_ID`, `CREATE_TIME`, `UPDATE_USER_ID`, `UPDATE_TIME`) VALUES (%s, %s, %s, %s, %s, %s, %s, %s)" mysqlops.insert_one(MysqlConf.DB.fof, sql, tuple(ls))
def compute_manager_product(model: OfflineTaskModel): if not del_data('fof_manager_product'): raise Error("删除历史数据失败,任务结束") test = managerAnnualReturn() mgrDF = test.output.fillna("999999999.9999") # mgrDF.to_csv("C:\\Users\\futanghang\\Desktop\\nn.csv") # mgrDF = pd.read_csv("C:\\Users\\futanghang\\Desktop\\mgr.csv") for hls in mgrDF.values.tolist(): objId = uuid_util.gen_uuid() ls = [transformFloatIfAvaliable3(l) for l in hls] lst = [] lst.append(objId) lst.append(ls[0]) lst.append(ls[1]) lst.append(ls[2]) lst.append(ls[5]) lst.append(ls[3]) lst.append(ls[4]) lst.append(ls[6][0:-2]) lst.append('sys') lst.append(datetime.now()) lst.append("sys") lst.append(datetime.now()) lst.append(0) sql = "insert into fof_manager_product values (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)" # fs = sql % tuple(lst) mysqlops.insert_one(MysqlConf.DB.fof, sql, tuple(lst))
def return_weight(model): if not del_data('fof_fund_excess_return_weight'): raise Error("删除历史数据失败,任务结束") # fof_fund_excess_return_weight # 一次性调取需要用到的公共数据 stockIndus, stockRet, indusRet, crIndus = publicData_stockAndIndustry() # 批量生成基金经理任职区间信息 ''' 首次存入数据库时需要全部运行,后续更新维护时只需更新在任基金经理信息即可. 选股能力仅针对于股票型与混合型基金 ''' managerInfo = getData_fundManagerInfo_all() fundSymbols = getData_fundSymbols(['股票型', '混合型']) tmp = [] for row in managerInfo.index: if managerInfo.loc[row, 'fund'] in fundSymbols: tmp.append(managerInfo.loc[row].to_dict()) managerInfo = pd.DataFrame(tmp, columns=managerInfo.columns) output_all = [] for i in range(len(managerInfo)): btParms = { 'symbol': managerInfo.loc[i, 'fund'], 'manager': managerInfo.loc[i, 'manager'] } try: test = stockPickingAbility_manager(btParms, stockIndus, stockRet, indusRet, crIndus) output = test.output # output['compareReturnIndustry'].to_csv("c:\\users\\futanghang\\desktop\\compareReturnIndustry.csv") # output['excessReturnIndustry'].to_csv("c:\\users\\futanghang\\desktop\\excessReturnIndustry.csv") # output['excessReturnQuarter'].to_csv("c:\\users\\futanghang\\desktop\\excessReturnQuarter.csv") # output['mainStcokReturn'].to_csv("c:\\users\\futanghang\\desktop\\mainStcokReturn.csv") dda = output['excessReturnQuarter'] for k, v in dda.items(): # k time,v :value wCode = output['symbol'] # 基金Wind代码 managerName = output['manager'] # 基金经理名称 managerId = output['managerId'] # 基金经理id id_ = uuid_util.gen_uuid() # objId lst = [] lst.append(id_) lst.append(wCode) lst.append(managerId) lst.append(managerName) lst.append(k._short_repr.replace("-", "")) lst.append(v) lst.append("sys") lst.append(datetime.now()) lst.append("sys") lst.append(datetime.now()) ls = [transformFloatIfAvaliable4(i) for i in lst] sql = "INSERT INTO `fof`.`fof_fund_excess_return_weight`(`OBJECT_ID`, `J_WINDCODE`, `MANAGER_ID`, `MANAGER_NAME`, `TRADE_DT`, `EXCESS_RETURN`, `CREATE_USER_ID`, `CREATE_TIME`, `UPDATE_USER_ID`, `UPDATE_TIME`) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s)" mysqlops.insert_one(MysqlConf.DB.fof, sql, tuple(ls)) except Exception as e: pass
def risk_multiple(model): # 风险因子业绩归因模块 # fof_multi_attr_riskmodel if not del_data('fof_multi_attr_riskmodel'): raise Error("删除历史数据失败,任务结束") stockIndus, indusReturn, factorExposure, factorReturn = publicData_riskModel( ) # 批量运行 ''' 跨期归因结果需要存储多个周期值 单期归因结果仅存储成立以来的值即可 跨期归因和单期归因可以用两张表来存储数据 ''' # dataframe 周期类型和数据库周期类型映射关系 cyleFormula = {'1': '1', '2': '2', '3': '3', '5': '4', '成立以来': '5'} fundSymbols = getData_fundSymbols(['股票型', '混合型']) cycleList = ['1', '2', '3', '5', '成立以来'] holdingTypeList = ['mainStockHolding', 'allStockHolding'] for holdingType in holdingTypeList: for symbol in fundSymbols: for cycle in cycleList: btParms = { 'symbol': symbol, 'cycle': cycle, 'holdingType': holdingType } self = attribution_riskModel(btParms, stockIndus, indusReturn, factorExposure, factorReturn) output = self.output tp = "1" if holdingType == 'mainStockHolding' else '2' # 持仓类型 param = output['multiAttr'] if len(param) == 0: continue for idx in param.index: di = param.loc[idx].to_dict() indexName = idx # 因子名称 indexType = di['factorType'] # 因子分类 indexAttr = di['multiAttr'] # 因子贡献收益 cyleTp = cyleFormula[cycle] # 周期类型 lsd = [] id_ = uuid_util.gen_uuid() lsd.append(id_) lsd.append(output['symbol']) lsd.append(tp) lsd.append(cyleTp) lsd.append(indexType) lsd.append(indexName) lsd.append(indexAttr) lsd.append("sys") lsd.append(datetime.now()) lsd.append("sys") lsd.append(datetime.now()) lsd = [transformFloatIfAvaliable4(l) for l in lsd] sql = 'INSERT INTO `fof`.`fof_multi_attr_riskmodel`(`OBJECT_ID`, `S_INFO_WINDCODE`, `DATA_TYPE`, `CYCLE_TYPE`, `FACTOR_TYPE`, `FACTOR_NAME`, `FACTOR_VALUE`, `CREATE_USER_ID`, `CREATE_TIME`, `UPDATE_USER_ID`, `UPDATE_TIME`) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)' mysqlops.insert_one(MysqlConf.DB.fof, sql, tuple(lsd))
def risk_single(model): # 风险因子业绩归因模块 # fof_single_attr_riskmodel if not del_data('fof_single_attr_riskmodel'): raise Error("删除历史数据失败,任务结束") stockIndus, indusReturn, factorExposure, factorReturn = publicData_riskModel( ) # 批量运行 ''' 跨期归因结果需要存储多个周期值 单期归因结果仅存储成立以来的值即可 跨期归因和单期归因可以用两张表来存储数据 ''' fundSymbols = getData_fundSymbols(['股票型', '混合型']) cycleList = ['成立以来'] # 这个程序跟之前的程序有一点特殊,就是多期归因要存多个周期的结果,单期归因只存成立以来的结果 holdingTypeList = ['mainStockHolding', 'allStockHolding'] for holdingType in holdingTypeList: for symbol in fundSymbols: for cycle in cycleList: btParms = { 'symbol': symbol, 'cycle': cycle, 'holdingType': holdingType } self = attribution_riskModel(btParms, stockIndus, indusReturn, factorExposure, factorReturn) output = self.output tp = "1" if holdingType == 'mainStockHolding' else '2' # 持仓类型 param = output['singleAttr'] if len(param) == 0: continue for idx in param.index: dt = idx # time di = param.loc[idx].to_dict() style = di['style'] # 风格因子归因 industry = di['industry'] # 行业因子归因 idiosyn = di['idiosyn'] # 特质因子归因 fundRet = di['fundRet'] # 基金当月收益率 lsd = [] id_ = uuid_util.gen_uuid() lsd.append(id_) lsd.append(output['symbol']) lsd.append(dt._short_repr.replace("-", "")) lsd.append(tp) lsd.append(style) lsd.append(industry) lsd.append(idiosyn) lsd.append(fundRet) lsd.append("sys") lsd.append(datetime.now()) lsd.append("sys") lsd.append(datetime.now()) lsd = [transformFloatIfAvaliable4(l) for l in lsd] sql = 'INSERT INTO `fof`.`fof_single_attr_riskmodel`(`OBJECT_ID`, `S_INFO_WINDCODE`, `TRADE_DT`, `DATA_TYPE`, `STYLE`, `INDUSTRY`, `IDIOSYN`, `FUND_RETURN`, `CREATE_USER_ID`, `CREATE_TIME`, `UPDATE_USER_ID`, `UPDATE_TIME`) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)' mysqlops.insert_one(MysqlConf.DB.fof, sql, tuple(lsd))
def other_indicator(model: OfflineTaskModel): # 离线计算其他指标 indicatorInfo = model.taskModel indicatorId = model.extVal if del_data(sqlPa="delete from fof_index_value where INDICATOR_ID = '%s'" % indicatorId) == False: raise Error("删除历史数据失败,任务结束") if indicatorId not in INDEX_CODE_NAME_CACHE.keys(): raise Error("指标id不存在:{}".format(indicatorId)) indexName = INDEX_CODE_NAME_CACHE[indicatorId] # fundAdjNav = getPublicData(['股票型', '混合型']) fundAdjNav = getPublicData() fundSymbols = list(fundAdjNav.columns) updatedType = [] fundClass_1 = pd.Series('混合型基金', index=fundSymbols) fundClass_tmp = getData_fundInformation(fundSymbols)['FUND_INVESTTYPE'] fundClass_1[fundClass_tmp.index] = fundClass_tmp for symbol in fundSymbols: allIndicators = [] fundType = fundClass_1[symbol] if fundType not in updatedType: for cycle in n_cycle_tp: btParms = {'indexName': indexName, 'cycle': cycle, 'symbol': symbol, 'sample': '一级', 'marketIndex': '', 'otherPar': ''} self = indexScore_fund(btParms, fundAdjNav, 'auto') op = self.output val = op['factorValue'] allIndicators.append(val) updatedType.append(fundType) df = pd.DataFrame(allIndicators).fillna(bus_const.blank) cols = df.columns.tolist() for col in cols: res = df[col] lst = res.values.tolist() lst = [transformFloatIfAvaliable(l) for l in lst] objId = uuid_util.gen_uuid() lst.insert(0, str(objId)) lst.insert(1, str(indicatorId)) lst.insert(2, col) date = formatDate2yyyymmdd() lst.insert(3, str(date)) lst.append("sys") lst.append(datetime.datetime.now()) lst.append("sys") lst.append(datetime.datetime.now()) tp = tuple(lst) sql = "INSERT INTO fof_index_value (`OBJECT_ID`, `INDICATOR_ID`, `J_WINDCODE`, `TRADE_DT`, `THISYEAR_VALUE`, `QUARTER_VALUE`, `HALFYEAR_VALUE`, `YEAR_VALUE`, `TWOYEA_VALUE`, `THREEYEAR_VALUE`, `FIVEYEAR_VALUE`, `N1_VALUE`, `N2_VALUE`, `CREATE_USER_ID`, `CREATE_TIME`, `UPDATE_USER_ID`, `UPDATE_TIME`, `DELETE_FLAG`) VALUES ( %s, %s, %s, %s, %s, %s,%s,%s, %s,%s, %s, %s, %s, %s, %s, %s, %s,0)" mysqlops.insert_one(MysqlConf.DB.fof, sql, tp) allIndicators.clear()
def volatility(model: OfflineTaskModel): # 离线计算年化波动率 if del_data('fof_variance') == False: raise Error("删除历史数据失败,任务结束") indicatorInfo = model.taskModel taskName = indicatorInfo.value[0] indiId = get_indicator_id(taskName) # fundAdjNav = getPublicData(['股票型', '混合型']) fundAdjNav = getPublicData() fundSymbols = list(fundAdjNav.columns) fundClass_1 = pd.Series('混合型基金', index=fundSymbols) fundClass_tmp = getData_fundInformation(fundSymbols)['FUND_INVESTTYPE'] fundClass_1[fundClass_tmp.index] = fundClass_tmp halfs = indicatorInfo.value[1] for half in halfs: updatedType = [] allIndicators = [] for symbol in fundSymbols: fundType = fundClass_1[symbol] if fundType not in updatedType: for cycle in n_cycle_tp: btParms = {'indexName': taskName, 'cycle': cycle, 'symbol': symbol, 'sample': '一级', 'marketIndex': '', 'otherPar': half} self = indexScore_fund(btParms, fundAdjNav, 'auto') op = self.output val = op['factorValue'] allIndicators.append(val) updatedType.append(fundType) df = pd.DataFrame(allIndicators).fillna(bus_const.blank) cols = df.columns.tolist() for col in cols: res = df[col] lst = res.values.tolist() lst = [transformFloatIfAvaliable(l) for l in lst] objId = uuid_util.gen_uuid() lst.insert(0, str(objId)) lst.insert(1, str(indiId)) lst.insert(2, str(col)) date = formatDate2yyyymmdd() lst.insert(3, str(date)) lst.insert(4, transformString2Decimal(half)) lst.append("sys") lst.append(datetime.datetime.now()) lst.append("sys") lst.append(datetime.datetime.now()) tp = tuple(lst) sql = "INSERT INTO fof_variance (`OBJECT_ID`, `INDICATOR_ID`, `J_WINDCODE`, `TRADE_DT`, `F_WEIGHT`, `THISYEAR_VALUE`, `QUARTER_VALUE`, `HALFYEAR_VALUE`, `YEAR_VALUE`, `TWOYEA_VALUE`, `THREEYEAR_VALUE`, `FIVEYEAR_VALUE`, `N1_VALUE`, `N2_VALUE`, `CREATE_USER_ID`, `CREATE_TIME`, `UPDATE_USER_ID`, `UPDATE_TIME`, `DELETE_FLAG`) VALUES ( %s, %s, %s, %s, %s, %s, %s,%s, %s, %s, %s, %s, %s, %s, %s, %s, %s,%s,0)" mysqlops.insert_one(MysqlConf.DB.fof, sql, tp) allIndicators.clear()
def industry_config_score(model): # sql = "delete from fof_fund_industry_score " # mysqlops.fetch_one(MysqlConf.DB.fof, sql) if not del_data('fof_fund_industry_score'): raise Error("删除历史数据失败,任务结束") # 为提高运行速度,公共数据一次性提取,并作为参数传入要调用的类 stockIndus, indusNet = getPublicDataAllocation() # 批量生成基金经理任职区间信息 managerInfo = getData_fundManagerInfo_all() fundSymbols = getData_fundSymbols(['股票型', '混合型']) tmp = [] for row in managerInfo.index: if managerInfo.loc[row, 'fund'] in fundSymbols: tmp.append(managerInfo.loc[row].to_dict()) managerInfo = pd.DataFrame(tmp, columns=managerInfo.columns) ''' 首次存入数据库时需要全部运行,后续更新维护时只需更新在任基金经理信息即可 ''' # 批量生成基金经理行业配置能力 for i in range(len(managerInfo)): btParms = { 'symbol': managerInfo.loc[i, 'fund'], 'manager': managerInfo.loc[i, 'manager'] } try: test = industryAllocation_manager(btParms, stockIndus, indusNet) output = test.output scoreDF = output['alloScore'] # fof_fund_industry_score for j, v in scoreDF.items(): sVal = v # 配置占股票市值比 dt_ = j # 时间 id_ = uuid_util.gen_uuid() # objId wCode = output['symbol'] # 基金代码 lst = [] lst.append(id_) lst.append(wCode) lst.append(output['managerId']) lst.append(output['manager']) lst.append(dt_._short_repr.replace("-", "")) lst.append(transformFloatIfAvaliable4(sVal)) lst.append("sys") lst.append(datetime.now()) lst.append("sys") lst.append(datetime.now()) sql = "INSERT INTO `fof`.`fof_fund_industry_score`(`OBJECT_ID`, `J_WINDCODE`, `MANAGER_ID`,`MANAGER_NAME`,`TRADE_DT`, `RETURN_SCORE`, `CREATE_USER_ID`, `CREATE_TIME`, `UPDATE_USER_ID`, `UPDATE_TIME`) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s)" mysqlops.insert_one(MysqlConf.DB.fof, sql, tuple(lst)) except: pass
def stockexpousre(model): # factorName即为mongodb数据库相关collection的名字,一次仅支持传入一个因子值 sql = "delete from fof_stockexpousre " mysqlops.fetch_one(MysqlConf.DB.fof, sql) if not del_data('fof_stockexpousre'): raise Error("删除历史数据失败,任务结束") fs = [ 'value', 'size', 'beta', 'earning', # 'factorReturn', 'growth', 'leverage', 'liquidity', 'momentum', 'nonlinear_size', 'size', 'volatility' ] sql = 'insert into ' \ 'fof_stockexpousre (OBJECT_ID,trade_dt,s_windcode,indicator_code,factor_value,CREATE_USER_ID,CREATE_TIME,UPDATE_USER_ID,UPDATE_TIME)' \ 'values (%s,%s,%s,%s,%s,%s,%s,%s,%s)' for fName in fs: valueExp = getData_factorExposure(fName) valueExp = valueExp.fillna("9999.000000") di = valueExp.to_dict() for k in di: # print("K===%s" % k) for v in di.get(k): s = uuid_util.gen_uuid() lsd = [] oid = s[:5] + '-' + s[5:9] + '-' + s[9:13] + '-' + s[ 13:18] + '-' + s[18:] lsd.append(oid) r = v._date_repr.replace("-", "") lsd.append(r) lsd.append(k[:-2] + "." + k[-2:]) lsd.append(fName) lsd.append(transformFloatIfAvaliable(di.get(k).get(v))) lsd.append("sys") lsd.append(datetime.now()) lsd.append("sys") lsd.append(datetime.now()) mysqlops.insert_one(MysqlConf.DB.fof, sql, tuple(lsd))
def net_value(model): if not del_data('fof_fundnav_style'): raise Error("删除历史数据失败,任务结束") # 基金净值风格划分 fof_fundnav_style fundSymbols = getData_fundSymbols(['股票型', '混合型']) # fundSymbols=fundSymbols[0:20] #生产环境删除此句 output_all = [] i = 0 for symbol in fundSymbols: i += 1 btParms = {'symbol': symbol, 'cycle': '成立以来'} # 离线入库时cycle设定为"成立以来"即可 test = fundStyle_nav(btParms) output = test.output tmp = output['regStat'] if tmp is None or len(tmp) == 0: continue for idx in tmp.index: di = tmp.loc[idx].to_dict() startDate = di['startDate'] endDate = di['endDate'] largeCoe = di['largeCoeff'] smallCoe = di['smallCoeff'] r2 = di['R2'] lst = [] id_ = uuid_util.gen_uuid() # objId lst.append(id_) lst.append(output['symbol']) lst.append(startDate) lst.append(endDate) lst.append(largeCoe) lst.append(smallCoe) lst.append(r2) lst.append("sys") lst.append(datetime.now()) lst.append("sys") lst.append(datetime.now()) ll = [transformFloatIfAvaliable4(j) for j in lst] sql = "INSERT INTO `fof`.`fof_fundnav_style`(`OBJECT_ID`, `J_WINDCODE`, `START_DATE`, `END_DATE`, `LARGE_COEFFICIENTS`, `SMALL_COEFFICIENTS`,`R2`, `CREATE_USER_ID`, `CREATE_TIME`, `UPDATE_USER_ID`, `UPDATE_TIME`) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s,%s, %s)" mysqlops.insert_one(MysqlConf.DB.fof, sql, tuple(ll))
def holding_style_main(model): if not del_data('fof_fund_tentop_stock_style'): raise Error("删除历史数据失败,任务结束") # 获取本模块公共数据 factorExposure = publicData_stockFactor() fundSymbols = getData_fundSymbols(['股票型', '混合型']) # fundSymbols = fundSymbols[0:10] # 生产环境删除此句 typeList = ['mainStockHolding', 'allStockHolding'] # 暴露度计算 # for holdingType in typeList: for symbol in fundSymbols: btParms = {'symbol': symbol, 'holdingType': 'mainStockHolding'} test = fundFactorExposure(btParms, factorExposure) output = test.output tmp = output['hisExposure'] # if holdingType == 'mainStockHolding': # 10 top for time in tmp: val = tmp[time] for idx in val.index: di = val.loc[idx].to_dict() exp = di['portExposure'] expscore = di['portExposureScore'] ls = [] ls.append(uuid_util.gen_uuid()) ls.append(output['symbol']) ls.append(time._short_repr.replace("-", "")) ls.append(idx) ls.append(transformFloatIfAvaliable3(exp)) ls.append(transformFloatIfAvaliable3(expscore)) ls.append("sys") ls.append(datetime.now()) ls.append("sys") ls.append(datetime.now()) sql = "INSERT INTO `fof`.`fof_fund_tentop_stock_style`(`OBJECT_ID`, `J_WINDCODE`, `TRADE_DT`, `INDICATOR_NAME`, `EXPOSE_VALUE`, `EXPOSE_SCORE`, `CREATE_USER_ID`, `CREATE_TIME`, `UPDATE_USER_ID`, `UPDATE_TIME`) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s)" mysqlops.insert_one(MysqlConf.DB.fof, sql, tuple(ls))
def industry_config_avgscore(model): if not del_data('fof_fund_industry_avgscore'): raise Error("删除历史数据失败,任务结束") # 为提高运行速度,公共数据一次性提取,并作为参数传入要调用的类 stockIndus, indusNet = getPublicDataAllocation() # 批量生成基金经理任职区间信息 managerInfo = getData_fundManagerInfo_all() fundSymbols = getData_fundSymbols(['股票型', '混合型']) tmp = [] for row in managerInfo.index: if managerInfo.loc[row, 'fund'] in fundSymbols: tmp.append(managerInfo.loc[row].to_dict()) managerInfo = pd.DataFrame(tmp, columns=managerInfo.columns) ''' 首次存入数据库时需要全部运行,后续更新维护时只需更新在任基金经理信息即可 ''' # 批量生成基金经理行业配置能力 for i in range(len(managerInfo)): btParms = { 'symbol': managerInfo.loc[i, 'fund'], 'manager': managerInfo.loc[i, 'manager'] } try: test = industryAllocation_manager(btParms, stockIndus, indusNet) output = test.output scoreDF = output['alloScore'] # fof_fund_stock_industry distributeDF = output['alloDistribute'] # fof_fund_industry_score evaluationDF = output[ 'alloEvaluation'] # fof_fund_industry_avgscore rdf = evaluationDF.T for index in rdf.index: res = rdf.loc[index] indus = index # 中信行业名称 dt_ = '123' # 时间 score = res['平均配置得分'] # 配置收益得分 bili = res['平均配置比例'] zhanbi = res['配置次数占比'] # id_ = uuid_util.gen_uuid() # objId wCode = output['symbol'] # 基金代码 lst = [] id_ = uuid_util.gen_uuid() # objId lst.append(id_) lst.append(wCode) lst.append(output['managerId']) lst.append(output['manager']) # lst.append(dt_._short_repr.replace("-", "")) lst.append(indus) lst.append(transformFloatIfAvaliable4(bili)) lst.append(transformFloatIfAvaliable4(score)) lst.append(transformFloatIfAvaliable4(zhanbi)) lst.append("sys") lst.append(datetime.now()) lst.append("sys") lst.append(datetime.now()) sql = "INSERT INTO `fof`.`fof_fund_industry_avgscore`(`OBJECT_ID`, `J_WINDCODE`, `MANAGER_ID`,`MANAGER_NAME`, `INDUSTRY_NAME`, `AVG_PERCENT`, `AVG_SCORE`, `AVG_TIMES`, `CREATE_USER_ID`, `CREATE_TIME`, `UPDATE_USER_ID`, `UPDATE_TIME`) VALUES (%s, %s, %s, %s, %s, %s,%s, %s, %s, %s, %s, %s)" mysqlops.insert_one(MysqlConf.DB.fof, sql, tuple(lst)) except: pass
def industry_config_indust(model): if del_data('fof_fund_stock_industry') == False: raise Error("删除历史数据失败,任务结束") # sql = "delete from fof_fund_stock_industry " # mysqlops.fetch_one(MysqlConf.DB.fof, sql) # 离线计算基金经理行业配置能力 # 为提高运行速度,公共数据一次性提取,并作为参数传入要调用的类 stockIndus, indusNet = getPublicDataAllocation() # 批量生成基金经理任职区间信息 managerInfo = getData_fundManagerInfo_all() fundSymbols = getData_fundSymbols(['股票型', '混合型']) tmp = [] for row in managerInfo.index: if managerInfo.loc[row, 'fund'] in fundSymbols: tmp.append(managerInfo.loc[row].to_dict()) managerInfo = pd.DataFrame(tmp, columns=managerInfo.columns) ''' 首次存入数据库时需要全部运行,后续更新维护时只需更新在任基金经理信息即可 ''' # 批量生成基金经理行业配置能力 for i in range(len(managerInfo)): btParms = { 'symbol': managerInfo.loc[i, 'fund'], 'manager': managerInfo.loc[i, 'manager'] } try: test = industryAllocation_manager(btParms, stockIndus, indusNet) output = test.output distributeDF = output['alloDistribute'] # fof_fund_stock_industry # for i in stockIndus.index: # print(i, "====", stockIndus[i]) # fof_fund_stock_industry # sWindCode = i # 玩的股票代码 # indsName = stockIndus[i] # 行业名称? wCode = output['symbol'] # 基金代码 for index in distributeDF.index: res = distributeDF.loc[index] for si in res.index: indus = index # 中信行业名称 dt_ = si # 时间 profit = res[si] # 配置收益得分 id_ = uuid_util.gen_uuid() # objId lst = [] lst.append(id_) lst.append(wCode) lst.append(output['managerId']) lst.append(output['manager']) lst.append(dt_._short_repr.replace("-", "")) lst.append(indus) lst.append(transformFloatIfAvaliable4(profit)) lst.append("sys") lst.append(datetime.now()) lst.append("sys") lst.append(datetime.now()) ls = [transformFloatIfAvaliable4(i) for i in lst] sql = "INSERT INTO `fof`.`fof_fund_stock_industry`(`OBJECT_ID`, `J_WINDCODE`, `MANAGER_ID`,`MANAGER_NAME`,`TRADE_DT`, `INDUSTRY_NAME`, `STOCK_RATE`, `CREATE_USER_ID`, `CREATE_TIME`, `UPDATE_USER_ID`, `UPDATE_TIME`) VALUES ( %s, %s,%s, %s, %s, %s, %s, %s,%s, %s, %s)" mysqlops.insert_one(MysqlConf.DB.fof, sql, tuple(ls)) except: pass
def indicator_score_rank(model: OfflineTaskModel): # 离线计算指标得分 indicatorId = model.extVal if indicatorId not in INDEX_CODE_NAME_CACHE.keys(): raise Error("指标id不存在:{}".format(indicatorId)) if not del_data( sqlPa="delete from fof_index_score where INDICATOR_ID = '%s' " % indicatorId): raise Error("删除历史数据失败,任务结束") iName = INDEX_CODE_NAME_CACHE[indicatorId] # fundAdjNav = getPublicData(['股票型', '混合型']) fundAdjNav = getPublicData() fundSymbols = list(fundAdjNav.columns) updatedType = [] fundClass_1 = pd.Series('混合型基金', index=fundSymbols) fundClass_tmp = getData_fundInformation(fundSymbols)['FUND_INVESTTYPE'] fundClass_1[fundClass_tmp.index] = fundClass_tmp for symbol in fundSymbols: indicators = [] ranks = [] fundType = fundClass_1[symbol] if fundType not in updatedType: for cycle in cycle_score: btParms = { 'indexName': iName, 'cycle': cycle, 'symbol': symbol, 'sample': '一级', 'marketIndex': '', 'otherPar': '' } self = indexScore_fund(btParms, fundAdjNav, 'auto') op = self.output score = op['factorScore'] rank = op['factorRank'] # 这个值是用来记录当前cycle及分类下参与排名的样本数量,因为前端显示的排名都是5 / 200 # 这种形式,本意是要存入数据库的,但我跟平赞沟通过了,他说他们前端直接根据数据库里的数据处理即可,不用存这个字段了 # sc = op['sampleCounts'] # tp = score # for ti in tp.index: # tp[ti] = str(sc) indicators.append(score) ranks.append(rank) ranks.append(pd.Series()) # 一级分类排名样本 ranks.append(pd.Series()) # 二级分类排名一期不上,先做个空值 ranks.append(pd.Series()) # 二级分类排名样本一期不上,先做个空值 updatedType.append(fundType) indicators.extend(ranks) df = pd.DataFrame(indicators).fillna('9999999999999.0') cols = df.columns.tolist() for col in cols: res = df[col] # [基金id,score,score,...] lst = res.values.tolist() lst = [transformFloatIfAvaliable2(l) for l in lst] objId = uuid_util.gen_uuid() lst.insert(0, str(objId)) lst.insert(1, str(indicatorId)) lst.insert(2, col) # lst.insert(2, list(map(lambda x: x[-2:] + x[:-2], [col]))[0]) date = formatDate2yyyymmdd() lst.insert(3, str(date)) lst.append("sys") lst.append(datetime.datetime.now()) lst.append("sys") lst.append(datetime.datetime.now()) lst = [ '9999999999999.0' if type(ele) == pd.Series else ele for ele in lst ] tp = tuple(lst) sql = "INSERT INTO fof_index_score VALUES ( %s, %s, %s, %s, %s, %s, %s,%s, %s, %s, %s, %s, %s, %s,%s, %s, %s, %s, %s, %s, %s, %s,%s,%s,%s, %s, %s, %s, %s, %s, %s, %s,%s,%s,%s, %s, %s, %s, %s, %s, %s, %s,%s,0)" mysqlops.insert_one(MysqlConf.DB.fof, sql, tp) indicators.clear() ranks.clear()
def return_total(model): if not del_data('fof_fund_excess_return_total'): raise Error("删除历史数据失败,任务结束") # fof_fund_excess_return_total # 一次性调取需要用到的公共数据 # logic_return_total() # 一次性调取需要用到的公共数据 stockIndus, stockRet, indusRet, crIndus = publicData_stockAndIndustry() # 批量生成基金经理任职区间信息 ''' 首次存入数据库时需要全部运行,后续更新维护时只需更新在任基金经理信息即可. 选股能力仅针对于股票型与混合型基金 ''' managerInfo = getData_fundManagerInfo_all() fundSymbols = getData_fundSymbols(['股票型', '混合型']) tmp = [] for row in managerInfo.index: if managerInfo.loc[row, 'fund'] in fundSymbols: tmp.append(managerInfo.loc[row].to_dict()) managerInfo = pd.DataFrame(tmp, columns=managerInfo.columns) output_all = [] for i in range(len(managerInfo)): # if i<5: #生产环境下删除该句,输入前100个仅为调试使用 btParms = { 'symbol': managerInfo.loc[i, 'fund'], 'manager': managerInfo.loc[i, 'manager'] } try: test = stockPickingAbility_manager(btParms, stockIndus, stockRet, indusRet, crIndus) output = test.output # output['compareReturnIndustry'].to_csv("c:\\users\\futanghang\\desktop\\compareReturnIndustry.csv") # output['excessReturnIndustry'].to_csv("c:\\users\\futanghang\\desktop\\excessReturnIndustry.csv") # output['excessReturnQuarter'].to_csv("c:\\users\\futanghang\\desktop\\excessReturnQuarter.csv") # output['mainStcokReturn'].to_csv("c:\\users\\futanghang\\desktop\\mainStcokReturn.csv") pdict = output['excessReturnIndustry'] for k, v in pdict.items(): # k 行业,v 值 indus = k val = v id_ = uuid_util.gen_uuid() # objId wCode = output['symbol'] # 基金Wind代码 managerName = output['manager'] # 基金经理名称 managerId = output['managerId'] # 基金经理id lst = [] lst.append(id_) lst.append(wCode) lst.append(managerId) lst.append(managerName) lst.append(indus) lst.append(val) lst.append("sys") lst.append(datetime.now()) lst.append("sys") lst.append(datetime.now()) ls = [transformFloatIfAvaliable4(i) for i in lst] sql = "INSERT INTO `fof`.`fof_fund_excess_return_total`(`OBJECT_ID`, `J_WINDCODE`, `MANAGER_ID`, `MANAGER_NAME`, `INDUSTRY_NAME`, `INDUSTRY_RETURN`, `CREATE_USER_ID`, `CREATE_TIME`, `UPDATE_USER_ID`, `UPDATE_TIME`) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s)" mysqlops.insert_one(MysqlConf.DB.fof, sql, tuple(ls)) except: output = { 'managerId': managerInfo.loc[i, 'managerId'], 'symbol': managerInfo.loc[i, 'fund'], 'manager': managerInfo.loc[i, 'manager'], 'excessReturnQuarter': [], 'excessReturnIndustry': [], 'compareReturnIndustry': {}, 'mainStcokReturn': [] } output_all.append(output)
def doLogic(model: OfflineTaskModel): #model = model[0] model = model start_time = time.time() taskType = model.taskType uuid = model.uuid taskModel = model.taskModel func = model.func if model.extVal is not None: res = mysqlops.fetch_one( MysqlConf.DB.fof, "select indicator_name from fof_index where indicator_code ='" + model.extVal + "'") if res is None: raise Error("指标id不存在:{}".format(model.extVal)) taskName = res['indicator_name'].decode("utf-8") model.otherIndexName = taskName else: taskName = taskModel.value[0] if OPEN_PREVENT_DUPLICATION == 'False': # 如果开启了任务防重就做判断拦截 sql = "select task_status " \ "from fof_task " \ "where DATE(UPDATE_TIME) = CURRENT_DATE() " \ "AND task_name='{}' " \ "AND task_type='{}' " \ "AND task_status in (0,1)" \ .format(taskName, taskType) res = mysqlops.fetch_one(MysqlConf.DB.fof, sql) if res: log.warning("taskName:{},taskType:{},今天的任务处理状态为{},不需要再做处理".format( taskName, taskType, res)) return sql = "INSERT INTO fof_task VALUES ( %s, %s, %s, %s,%s ,%s,%s)" tp = (uuid, taskName, taskType, TaskStatus.accept.value[0], "", datetime.now(), datetime.now()) mysqlops.insert_one(MysqlConf.DB.fof, sql, tp) log.info("taskType:%s,taskName:%s,开始 查询流水号:%s" % (taskType, taskName, uuid)) try: func(model) except Exception as e: log.exception("未知异常:\n") sql = "update fof_task set task_status=%s,error_msg = %s,update_time = %s where task_id = %s" tp = (TaskStatus.fail.value[0], str(e), datetime.now(), uuid) mysqlops.insert_one(MysqlConf.DB.fof, sql, tp) hours, minutes, seconds = compute_time(start_time) log.error("taskType:%s,taskName:%s,出错 %s,查询流水号:%s,耗时: %s" % (taskType, taskName, e, uuid, "{:>02d}:{:>02d}:{:>02d}".format(hours, minutes, seconds))) return sql = "update fof_task set task_status=%s,update_time = %s where task_id = %s" tp = (TaskStatus.ok.value[0], datetime.now(), uuid) mysqlops.insert_one(MysqlConf.DB.fof, sql, tp) hours, minutes, seconds = compute_time(start_time) log.info("taskType:%s,taskName:%s,成功,查询流水号:%s,耗时: %s" % (taskType, taskName, uuid, "{:>02d}:{:>02d}:{:>02d}".format( hours, minutes, seconds)))
di = tmp.loc[idx].to_dict() startDate = di['startDate'] endDate = di['endDate'] largeCoe = di['largeCoeff'] smallCoe = di['smallCoeff'] r2 = di['R2'] lst = [] id_ = uuid_util.gen_uuid() # objId lst.append(id_) lst.append(output['symbol']) lst.append(startDate) lst.append(endDate) lst.append(largeCoe) lst.append(smallCoe) lst.append(r2) lst.append("sys") lst.append(datetime.now()) lst.append("sys") lst.append(datetime.now()) ll = [transformFloatIfAvaliable4(j) for j in lst] sql = "INSERT INTO `fof`.`fof_fundnav_style`(`OBJECT_ID`, `J_WINDCODE`, `START_DATE`, `END_DATE`, `LARGE_COEFFICIENTS`, `SMALL_COEFFICIENTS`,`R2`, `CREATE_USER_ID`, `CREATE_TIME`, `UPDATE_USER_ID`, `UPDATE_TIME`) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s,%s, %s)" mysqlops.insert_one(MysqlConf.DB.fof, sql, tuple(ll)) ''' 实时交互demo btParms={'symbol':'000001.OF','cycle':'20120101,20190331'} test=fundStyle_nav(btParms) output_json=test.output_json print(output_json) '''
def return_(model): if not del_data('fof_fund_main_stock_return'): raise Error("删除历史数据失败,任务结束") # fof_fund_main_stock_return # logic_return_() # 一次性调取需要用到的公共数据 stockIndus, stockRet, indusRet, crIndus = publicData_stockAndIndustry() # 批量生成基金经理任职区间信息 ''' 首次存入数据库时需要全部运行,后续更新维护时只需更新在任基金经理信息即可. 选股能力仅针对于股票型与混合型基金 ''' managerInfo = getData_fundManagerInfo_all() fundSymbols = getData_fundSymbols(['股票型', '混合型']) tmp = [] for row in managerInfo.index: if managerInfo.loc[row, 'fund'] in fundSymbols: tmp.append(managerInfo.loc[row].to_dict()) managerInfo = pd.DataFrame(tmp, columns=managerInfo.columns) output_all = [] for i in range(len(managerInfo)): btParms = { 'symbol': managerInfo.loc[i, 'fund'], 'manager': managerInfo.loc[i, 'manager'] } try: test = stockPickingAbility_manager(btParms, stockIndus, stockRet, indusRet, crIndus) output = test.output # output['compareReturnIndustry'].to_csv("c:\\users\\futanghang\\desktop\\compareReturnIndustry.csv") # output['excessReturnIndustry'].to_csv("c:\\users\\futanghang\\desktop\\excessReturnIndustry.csv") # output['excessReturnQuarter'].to_csv("c:\\users\\futanghang\\desktop\\excessReturnQuarter.csv") # output['mainStcokReturn'].to_csv("c:\\users\\futanghang\\desktop\\mainStcokReturn.csv") pdict = output['compareReturnIndustry'] for k in pdict: df = pdict[k] for index in df.index: dt_ = index # dt di = df.loc[index].to_dict() xuangushouyilv = di['选股收益率'] ershiwufenweishu = di['25分位数'] wushifenweishu = di['50分位数'] qishiwufenweishuy = di['75分位数'] wCode = output['symbol'] # 基金Wind代码 managerName = output['manager'] # 基金经理名称 managerId = output['managerId'] # 基金经理id id_ = uuid_util.gen_uuid() # objId lst = [] lst.append(id_) lst.append(wCode) lst.append(managerId) lst.append(managerName) lst.append(dt_._short_repr.replace("-", "")) lst.append(k) lst.append(ershiwufenweishu) lst.append(wushifenweishu) lst.append(qishiwufenweishuy) lst.append(xuangushouyilv) lst.append("sys") lst.append(datetime.now()) lst.append("sys") lst.append(datetime.now()) ls = [transformFloatIfAvaliable4(i) for i in lst] sql = "INSERT INTO `fof`.`fof_fund_main_stock_return`(`OBJECT_ID`, `J_WINDCODE`, `MANAGER_ID`, `MANAGER_NAME`, `TRADE_DT`, `INDUSTRY_NAME`, `25_POINTS`, `50_POINTS`, `75_POINTS`, `INDUSTRY_RETURN`, `CREATE_USER_ID`, `CREATE_TIME`, `UPDATE_USER_ID`, `UPDATE_TIME`) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)" mysqlops.insert_one(MysqlConf.DB.fof, sql, tuple(ls)) except Exception as e: pass
def return_his(request): if not del_data('fof_fund_main_stock_return_his'): raise Error("删除历史数据失败,任务结束") # fof_fund_main_stock_return_his # logic_return_his() # 一次性调取需要用到的公共数据 stockIndus, stockRet, indusRet, crIndus = publicData_stockAndIndustry() # 批量生成基金经理任职区间信息 ''' 首次存入数据库时需要全部运行,后续更新维护时只需更新在任基金经理信息即可. 选股能力仅针对于股票型与混合型基金 ''' managerInfo = getData_fundManagerInfo_all() fundSymbols = getData_fundSymbols(['股票型', '混合型']) tmp = [] for row in managerInfo.index: if managerInfo.loc[row, 'fund'] in fundSymbols: tmp.append(managerInfo.loc[row].to_dict()) managerInfo = pd.DataFrame(tmp, columns=managerInfo.columns) for i in range(len(managerInfo)): btParms = { 'symbol': managerInfo.loc[i, 'fund'], 'manager': managerInfo.loc[i, 'manager'] } try: test = stockPickingAbility_manager(btParms, stockIndus, stockRet, indusRet, crIndus) output = test.output # output['compareReturnIndustry'].to_csv("c:\\users\\futanghang\\desktop\\compareReturnIndustry.csv") # output['excessReturnIndustry'].to_csv("c:\\users\\futanghang\\desktop\\excessReturnIndustry.csv") # output['excessReturnQuarter'].to_csv("c:\\users\\futanghang\\desktop\\excessReturnQuarter.csv") # output['mainStcokReturn'].to_csv("c:\\users\\futanghang\\desktop\\mainStcokReturn.csv") ddis = output['mainStcokReturn'] ddis = ddis.fillna(blank) for idx in ddis.index: # idx 股票代码,dict :columns values stockId = idx # dt di = ddis.loc[idx].to_dict() peizhiqishu = di['配置期数(季度)'] pingjunquanzhong = di['平均权重(%)'] indust = di['所属行业'] chiyoujidupingjunshouyi = di['持有季度平均收益(%)'] chiyoujidupingjunhangyechaoeshouyi = di['持有季度平均行业超额收益(%)'] stockName = di['stockName'] wCode = output['symbol'] # 基金Wind代码 managerName = output['manager'] # 基金经理名称 managerId = output['managerId'] # 基金经理id id_ = uuid_util.gen_uuid() # objId lst = [] lst.append(id_) lst.append(wCode) lst.append(managerId) lst.append(managerName) lst.append(stockId) lst.append(stockName) lst.append(indust) lst.append(transformFloatIfAvaliable4(peizhiqishu)) lst.append(transformFloatIfAvaliable4(pingjunquanzhong)) lst.append(transformFloatIfAvaliable4(chiyoujidupingjunshouyi)) lst.append( transformFloatIfAvaliable4( chiyoujidupingjunhangyechaoeshouyi)) lst.append("sys") lst.append(datetime.now()) lst.append("sys") lst.append(datetime.now()) ls = [transformFloatIfAvaliable4(i) for i in lst] sql = "INSERT INTO `fof`.`fof_fund_main_stock_return_his`(`OBJECT_ID`, `J_WINDCODE`, `MANAGER_ID`, `MANAGER_NAME`, `G_WINDCODE`, `STOCK_NAME`,`INDUSTRY_NAME`, `HOLDING_PERIODS`, `AVG_WEIGHTS`, `AVG_RETURN`, `AVG_RETURN_QUARTER`, `CREATE_USER_ID`, `CREATE_TIME`, `UPDATE_USER_ID`, `UPDATE_TIME`) VALUES (%s, %s, %s, %s, %s,%s, %s, %s, %s, %s, %s, %s, %s, %s, %s)" mysqlops.insert_one(MysqlConf.DB.fof, sql, tuple(ls)) except Exception as e: pass
def information_ratio(model: OfflineTaskModel): # 离线计算信息比率 if del_data('fof_inforatio') == False: raise Error("删除历史数据失败,任务结束") # sql = "delete from fof_inforatio " # mysqlops.fetch_one(MysqlConf.DB.fof, sql) indicatorInfo = model.taskModel taskName = indicatorInfo.value[0] indiId = get_indicator_id(taskName) # fundAdjNav = getPublicData(['股票型', '混合型']) fundAdjNav = getPublicData() fundSymbols = list(fundAdjNav.columns) updatedType = [] fundClass_1 = pd.Series('混合型基金', index=fundSymbols) fundClass_tmp = getData_fundInformation(fundSymbols)['FUND_INVESTTYPE'] fundClass_1[fundClass_tmp.index] = fundClass_tmp for symbol in fundSymbols: allIndicators = [] fundType = fundClass_1[symbol] if fundType not in updatedType: for cycle in n_cycle_tp: btParms = {'indexName': taskName, 'cycle': cycle, 'symbol': symbol, 'sample': '一级', 'marketIndex': '', 'otherPar': ''} self = indexScore_fund(btParms, fundAdjNav, 'auto') op = self.output val = op['factorValue'] allIndicators.append(val) updatedType.append(fundType) df = pd.DataFrame(allIndicators).fillna(bus_const.blank) cols = df.columns.tolist() for col in cols: res = df[col] dd = res.values.tolist() lst = [transformFloatIfAvaliable(l) for l in dd] objId = uuid_util.gen_uuid() lst.insert(0, str(objId)) lst.insert(1, str(indiId)) lst.insert(2, col) date = formatDate2yyyymmdd() lst.insert(3, str(date)) # idxVal = None # try: # idxVal = CODE_INDEX_CACHE[col] # except: # # 缓存没有查一次数据库 # sql = "SELECT s_info_windcode,s_info_indexwindcode FROM chinamutualfundbenchmark where S_INFO_WINDCODE = '{}'".format( # col) # res = mysqlops.fetchmany(MysqlConf.DB.fof, sql) # if res and 's_info_indexwindcode' in res and res['s_info_indexwindcode'] is not None: # idxVal = res['s_info_indexwindcode'].decode() lst.insert(4, indicatorInfo.value[1]) # 比较基准wind代码 lst.append("sys") lst.append(datetime.datetime.now()) lst.append("sys") lst.append(datetime.datetime.now()) tp = tuple(lst) sql = "INSERT INTO fof_inforatio (`OBJECT_ID`, `INDICATOR_ID`, `J_WINDCODE`, `TRADE_DT`, `B_WINDCODE`, `THISYEAR_VALUE`, `QUARTER_VALUE`, `HALFYEAR_VALUE`, `YEAR_VALUE`, `TWOYEA_VALUE`, `THREEYEAR_VALUE`, `FIVEYEAR_VALUE`, `N1_VALUE`, `N2_VALUE`, `CREATE_USER_ID`, `CREATE_TIME`, `UPDATE_USER_ID`, `UPDATE_TIME`, `DELETE_FLAG`) VALUES ( %s, %s, %s, %s, %s, %s, %s,%s,%s,%s, %s, %s, %s, %s, %s, %s, %s, %s,0)" mysqlops.insert_one(MysqlConf.DB.fof, sql, tp) allIndicators.clear()