def writeDailyKbarDataToDB(StkDF: pd.DataFrame):
if StkDF.empty:
return
tb = cfg().getValueByConfigFile(key="tb_daily")
sql = f"SELECT MAX(TradeDate) as TradeDate FROM {tb}"
daily_maxday = db().selectDatatoDF(sql_statment=sql).iloc[0, 0]
tb = cfg().getValueByConfigFile(key="tb_mins")
sql = f"SELECT MAX(TradeDate) as TradeDate FROM {tb}"
mins_maxday = db().selectDatatoDF(sql_statment=sql).iloc[0, 0]
if daily_maxday < mins_maxday:
stktuple = tuple(tool.DFcolumnToList(StkDF, colname="StockID"))
tb = cfg().getValueByConfigFile(key="tb_mins")
mins_maxday = mins_maxday.strftime("%Y%m%d")
sql = f"SELECT * FROM {tb} WHERE TradeDate = {mins_maxday} AND StockID in {stktuple}"
minsDF = db().selectDatatoDF(sql_statment=sql).drop(
columns=["modifytime"])
DkBarDF = minsDF.groupby(["StockID", "TradeDate"], sort=True).agg({
"Open":
"first",
"High":
max,
"Low":
min,
"Close":
"last",
"Volume":
sum
}).reset_index()
tb = cfg().getValueByConfigFile(key="tb_daily")
db().updateDFtoDB(DkBarDF, tb_name=tb)
def writeDailyFocusStockTicks(api):
stkDF_new = file().getLastFocusStockDF()
stkDF = stg(stkDF_new).getFromFocusOnByStrategy()
BuyList = tool.DFcolumnToList(stkDF, "StockID")
tickDF = pd.DataFrame()
for id in BuyList:
tk = api.ticks(contract=api.Contracts.Stocks[id],
date=date.today().strftime("%Y-%m-%d"))
tDF = pd.DataFrame({**tk})
tDF.ts = pd.to_datetime(tDF.ts)
tDF.insert(0, "StockID", id)
tickDF = tickDF.append(tDF)
tickDF = tickDF.filter(items=[
"StockID", "ts", "close", "volume", "bid_price", "bid_volume",
"ask_price", "ask_volume"
]).rename(
columns={
"ts": "TradeDateTime",
"close": "Close",
"volume": "Volume",
"bid_price": "BidPrice",
"bid_volume": "BidVolume",
"ask_price": "AskPrice",
"ask_volume": "AskVolume"
})
db().updateDFtoDB(tickDF, tb_name="dailyticks")
def decideRealBuyList(stgBuy: pd.DataFrame, price: float,
Stk_num: int) -> list:
BuyIDs = []
try:
excludeID = tool.DFcolumnToList(pd.read_excel("./data/Exclude.xlsx"),
"StockID")
stgBuyDF = stgBuy[~stgBuy.StockID.isin(excludeID)]
except:
stgBuyDF = stgBuy
# 產生一個連續數值的list, 2->[2,1] , 3->[3,2,1]....
BuyDecide = buyStocksGenerate(Stk_num)
for i in BuyDecide:
try:
BuyIDs = random.sample(tool.DFcolumnToList(
stgBuyDF.loc[stgBuyDF.Open <= price], "StockID"),
k=i)
break
except:
continue
return BuyIDs
def getStockDailyDataFromDB(stkBsData: pd.DataFrame,
days: int = 250) -> pd.DataFrame:
dytb = cfg().getValueByConfigFile(key="tb_daily")
slst = tuple(tool.DFcolumnToList(inDF=stkBsData, colname="StockID"))
sql = f"SELECT DISTINCT TradeDate FROM {dytb}"
sdate = db().selectDatatoDF(sql_statment=sql)
sdate = sdate.sort_index(ascending=False).reset_index(drop=True)
sdate = sdate.loc[days][0].strftime("%Y%m%d")
sql = f"SELECT * FROM {dytb} WHERE TradeDate >= {sdate} AND StockID IN {slst}"
df = db().selectDatatoDF(sql_statment=sql).sort_values(
by=["StockID", "TradeDate"], ascending=True)
df = mergeVolumeDataDB(df)
df = df.merge(stkBsData, on=["StockID"], how="left")
return df
l.append(bidask.ask_volume[1])
l.append(bidask.ask_volume[2])
l.append(bidask.ask_volume[3])
l.append(bidask.ask_volume[4])
bidasks.append(l)
# logger.info(f"Exchange: {exchange}, BidAsk: {bidask}")
@api.quote.on_event
def event_callback(resp_code: int, event_code: int, info: str, event: str):
logger.info(f'Event code: {event_code} | Event: {event}')
api.quote.set_event_callback(event_callback)
# 1.2 取得現有庫存
whList = tool.DFcolumnToList(con(api).getTreasuryStockDF(), "code")
# whList = ["00885", "1301", "1904", "2002", "2330", "2353", "2616", "2705", "2823", "2883", "3186", "3258", "3704"]
# 1.3 設定交易即時回報
api.set_order_callback(placeOrderCallBack)
# 2.依策略決定下單清單
stkDF = file().getLastFocusStockDF()
stkDF = stg(stkDF).getFromFocusOnByStrategy()
# 2.1 需要訂閱的股票清單
subList = tool.DFcolumnToList(stkDF, "StockID")
# 2.2 訂閱(Focus)
con(api).SubscribeTickBidAskByStockList(subList, "tick")
# 3.組合需要抓價量的Stocks(不能當沖的不放進來)
contracts = con(api).getContractForAPI(stkDF)
if __name__ == "__main__":
# 先檢查資料夾是否存在..沒有就建立
tool.checkCreateYearMonthPath()
# 開始log
logger = create_logger("./logs")
# 設定更新秒數
wait_secs = 20
# 1.連接Server,指定帳號(預設chris),使用的CA(預設None)
api = con().ServerConnectLogin(ca="chris")
# 2.取得股票清單(只留下可以當沖的)
stkDF = file.getPreviousTransactionFocusStockDF()
stkLst = tool.DFcolumnToList(stkDF, "StockID")
contracts = con(api).getContractForAPI(stkDF)
# 3.訂閱/回報
# 3.1 設定交易即時回報
api.set_order_callback(placeOrderCallBack)
# 3.2 設定回報資料(Tick / Bidask / Event)
@api.on_tick_stk_v1()
def quote_callback_tick(exchange: Exchange, tick: TickSTKv1):
global ticks
l = []
l.append(tick.code)
l.append(tick.datetime.strftime("%H:%M:%S.%f"))
l.append(tick.open)
# l.append(tick.avg_price)
l.append(tick.close)