def refresh_all_stock_day_k_noadjust(start_date="2020-03-27", current_date="2020-03-30"): bs.login() stock_rs = bs.query_all_stock(day=current_date) stock_df = stock_rs.get_data() data_list = [] for code in stock_df["code"]: k_rs = bs.query_history_k_data_plus( code, "date,code,open,high,low,close,preclose,volume,amount,adjustflag,turn,tradestatus,pctChg,peTTM,pbMRQ,psTTM,pcfNcfTTM,isST", start_date=start_date, end_date='', frequency="d", adjustflag="3") while (k_rs.error_code == '0') & k_rs.next(): data_list.append(k_rs.get_row_data()) print('query_history_k_data_plus no adjust code:' + code) bs.logout() db_conn = create_engine(common.db_path_sqlalchemy) db_conn.execute( r''' INSERT OR REPLACE INTO stock_day_k_noadjust VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?) ''', data_list)
def get_name(): # login lg = bs.login() # show login info print('login respond error_code:' + lg.error_code) print('login respond error_msg:' + lg.error_msg) # show stock info nowdate = datetime.datetime.now() delta = datetime.timedelta(days = -4) # today is Monday today = nowdate + delta today = today.strftime('%Y-%m-%d') rs = bs.query_all_stock(day = today) print('query_all_stock respond error_code:' + rs.error_code) print('query_all_stock respond error_msg:' + rs.error_msg) # show results data_list = [] while(rs.error_code == '0') & rs.next(): data_list.append(rs.get_row_data()) result = pd.DataFrame(data_list,columns = rs.fields) # logout bs.logout() conn = pymysql.connect(host = "localhost", user = "******", database = "STOCK", password = "******", use_unicode = True, charset = "utf8") cursor1 = conn.cursor() ls = [] for i in range(len(result['code'])): ls.append([result['code'][i].split('.')[1], result['tradeStatus'][i], result['code_name'][i]]) for i in range(len(ls)): commit_data = [ls[i][0], ls[i][1].encode('utf-8'), ls[i][2].encode('utf-8'), today] cursor1.execute('INSERT INTO stock_code_data values(%s,%s,%s,%s)', commit_data) conn.commit() conn.close() return ls
def prepare_fetch_data(): # 获取当前年/季度 current_date = datetime.date.today().strftime('%Y-%m-%d') current_year = datetime.date.today().year current_month = datetime.date.today().month current_quarter = (current_month - 1) // 3 + 1 hour = datetime.datetime.now().hour # baostock data maybe not update right after the trade market close. if hour < 20: dd = datetime.date.today() + datetime.timedelta(-1) current_date = dd.strftime('%Y-%m-%d') # 计算最后一个交易日时间 start_date = "2006-01-01" data_list = [] bs.login() rs = bs.query_trade_dates(start_date=start_date, end_date=current_date) while (rs.error_code == '0') & rs.next(): data_list.append(rs.get_row_data()) bs.logout() lasttradedate = current_date for i in range(len(data_list) - 1, -1, -1): if data_list[i][1] == '1': lasttradedate = data_list[i][0] break print('start query day:' + start_date + ', last query data:' + lasttradedate) bs.login() stock_rs = bs.query_all_stock(day=lasttradedate) stock_df = stock_rs.get_data() bs.logout() return stock_df, current_year, current_quarter
def all_stock_list(): #### 登陆系统 #### lg = bs.login() # 显示登陆返回信息 print('login respond error_code:' + lg.error_code) print('login respond error_msg:' + lg.error_msg) #### 获取证券信息 #### date = datetime.datetime.now() print(date) start_date = (date + datetime.timedelta(days=-5)).strftime('%Y-%m-%d') rs = bs.query_all_stock(day='2020-07-16') print('query_all_stock respond error_code:' + rs.error_code) print('query_all_stock respond error_msg:' + rs.error_msg) #### 登出系统 #### bs.logout() #### 打印结果集 #### data_list = [] while (rs.error_code == '0') & rs.next(): # 获取一条记录,将记录合并在一起 data = rs.get_row_data() if data[1] != '0': #data[0] = data[0].replace('.', '') data_list.append(data) result = pd.DataFrame(data_list, columns=rs.fields) return result
def update_stk_list(date=None): # 获取指定日期的指数、股票数据 stock_rs = bs.query_all_stock(date) stock_df = stock_rs.get_data() stock_df.to_csv('./stk_data/all_list.csv', encoding='gbk', index=False) stock_df.drop(stock_df[stock_df.code < 'sh.600000'].index, inplace=True) stock_df.drop(stock_df[stock_df.code > 'sz.399000'].index, inplace=True) stock_df.to_csv('./stk_data/stk_list.csv', encoding='gbk', index=False) cursor = db.cursor() stock_list_sql = "SELECT * FROM `StockList`" cursor.execute(stock_list_sql) stock_list = cursor.fetchall() stock_tables = pd.DataFrame(stock_list, columns=['code', 'name', 'last_update_date']) for index, data in stock_df.iterrows(): if data.code in stock_tables['code'].values: continue sql = 'INSERT INTO `StockList` (`id`, `name`, `last_update_date`) VALUES (\'{stockId}\', \'{stockName}\', \'{stockTime}\')'\ .format(stockId=data.code, stockName=data.code_name, stockTime='1970-01-01') try: cursor.execute(sql) db.commit() except Exception as e: print('failed', e) db.rollback() stock_list_sql = "SELECT * FROM `StockList`" cursor.execute(stock_list_sql) stock_list = cursor.fetchall() stock_tables = pd.DataFrame(stock_list, columns=['code', 'name', 'last_update_date']) return stock_tables
def updateCode(conn, date): stock_rs = bs.query_all_stock(date) stock_df = stock_rs.get_data() kList = ['code', 'code_name', 'trade_status'] for i, row in stock_df.iterrows(): item = [row['code'], row['code_name'], row['tradeStatus']] comm.upsertRow(conn, 'stock_code', kList, item, 'code')
def query_stock_k_data_plus(date): #### 登陆系统 #### lg = bs.login() # 显示登陆返回信息 print('login respond error_code:' + lg.error_code) print('login respond error_msg:' + lg.error_msg) date = date.strftime("%Y-%m-%d") print(date) stock_rs = bs.query_all_stock(date) stock_df = stock_rs.get_data() print(stock_df.empty) if not (stock_df is None or stock_df.empty): data_df = pd.DataFrame() for code in stock_df["code"]: k_rs = bs.query_history_k_data_plus( code, "date,code,open,high,low,close,preclose,volume," "amount,adjustflag,turn,tradestatus,pctChg,peTTM," "pbMRQ,psTTM,pcfNcfTTM,isST", date, date) data_df = data_df.append(k_rs.get_data()) data = data_df.drop_duplicates(subset={"date", "code"}, keep="last") common.insert_db(data, "bs_stock_k_data", False, "`date`,`code`") print(date + " done!") else: print(date + " no data .") bs.logout()
def getAllStockCode(): rs = bs.query_all_stock(day="2019-03-01") data_list = [] while (rs.error_code == '0') & rs.next(): data_list.append(rs.get_row_data()) result = pd.DataFrame(data_list, columns=rs.fields) result.to_csv("D:\StockDemo\_allStockCode.csv", encoding="utf-8")
def get_data_from_baostock_first_time(stock): # stock_list = ['sh.000001'] stock = 'sz.002241' stock = stock all_stocks = bs.query_all_stock() stock_df = all_stocks.get_data() print(stock_df.head())
def getind(): lg = bs.login() # 显示登陆返回信息 #print('login respond error_code:' + lg.error_code) #print('login respond error_msg:' + lg.error_msg) #### 获取证券信息 #### rs = bs.query_all_stock(day="2020-01-02") #print('query_all_stock respond error_code:' + rs.error_code) #print('query_all_stock respond error_msg:' + rs.error_msg) #### 打印结果集 #### data_list = [] while (rs.error_code == '0') & rs.next(): # 获取一条记录,将记录合并在一起 data_list.append(rs.get_row_data()) result = pd.DataFrame(data_list, columns=rs.fields) #### 结果集输出到csv文件 #### #result.to_csv("D:\\all_stock.csv", encoding="gbk", index=False) print(result) if data_list: ce = create_engine( 'mysql+mysqlconnector://root:coship@localhost:3306/stock?charset=utf8' ) result.to_sql('stname', ce, if_exists='append', index=False) #### 登出系统 #### bs.logout()
def refresh_all_stock_adjust(start_date="2020-03-27", current_date="2020-03-30"): bs.login() db_conn = create_engine(common.db_path_sqlalchemy) stock_rs = bs.query_all_stock(day=current_date) stock_df = stock_rs.get_data() rs_list = [] for code in stock_df["code"]: if code.startswith("sh.6") | code.startswith( "sz.00") | code.startswith("sz.300"): print('query_stock_factor code:' + code) rs_factor = bs.query_adjust_factor(code=code, start_date=start_date, end_date=current_date) while (rs_factor.error_code == '0') & rs_factor.next(): rs_list.append(rs_factor.get_row_data()) factor_list = [] for d in rs_list: code = d[0] print("refresh all factor for " + code) rs_factor = bs.query_adjust_factor(code=code, start_date='2006-01-01', end_date=current_date) while (rs_factor.error_code == '0') & rs_factor.next(): factor_list.append(rs_factor.get_row_data()) if len(factor_list) > 0: db_conn.execute( r''' INSERT OR REPLACE INTO stock_adjustfactor VALUES (?, ?, ?, ?, ?) ''', factor_list) bs.logout()
def getStocks(tradeDay): stocks = bs.query_all_stock(tradeDay) # print('query_all_stock respond error_code:'+stocks.error_code) # print('query_all_stock respond error_msg:'+stocks.error_msg) stocks_data_list = [] while (stocks.error_code == '0') & stocks.next(): stocks_data_list.append(stocks.get_row_data()) return pd.DataFrame(stocks_data_list, columns=stocks.fields)
def update_stk_list(date=None): # 获取指定日期的指数、股票数据 stock_rs = bs.query_all_stock(date) stock_df = stock_rs.get_data() stock_df.to_csv('F://all_list.csv', encoding='gbk', index=False) stock_df.drop(stock_df[stock_df.code < 'sh.600000'].index, inplace=True) stock_df.drop(stock_df[stock_df.code > 'sz.399000'].index, inplace=True) stock_df = stock_df['code'] stock_df.to_csv('F://stk_list.csv', encoding='gbk', index=False) return stock_df.tolist()
def get_all_stock_code(date): """ fetch all the stock in the market in specific date :param date: :return: """ stock_rs = bs.query_all_stock(date) stock_df = stock_rs.get_data() stock_df = stock_df[stock_df.tradeStatus == '1'].reset_index(drop=True) return stock_df
def get_all_stock_data_by_day(date): stock_rs = bs.query_all_stock(date) stock_df = stock_rs.get_data() data_df = pd.DataFrame() for code in stock_df["code"]: print("Downloading :" + code) k_rs = bs.query_history_k_data_plus( code, "date,code,open,high,low,turn,liqaShare,close", date, date) data_df = data_df.append(k_rs.get_data()) data_df.to_csv(r"C:\Users\Administrator\Desktop\data.csv") print(data_df)
def refresh_all_stock(current_date="2020-03-27"): db_conn = create_engine('sqlite:///mystock.db') lg = bs.login() rs = bs.query_all_stock(day=current_date) data_list = [] while (rs.error_code == '0') & rs.next(): data_list.append(rs.get_row_data()) db_conn.execute( r''' INSERT OR REPLACE INTO allstock VALUES (?, ?, ?) ''', data_list)
def get_all_stocks(start_date): rs = BS.query_all_stock(day=start_date) data_list = [] while (rs.error_code == '0') & rs.next(): data_list.append(rs.get_row_data()) result = PD.DataFrame(data_list, columns=rs.fields) print(result) return result
def query_all_stock_fullinfo(self, query_day="2020-01-23"): data_list = [] rs = bs.query_all_stock(day=query_day) print('query_all_stock respond error_code:' + rs.error_code) print('query_all_stock respond error_msg:' + rs.error_msg) while (rs.error_code == '0') & rs.next(): # 获取一条记录,将记录合并在一起 stock = rs.get_row_data() data_list.append(stock) # print (type(stock),stock) # result = pd.DataFrame(data_list, columns=rs.fields) return data_list
def download_data(date): file_path = os.getcwd() + '\\result.csv' # 获取指定日期的指数、股票数据 stock_rs = bs.query_all_stock(date) stock_df = stock_rs.get_data() data_df = pd.DataFrame() for code in stock_df["code"]: k_rs = bs.query_history_k_data_plus(code, "date,code,open,high,low,close", date, date) data_df = data_df.append(k_rs.get_data()) data_df.to_csv(file_path) return data_df
def download_data(date): bs.login() # 获取指定日期的指数、股票数据 stock_rs = bs.query_all_stock(date) stock_df = stock_rs.get_data() # data_df = pd.DataFrame() # for code in stock_df["code"]: # print("Downloading :" + code) # k_rs = bs.query_history_k_data_plus(code, "date,code,open,high,low,close", date, date) # data_df = data_df.append(k_rs.get_data()) bs.logout() stock_df.to_csv("allstock.csv", encoding="gbk", index=False)
def query_all_stock(self, day = None): ''' day:需要查询的交易日期,为空时默认当前日期 ''' rs = bs.query_all_stock(day=day) self.log('query_all_stock respond error_msg:', rs) data_list = [] while (rs.error_code == '0') & rs.next(): # 获取一条记录,将记录合并在一起 data_list.append(rs.get_row_data()) result = pd.DataFrame(data_list, columns=rs.fields) return result
def update_stk_list(stk_type, out_file, date=None): # 获取指定日期的指数、股票数据 stock_df = bs.query_all_stock(date).get_data() if 0 == len(stock_df): if date is not None: print('当前选择日期为非交易日或尚无交易数据,请设置date为历史某交易日日期') sys.exit(0) delta = 1 while 0 == len(stock_df): stock_df = bs.query_all_stock(datetime.date.today() - datetime.timedelta( days=delta)).get_data() delta += 1 stock_df.drop(stock_df[(stock_df.code < 'sh.600000') | (stock_df.code > 'sz.399000')].index, inplace=True) # 所有股票 if '' == stk_type or 'all' == stk_type: pass # 主板 elif 'main' == stk_type: stock_df.drop(stock_df[stock_df.code > 'sh.688000'].index, inplace=True) # 科创板 elif 'star' == stk_type: stock_df.drop(stock_df[(stock_df.code < 'sh.688000') | (stock_df.code > 'sz.000000')].index, inplace=True) # 中小板 elif 'ms' == stk_type: stock_df.drop(stock_df[(stock_df.code < 'sz.000000') | (stock_df.code > 'sz.300000')].index, inplace=True) # 创业板 elif 'gem' == stk_type: stock_df.drop(stock_df[stock_df.code < 'sz.300000'].index, inplace=True) stock_df['code'].to_csv(out_file, encoding='gbk', index=False)
def download_data(date): bs.login() # 获取指定日期的指数、股票数据 stock_rs = bs.query_all_stock(date) stock_df = stock_rs.get_data() data_df = pd.DataFrame() for code in stock_df["code"]: print("Downloading :" + code) k_rs = bs.query_history_k_data_plus(code, "date,code,open,high,low,close", date, date) data_df = data_df.append(k_rs.get_data()) bs.logout() data_df.to_csv("./data/demo_assignDayData.csv", encoding="utf-8", index=False) print(data_df)
def download_data_by_day(date): # 获取指定日期的指数、股票数据 stock_rs = bs.query_all_stock(date) stock_df = stock_rs.get_data() data_df = pd.DataFrame() # stock_names= [] for code in stock_df["code"]: if code.startswith( 'sz.300' ): #or code.startswith('sz.00') or code.startswith('sh.60'): print("Downloading :" + code + '...') k_rs = get_history_k_data(code, date) data_df = data_df.append(k_rs.get_data()) return data_df
def getStockListFromBaoStock(inDate=getTodayDate()): rs = bs.query_all_stock(getLatestTradeDay(inDate)) if rs.error_code != '0': print('获取股票列表失败 : %s' % rs.error_msg) return data_list = [] while (rs.error_code == '0') & rs.next(): data_list.append(rs.get_row_data()) rtnList = [] for i in data_list: if i[1] != '1' or 'sh.00' in i[0] or 'sz.39' in i[0]: continue rtnList.append(i[0][3:]) return rtnList
def update_pool2redis(): # 登陆系统 #### lg = bs.login() # 显示登陆返回信息 print('login respond error_code:' + lg.error_code + ', error_msg:' + lg.error_msg) dt = utils.get_recently_trade_date() stock_rs = bs.query_all_stock(day=dt) stock_df = stock_rs.get_data() # print(stock_df) stocks = stock_df.set_index('code').T.to_dict(orient='list') sJsonStr = json.dumps(stocks, indent=4, ensure_ascii=False).encode('utf-8') r = redisUtils.set("Ashare", sJsonStr) print("update Ashare to Redis, status:", r) bs.logout()
def query_all_stock(self): """ 证券代码查询:query_all_stock() 方法说明:查询证券代码及股票交易状态信息信息,可以通过参数‘某交易日’获取数据(包括:A股、指数),提供2014-今数据。 返回类型:pandas的DataFrame类型。 """ trading_days = pd.read_csv(self.path + "trade_dates.csv") trading_days = trading_days[trading_days.is_trading_day == 1] #print(trading_days) #### 登陆系统 #### lg = bs.login() # 显示登陆返回信息 #print('login respond error_code:'+lg.error_code) #print('login respond error_msg:'+lg.error_msg) result_all = pd.DataFrame([]) for day in trading_days.calendar_date: if day >= self.start_date: #### 获取证券信息 #### rs = bs.query_all_stock(day=day) #print('query_all_stock respond error_code:'+rs.error_code) #print('query_all_stock respond error_msg:'+rs.error_msg) #### 打印结果集 #### data_list = [] while (rs.error_code == '0') & rs.next(): # 获取一条记录,将记录合并在一起 data_list.append(rs.get_row_data()) result = pd.DataFrame(data_list, columns=rs.fields) #result.drop_duplicates(subset=['code'], keep='first', inplace=True) result['date'] = day result_all = result_all.append(result) #### 结果集输出到csv文件 #### all_stock = pd.read_csv(self.path + "all_stock.csv") result_all = result_all.append(all_stock) result_all.drop_duplicates(subset=['code', 'tradeStatus', 'date'], keep='first', inplace=True) result_all.to_csv(self.path + "all_stock.csv", encoding="gbk", index=False) #print(result_all) #### 登出系统 #### bs.logout() return
def download_data(date): bs.login() # 获取指定日期的指数、股票数据 stock_rs = bs.query_all_stock(date) stock_df = stock_rs.get_data() data_df = pd.DataFrame() for i, row in stock_df.iterrows(): print(row['code'], row['code_name'], row['tradeStatus']) return for code in stock_df["code"]: print("Downloading :" + code) k_rs = bs.query_history_k_data_plus(code, "date,code,open,high,low,close,volume,amount,pctChg", date, date) data_df = data_df.append(k_rs.get_data()) bs.logout() data_df.to_csv("./demo_assignDayData.csv", encoding="gbk", index=False) print(data_df)
def stocks_list(date = today): stocks = bs.query_all_stock(date) stocks_data = stocks.get_data() stocks_data['isIndex'] = stocks_data['code'].str.contains('sh.000|sz.399|sh.688') stocks_data['isSt'] = stocks_data['code_name'].str.contains('/*ST|ST') print('test') print(stocks_data.info()) stocks_data.drop(stocks_data[stocks_data['isIndex']==True].index,inplace=True) stocks_data.drop(stocks_data[stocks_data['isSt']==True].index,inplace=True) stocks_data.to_csv(stocks_init_path) print('Downloading:' + code)
def get_all_data(): # 获取沪深300和中证500成分股 rs_all = bs.query_all_stock(day="2020-07-30") print('query_all error_code:' + rs_all.error_code) print('query_all error_msg:' + rs_all.error_msg) # 打印结果集 all_stocks = [] while (rs_all.error_code == '0') & rs_all.next(): # 获取一条记录,将记录合并在一起 all_stocks.append(rs_all.get_row_data()) stocks_result = pd.DataFrame(all_stocks, columns=rs_all.fields) # stocks_result.to_csv("./stocks-pool/all_stocks.csv", encoding="gbk", index=False) return stocks_result