def stat_index_all(tmp_datetime):
datetime_str = (tmp_datetime).strftime("%Y-%m-%d")
datetime_int = (tmp_datetime).strftime("%Y%m%d")
print("datetime_str:", datetime_str)
print("datetime_int:", datetime_int)
# 查询今日满足股票数据。剔除数据：创业板股票数据，中小板股票数据，所有st股票
# #`code` not like '002%' and `code` not like '300%' and `name` not like '%st%'
sql_1 = """
SELECT `date`, `code`, `name`, `changepercent`, `trade`, `open`, `high`, `low`,
`settlement`, `volume`, `turnoverratio`, `amount`, `per`, `pb`, `mktcap`, `nmc`
FROM stock_data.ts_today_all WHERE `date` = %s and `trade` > 0 and `open` > 0 and trade <= 20
and `code` not like %s and `code` not like %s and `name` not like %s
"""
print(sql_1)
data = pd.read_sql(sql=sql_1,
con=common.engine(),
params=[datetime_int, '002%', '300%', '%st%'])
data = data.drop_duplicates(subset="code", keep="last")
print("########data[trade]########:")
# print(data["trade"])
# 使用 trade 填充数据
stock_guess = pd.DataFrame(
{
"date": data["date"],
"code": data["code"],
"5d": data["trade"],
"10d": data["trade"],
"20d": data["trade"],
"60d": data["trade"],
"5-10d": data["trade"],
"5-20d": data["trade"],
"return": data["trade"],
"mov_vol": data["trade"]
},
index=data.index.values)
stock_guess = stock_guess.apply(apply_guess, axis=1) # , axis=1)
# print(stock_guess.head())
# stock_guess.astype('float32', copy=False)
stock_guess.drop('date', axis=1, inplace=True) # 删除日期字段，然后和原始数据合并。
# print(stock_guess["5d"])
data_new = pd.merge(data, stock_guess, on=['code'], how='left')
print("#############")
# 使用pandas 函数 ： https://pandas.pydata.org/pandas-docs/stable/api.html#id4
data_new["return"] = data_new["return"].mul(100) # 扩大100 倍方便观察
data_new["mov_vol"] = data_new["mov_vol"].mul(100)
data_new = data_new.round(2) # 数据保留2位小数
# 删除老数据。
del_sql = " DELETE FROM `stock_data`.`guess_return_daily` WHERE `date`= '%s' " % datetime_int
common.insert(del_sql)
# data_new["down_rate"] = (data_new["trade"] - data_new["wave_mean"]) / data_new["wave_base"]
common.insert_db(data_new, "guess_return_daily", False, "`date`,`code`")
def stat_all_lite(tmp_datetime):
# 要操作的数据库表名称。
table_name = "guess_indicators_lite_buy_daily"
datetime_str = (tmp_datetime).strftime("%Y-%m-%d")
datetime_int = (tmp_datetime).strftime("%Y%m%d")
print("datetime_str:", datetime_str)
print("datetime_int:", datetime_int)
# try:
# # 删除老数据。guess_indicators_lite_buy_daily 是一张单表，没有日期字段。
# del_sql = " DELETE FROM `stock_data`.`%s` WHERE `date`= '%s' " % (table_name, datetime_int)
# print("del_sql:", del_sql)
# common.insert(del_sql)
# print("del_sql")
# except Exception as e:
# print("error :", e)
sql_1 = """
SELECT `date`, `code`, `name`, `changepercent`, `trade`,`turnoverratio`, `pb` ,`kdjj`,`rsi_6`,`cci`
FROM stock_data.guess_indicators_lite_daily WHERE `date` = %s
and `changepercent` > 2 and `pb` > 0
"""
# and `changepercent` > 2 and `pb` > 0 and `turnoverratio` > 5 去除掉换手率参数。
data = pd.read_sql(sql=sql_1, con=common.engine(), params=[datetime_int])
data = data.drop_duplicates(subset="code", keep="last")
print("######## len data ########:", len(data))
# del data["name"]
# print(data)
data["trade_float32"] = data["trade"].astype('float32', copy=True)
# 输入 date 用作历史数据查询。
stock_merge = pd.DataFrame(
{
"date": data["date"],
"code": data["code"],
"wave_mean": data["trade"],
"wave_crest": data["trade"],
"wave_base": data["trade"]
},
index=data.index.values)
print(stock_merge.head(1))
stock_merge = stock_merge.apply(apply_merge, axis=1) # , axis=1)
del stock_merge["date"] # 合并前删除 date 字段。
# 合并数据
data_new = pd.merge(data, stock_merge, on=['code'], how='left')
# 使用 trade_float32 参加计算。
data_new = data_new[data_new["trade_float32"] >
data_new["wave_base"]] # 交易价格大于波谷价格。
data_new = data_new[data_new["trade_float32"] <
data_new["wave_crest"]] # 小于波峰价格
# wave_base wave_crest wave_mean
data_new["wave_base"] = data_new["wave_base"].round(2) # 数据保留2位小数
data_new["wave_crest"] = data_new["wave_crest"].round(2) # 数据保留2位小数
data_new["wave_mean"] = data_new["wave_mean"].round(2) # 数据保留2位小数
data_new["up_rate"] = (data_new["wave_mean"].sub(
data_new["trade_float32"])).div(data_new["wave_crest"]).mul(100)
data_new["up_rate"] = data_new["up_rate"].round(2) # 数据保留2位小数
data_new["buy"] = 1
data_new["sell"] = 0
data_new["today_trade"] = data_new["trade"]
data_new["income"] = 0
# 重命名 date
data_new.columns.values[0] = "buy_date"
del data_new["trade_float32"]
try:
common.insert_db(data_new, table_name, False, "`code`")
print("insert_db")
except Exception as e:
print("error :", e)
# 重命名
del data_new["name"]
print(data_new)
def stat_all_batch(tmp_datetime):
datetime_str = (tmp_datetime).strftime("%Y-%m-%d")
datetime_int = (tmp_datetime).strftime("%Y%m%d")
print("datetime_str:", datetime_str)
print("datetime_int:", datetime_int)
try:
# 删除老数据。
del_sql = " DELETE FROM `stock_data`.`%s` WHERE `date`= %s " % (
table_name, datetime_int)
print("del_sql:", del_sql)
common.insert(del_sql)
except Exception as e:
print("error :", e)
sql_count = """
SELECT count(1) FROM stock_data.ts_today_all WHERE `date` = %s and `trade` > 0 and `open` > 0 and trade <= 20
and `code` not like %s and `name` not like %s
"""
# 修改逻辑，增加中小板块计算。 中小板：002，创业板：300 。and `code` not like %s and `code` not like %s and `name` not like %s
# count = common.select_count(sql_count, params=[datetime_int, '002%', '300%', '%st%'])
count = common.select_count(sql_count,
params=[datetime_int, '300%', '%st%'])
print("count :", count)
batch_size = 100
end = int(math.ceil(float(count) / batch_size) * batch_size)
print(end)
# for i in range(0, end, batch_size):
for i in range(0, end, batch_size):
print("loop :", i)
# 查询今日满足股票数据。剔除数据：创业板股票数据，中小板股票数据，所有st股票
# #`code` not like '002%' and `code` not like '300%' and `name` not like '%st%'
sql_1 = """
SELECT `date`, `code`, `name`, `changepercent`, `trade`, `open`, `high`, `low`,
`settlement`, `volume`, `turnoverratio`, `amount`, `per`, `pb`, `mktcap`, `nmc`
FROM stock_data.ts_today_all WHERE `date` = %s and `trade` > 0 and `open` > 0 and trade <= 20
and `code` not like %s and `name` not like %s limit %s , %s
"""
print(sql_1)
# data = pd.read_sql(sql=sql_1, con=common.engine(), params=[datetime_int, '002%', '300%', '%st%', i, batch_size])
data = pd.read_sql(
sql=sql_1,
con=common.engine(),
params=[datetime_int, '300%', '%st%', i, batch_size])
data = data.drop_duplicates(subset="code", keep="last")
print("########data[trade]########:", len(data))
# 使用 trade 填充数据
stock_sklearn = pd.DataFrame(
{
"date": data["date"],
"code": data["code"],
"next_close": data["trade"],
"sklearn_score": data["trade"]
},
index=data.index.values)
print(stock_sklearn.head())
stock_sklearn_apply = stock_sklearn.apply(apply_sklearn,
axis=1) # , axis=1)
# 重命名
del stock_sklearn_apply["date"] # 合并前删除 date 字段。
# 合并数据
data_new = pd.merge(data, stock_sklearn_apply, on=['code'], how='left')
# for index, row in data.iterrows():
# next_stock, score = stat_index_all(row, i)
# print(next_stock, score)
data_new["next_close"] = data_new["next_close"].round(2) # 数据保留4位小数
data_new["sklearn_score"] = data_new["sklearn_score"].round(
2) # 数据保留2位小数
data_new["trade_float32"] = data["trade"].astype('float32', copy=False)
data_new["up_rate"] = (
data_new["next_close"] -
data_new["trade_float32"]) * 100 / data_new["trade_float32"]
data_new["up_rate"] = data_new["up_rate"].round(2) # 数据保留2位小数
del data_new["trade_float32"]
try:
common.insert_db(data_new, table_name, False, "`date`,`code`")
print("insert_db")
except Exception as e:
print("error :", e)
# 重命名
del data_new["name"]
print(data_new)
def stat_index_calculate(tmp_datetime):
# 要操作的数据库表名称。
table_name = "guess_indicators_lite_sell_daily"
datetime_str = (tmp_datetime).strftime("%Y-%m-%d")
datetime_int = (tmp_datetime).strftime("%Y%m%d")
print("datetime_str:", datetime_str)
print("datetime_int:", datetime_int)
sql_1 = """
SELECT `buy_date`, `code`, `name`, `changepercent`, `trade`, `turnoverratio`, `pb`, `kdjj`, `rsi_6`,
`cci`, `wave_base`, `wave_crest`, `wave_mean`, `up_rate`
FROM guess_indicators_lite_buy_daily where `buy_date` <= """ + datetime_int
print(sql_1)
data = pd.read_sql(sql=sql_1, con=common.engine(), params=[])
data = data.drop_duplicates(subset="code", keep="last")
print(data["trade"])
data["trade_float32"] = data["trade"].astype('float32', copy=False)
print(len(data))
data["date"] = datetime_int
stock_merge = pd.DataFrame(
{
"date": data["date"],
"code": data["code"],
"today_trade": data["trade"]
},
index=data.index.values)
print(stock_merge.head(1))
stock_merge = stock_merge.apply(apply_merge, axis=1) # , axis=1)
del stock_merge["date"] # 合并前删除 date 字段。
# 合并数据
data_new = pd.merge(data, stock_merge, on=['code'], how='left')
data_new["income"] = (data_new["today_trade"] -
data_new["trade_float32"]) * 100
data_new["income"] = data_new["income"].round(4) # 保留4位小数。
# 增加售出列。看看是否需要卖出。
stock_sell_merge = pd.DataFrame(
{
"date": data["date"],
"code": data["code"],
"sell": 0,
"buy": 0,
"sell_kdjj": 0,
"sell_rsi_6": 0,
"sell_cci": 0
},
index=data.index.values)
print(stock_sell_merge.head(1))
merge_sell_data = stock_sell_merge.apply(apply_merge_sell,
axis=1) # , axis=1)
# 重命名
del merge_sell_data["date"] # 合并前删除 date 字段。
# 合并数据
data_new = pd.merge(data_new, merge_sell_data, on=['code'], how='left')
# 删除老数据。
try:
del_sql = " DELETE FROM `stock_data`.`" + table_name + "` WHERE `date`= '%s' " % datetime_int
common.insert(del_sql)
print("insert_db")
except Exception as e:
print("error :", e)
del data_new["trade_float32"]
try:
common.insert_db(data_new, table_name, False, "`date`,`code`")
print("insert_db")
except Exception as e:
print("error :", e)
# 重命名
del data_new["name"]
print(data_new)