def writeResultDataToFile(fullDF: pd.DataFrame):
max_ymd = fullDF.TradeDate.max().strftime("%Y%m%d")
fpath = cfg().getValueByConfigFile(key="dailypath") + f"/{max_ymd[0:6]}"
fname = f"{fpath}/" + cfg().getValueByConfigFile(
key="resultname") + f"_{max_ymd}.xlsx"
outDF = getLastPeriodDF(in_DF=fullDF, period=1).filter(items=([
"StockID",
"StockName",
"上市/上櫃",
"投信(股數)",
"外資(股數)",
"自營商(股數)",
"TradeDate",
"Close",
"Volume",
"MFI",
] + [x for x in fullDF.columns[fullDF.columns.str.contains("sgl")]]))
file.GeneratorFromDF(outDF, fname)
fname = f"{fpath}/FocusList_{max_ymd}.xlsx"
stgDF = pd.DataFrame()
stgDF = stg(outDF).getFromFocusOnByStrategy(no_credit=True)
stgDF = stgDF[[
"StockID", "StockName", "cateDesc", "上市/上櫃", "投信(股數)", "外資(股數)",
"自營商(股數)", "TradeDate", "Close", "Volume", "MFI", "sgl_SMA", "sgl_SAR",
"sgl_MAXMIN", "sgl_BBANDS", "sgl_MACD"
]]
file.GeneratorFromDF(stgDF, fname)
fname = cfg().getValueByConfigFile(key="dailypath") + "/8002.xlsx"
file.GeneratorFromDF(stgDF, fname)
stgDF = stgDF[[
"TradeDate", "StockID", "StockName", "上市/上櫃", "cateDesc", "Close",
"Volume", "MFI", "sgl_SMA", "sgl_SAR", "sgl_MAXMIN", "sgl_BBANDS",
"sgl_MACD"
]].rename(
columns={
"TradeDate": "Date",
"上市/上櫃": "Market",
"cateDesc": "Category",
"sgl_SMA": "signalSMA",
"sgl_SAR": "signalSAR",
"sgl_MAXMIN": "signalMAXMIN",
"sgl_BBANDS": "signalBBANDS",
"sgl_MACD": "signalMACD"
})
db().updateDFtoDB(stgDF, tb_name="dailybuystrategy")
def callbackListDataToDF():
global GorderDF, GdealDF, GtickDF, GbidaskDF, itemorder, itemdeal, ticks
oDF = pd.DataFrame()
dDF = pd.DataFrame()
ymdt = datetime.now().strftime("%Y%m%d_%H%M%S")
if itemorder != []:
col = [
"StockID", "Action", "Price", "Qty", "OrderType", "PriceType",
"CancelQty", "TradeDate", "TradeTime", "ReceiveTime"
]
GorderDF = GorderDF.append(pd.DataFrame(itemorder, columns=col))
oDF = oDF.append(pd.DataFrame(itemorder, columns=col))
itemorder.clear()
fpath = f"./data/ActuralTrade/order_{ymdt}.xlsx"
file.GeneratorFromDF(oDF, fpath)
if itemdeal != []:
col = [
"StockID", "Action", "Price", "Qty", "OrderCond", "OrderLot",
"TradeDate", "TradeTime", "ReceiveTime"
]
GdealDF = GdealDF.append(pd.DataFrame(itemdeal, columns=col))
dDF = dDF.append(pd.DataFrame(itemdeal, columns=col))
itemdeal.clear()
fpath = f"./data/ActuralTrade/deal_{ymdt}.xlsx"
file.GeneratorFromDF(dDF, fpath)
if ticks != []:
col = [
"StockID", "TradeTime", "Open", "Close", "High", "Low", "Volume"
]
GtickDF = GtickDF.append(pd.DataFrame(ticks, columns=col))
ticks.clear()
if bidasks != []:
col = [
"StockID", "TradeTime", "BidPrice_1", "BidPrice_2", "BidPrice_3",
"BidPrice_4", "BidPrice_5", "BidVolume_1", "BidVolume_2",
"BidVolume_3", "BidVolume_4", "BidVolume_5", "AskPrice_1",
"AskPrice_2", "AskPrice_3", "AskPrice_4", "AskPrice_5",
"AskVolume_1", "AskVolume_2", "AskVolume_3", "AskVolume_4",
"AskVolume_5"
]
GbidaskDF = GbidaskDF.append(pd.DataFrame(bidasks, columns=col))
bidasks.clear()
def CallBackListToDF(last: bool = False) -> None:
global GorderDF, GdealDF, GoperDF, itemdeal, itemorder
oDF = pd.DataFrame()
dDF = pd.DataFrame()
ymdt = datetime.now().strftime("%Y%m%d_%H%M%S")
file_path = cfg().getValueByConfigFile(key="dailypath") + f"/{ymdt[0:6]}"
if itemorder != []:
col = [
"StockID", "Action", "Price", "Qty", "OrderType", "PriceType",
"CancelQty", "TradeDate", "TradeTime", "ReceiveTime"
]
GorderDF = GorderDF.append(pd.DataFrame(itemorder, columns=col))
# 準備存成File用的DF
oDF = oDF.append(pd.DataFrame(itemorder, columns=col))
file.GeneratorFromDF(oDF, f"{file_path}/order_{ymdt}.xlsx")
itemorder.clear()
if itemdeal != []:
col = [
"StockID", "Action", "Price", "Qty", "OrderCond", "OrderLot",
"TradeDate", "TradeTime", "ReceiveTime"
]
GdealDF = GdealDF.append(pd.DataFrame(itemdeal, columns=col))
# 準備存成File用的DF
dDF = dDF.append(pd.DataFrame(itemdeal, columns=col))
file.GeneratorFromDF(dDF, f"{file_path}/deal_{ymdt}.xlsx")
itemdeal.clear()
buyDF = pd.DataFrame()
sellDF = pd.DataFrame()
buyDF = dDF.loc[dDF.Action == "Buy"]
sellDF = dDF.loc[dDF.Action == "Sell"]
if not buyDF.empty:
GoperDF = GoperDF.append(
buyDF.filter(items=["StockID", "Price", "Qty", "TradeTime"]))
# 產生當天最後的結果檔案
if last:
if not GorderDF.empty:
file.GeneratorFromDF(GorderDF,
f"{file_path}/order_{ymdt[0:8]}.xlsx")
if not GdealDF.empty:
file.GeneratorFromDF(GdealDF, f"{file_path}/deal_{ymdt[0:8]}.xlsx")
def calFocusStockTrend():
global GtickDF
getTrend = []
try:
if not GtickDF.empty:
bkTickDF = GtickDF.copy(deep=True).sort_values(
by=["StockID", "TradeTime"]) # deep = True 才不會改到原始的DF
for stockid, oneStkDF in bkTickDF.groupby("StockID"):
# oneStkDF.reset_index(inplace = True, drop = True)
reg_up = linregress(x=oneStkDF.index,
y=oneStkDF.Close.astype(float))
up_line = reg_up.intercept + reg_up.slope * oneStkDF.index
oneStkDFtmp = oneStkDF[oneStkDF.Close < up_line]
feq = 0
while len(oneStkDFtmp) >= 5 and feq < 50:
feq += 1
reg_new = linregress(x=oneStkDFtmp.index,
y=oneStkDFtmp.Close.astype(float))
up_new = reg_new.intercept + reg_new.slope * oneStkDFtmp.index
oneStkDFtmp = oneStkDFtmp[oneStkDFtmp.Close < up_new]
oneStkDF[
"Low_Trend"] = reg_new.intercept + reg_new.slope * oneStkDF.index
if reg_up.slope >= 0:
val = "+"
else:
val = "-"
l = []
l.append(str(stockid))
l.append(str(val))
l.append(float(reg_up.slope))
l.append(float(reg_new.slope))
getTrend.append(l)
TrendDF = pd.DataFrame(
getTrend, columns=["StockID", "Trend", "SlopeOrg", "SlopeNew"])
ymd = datetime.now().strftime("%Y%m%d")
path = f"./data/ActuralTrade/{ymd[0:6]}"
if not TrendDF.empty:
fpath = f"{path}/Trend_{ymd}.xlsx"
file.GeneratorFromDF(TrendDF, fpath)
logger.info(f"Generate Trend File Down!")
except Exception as exc:
logger.error(f"Tick to DF error! {exc}")
# 取消訂閱
con(api).UnsubscribeTickBidAskByStockList(subList, "bidask")
break
if datetime.now().strftime("%H:%M") >= closepoint:
# 取消訂閱
con(api).UnsubscribeTickBidAskByStockList(subList, "bidask")
break
tool.WaitingTimeDecide(check_secs)
ymd = datetime.now().strftime("%Y%m%d_%H%M%S")
path = f"./data/ActuralTrade/{ymd[0:6]}"
if not GorderDF.empty:
fpath = f"{path}/order_{ymd}.xlsx"
file.GeneratorFromDF(GorderDF, fpath)
logger.info(f"Generate Order File Down!")
if not GdealDF.empty:
fpath = f"{path}/deal_{ymd}.xlsx"
file.GeneratorFromDF(GdealDF, fpath)
logger.info(f"Generate Deal File Down!")
if not GtickDF.empty:
fpath = f"{path}/tick_{ymd}.xlsx"
file.GeneratorFromDF(GtickDF, fpath)
logger.info(f"Generate Tick File Down!")
if not GbidaskDF.empty:
fpath = f"{path}/bidask_{ymd}.xlsx"
file.GeneratorFromDF(GbidaskDF, fpath)
logger.info(f"Generate BidAsk File Down!")
logger.info("End")
"SMA", "SAR", "MAXMIN", "BBANDS", "MACD"
]) # Default = ["SMA", "SAR", "MAXMIN", "BBands", "RSI", "MACD", "KDJ"]
writeResultDataToFile(stkDFwithInd)
# 做盤後模擬測試
RSIsmiDF = sim().useRSItoMakeResultDF(p_days=-1, RSI_period=12)
if not RSIsmiDF.empty:
fpath = "./data/Simulation/RSI.xlsx"
if tool.checkFileExist(fpath):
RSIsmiDF = RSIsmiDF.append(pd.read_excel(fpath)).reset_index(drop=True)
RSIsmiDF[["TradeDate", "StockID"]] = RSIsmiDF[["TradeDate",
"StockID"]].astype(str)
RSIsmiDF = RSIsmiDF.drop_duplicates(
subset=["TradeDate", "StockID", "Frequency"], keep="first")
file.GeneratorFromDF(RSIsmiDF, fpath)
today = datetime.now().strftime("%Y-%m-%d")
sql = f"SELECT StockID, Date(TradeDateTime) as TickDate, TIME_FORMAT(TradeDateTime, '%T.%f') as TickTime, Close, Volume, BidPrice, BidVolume, AskPrice, AskVolume FROM dailyticks WHERE Date(TradeDateTime) = '{today}' AND Time(TradeDateTime) <= '09:05:00'"
TicksDF = db().selectDatatoDF(sql_statment=sql).sort_values(
by=["StockID", "TickDate", "TickTime"])
tickpath = "./data/PlotData/Ticks.csv"
if not tool.checkFileExist(tickpath):
file.GeneratorFromDF(TicksDF, tickpath, "csv")
else:
with open(tickpath, "a") as f:
TicksDF.to_csv(f, header=False, index=False, line_terminator="\n")
# %%
R0_BuyDF, SoldOut = checkPriceToSell(R0_BuyDF, secDF, "X")
R1_BuyDF, SoldOut = checkPriceToSell(R1_BuyDF, secDF, "X")
break
tool.WaitingTimeDecide(chk_sec)
R0_TradeDF = getTradeResultDF(stkDF, R0_BuyDF, "R0")
R1_TradeDF = getTradeResultDF(stkDF, R1_BuyDF, "R1")
R0_TradeDF = R0_TradeDF.merge(SlopeDF, on = ["StockID"], how = "left")
R1_TradeDF = R1_TradeDF.merge(SlopeDF, on = ["StockID"], how = "left")
# 準備存檔資料
ymd = date.today().strftime("%Y%m%d")
trade_path = cfg().getValueByConfigFile(key = "tradepath") + f"/{ymd[0:6]}"
# Rule0 / Rule1處理檔案的部份
file.GeneratorFromDF(R0_TradeDF, f"{trade_path}/Trade_{ymd}_R0.xlsx")
file.GeneratorFromDF(R1_TradeDF, f"{trade_path}/Trade_{ymd}_R1.xlsx")
# while True:
# secDF = con(api).getSnapshotDataByStockID(contracts)
# # 每分鐘存留一版secDF,做成minDF(0也要做一次)
# if dotimes % 2 == 0:
# minDF = minDF.append(secDF)
# # 09:05前就只要做snapshot
# if datetime.now().strftime("%H:%M") < "09:05":
# # if datetime.now().strftime("%H:%M") < getExcuteTime():
# dotimes += 1
# tool.WaitingTimeDecide(chk_sec)
oneStkDF.Close.plot()
plt.plot(oneStkDF.Low_Trend)
l = []
l.append(str(stockid))
l.append(str(val))
l.append(float(reg_up.slope))
l.append(float(reg_new.slope))
getTrend.append(l)
TrendDF = pd.DataFrame(getTrend,
columns=["StockID", "Trend", "SlopeOrg", "SlopeNew"])
ymd = datetime.now().strftime("%Y%m%d")
path = f"./data/ActuralTrade/{ymd[0:6]}"
if not TrendDF.empty:
fpath = f"{path}/Trend_{ymd}.xlsx"
file.GeneratorFromDF(TrendDF, fpath)
# %%
# @api.quote.on_event
# def event_callback(resp_code: int, event_code: int, info: str, event: str):
# global eventDF
# l = []
# l.append(resp_code)
# l.append(event_code)
# l.append(info)
# l.append(event)
# l.append(datetime.now().strftime("%H:%M:%S.%f"))
# eventDF = eventDF.append(pd.DataFrame([l], columns = ["resp_code", "event_code", "info", "event", "ReceiveTime"]))
# # print(f'Event code: {event_code} | Event: {event} | Resp_Code: {resp_code} | info: {info}')