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)