def test_check_liquidity_threshold(self): """检查数据库流动性阈值数据准确性""" connect_mysql = ConnectMysql(host='202.104.102.166', user='******', password='******', port=3306, db='jydb') valuation_day = "'2021-01-13'" # 估值日期 # valuation_day_split = valuation_day.split('-') # valuation_five_day = '{}-{}-{}'.format(valuation_day_split[0], valuation_day_split[1], # (int(valuation_day_split[2])-4)) valuation_five_day = "'2021-01-07'" # 估值日前5日 sql = "SELECT SUM(d.TurnoverValue) AS calnum, s.SecuCode AS calid FROM jydb.QT_DailyQuote d " \ "INNER JOIN jydb.SecuMain s ON d.InnerCode = s.InnerCode INNER JOIN jydb.CT_SystemConst c " \ "ON (s.ListedState = c.DM) WHERE s.SecuCategory = 1 AND s.SecuMarket IN (83, 90) AND " \ "c.LB = 1176 AND c.DM IN (1) AND SUBSTR(d.TradingDay,1,10) >= {} AND " \ "SUBSTR(d.TradingDay,1,10) <= {} GROUP BY s.SecuCode".format(valuation_five_day, valuation_day) sql_result = connect_mysql.fetchall(sql) # 获取估值日近5日成交额 # print(sql_result) turnover_list = [] # 成交额列表 turnover_dict = {} # 成交额与secucode映射表 secucode_list = [] # secucode列表 for i in sql_result: turnover = float(i[0]) secucode = i[1] turnover_dict[turnover] = secucode turnover_list.append(float(i[0])) turnover_sorted_list = sorted(turnover_list, reverse=True) # 成交额倒序排列 print(turnover_sorted_list) print(turnover_sorted_list[int(len(turnover_sorted_list) * 1/5)]) print(turnover_sorted_list[int(len(turnover_sorted_list) * 2/5)]) print(turnover_sorted_list[int(len(turnover_sorted_list) * 3/5)]) print(turnover_sorted_list[int(len(turnover_sorted_list) * 4/5)]) print(turnover_dict[turnover_sorted_list[int(len(turnover_sorted_list) * 1/5)]]) print(turnover_dict[turnover_sorted_list[int(len(turnover_sorted_list) * 2/5)]]) print(turnover_dict[turnover_sorted_list[int(len(turnover_sorted_list) * 3/5)]]) print(turnover_dict[turnover_sorted_list[int(len(turnover_sorted_list) * 4/5)]])
def test_check_price_threshold(self): """检查数据库高价低价股阈值数据准确性""" connect_mysql = ConnectMysql(host='202.104.102.166', user='******', password='******', port=3306, db='jydb') day = "'2021-01-11 00:00:00'" sql = "SELECT ClosePrice,InnerCode FROM QT_StockPerformance WHERE TradingDay = {}".format(day) sql_result = connect_mysql.fetchall(sql) # 获取某一天的股票价格,表数据量很大,查询时间需要好几分钟 price_list = [] # 价格列表 innercode_sorted_list = [] # 股票id倒序列表 innercode_dict = {} # 价格与股票id相对应字典 for i in sql_result: price = float(i[0]) innercode = i[1] innercode_dict[price] = innercode price_list.append(float(i[0])) price_sorted_list = sorted(price_list, reverse=True) # 价格倒序列表 for i in price_sorted_list: for key, value in innercode_dict.items(): if i == key: innercode_sorted_list.append(value) break print('股票价格原始数据:', sql_result) print('股票价格列表:', price_list) print('股票价格个数:', len(price_sorted_list)) print('股票价格倒序列表:', price_sorted_list) print('股票价格三分之一处价格', price_sorted_list[int(len(price_sorted_list) * 1/3)]) print('股票价格三分之二处价格', price_sorted_list[int(len(price_sorted_list) * 2/3)]) print('股票价格三分之一股票数量值', len(price_sorted_list) * 1/3) print('股票价格三分之二股票数量值', len(price_sorted_list) * 2/3) print('股票价格三分之一股票数量值int型', int(len(price_sorted_list) * 1/3)) print('股票价格三分之二股票数量值int型', int(len(price_sorted_list) * 2/3)) print('股票id根据价格排序后的id排序', innercode_sorted_list) print('价格排序后三分之一处的股票id', innercode_sorted_list[int(len(price_sorted_list) * 1/3)]) print('价格排序后三分之二处的股票id', innercode_sorted_list[int(len(price_sorted_list) * 2/3)])
def test_calculate_profit_and_loss(self): """计算持仓周期收益贡献中的盈亏""" fund_id = 'CF0000003J' # 产品id,用来查找对应的账户 productname = '螺纹' # 品种名称,用来查找品种id unwind_data_sql = "SELECT posiact,tradedir,trdprc,trdvol,trdtms FROM ppw_gtjaqh.cm_gj_future_trade_data WHERE"\ " tradingday >= '20200801' AND tradingday <= '20200831' AND posiact IN ('T'," \ "'C') AND productcode = (SELECT productcode FROM ppw_gtjaqh.`cm_gj_future_product` WHERE " \ "productname = '{}') AND assetacc = (SELECT assetacc FROM " \ "ppw_gtjaqh.`cm_fund_future_account_map` WHERE fund_id = '{}') ".format(productname, fund_id) connet_mysql = ConnectMysql(host='202.104.102.166', user='******', password='******', port=3306) unwind_data_result = connet_mysql.fetchall(unwind_data_sql) print(unwind_data_result)
def test_position_profit_and_loss(self): """持仓盈亏计算""" fund_id = 'CF0000003J' # 产品id,用来查找对应的账户 productname = '白银' # 品种名称,用来查找品种id position_profit_and_loss = 0 # 平仓盈亏累加 connet_mysql = ConnectMysql(host='202.104.102.166', user='******', password='******', port=3306) unwind_sql = "SELECT holdprlsm FROM ppw_gtjaqh.`cm_gj_future_trade_hold` WHERE assetacc = (SELECT assetacc " \ "FROM ppw_gtjaqh.`cm_fund_future_account_map` WHERE fund_id = '{}') AND productcode = " \ "(SELECT productcode FROM ppw_gtjaqh.`cm_gj_future_product` WHERE productname = '{}') " \ .format(fund_id, productname) print(unwind_sql) unwind_sql_result = connet_mysql.fetchall(unwind_sql) print(unwind_sql_result) for i in unwind_sql_result: position_profit_and_loss += float(i[0]) return position_profit_and_loss
def test_check_roe_threshold_greater_zero(): """检查数据库绩优股(roe大于0)阈值数据准确性""" connect_mysql = ConnectMysql(host='202.104.102.166', user='******', password='******', port=3306, db='jydb') day = "'2019-03-31'" sql = "SELECT m.roe,s.SecuCode FROM jydb.LC_MainIndexNew m INNER JOIN jydb.SecuMain s " \ "ON (m.CompanyCode = s.CompanyCode) INNER JOIN jydb.CT_SystemConst c ON " \ "(s.ListedState = c.DM) WHERE s.SecuCategory = 1 AND s.SecuMarket IN (83,90) AND c.LB = 1176 " \ "AND c.DM IN (1) AND SUBSTR(EndDate,1,10) = {} AND m.roe > 0".format(day) sql_result = connect_mysql.fetchall(sql) # 获取某一天的所有A股ROE值 roe_greater_zero = [] # 大于0的roe值的列表 roe_secucode_dict = {} # 大于0的roe与secucode映射表 # secucode_list = [] for i in sql_result: roe = float(i[0]) secucode = i[1] roe_secucode_dict[roe] = secucode roe_greater_zero.append(float(i[0])) roe_greater_zero_sorted = sorted(roe_greater_zero) print(sql_result) print(roe_greater_zero) print(roe_greater_zero_sorted) if (len(roe_greater_zero_sorted)) % 2 == 0: list_len = int(len(roe_greater_zero_sorted) / 2) roe_median = (roe_greater_zero_sorted[list_len] + roe_greater_zero_sorted[list_len + 1]) / 2 print(list_len) print(roe_median) print(roe_secucode_dict[roe_greater_zero_sorted[list_len]]) print(roe_secucode_dict[roe_greater_zero_sorted[list_len + 1]]) else: list_len = len(roe_greater_zero_sorted) / 2 print(list_len) list_len = int(len(roe_greater_zero_sorted) / 2) + 1 print(list_len) roe_median = roe_greater_zero_sorted[list_len] print(roe_median) print(roe_secucode_dict[roe_median])
def test_bear_market_value(self): """计算空头市值""" bear_market = 0 open_time = '20200513' # 持仓日期 fund_id = 'CF0000003J' # 产品id,用来查找对应的账户 productname = '中证500' # 品种名称,用来查找品种id connet_mysql = ConnectMysql(host='202.104.102.166', user='******', password='******', port=3306) bull_market_sql = "SELECT holdvol,stlprc FROM ppw_gtjaqh.`cm_gj_future_trade_hold` WHERE " \ "tradingday = '{}' AND assetacc = (SELECT assetacc FROM " \ "ppw_gtjaqh.`cm_fund_future_account_map` WHERE fund_id = '{}') " \ "AND productcode = (SELECT productcode FROM ppw_gtjaqh.`cm_gj_future_product` " \ "WHERE productname = '{}') AND tradedir = 'S'".format(open_time, fund_id, productname) print(bull_market_sql) bull_market_result = connet_mysql.fetchall( bull_market_sql) # 返回持仓表的对应的持仓量和结算价 print(bull_market_result) connet_mysql = ConnectMysql(host='202.104.102.166', user='******', password='******', port=3306) multiplier_sql = "SELECT multiplier FROM ppw_gtjaqh.`cm_gj_future_product` WHERE productname = '中证500'" multiplier_result = connet_mysql.fetchall( multiplier_sql) # 返回对应的品种的标的乘数 for i in bull_market_result: bear_market += float(i[0]) * float(i[1]) * multiplier_result return bear_market
def test_transaction_costs(self): """交易费用计算""" start_time = '20200801' end_time = '20200831' fund_id = 'CF0000003J' # 产品id,用来查找对应的账户 productname = '白银' # 品种名称,用来查找品种id transaction_costs = 0 # 平仓盈亏累加 connet_mysql = ConnectMysql(host='202.104.102.166', user='******', password='******', port=3306) transaction_costs_sql = "SELECT trdchrg FROM ppw_gtjaqh.cm_gj_future_trade_data where " \ "assetacc = (SELECT assetacc FROM ppw_gtjaqh.`cm_fund_future_account_map` WHERE " \ "fund_id = '{}') AND productcode = (SELECT productcode FROM " \ "ppw_gtjaqh.`cm_gj_future_product` WHERE productname = '{}')AND tradingday BETWEEN {} " \ "AND {} ".format(fund_id, productname, start_time, end_time) print(transaction_costs_sql) unwind_sql_result = connet_mysql.fetchall(transaction_costs_sql) print(unwind_sql_result) for i in unwind_sql_result: transaction_costs += float(i[0]) return transaction_costs
def test_unwind_profit_and_loss(self): """平仓盈亏计算""" start_time = '20200801' end_time = '20200831' fund_id = 'CF0000003J' # 产品id,用来查找对应的账户 productname = '白银' # 品种名称,用来查找品种id unwind_profit_and_loss = 0 # 平仓盈亏累加 connet_mysql = ConnectMysql(host='202.104.102.166', user='******', password='******', port=3306) unwind_sql = "SELECT closeprlsm FROM ppw_gtjaqh.cm_gj_future_trade_data WHERE posiact IN ('C','T') AND " \ "assetacc = (SELECT assetacc FROM ppw_gtjaqh.`cm_fund_future_account_map` WHERE fund_id = " \ "'{}') AND productcode = (SELECT productcode FROM ppw_gtjaqh.`cm_gj_future_product` " \ "WHERE productname = '{}')AND tradingday BETWEEN {} AND {} ".format(fund_id, productname, start_time, end_time) print(unwind_sql) unwind_sql_result = connet_mysql.fetchall(unwind_sql) print(unwind_sql_result) for i in unwind_sql_result: unwind_profit_and_loss += float(i[0]) return unwind_profit_and_loss
def test_check_threshold_year(self): """检查数据库大盘股小盘股阈值年数据准确性""" connect_mysql = ConnectMysql(host='202.104.102.166', user='******', password='******', port=3306) sql = 'SELECT avg_totalmv,InnerCode FROM rz_combination_master.cm_jydb_stock_avg_market_year ' \ 'where trading_day = 2018' sql_result = connect_mysql.fetchall(sql) market_value_list = [] # 总市值列表 innercode_list = [] # 股票id列表 innercode_dict = {} # 市值与股票id映射字典 print(sql_result) for i in sql_result: market_value = float(i[0]) innercode = i[1] innercode_dict[market_value] = innercode market_value_list.append(int(i[0])) print(market_value_list) market_value_sorted_list = sorted(market_value_list, reverse=True) print(len(market_value_sorted_list)) print(market_value_sorted_list) list_sum = sum(market_value_sorted_list) # 所有的总数 i = 0 print(list_sum) cumulative_sum = 0 # 累加的总数 for m in market_value_sorted_list: cumulative_sum += m i += 1 if cumulative_sum >= list_sum * 0.75 and ((cumulative_sum - m) < (list_sum * 0.75)): print(market_value_sorted_list[i - 1]) print(innercode_dict[market_value_sorted_list[i - 1]]) elif cumulative_sum >= list_sum * 0.9 and ((cumulative_sum - m) < (list_sum * 0.9)): print(market_value_sorted_list[i - 1]) print(innercode_dict[market_value_sorted_list[i - 1]])
def get_month_earnning(starttime, endtime): """根据开始和结束时间,从数据库中获取月度收益的list""" sql = "" sql_result = ConnectMysql().fetchall(sql=sql) print(sql_result) return sql_result
def test_chenk_value_weight(): """ 检查持仓分析中的股票市值权重占大盘小盘股权重的准确性 步骤:把这个基金的股票id找出来,然后通过这些股票id找出这些股票的这年的日均总股本市值,然后把循环这些查询出来的日均总股本市值和阈值做对比。 """ connect_mysql = ConnectMysql(host='202.104.102.166', user='******', password='******', port=3306) valuation_data = '2016-12-31' # stock_id_sql = "SELECT t.securities_code FROM rz_combination_master.cm_fund_position_detail t WHERE " \ # "t.fund_id = 'MF00003PWC' AND t.isvalid = 1 AND t.valuation_date = {} AND " \ # "t.userid = 864859 AND t.sec_type = 0 AND t.securities_code IS NOT NULL AND " \ # "t.sec_id IS NOT NULL".format(valuation_data) # 查找基金下的某个估值日的股票id stock_id_sql = "SELECT t.securities_code FROM rz_hfdb_core.fund_position_detail t LEFT JOIN " \ "rz_hfdb_core.base_underlying_information tt ON t.sec_id = tt.sec_id AND " \ "tt.isvalid = 1 AND t.isvalid = 1 WHERE t.fund_id = 'MF00003PWC' AND t.isvalid = 1 " \ "AND t.valuation_date = '{}' AND t.sec_id IS NOT NULL AND t.market_value IS NOT NULL " \ "AND t.sec_type = 0".format(valuation_data) # 查找基金下的某个估值日的股票id stock_sql_result = connect_mysql.fetchall(stock_id_sql) print(stock_sql_result) stock_id_list = [] for i in stock_sql_result: stock_id_list.append(i[0]) print(stock_id_list) print(len(stock_id_list)) valuation_year = valuation_data.split('-')[0] # 因为一年日均总股本市值的查找时间是为年 stock_value_sql = "select sm.SecuCode,my.avg_totalmv from jydb.SecuMain sm left join " \ "rz_combination_master.cm_jydb_stock_avg_market_year my on my.InnerCode = sm.InnerCode " \ "and my.trading_day = '{}' and my.isvalid = 1 where sm.SecuCode in {} and " \ "sm.SecuCategory = 1".format(valuation_year, tuple(stock_id_list)) # 查询每个个股的一年日均总股本市值: connect_mysql = ConnectMysql(host='202.104.102.166', user='******', password='******', port=3306) stock_value_sql_result = connect_mysql.fetchall(stock_value_sql) print(stock_value_sql_result) stock_value_list = [] # 个股日均总市值列表 large_cap_num = 0 # 大盘股数量 mid_cap_num = 0 # 中盘股数量 small_cap_num = 0 # 小盘股数量 stock_num = 0 # 基金对应股票总数量 for i in stock_value_sql_result: stock_num += 1 if i[1] is None: small_cap_num += 1 break else: stock_value_list.append(int(i[1])) print(stock_value_list) year_value_threshold_sql = "select cum_avg_totalmv_n1,cum_avg_totalmv_n2 from " \ "rz_combination_master.cm_stock_market_performance_year " \ "where trading_day = '{}' and isvalid = 1".format(valuation_year) # 大小盘阈值 connect_mysql = ConnectMysql(host='202.104.102.166', user='******', password='******', port=3306) year_value_threshold = connect_mysql.fetchall(year_value_threshold_sql) print(year_value_threshold) for i in stock_value_list: if i >= int(year_value_threshold[0][0]): large_cap_num += 1 elif i < int(year_value_threshold[0][1]): small_cap_num += 1 else: mid_cap_num += 1 print(valuation_data + '大盘股权重:', large_cap_num / stock_num) print(valuation_data + '小盘股权重:', small_cap_num / stock_num) print(valuation_data + '中盘股权重:', mid_cap_num / stock_num)