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)