黄金矿工

1
from tools.dbtools import *
2

3
# 计算相似度的行情区间
4
start_date = datetime(2020, 1, 1, tzinfo=pytz.utc)
5
end_date = datetime(2023, 1, 1, tzinfo=pytz.utc)
6
start_date_str= start_date.strftime("%Y-%m-%d")
7
end_date_str= end_date.strftime("%Y-%m-%d")
8

9
def low_correlation_set(symbols=[],max_nan_days = 100,min_price_var=5.0,similar_threshold = 0.5):
10
    # max_nan_days: 最大nan行情，不添加此筛选，会选出长期停牌标的
11
    # min_price_var: 过滤掉低方差标的，不添加此筛选，会选出小波动标的（大多为债基etf）
12
    # similar_threshold: 相似度阈值，高于此取值认为相似的
13

14
    choose_set = set() # 低相关度标的集合
15
    # 标的行情
16
    yfdata=MySQLData.download(symbols,start_dt=start_date_str,end_dt=end_date_str) # 自带工具类查询
17

18
    ohlcv = yfdata.concat()
19
    price = ohlcv['Close'] # 收盘价
20

21
    # 过滤掉停牌次数过多的标的
22
    price_nan=price.isna().sum()>=max_nan_days
23
    for key,value in price_nan.items():
24
        if value:
25
            price.drop(columns=key,inplace=True)
26

27
    # 过滤掉波动过小的标的
28
    price_var=price.var()<min_price_var
29
    for key,value in price_var.items():
30
        if value:
31
            price.drop(columns=key,inplace=True)
32

33
    # 相似度计算
34
    returns = price.pct_change()
35
    return_corr=returns.corr()
36
    corr_stack=return_corr.stack()
37
    corr_stack=corr_stack.sort_values() #相关度排序
38

39
    # 低相关度股票组合
40
    for idx in range(0,corr_stack.size,2): # 相似度矩阵corr是对称矩阵，stack后存在一半的重复记录，0-1或2-3其实对应同一个标的,类似pair(a,b)和(b,a)的关系，只取偶数避免重复计算
41
        pair_stock=corr_stack.index[idx]
42
        if corr_stack[idx]<similar_threshold:
43
            for stock in pair_stock:
44
                if stock in choose_set:
45
                    continue;
46
                isBreak=False
47
                for base_stock in choose_set:
48
                    if corr_stack[(base_stock,stock)]>similar_threshold:
49
                        isBreak=True # 说明和已经选中的某标的相似度过高
50
                        break
51
                if not isBreak: # 和选中的标的均不相似
52
                    choose_set.add(stock)
53
    return choose_set

1
def choose_stock_hs300():
2
    batch_size=50
3
    choose_set=set()
4
    for i in range(0,6):
5
        batch_start=i*batch_size
6
        batch_end=batch_start+batch_size
7

8
        # 本批次标的，只考虑股票
9
        sql_str ="select t.stock_code_market as code_market from jq_index_stocks t where t.index_code_market = '000300.XSHG' limit {},{};".format(batch_start,batch_size)
10

11
        df = pd.read_sql(sql_str, DButil.get_conn());
12
        symbols = df['code_market'].values
13
        print("limit {} - {} symbols:{}".format(batch_start,batch_end,symbols.size))
14

15
        choose_set=choose_set.union(low_correlation_set(symbols,min_price_var=10.0,similar_threshold=0.3))
16
        print("limit {} - {} choose_stock:{}".format(batch_start,batch_end,len(choose_set)))
17
    return choose_set

1
# 低相关度集合：沪深300
2
choose_set=choose_stock_hs300()   # 二次筛选，各个批次之间可能有高相似度的
3
second_choose_set=low_correlation_set(list(choose_set))
4
print("choose_set:{} choose_stock_hs300:{}".format(len(choose_set),len(second_choose_set)))
5
print(second_choose_set)
6
save_set_file(second_choose_set,"output/20231116_low_correlation_stock_hs300.txt")

1
# 可视化筛选出标的，观察是否存在显著问题
2
# todo筛选出的均为，存在净值剧烈变动（累计净值没变）的etf
3

4
# 验证是否真的不相似
5
import random
6
random_symbols = random.sample(second_choose_set, k=5) # 随机选择5个标的
7
print("random_symbols:",random_symbols)
8
yfdata=MySQLData.download(random_symbols,start_dt=start_date_str,end_dt=end_date_str) # 自带工具类查询
9

10
# 获取收盘价
11
ohlcv = yfdata.concat()
12
price = ohlcv['Close']
13

14
# 行情（收盘价）可视化
15
(price / price.iloc[0]).vbt.plot().show_svg()
16

17
# 相似度
18
returns = price.pct_change()
19
return_corr=returns.corr()
20
# print(returns.mean())
21
# print(returns.std())
22
print(return_corr)