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
