from jqdatasdk import *
import pandas as pd
import pymysql
from sqlalchemy import create_engine
import threading
from datetime import datetime as dt
from jqdatasdk.technical_analysis import *
auth('18616891214', 'Ea?*7f68nD.dafcW34d!')
stocks = list(get_all_securities(['stock'], date=dt.today().strftime('%Y-%m-%d')).index)
# stocks = stocks[0:200]
start = dt.now()
for fre in ['1d']:
start = dt.now()
print(fre)
# 连接数据库
db = pymysql.connect(host='localhost',
user='root',
port=3307,
password='r6kEwqWU9!v3',
database='hlfx')
cursor = db.cursor()
cursor.execute("show tables like '%%%s%%' " % fre)
table_list = [tuple[0] for tuple in cursor.fetchall()]
print('取得 table_list %s' % fre)
db_pool = pymysql.connect(host='localhost',
user='root',
port=3307,
password='r6kEwqWU9!v3',
database='hlfx_pool')
cursor_pool = db_pool.cursor()
stk = locals()
thd = threading.local()
def hlfx(stocks, engine_stock, engine_hlfx):
for thd.stock in stocks:
print(thd.stock)
if ('stk%s_%s' % (thd.stock, fre)) in table_list:
# 有历史数据
index_len = pd.read_sql_table('stk%s_%s' % (thd.stock, fre), con=engine_hlfx).iloc[-1, 0]
startdate = pd.read_sql_table('stk%s_%s' % (thd.stock, fre), con=engine_hlfx).iloc[-1, 1]
thd.get_price = pd.read_sql_query(
'select date,open,close,high,low,volume,money from `stk%s_%s`' % (thd.stock, fre), engine_stock)
thd.get_price = thd.get_price.loc[thd.get_price['date'] > startdate]
thd.df_day = pd.read_sql_query(
'select date,open,close,high,low,volume,money,HL from `stk%s_%s`' % (thd.stock, fre), engine_hlfx)
# 先处理去包含
for i in thd.get_price.index:
# 不包含
if (thd.df_day.iloc[-1, 3] > thd.get_price.loc[i, 'high']
and thd.df_day.iloc[-1, 4] > thd.get_price.loc[i, 'low']) \
or (thd.df_day.iloc[-1, 3] < thd.get_price.loc[i, 'high']
and thd.df_day.iloc[-1, 4] < thd.get_price.loc[i, 'low']):
thd.df_day = pd.concat([thd.df_day, thd.get_price.loc[[i]]], ignore_index=True)
# 包含
else:
# (new_df.iloc[-1,3]>=df_day.loc[i,'high'] and new_df.iloc[-1,4]<= df_day.loc[i,'low']):
# 左高，下降
if thd.df_day.iloc[-2, 3] > thd.df_day.iloc[-1, 3]:
thd.df_day.iloc[-1, 3] = min(thd.df_day.iloc[-1, 3], thd.get_price.loc[i, 'high'])
thd.df_day.iloc[-1, 4] = min(thd.df_day.iloc[-1, 4], thd.get_price.loc[i, 'low'])
else:
# 右高，上升
thd.df_day.iloc[-1, 3] = max(thd.df_day.iloc[-1, 3], thd.get_price.loc[i, 'high'])
thd.df_day.iloc[-1, 4] = max(thd.df_day.iloc[-1, 4], thd.get_price.loc[i, 'low'])
# 寻找顶底分型
if len(thd.df_day.index) > 2:
for x in range(index_len, len(thd.df_day.index)):
m = x - 1
# 底
if ((thd.df_day.loc[x, 'high'] > thd.df_day.loc[x - 1, 'high']) and (
thd.df_day.loc[x - 2, 'high'] > thd.df_day.loc[x - 1, 'high'])):
# if ((stk.df_day.loc[i-2, 'date'] != stk.fxdf.iloc[-1,0]) and (stk.df_day.loc[i-3,'date'] != stk.fxdf.iloc[-1,0]) and (stk.df_day.loc[i-1,'date'] != stk.fxdf.iloc[-1,0])):
# stk.fxdf = pd.concat([stk.fxdf, stk.df_day.iloc[[i]]], ignore_index=True)
thd.df_day.loc[x, 'HL'] = 'L*'
while m:
if thd.df_day.loc[m, 'HL'] == 'H':
if (x - m) > 3:
thd.df_day.loc[x, 'HL'] = 'L'
# 此处可以获得MACD指标
# pre-macd_dif, pre-macd_dea, pre-macd_macd = MACD(thd.stock,check_date=thd.df_day.loc[m, 'datetime'], SHORT = 12, LONG = 26, MID = 9)
if x == len(thd.df_day.index) - 1 :
# pass
print(thd.stock, '$$$$$$$', '\n', thd.df_day.loc[x, 'date'], '\n', thd.df_day.loc[m, 'date'], '买买买买买！！')
results.append(thd.stock)
print('222')
# break
elif (thd.df_day.loc[m, 'HL'] == 'L'):
if thd.df_day.loc[x - 1, 'low'] < thd.df_day.loc[m - 1, 'low']:
# 前一个为底，且中间存在不包含 or 更低的底
thd.df_day.loc[x, 'HL'] = 'L'
x_macd_dif, x_macd_dea, x_macd_macd = MACD(thd.stock,
check_date=thd.df_day.loc[x, 'date'],
SHORT=12, LONG=26, MID=9, unit=fre)
m_macd_dif, m_macd_dea, m_macd_macd = MACD(thd.stock,
check_date=thd.df_day.loc[m, 'date'],
SHORT=12, LONG=26, MID=9, unit=fre)
if x == len(thd.df_day.index) - 1 and x_macd_dif[thd.stock] > m_macd_dif[thd.stock]:
# pass
# print(thd.df_day.loc[m, 'date'], thd.df_day.loc[m, 'low'],
# m_macd_dif[thd.stock])
# print(thd.df_day.loc[x, 'date'], thd.df_day.loc[x, 'low'],
# x_macd_dif[thd.stock])
print(thd.stock, '$$$$$$$', '\n', thd.df_day.loc[x, 'date'], 'MACD背驰————买吗？！')
results.append(thd.stock)
print('333')
break
else:
break
m = m - 1
if m == 0:
thd.df_day.loc[x, 'HL'] = 'L'
results.append(thd.stock)
print('444')
# 顶
elif ((thd.df_day.loc[x, 'high'] < thd.df_day.loc[x - 1, 'high']) and (
thd.df_day.loc[x - 2, 'high'] < thd.df_day.loc[x - 1, 'high'])):
# if ((stk.df_day.loc[i-2, 'date'] != stk.fxdf.iloc[-1,0]) and (stk.df_day.loc[i-3,'date'] != stk.fxdf.iloc[-1,0]) and (stk.df_day.loc[i-1,'date'] != stk.fxdf.iloc[-1,0])):
# stk.fxdf = pd.concat([stk.fxdf, stk.df_day.iloc[[i]]], ignore_index=True)
thd.df_day.loc[x, 'HL'] = 'H*'
while m:
if thd.df_day.loc[m, 'HL'] == 'L':
if x - m > 3:
thd.df_day.loc[x, 'HL'] = 'H'
if x == len(thd.df_day.index) - 1:
print(thd.stock, '!!!!!!!', '\n', '卖卖卖卖卖卖卖!')
# pass
results_short.append(thd.stock)
if thd.stock in results:
results.remove(thd.stock)
# break
elif (thd.df_day.loc[m, 'HL'] == 'H'):
if thd.df_day.loc[x - 1, 'high'] > thd.df_day.loc[m - 1, 'high']:
# 前一个为顶，且中间存在不包含 or 更高的顶
thd.df_day.loc[x, 'HL'] = 'H'
x_macd_dif, x_macd_dea, x_macd_macd = MACD(thd.stock,
check_date=thd.df_day.loc[x, 'date'],
SHORT=12, LONG=26, MID=9, unit=fre)
m_macd_dif, m_macd_dea, m_macd_macd = MACD(thd.stock,
check_date=thd.df_day.loc[m, 'date'],
SHORT=12, LONG=26, MID=9, unit=fre)
if x == len(thd.df_day.index) - 1 and x_macd_dif[thd.stock] < m_macd_dif[thd.stock]:
# pass
print(thd.stock, '/\/\/\/\/\/\/', '顶背离了！！！！')
results_short.append(thd.stock)
if thd.stock in results:
results.remove(thd.stock)
break
break
m = m - 1
if m == 0:
thd.df_day.loc[x, 'HL'] = 'H'
results_short.append(thd.stock)
if thd.stock in results:
results.remove(thd.stock)
else:
thd.df_day.loc[x, 'HL'] = '-'
# 更新数据库
# 可以使用normalize_code(code) 方法 改变代码格式
# thd.df_day[index_len + 1:].to_sql('stk%s_%s' % (thd.stock, fre), con=engine_hlfx, index=True, if_exists='append')
else:
# 没有历史数据表
thd.df_day = pd.DataFrame(columns=('date', 'open', 'close', 'high', 'low', 'volume', 'money', 'HL'))
thd.get_price = pd.read_sql_query(
'select date,open,close,high,low,volume,money from `stk%s_%s`' % (thd.stock, fre), engine_stock)
# 先处理去包含
for i in thd.get_price.index:
if i == 0 or i == 1:
thd.df_day = pd.concat([thd.df_day, thd.get_price.iloc[[i]]], ignore_index=True)
# 不包含
elif (thd.df_day.iloc[-1, 3] > thd.get_price.loc[i, 'high']
and thd.df_day.iloc[-1, 4] > thd.get_price.loc[i, 'low']) \
or (thd.df_day.iloc[-1, 3] < thd.get_price.loc[i, 'high']
and thd.df_day.iloc[-1, 4] < thd.get_price.loc[i, 'low']):
thd.df_day = pd.concat([thd.df_day, thd.get_price.loc[[i]]], ignore_index=True)
# 包含
else:
# 左高，下降
if thd.df_day.iloc[-2, 3] > thd.df_day.iloc[-1, 3]:
thd.df_day.iloc[-1, 3] = min(thd.df_day.iloc[-1, 3], thd.get_price.loc[i, 'high'])
thd.df_day.iloc[-1, 4] = min(thd.df_day.iloc[-1, 4], thd.get_price.loc[i, 'low'])
else:
# 右高，上升
thd.df_day.iloc[-1, 3] = max(thd.df_day.iloc[-1, 3], thd.get_price.loc[i, 'high'])
thd.df_day.iloc[-1, 4] = max(thd.df_day.iloc[-1, 4], thd.get_price.loc[i, 'low'])
if len(thd.df_day.index) > 2:
# 寻找顶底分型
for x in range(2, len(thd.df_day.index)):
m = x - 1
# 底
if ((thd.df_day.loc[x, 'high'] > thd.df_day.loc[x - 1, 'high']) and (
thd.df_day.loc[x - 2, 'high'] > thd.df_day.loc[x - 1, 'high'])):
# if ((stk.df_day.loc[i-2, 'date'] != stk.fxdf.iloc[-1,0]) and (stk.df_day.loc[i-3,'date'] != stk.fxdf.iloc[-1,0]) and (stk.df_day.loc[i-1,'date'] != stk.fxdf.iloc[-1,0])):
# stk.fxdf = pd.concat([stk.fxdf, stk.df_day.iloc[[i]]], ignore_index=True)
thd.df_day.loc[x, 'HL'] = 'L*'
while m:
if thd.df_day.loc[m, 'HL'] == 'H':
if (x - m) > 3:
thd.df_day.loc[x, 'HL'] = 'L'
# 此处可以获得MACD指标
# pre-macd_dif, pre-macd_dea, pre-macd_macd = MACD(thd.stock,check_date=thd.df_day.loc[m, 'datetime'], SHORT = 12, LONG = 26, MID = 9)
if x == len(thd.df_day.index) - 1:
# pass
print(thd.stock, '$$$$$$$', '\n', thd.df_day.loc[x, 'date'], '买买买买买！！')
results.append(thd.stock)
# break
elif (thd.df_day.loc[m, 'HL'] == 'L'):
if thd.df_day.loc[x - 1, 'low'] < thd.df_day.loc[m - 1, 'low']:
# 前一个为底，且中间存在不包含 or 更低的底
thd.df_day.loc[x, 'HL'] = 'L'
x_macd_dif, x_macd_dea, x_macd_macd = MACD(thd.stock,
check_date=thd.df_day.loc[x, 'date'],
SHORT=12, LONG=26, MID=9, unit=fre)
m_macd_dif, m_macd_dea, m_macd_macd = MACD(thd.stock,
check_date=thd.df_day.loc[m, 'date'],
SHORT=12, LONG=26, MID=9, unit=fre)
if x == len(thd.df_day.index) - 1 and x_macd_dif[thd.stock] > m_macd_dif[
thd.stock]:
# pass
# print(thd.df_day.loc[m, 'date'], thd.df_day.loc[m, 'low'],
# m_macd_dif[thd.stock])
# print(thd.df_day.loc[x, 'date'], thd.df_day.loc[x, 'low'],
# x_macd_dif[thd.stock])
print(thd.stock, '$$$$$$$', '\n', thd.df_day.loc[x, 'date'],
'MACD背驰————买吗？！')
results.append(thd.stock)
break
else:
break
m = m - 1
if m == 0:
thd.df_day.loc[x, 'HL'] = 'L'
results.append(thd.stock)
# 顶
elif ((thd.df_day.loc[x, 'high'] < thd.df_day.loc[x - 1, 'high']) and (
thd.df_day.loc[x - 2, 'high'] < thd.df_day.loc[x - 1, 'high'])):
# if ((stk.df_day.loc[i-2, 'date'] != stk.fxdf.iloc[-1,0]) and (stk.df_day.loc[i-3,'date'] != stk.fxdf.iloc[-1,0]) and (stk.df_day.loc[i-1,'date'] != stk.fxdf.iloc[-1,0])):
# stk.fxdf = pd.concat([stk.fxdf, stk.df_day.iloc[[i]]], ignore_index=True)
thd.df_day.loc[x, 'HL'] = 'H*'
while m:
if thd.df_day.loc[m, 'HL'] == 'L':
if x - m > 3:
thd.df_day.loc[x, 'HL'] = 'H'
if x == len(thd.df_day.index) - 1:
print(thd.stock, '!!!!!!!', '\n', '卖卖卖卖卖卖卖!')
# pass
results.remove(thd.stock)
# break
elif (thd.df_day.loc[m, 'HL'] == 'H'):
if thd.df_day.loc[x - 1, 'high'] > thd.df_day.loc[m - 1, 'high']:
# 前一个为顶，且中间存在不包含 or 更高的顶
thd.df_day.loc[x, 'HL'] = 'H'
x_macd_dif, x_macd_dea, x_macd_macd = MACD(thd.stock,
check_date=thd.df_day.loc[x, 'date'],
SHORT=12, LONG=26, MID=9, unit=fre)
m_macd_dif, m_macd_dea, m_macd_macd = MACD(thd.stock,
check_date=thd.df_day.loc[m, 'date'],
SHORT=12, LONG=26, MID=9, unit=fre)
if x == len(thd.df_day.index) - 1 and x_macd_dif[thd.stock] < m_macd_dif[
thd.stock]:
# pass
print(thd.stock, '/\/\/\/\/\/\/', '顶背离了！！！！')
results.remove(thd.stock)
break
break
m = m - 1
if m == 0:
thd.df_day.loc[x, 'HL'] = 'H'
results.remove(thd.stock)
else:
thd.df_day.loc[x, 'HL'] = '-'
print(thd.df_day[-20:])
# 更新数据库
# thd.df_day.to_sql('stk%s_%s' % (thd.stock, fre), con=engine_hlfx, index=True, if_exists='append')
step = 500
thread_list = []
engine_stock = []
engine_hlfx = []
times_engine = 0
engine_hlfx_pool = create_engine('mysql+pymysql://root:r6kEwqWU9!v3@localhost:3307/hlfx_pool?charset=utf8')
results= pd.read_sql_query(
'select value from `%s`' % fre, engine_hlfx_pool)
results = results.iloc[-1, 0]
results = results.split(",")
results_short=[]
print('数据库读取', results, type(results))
# for i in range(0, len(stocks), step):
# engine_stock.append(create_engine('mysql+pymysql://root:r6kEwqWU9!v3@localhost:3307/stocks?charset=utf8'))
# engine_hlfx.append(create_engine('mysql+pymysql://root:r6kEwqWU9!v3@localhost:3307/hlfx?charset=utf8'))
# thread = threading.Thread(target=hlfx, args=(stocks[i:i + step], engine_stock[times_engine], engine_hlfx[times_engine]))
# times_engine = times_engine + 1
# thread.start()
# thread_list.append(thread)
#
# for thread in thread_list:
# thread.join()
# db.close()
time = dt.now().strftime('%Y-%m-%d %H:%M:%S')
results_list =','.join(set(results))
print(set(results))
# sql = "INSERT INTO %s (date,value) VALUES('%s','%s')" % (fre, dt.now().strftime('%Y-%m-%d %H:%M:%S'), results_list)
# cursor_pool.execute(sql)
# db_pool.commit()
print('做多', set(results))
print('做空', set(results_short))
print(len(set(results)))
print(len(set(results_short)))
end= dt.now()
print('总时长：', (end - start).seconds)