def buy(code, threadName=None):
# loadDataSql = "select * " \
# "from stock_daily_macd_deviate d " \
# "left join stock_daily_data sd on sd.sCode = d.sCode " \
# "where d.iDirectionType=2 " \
# "and d.sCode='" + code + "' " \
# "and d.tDeviateDateTime>='2008-01-01' " \
# "and sd.iOpeningPrice is not null " \
# "group by d.tDeviateDateTime "
# # " and tApexDateTime='2017-04-11'"
engine = sql_model.get_conn()
loadDataSql = "SELECT d.id, d.sCode, d.tDeviateDateTime FROM stock_daily_macd_deviate d LEFT JOIN stock_daily_macd_deviate_buy buy ON buy.iDeviateId = d.id " \
"WHERE d.sCode = '" + code + "' AND d.iDirectionType=2 AND buy.id IS NULL"
# print(loadDataSql)
df = pd.read_sql(loadDataSql, engine)
if len(df) < 1:
return
# exit()
# row_array = sql_model.getAll(loadDataSql)
# for a in row_array:
# print(a)
# exit()
# 最终结果列表
dataList = pd.DataFrame(columns=['sCode', 'iDeviateId', 'tDateTime'])
for index, row in df.iterrows():
tDeviateDateTime = str(row['tDeviateDateTime'])
#确认买入点
loadDataSql = "select m.tDateTime, d.iOpeningPrice, d.iClosingPrice " \
"from stock_daily_macd m " \
"left join stock_daily_data d on m.tDateTime=d.tDateTime and m.sCode=d.sCode " \
"where m.tDateTime > '" + tDeviateDateTime +"' " \
"and m.iBar > 0 " \
"and m.sCode='" + code + "' " \
"and d.iOpeningPrice is not null " \
"limit 1"
# print(loadDataSql)
buy_df = pd.read_sql(loadDataSql, engine)
_dataList = pd.DataFrame(
[[code, str(row['id']), buy_df['tDateTime'][0]]],
columns=['sCode', 'iDeviateId', 'tDateTime'])
dataList = dataList.append(_dataList)
result = sql_model.loadData('stock_daily_macd_deviate_buy',
dataList.keys(), dataList.values, threadName)
print(result)
def reset_codelist_ssdb(id=None):
c = ssdb_client()
if id:
_key = key + "_" + id
else:
_key = key
c.qclear(_key)
engine = sql_model.get_conn()
sql = "select * from stock_basics order by code asc"
df = pd.read_sql(sql, engine)
code_list = df['code']
num = 0
for s in code_list:
c.qpush_back(_key, s)
# print(s)
num += 1
print(num)
def reset_codelist_redis(id=None):
c = redis_client()
if id:
_key = key + "_" + id
else:
_key = key
c.delete(_key)
engine = sql_model.get_conn()
sql = "select * from stock_basics order by code asc"
# sql = "select * from stock_basics where code > '600000' order by code asc"
df = pd.read_sql(sql, engine)
code_list = df['code']
num = 0
for s in code_list:
c.lpush(_key, s)
# print(s)
num += 1
print(_key)
return num
print(num)
df.at[index, 'iDea'] = row['iDif']
if index > 0:
# 今日DEA（MACD）=前一日DEA×8 / 10＋今日DIF×2 / 10
df.at[index, 'iDea'] = df.ix[index - 1, 'iDea'] * (M - 1) / (
M + 1) + row['iDif'] * 2 / (M + 1)
# BAR=2×(DIF－DEA)
end = time.time()
totle_time += end - start
print(totle_time)
df['iBar'] = 2 * (df['iDif'] - df['iDea'])
return df
# 获取所有股票
engine = sql_model.get_conn()
sql = "select * from stock_basics order by code asc"
# sql = "select * from stock_basics where code = '002030' order by code asc"
df = pd.read_sql(sql, engine)
code_list = df['code']
macd_data = pd.DataFrame()
for s in code_list:
print(s + " begin ")
# macd_data = get_macd(s)
conn = sql_model.get_conn()
# 原代码
# loadDataSql = "select * from stock_daily_data where sCode='" + s + "' order by tDateTime"
# 修改开始
def get_h_data(code):
# engine = sql_model.get_conn()
# stock_data = ts.get_h_data(code, start="2017-01-01", end="2017-01-05", autype='hfq')
# stock_data['sCode'] = code
# stock_data['tDateTime'] = stock_data.index
# stock_data.rename(columns={'open': 'iOpeningPrice', 'high': 'iMaximumPrice', 'close': 'iClosingPrice',
# 'low': 'iMinimumPrice', 'volume': 'iVolume', 'amount': 'iAmount'}, inplace=True)
# stock_data = common.get_average_line('600077', stock_data)
# stock_data2 = stock_data.sort_index(ascending=True)
# stock_data2.to_sql('stock_daily_data', engine, if_exists='append', index=False)
# debug.p(stock_data2)
# start_year = 1991
# start_year = 1990
# 从这个股票已有数据的最后一个日期开始获取
engine = sql_model.get_conn()
sql = "select * from stock_daily_data where sCode = " + code + " order by tDateTime desc limit 1"
print(sql)
df = pd.read_sql(sql, engine)
if not df.empty:
start_date = df.loc[0, ['tDateTime']].values[0] + datetime.timedelta(
days=1)
start_time = datetime.datetime.strptime(str(start_date), "%Y-%m-%d")
# s = start_datetime.strftime("%Y-%m-%d")
date_str = "2016-11-30 13:53:59"
# datetime.datetime.strptime(date_str, "%Y-%m-%d %H:%M:%S")
# start_year = t.year
else:
start_date = "1990-01-01"
start_time = datetime.datetime.strptime(str(start_date), "%Y-%m-%d")
while start_time < datetime.datetime.now():
try:
# fh = open("testfile", "w")
# fh.write("这是一个测试文件，用于测试异常!!")
# fh.close()
# if start_time >= datetime.datetime.now():
# continue
end_time = start_time + datetime.timedelta(days=365)
# end = str(end_year) + "-01-01"
# debug.p(start_time.year)
print(code, start_time, end_time)
stock_data = ts.get_h_data(code,
start=str(start_time),
end=str(end_time),
autype='hfq')
stock_data['sCode'] = code
stock_data['tDateTime'] = stock_data.index
stock_data2 = stock_data.sort_index(ascending=True)
stock_data2.rename(columns={
'open': 'iOpeningPrice',
'high': 'iMaximumPrice',
'close': 'iClosingPrice',
'low': 'iMinimumPrice',
'volume': 'iVolume',
'amount': 'iAmount'
},
inplace=True)
# 存入数据库
stock_data2.to_sql('stock_daily_data',
engine,
if_exists='append',
index=False)
except IOError:
traceback.print_exc()
# print("IOError等待60秒")
# time.sleep(60)
proxy_address = requests.get("http://112.124.4.247:5010/get/").text
print("更换代理" + proxy_address)
# 请求接口获取数据
proxy = {
# 'http': '106.46.136.112:808'
# 'https': "https://112.112.236.145:9999",
"http": proxy_address
}
print(proxy)
# 创建ProxyHandler
proxy_support = ProxyHandler(proxy)
# 创建Opener
opener = build_opener(proxy_support)
# 安装OPener
install_opener(opener)
else:
print(start_time, end_time, "成功")
start_time = end_time + datetime.timedelta(days=1)
def get_stock_basics_list():
engine = sql_model.get_conn()
sql = "select * from stock_basics"
df = pd.read_sql(sql, engine)
return df
def get_h_data(code, threadName):
# engine = sql_model.get_conn()
# stock_data = ts.get_h_data(code, start="2017-01-01", end="2017-01-05", autype='hfq')
# stock_data['sCode'] = code
# stock_data['tDateTime'] = stock_data.index
# stock_data.rename(columns={'open': 'iOpeningPrice', 'high': 'iMaximumPrice', 'close': 'iClosingPrice',
# 'low': 'iMinimumPrice', 'volume': 'iVolume', 'amount': 'iAmount'}, inplace=True)
# stock_data = common.get_average_line('600077', stock_data)
# stock_data2 = stock_data.sort_index(ascending=True)
# stock_data2.to_sql('stock_daily_data', engine, if_exists='append', index=False)
# debug.p(stock_data2)
# start_year = 1991
# start_year = 1990
# 从这个股票已有数据的最后一个日期开始获取
engine = sql_model.get_conn()
sql = "select * from stock_daily_data where sCode = '" + code + "' order by tDateTime desc limit 1"
print("%s %s" % (threadName, sql))
df = pd.read_sql(sql, engine)
if not df.empty:
start_date = df.loc[0, ['tDateTime']].values[0] + datetime.timedelta(
days=1)
# s = start_datetime.strftime("%Y-%m-%d")
date_str = "2016-11-30 13:53:59"
# datetime.datetime.strptime(date_str, "%Y-%m-%d %H:%M:%S")
# start_year = t.year
else:
start_date = "1990-01-01"
# 日期字符串转换为日期格式
start_time = datetime.datetime.strptime(str(start_date), "%Y-%m-%d")
# 判断开始时间如果是礼拜六, 则加两天
weekday = start_time.weekday()
if weekday == 5:
start_time = start_time + datetime.timedelta(days=2)
if start_time.strftime("%Y-%m-%d") == datetime.datetime.now().strftime(
"%Y-%m-%d") and datetime.datetime.now().hour < 15:
print("\n")
print("%s %s %s 无需更新" % (threadName, code, start_time))
return 1
conn = ts.get_apis()
while start_time < datetime.datetime.now():
try:
# fh = open("testfile", "w")
# fh.write("这是一个测试文件，用于测试异常!!")
# fh.close()
# if start_time >= datetime.datetime.now():
# continue
end_time = start_time + datetime.timedelta(days=365)
# end = str(end_year) + "-01-01"
# debug.p(start_time.year)
print("%s %s %s %s 开始" % (threadName, code, start_time, end_time))
stock_data = ts.bar(code,
conn,
adj="hfq",
start_date=str(start_time),
end_date=str(end_time),
factors=['tor'])
# p(start_time)
# stock_data = ts.get_h_data(code, start=str(start_time), end=str(end_time), autype='hfq')
# stock_data = ts.get_h_data('000007', start="2018-06-13", end="2018-07-12", autype='hfq')
# print(stock_data)
# stock_data = ts.bar('000010', conn, adj="hfq", start_date="2018-06-14", end_date="2018-06-14",
# factors=['vr', 'tor'])
# p(stock_data)
# exit("dsfds")
# p(stock_data )
# 下市了的股 df的值是None。 没下市只是当时没数据的股 df的值空的dataframe
if stock_data is None:
return False
del stock_data['p_change']
stock_data['tDateTime'] = stock_data.index
stock_data2 = stock_data.sort_index(ascending=True)
# stock_data['sCode'] = code
# stock_data2.rename(columns={'open': 'iOpeningPrice', 'high': 'iMaximumPrice', 'close': 'iClosingPrice',
# 'low': 'iMinimumPrice', 'volume': 'iVolume', 'amount': 'iAmount'}, inplace=True)
stock_data2.rename(columns={
'open': 'iOpeningPrice',
'high': 'iMaximumPrice',
'close': 'iClosingPrice',
'low': 'iMinimumPrice',
'vol': 'iVolume',
'amount': 'iAmount',
'code': 'sCode',
'tor': 'iTurnoverRate'
},
inplace=True)
# p(stock_data2.iloc[1]['iAmount'] > 0)
# p(stock_data2.iloc[1]['iAmount'] > 0)
stock_data2 = stock_data2[stock_data2.iVolume > 1]
# stock_data2['iVolume'] = '{:.5f}'.format(stock_data2['iVolume'])
# p(stock_data2)
# 存入数据库
tosql_res = stock_data2.to_sql('stock_daily_data',
engine,
if_exists='append',
index=False)
if tosql_res:
common.file_write("tosql_" + threadName, tosql_res)
print("%s %s %s" % (threadName, __name__, str(tosql_res)))
except IOError:
conn = ts.get_apis()
except TypeError:
conn = ts.get_apis()
# traceback.print_exc()
# # print("IOError等待60秒")
# # time.sleep(60)
# proxy_address = requests.get("http://112.124.4.247:5010/get/").text
# print("%s 更换代理 %s" % (threadName, proxy_address))
#
# # 请求接口获取数据
# proxy = {
# # 'http': '106.46.136.112:808'
# # 'https': "https://112.112.236.145:9999",
# "http": proxy_address
# }
# print(proxy)
# # 创建ProxyHandler
# proxy_support = ProxyHandler(proxy)
# # 创建Opener
# opener = build_opener(proxy_support)
# # 安装OPener
# install_opener(opener)
else:
print("\n")
print("%s %s %s %s 成功" % (threadName, code, start_time, end_time))
start_time = end_time + datetime.timedelta(days=1)
def get_h_week_data(code, threadName, conn):
# 从这个股票已有数据的最后一个日期开始获取
engine = sql_model.get_conn()
sql = "select * from stock_weekly_data where sCode = '" + code + "' order by tDateTime desc limit 1"
print("%s %s" % (threadName, sql))
df = pd.read_sql(sql, engine)
if not df.empty:
start_date = df.loc[0, ['tDateTime']].values[0] + datetime.timedelta(
days=1)
start_time = datetime.datetime.strptime(str(start_date), "%Y-%m-%d")
else:
start_date = "1990-01-01"
start_time = datetime.datetime.strptime(str(start_date), "%Y-%m-%d")
while start_time < datetime.datetime.now():
try:
end_time = start_time + datetime.timedelta(days=365)
print("%s %s %s %s 开始" % (threadName, code, start_time, end_time))
stock_data = ts.bar(code,
conn=conn,
freq='W',
start_date=str(start_time),
end_date=str(end_time),
adj='hfq')
# stock_data = ts.get_h_data(code, start=str(start_time), end=str(end_time), autype='hfq')
# stock_data['sCode'] = code
stock_data['tDateTime'] = stock_data.index
stock_data2 = stock_data.sort_index(ascending=True)
stock_data2.rename(columns={
'code': 'sCode',
'open': 'iOpeningPrice',
'high': 'iMaximumPrice',
'close': 'iClosingPrice',
'low': 'iMinimumPrice',
'vol': 'iVolume',
'amount': 'iAmount'
},
inplace=True)
# 存入数据库
tosql_res = None
if len(stock_data2) > 1:
tosql_res = stock_data2.to_sql('stock_weekly_data',
engine,
if_exists='append',
index=False)
if tosql_res:
common.file_write("tosql_" + threadName, tosql_res)
print("%s %s %s" % (threadName, __name__, str(tosql_res)))
except IOError:
traceback.print_exc()
# print("IOError等待60秒")
# time.sleep(60)
proxy_address = requests.get("http://112.124.4.247:5010/get/").text
print("%s 更换代理 %s" % (threadName, proxy_address))
# 请求接口获取数据
proxy = {
# 'http': '106.46.136.112:808'
# 'https': "https://112.112.236.145:9999",
"http": proxy_address
}
print(proxy)
# 创建ProxyHandler
proxy_support = ProxyHandler(proxy)
# 创建Opener
opener = build_opener(proxy_support)
# 安装OPener
install_opener(opener)
else:
print("\n")
print("%s %s %s %s 成功" % (threadName, code, start_time, end_time))
start_time = end_time + datetime.timedelta(days=1)
def get_macd_deviate(code):
# 获取所有顶点
loadDataSql = "select a.sCode, a.tApexDateTime, a.iApexDif, d.iMinimumPrice, a.iDirectionType" \
" from stock_daily_macd_apex a" \
" left join stock_daily_data d on a.tApexDateTime=d.tDateTime and a.sCode=d.sCode" \
" where a.sCode='" + code + "' " \
" and a.iDirectionType=2" \
# " and tApexDateTime='2014-03-03'"
print(loadDataSql)
conn = sql_model.get_conn()
df = pd.read_sql(loadDataSql, conn)
df['tApexDateTime'] = pd.to_datetime(df['tApexDateTime'])
print("select ok")
# row_array = sql_model.getAll(loadDataSql)
# 获取该股票历史数据
loadDataSql = "select * from stock_daily_data where sCode='" + code + "' "
print(loadDataSql)
h_data_df = pd.read_sql(loadDataSql, conn)
h_data_df['tDateTime'] = pd.to_datetime(h_data_df['tDateTime'])
# 最终结果列表
data_list = []
# 记录背离数据
deviate = {}
macd_data_deviate = pd.DataFrame()
loadDataSql = "select * from stock_daily_macd_deviate where sCode='" + code + "' order by tDeviateDateTime desc limit 1"
print(loadDataSql)
newest_df = pd.read_sql(loadDataSql, conn)
if len(newest_df) > 0:
newest_df['tDeviateDateTime'] = pd.to_datetime(
newest_df['tDeviateDateTime'])
offset = df[df['tApexDateTime'] == newest_df.iloc[0]
['tDeviateDateTime']].index[0]
source_df = df.iloc[offset + 1:]
else:
source_df = df
# for row in df.values:
for index, row in source_df.iterrows():
start = time.time()
sCode = str(row['sCode'])
tThisApexDateTime = row['tApexDateTime']
iThisApexDif = row['iApexDif']
iThisMinimumPrice = row['iMinimumPrice']
iThisDirectionType = row['iDirectionType']
# print(tThisApexDateTime)
# 拿出9天内的最低点
# print("sdf1")
# withinDaysKLineApex = common.getWithinDaysKLineApex(sCode, tThisApexDateTime, iThisDirectionType, 9)
withinDaysKLineApex = getWithinDaysKLineApex(h_data_df,
tThisApexDateTime,
iThisDirectionType)
# end = time.time()
# print("withinDaysKLineApex" + str(end - start))
# print("sdf1")
if withinDaysKLineApex is None:
continue
withinDaysKLineApexDate = withinDaysKLineApex['tDateTime']
# 筛选第一步 先初步判断这个点是不是一个相对的一个顶点
# print("sdf2")
# beforDaysKLineApex = common.getBeforDaysKLineApex(sCode, tThisApexDateTime, iThisDirectionType, 40)
beforDaysKLineApex = getBeforDaysKLineApex(h_data_df,
tThisApexDateTime,
iThisDirectionType, 40)
# end = time.time()
# print("beforDaysKLineApex" + str(end - start))
# print("sdf2")
if len(beforDaysKLineApex) < 1:
continue
beforDaysKLineApexDate = beforDaysKLineApex['tDateTime']
# 如果40天内的顶点的日期 > (当前日期前后5天内的顶点)的日期。 那么这个点可能是个背离点
# if common.strtotime(beforDaysKLineApexDate) > common.strtotime(withinDaysKLineApexDate):
if beforDaysKLineApexDate > withinDaysKLineApexDate:
continue
# print(withinDaysKLineApexDate, beforDaysKLineApexDate)
# 筛选第二步 获取 从前10个macd顶点里找 符合条件的顶点
# 条件:
# 1、当前日期dif > 历史日期dif (底背离)
# 2、当前日期最低价 < 历史日期最低价 (底背离)
# 注:顶背离相反
# print("sdf3")
# beforMacdApexArray = common.getBeforeMacdApex(sCode, tThisApexDateTime, iThisApexDif, iThisMinimumPrice,
# iThisDirectionType, 10)
# 根据macd数据,提取背离起始点
beforMacdApexDf = getBeforeMacdApex(df, tThisApexDateTime,
iThisDirectionType, 10)
# end = time.time()
# print("beforMacdApexDf" + str(end - start))
# print("sdf3")
if len(beforMacdApexDf) < 1:
continue
# tmpBeginApex = []
# print(beforMacdApexArray)
# 筛选第三步 排除早期dif值较低的情况(顶背离相反)
beforeApexApex = 0
# for beforMacdApex in beforMacdApexDf:
for index, row in beforMacdApexDf.iterrows():
beginDate = str(row['tApexDateTime'])
beginDif = row['iApexDif']
# 如果起始点的最低价比当前最低价高则排除
beginKLineApex = getWithinDaysKLineApex(h_data_df, beginDate,
iThisDirectionType)
if beginKLineApex['iMinimumPrice'] <= withinDaysKLineApex[
'iMinimumPrice']:
continue
# k线起始点的x y
begin_kline_df = h_data_df[h_data_df['tDateTime'] ==
beginKLineApex['tDateTime']]
x1 = begin_kline_df.index[0]
y1 = begin_kline_df.iloc[0]['iMinimumPrice']
# k线结束点的x y
end_kline_df = h_data_df[h_data_df['tDateTime'] ==
withinDaysKLineApexDate]
x2 = end_kline_df.index[0]
y2 = end_kline_df.iloc[0]['iMinimumPrice']
fc = equation_of_line(x1, y1, x2, y2)
a = fc[0]
b = fc[1]
# 越界的次数
ctb = 0
# 根据直线方程,找出能够穿过直线的点
for index2, row2 in h_data_df[x1 + 1:x2].iterrows():
x = index2
y = row2['iMinimumPrice']
yline = a * x + b
if yline - y > 0:
ctb += 1
# 超过3次则不算做背离
# 超过3次 则判断不是背离点
if ctb > 3:
# debug.p(row2)
continue
# 满足条件 属于背离
data_list.append({
'sCode': sCode,
'tBeginDateTime': beginDate,
'iBeginDif': str(beginDif),
'tDeviateDateTime': tThisApexDateTime,
'iDeviateDif': str(iThisApexDif),
'iDirectionType': str(iThisDirectionType),
})
# end = time.time()
# print("data_list" + str(end - start))
#
# # 符合筛选第三步的进入
# # if (beforeApexApex == 0 or beforeApexApex > beginDif) and iThisApexDif - beginDif > 0.3:
# if beforeApexApex == 0 or beforeApexApex > beginDif:
# beforeApexApex = beginDif
#
# # 筛选第四步 判断当前最低价日期是否是整个周期(历史macd低点的前后5日内最低价格的日期-->当前macd低点的前后5日内最低价格的日期)内最低的日期
# # print("sdf4")
# # beginWithinDaysKLineApex = common.getWithinDaysKLineApex(sCode, beginDate, iThisDirectionType)
# beginWithinDaysKLineApex = getBeforDaysKLineApex(h_data_df, beginDate, iThisDirectionType, 5)
#
# end = time.time()
# print("beginWithinDaysKLineApex" + str(end - start))
# # print("sdf4")
# if beginWithinDaysKLineApex is None:
# continue
# # beginWithinDaysKLineDate: 开始的日期
# # withinDaysKLineApexDate: 当前K线顶点的日期
#
# beginWithinDaysKLineDate = beginWithinDaysKLineApex[2]
# # print("sdf4")
# print(beginWithinDaysKLineDate)
# withinApex = common.getKLineApexByTime(sCode, beginWithinDaysKLineDate, withinDaysKLineApexDate,
# iThisDirectionType)
# debug.p(withinApex)
# # print("sdf5")
# # 顶点日期
# withinApexDate = withinApex[2]
# # print(withinDaysKLineApexDate, withinApexDate)
# # print(withinDaysKLineApexDate == withinApexDate)
#
# if withinDaysKLineApexDate == withinApexDate:
# # 满足条件 属于背离
# data_list.append({
# 'sCode': sCode,
# 'tBeginDateTime': beginDate,
# 'iBeginDif': str(beginDif),
# 'tDeviateDateTime': tThisApexDateTime,
# 'iDeviateDif': str(iThisApexDif),
# 'iDirectionType': str(iThisDirectionType),
# })
# # dataList.append([sCode, beginDate, str(beginDif), tThisApexDateTime, str(iThisApexDif), str(iThisDirectionType)])
end = time.time()
print("end " + str(end - start), tThisApexDateTime)
# debug.p(data_list)
if len(data_list) > 0:
macd_data_deviate = macd_data_deviate.append(data_list,
ignore_index=True)
return macd_data_deviate
def get_macd(code):
#快速平滑移动平均线EMA1的参数(日)
short = 12
#慢速平滑移动平均线EMA1的参数(日)
long = 26
#DIF的参数(日)
m = 9
loadDataSql = "select * from stock_daily_data where sCode='" + code + "' order by tDateTime"
print(loadDataSql)
conn = sql_model.get_conn()
df = pd.read_sql(loadDataSql, conn)
# 最终结果列表
dataList = []
# 前一天的内容
before_data = []
macd_data = pd.DataFrame()
# df = df[['tDateTime', 'iOpeningPrice']]
# df = df.set_index(['id'])
# debug.p(df.head())
for index, row in df.iterrows():
id = index
sCode = str(row['sCode'])
tDateTime = str(row['tDateTime'])
iClosingPrice = float(row['iClosingPrice'])
if len(before_data):
beforeEmaShort = float(before_data['iEmaShort'])
beforeEmaLong = float(before_data['iEmaLong'])
beforeDea = float(before_data['iDea'])
else:
beforeEmaShort = iClosingPrice
beforeEmaLong = iClosingPrice
beforeDea = 0
# # 查询前一天的macd信息
# getBeforeMacdSql = "select * from stock_daily_macd where sCode='" + sCode + "' ORDER BY tDateTime Desc limit 1"
# before_df = pd.read_sql(getBeforeMacdSql, conn)
# # cursorSub = conn.cursor()
# # effect_row = cursorSub.execute(getBeforeMacdSql)
# if not before_df.empty:
# # if effect_row != 0:
# # subRow = cursorSub.fetchone()
# subRow = before_df.head(1)
# beforeEmaShort = float(subRow['iEmaShort'])
# beforeEmaLong = float(subRow['iEmaLong'])
# beforeDea = float(subRow['iDea'])
# print(beforeEmaShort, beforeEmaLong, beforeDea)
# EMA（12）=前一日EMA（12）×11/13＋今日收盘价×2/13
emaShort = round(
beforeEmaShort * (short - 1) / (short + 1) + iClosingPrice * 2 /
(short + 1), 3)
# EMA（26）=前一日EMA（26）×25/27＋今日收盘价×2/27
emaLong = round(
beforeEmaLong * (long - 1) / (long + 1) + iClosingPrice * 2 /
(long + 1), 3)
# DIF=今日EMA（12）－今日EMA（26）
dif = round(emaShort - emaLong, 3)
# 今日DEA（MACD）=前一日DEA×8 / 10＋今日DIF×2 / 10
dea = round(beforeDea * (m - 1) / (m + 1) + dif * 2 / (m + 1), 3)
# BAR=2×(DIF－DEA)
bar = round(2 * (dif - dea), 3)
# beforeData = [sCode, tDateTime, str(emaShort), str(emaLong), str(dif), str(dea), str(bar)]
before_data = {
'sCode': sCode,
'tDateTime': tDateTime,
'iEmaShort': str(emaShort),
'iEmaLong': str(emaLong),
'iDif': str(dif),
'iDea': str(dea),
'iBar': str(bar),
}
df.at[id, 'iEmaShort'] = emaShort
df.at[id, 'iEmaLong'] = emaLong
df.at[id, 'iDif'] = dif
df.at[id, 'iDea'] = dea
df.at[id, 'iBar'] = bar
# print(before_data)
# row = pd.DataFrame(before_data, index=[0])
# print(end - start)
# print(totle_time)
# # debug.p(row)
# macd_data = macd_data.append(row, ignore_index=True)
# debug.p(macd_data)
# dataList.append(beforeData)
# df = df[['sCode', 'tDateTime', 'iEmaShort', 'iEmaLong', 'iDif', 'iDea', 'iBar']]
print(totle_time)
return df
def get_mack_apex(code):
# 打开数据库连接
conn = pymysql.connect(host='112.124.4.247',
port=3306,
user='******',
passwd='gedongSql@123',
db='stock',
charset='utf8')
# 创建游标
cursor = conn.cursor()
# 执行SQL，并返回收影响行数
# loadDataSql = "load data local infile '" + mysqlFilePath + "' ignore into table stock_daily_data;"
loadDataSql = "select sCode, tDateTime, iDif, iBar from stock_daily_macd where sCode='" + code + "' order by tDateTime"
# loadDataSql = "select sCode, tDateTime, iDif, iBar from stock_daily_macd where sCode='" + code + "' and tDateTime > '1993-04-20' and tDateTime < '1993-08-01' order by tDateTime"
print(loadDataSql)
conn = sql_model.get_conn()
df = pd.read_sql(loadDataSql, conn)
row_array = sql_model.getAll(loadDataSql)
# 最终结果列表
data_list = []
# 斜率系数
slope = 0.05
# 记录顶点数据
apex = {}
macd_data_apex = pd.DataFrame()
totle_time = 0
# for row in df.values:
for index, row in df.iterrows():
start = time.time()
sCode = str(row['sCode'])
tDateTime = str(row['tDateTime'])
thisDif = row['iDif']
thisBar = row['iBar']
# 是否有顶点数据
if 'tApexDateTime' in apex.keys():
# 计算斜率k = (y2 - y1) / (x2 - x1) 由于我们是按天算的分母肯定是1 这里乘以8确保和y轴相对平衡
direction = apex['iDirectionType']
# 当前dif - 顶点dif
k = thisDif - apex['iApexDif']
# 当 在非上升趋势中,出现当前dif比最低dif高了一个系数则认为是改变了向上
if k > slope and direction != 1 and thisBar > 0:
# data_list = {
data_list.append({
'sCode': sCode,
'tBeginDateTime': apex['tBeginDateTime'],
'iBeginDif': str(apex['iBeginDif']),
'tApexDateTime': str(apex['tApexDateTime']),
'iApexDif': str(apex['iApexDif']),
'tEndDateTime': tDateTime,
'iEndDif': str(thisDif),
'iDirectionType': str(2),
# }
})
# debug.p(pd.Series(data_list))
# macd_data_apex = macd_data_apex.append(data_list, ignore_index=True)
# debug.p(macd_data_apex)
# dataList.append([sCode, apex['tBeginDateTime'], str(apex['iBeginDif']), apex['tApexDateTime'], str(apex['iApexDif']), tDateTime, str(thisDif), str(2)])
apex['iBeginDif'] = thisDif
apex['tBeginDateTime'] = tDateTime
apex['iApexDif'] = thisDif
apex['tApexDateTime'] = tDateTime
apex['iDirectionType'] = 1
# 当 在非下降趋势中,出现当前dif比最低dif低了一个系数则认为是改变成了下降
elif k < slope * -1 and direction != 2 and thisBar < 0:
data_list.append({
'sCode': sCode,
'tBeginDateTime': apex['tBeginDateTime'],
'iBeginDif': str(apex['iBeginDif']),
'tApexDateTime': str(apex['tApexDateTime']),
'iApexDif': str(apex['iApexDif']),
'tEndDateTime': tDateTime,
'iEndDif': str(thisDif),
'iDirectionType': str(1),
})
# macd_data_apex = macd_data_apex.append(data_list, ignore_index=True)
# dataList.append([sCode, apex['tBeginDateTime'], str(apex['iBeginDif']), apex['tApexDateTime'], str(apex['iApexDif']), tDateTime, str(thisDif), str(1)])
apex['iBeginDif'] = thisDif
apex['tBeginDateTime'] = tDateTime
apex['iApexDif'] = thisDif
apex['tApexDateTime'] = tDateTime
apex['iDirectionType'] = 2
# 当 在上升趋势中, 当前dif比最高dif还要高则替换
if direction == 1 and apex['iApexDif'] <= thisDif:
apex['iApexDif'] = thisDif
apex['tApexDateTime'] = tDateTime
# 当 在下降趋势中, 当前dif比最低dif还要低则替换
elif direction == 2 and apex['iApexDif'] >= thisDif:
apex['iApexDif'] = thisDif
apex['tApexDateTime'] = tDateTime
# 初始数据
else:
apex['iBeginDif'] = thisDif
apex['tBeginDateTime'] = tDateTime
apex['iApexDif'] = thisDif
apex['tApexDateTime'] = tDateTime
apex['iDirectionType'] = 0 # 方向 0:不确定方向 1:向上 2:向下
end = time.time()
totle_time += end - start
print(totle_time)
if data_list:
macd_data_apex = macd_data_apex.append(data_list, ignore_index=True)
# debug.p(macd_data_apex[[ 'tBeginDateTime', 'tApexDateTime','tEndDateTime', 'iDirectionType']])
return macd_data_apex
