def get_remote_data(self):
if not path.exists('static'):
os.mkdir('static')
wiret_path = path.join('static', f'{self.__symbol}_history.csv')
if(path.exists(wiret_path)):
# fetch file every day
if (time.time() - path.getmtime(wiret_path)) <= float(24*60*60):
with open(wiret_path) as f:
print(f'fetch local data = {self.__symbol}_history.csv')
stock_us_df = pd.read_csv(
f, index_col='date', parse_dates=True)
return stock_us_df
try:
stock_us_df = ak.stock_us_daily(symbol=self.__symbol)
stock_us_df.to_csv(wiret_path)
print('fetch remote data = akshare')
return stock_us_df
except KeyError as e:
return None
def extract_stock_records(code='sh600000', start=None, end =None,adjust = 'qfq', identifier = 'a'):
#qfq:前复权, hfq:后复权
if identifier == 'a':
result = ak.stock_zh_a_daily(symbol=code, adjust=adjust)
elif identifier == 'hk':
result = ak.stock_hk_daily(symbol=code, adjust=adjust)
elif identifier == 'us':
result = ak.stock_us_daily(symbol=code, adjust=adjust)
result.index = result.index.tz_localize(None)
result['date'] = result.index
result = result[['date','open','close','high','low','volume']]
result['total'] = 'NaN'
result['amp'] = 'NaN'
result.columns = ['date','open_price','close_price','high_price','low_price','trancaction','total','amp']
result['rate'] = pd.Series([0]).append(pd.Series((result['close_price'].iloc[1:].values-result['close_price'].iloc[0:-1].values)/result['close_price'].iloc[0:-1].values)).values*100
if start==None and end==None:
pass
else:
result = result[(result['date']<=pd.Timestamp(end)) & (result['date']>=pd.Timestamp(start))]
result['date'] = result['date'].apply(lambda x: (x.date()-datetime.date(1, 1, 1)).days)
result.index = result.index.rename('')
result = result.reset_index()
return result[['date','open_price','close_price','low_price','high_price','trancaction','total','rate','amp']]
def save_stock_daily(symbol, save_path):
info = ak.stock_us_daily(symbol)
info.to_csv(osp.join(save_path, f"{symbol}.csv"))
def main():
db_conn1 = sqlite3.connect(
r'D:\PythonCodes\vnpy\.vntrader\symb_list.db'
) # Connect to a database that hosts all stock symbols (i.e. corporate database)
db_conn2 = sqlite3.connect(
r'D:\PythonCodes\vnpy\.vntrader\database.db'
) # Connect to the main database that host all stock data (i.e. bar database)
# dict_example = {'name':['Microsoft Corp.'],
# 'cname': ['微软公司'],
# 'symbol':['MSFT']}
# dict_example = {'name':['Microsoft Corp.','Apple Company'],
# 'cname': ['微软公司', '苹果公司'],
# 'symbol':['MSFT', 'AAPL']}
# dict_example = {'name':['Microsoft Corp.','Apple Company', 'Facebook'],
# 'cname': ['微软公司', '苹果公司', '脸书'],
# 'symbol':['MSFT', 'AAPL', 'FB']}
# dict_example = {'name':['Microsoft Corp.','Apple Company', 'Facebook', 'Amazon'],
# 'cname': ['微软公司', '苹果公司', '脸书','亚马逊'],
# 'symbol':['MSFT', 'AAPL', 'FB', 'AMZN']}
# df_example = pd.DataFrame.from_dict(dict_example)
# df_example.to_csv(Path.cwd().joinpath('temp.csv'), encoding='utf_8_sig', index=False)
# df_example = pd.read_csv(Path.cwd().joinpath('temp.csv'), encoding='utf_8_sig')
# df_allstocks = ak.get_us_stock_name() # Download all stock symbols using AkShare service. Expect to run this line and update stock symbols periodically.
# df_allstocks.to_csv(Path.cwd().joinpath('temp.csv'), encoding='utf_8_sig', index=False) # Save all stock symbols to a csv file. This is for testing purpose.
df_example = pd.read_csv(
Path.cwd().joinpath('temp.csv'), encoding='utf_8_sig'
) # Load all stock symbols from the csv file. This is for testing purpose.
# df_example = df_example.iloc[0:2, :] # Only take a few lines for testing. This is for testing purpose.
df_example.to_sql(
"dbcorpdata", db_conn1,
if_exists='replace') # Save all stock symbols to corporate database
df_corpsdata_dl = pd.read_sql_query(
"SELECT * from dbcorpdata",
db_conn1) # Load all stock symbols from corporate database
df_bardata_ex = pd.read_sql_query(
"SELECT * from dbbardata",
db_conn2) # Load all existing stock data from bardata database
totalSymbol = len(df_corpsdata_dl.symbol)
procSymbol = 0
forcedFullLoad = False
for s in df_corpsdata_dl.symbol: # For each symbol read from the corporate database
try:
procSymbol += 1
progress(procSymbol, totalSymbol)
bars = []
bar_latestBarOneStock_ex = database_manager.get_newest_bar_data(
s, Exchange.LOCAL, Interval.DAILY
) # Find the latest bar record for that symbol from the bar database
df_allBarOneStock_dl = ak.stock_us_daily(
symbol=s, adjust="qfq"
).fillna(method='ffill').fillna(
0
) # Download the history data for that symbol using AkShare service.
# Fill NaN or Null fields with previous value, and then zero.
if (
(bar_latestBarOneStock_ex is not None) and (~forcedFullLoad)
): # If the bar database contains this symbol, and not full load we will decide if incremental data will be needed or full history data will be saved to bar database
lastDayDate_ex = bar_latestBarOneStock_ex.datetime.replace(
tzinfo=None
) # VNPY datetime is aware type, but the AkShare datetime is unaware type
latestDayDate_dl = df_allBarOneStock_dl.index[
-1] # Be careful of the difference between last day and latest date.
dailyDataMatched = comp_bar(
bar_latestBarOneStock_ex, df_allBarOneStock_dl
) # This is a simplified logic check to compare the OHLC prices and see if they are all equal.
if dailyDataMatched: # If the close prices from existing and new sources match, we assume data remain correct and will only incrementally update
time_range = DateTimeRange(
lastDayDate_ex, latestDayDate_dl
) # Find the date range for incremental update
for dt in time_range.range(timedelta(days=1)):
# print(dt)
if dt == latestDayDate_dl: # When last date equals latest date, there is still a day in the date range, and we need to break the loop
# print('I am going to break...')
break
bar = BarData(
symbol=s,
exchange=Exchange.LOCAL,
datetime=dt, # Here dt is a native datetime object
interval=Interval.DAILY,
volume=df_allBarOneStock_dl.loc[dt].volume,
open_price=df_allBarOneStock_dl.loc[dt].open,
high_price=df_allBarOneStock_dl.loc[dt].high,
low_price=df_allBarOneStock_dl.loc[dt].low,
close_price=df_allBarOneStock_dl.loc[dt].close,
open_interest=0,
gateway_name='Sim')
bars.append(bar)
# print('only add incremental updates for '+s)
else: # If the close prices from existing and new sources do not match, we assume data are corrupted and will fully update
for i, dt in enumerate(df_allBarOneStock_dl.index):
bar = BarData(
symbol=s,
exchange=Exchange.LOCAL,
datetime=dt.to_pydatetime(
), # Convert to a datetime object
interval=Interval.DAILY,
volume=df_allBarOneStock_dl.loc[dt].volume,
open_price=df_allBarOneStock_dl.loc[dt].open,
high_price=df_allBarOneStock_dl.loc[dt].high,
low_price=df_allBarOneStock_dl.loc[dt].low,
close_price=df_allBarOneStock_dl.loc[dt].close,
open_interest=0,
gateway_name='Sim')
bars.append(bar)
# print('correct database data for '+s)
else: # If bar database does not have this symbol, or just want to force full load, we will fully update
for i, dt in enumerate(df_allBarOneStock_dl.index):
bar = BarData(
symbol=s,
exchange=Exchange.LOCAL,
datetime=dt.to_pydatetime(
), # Convert to a datetime object
interval=Interval.DAILY,
volume=df_allBarOneStock_dl.loc[dt].volume,
open_price=df_allBarOneStock_dl.loc[dt].open,
high_price=df_allBarOneStock_dl.loc[dt].high,
low_price=df_allBarOneStock_dl.loc[dt].low,
close_price=df_allBarOneStock_dl.loc[dt].close,
open_interest=0,
gateway_name='Sim')
bars.append(bar)
# print('reload data for '+s)
database_manager.save_bar_data(
bars) # Push the updates to the bar database
print("Saved stock data of " + s + " into database.")
except: # When exceptoin occurs, assume it is because database buffer full, reconnect databases.
time.sleep(5)
print('Exception detected. Now reconnect to the databases.')
db_conn1.close()
db_conn2.close()
db_conn1 = sqlite3.connect(
r'D:\PythonCodes\vnpy\.vntrader\symb_list.db')
db_conn2 = sqlite3.connect(
r'D:\PythonCodes\vnpy\.vntrader\database.db')
time.sleep(5)
db_conn1.close() # When done with the database, close the connection
db_conn2.close() # When done with the database, close the connection
def test(self):
stock_us_df = ak.stock_us_daily(symbol='AAPL')
def get_factor_data(self):
qfq_df = ak.stock_us_daily(symbol=self.__symbol, factor="qfq")
print(qfq_df)
def get_stock_us_daily(self, stockcode, adjust):
stock_us_daily_df = ak.stock_us_daily(symbol=stockcode, adjust=adjust)
return stock_us_daily_df
def get_one_us_stock(self, symbol):
stock_us_daily_df = ak.stock_us_daily(symbol)
print(stock_us_daily_df)
def update_us_all_stock_point_day(self, symbol_str, oneday_str):
'''
更新每一只美股指定的某一天历史行情
:return:
'''
start_time = datetime.now()
print("update_us_all_stock_point_day 更新每一只美股指定的某一天历史行情 start_time:",
start_time)
# 获取所有美股代码字符串
stock_exist = self.get_us_stock_exist()
stock_exist_list = stock_exist.split(',')
stock_list = symbol_str.split(",")
df_list_tuple = []
i = 0
for symbol in stock_exist_list:
flag = True
if symbol_str != "" and symbol not in stock_list:
flag = False
if flag:
#print("symbol_str", symbol_str)
#print("symbol", symbol, "flag", flag)
try:
stock_us_daily_df = ak.stock_us_daily(symbol=symbol)
# print(stock_us_daily_df, type(stock_us_daily_df))
except Exception as e:
stock_us_daily_df = None
print(" stock_us_daily_df exception, reason:", e)
df_list_list = stock_us_daily_df.values.__array__(
) if stock_us_daily_df is not None else []
df_date_list = stock_us_daily_df.axes[
0].array if stock_us_daily_df is not None else []
#print(tuple(df_list_list))
#print(df_date_list)
count = len(df_date_list)
if count > 0:
oneday_list = oneday_str.split(',')
for oneday in oneday_list:
# print('df_date_list', tuple(df_date_list))
# print('count', count)
last_date = datetime.date(df_date_list[count - 1])
#print('last_date', last_date)
onedate = self.exchange_oneday_to_date(oneday)
#print('onedate', onedate)
days = self.days_reduce(last_date, onedate)
#print('days', days)
if days >= 0 and count > days:
onedate_index = count - 1 - days
#print('onedate_index', onedate_index)
ondedate_lt = df_list_list[onedate_index]
# print('ondedate_lt', tuple(ondedate_lt))
df_list_tuple.append(
(symbol, onedate, float(ondedate_lt[0]),
float(ondedate_lt[1]), float(ondedate_lt[2]),
float(ondedate_lt[3]), float(ondedate_lt[4])))
print(i, symbol, onedate, float(ondedate_lt[0]),
float(ondedate_lt[1]), float(ondedate_lt[2]),
float(ondedate_lt[3]), float(ondedate_lt[4]))
i += 1
if i % 500 == 0:
df_tuple_tuple = tuple(df_list_tuple)
flag = False
try:
flag = self.us_stock_service.insert_all_stock_daily_batch(
df_tuple_tuple)
df_list_tuple = []
print(oneday, flag, i)
except Exception as ex:
print(
"us_all_stock_point_day_update exception, reason:",
ex)
df_tuple_tuple = tuple(df_list_tuple)
flag = False
try:
flag = self.us_stock_service.insert_all_stock_daily_batch(
df_tuple_tuple)
print(oneday, flag, i)
except Exception as ex:
print("us_all_stock_point_day_update exception, reason:", ex)
end_time = datetime.now()
time = (end_time - start_time)
print("update_us_all_stock_point_day 更新每一只美股指定的某一天历史行情 end_time:",
end_time, "耗时:", time)
def get_us_all_stock_daily(self):
'''
获取每一只美股的所有历史行情，不包含今天
:return:
'''
start_time = datetime.now()
print("get_us_all_stock_daily 获取每一只美股的所有历史行情，不包含今天 start_time:",
start_time)
# 获取所有美股代码字符串
stock_exist = self.get_us_stock_exist()
stock_exist_list = stock_exist.split(',')
# 获取历史行情的所有美股代码字符串
all_stock_daily_exist = self.get_us_all_stock_daily_exist()
us_all_stock_daily_str = all_stock_daily_exist
us_all_stock_daily_count = 0
for symbol in stock_exist_list:
# if symbol > '01858' and symbol < '02226':
# flag = us_stock_service.test(symbol)
# print(symbol, flag)
# if symbol == 'FFR':
if all_stock_daily_exist.find(symbol) < 0:
# print(symbol)
# 根据港股代码获取某一只美股的所有历史行情
try:
stock_us_daily_df = ak.stock_us_daily(symbol=symbol)
# print(stock_us_daily_df, type(stock_us_daily_df))
except Exception as e:
stock_us_daily_df = None
print(" stock_hk_daily_hfq_df exception, reason:", e)
df_list_list = stock_us_daily_df.values.__array__(
) if stock_us_daily_df is not None else []
df_date_list = stock_us_daily_df.axes[
0].array if stock_us_daily_df is not None else []
df_list_tuple = []
for i in range(len(df_list_list)):
lt = df_list_list[i]
# print(tuple(lt))
date = datetime.date(df_date_list[i])
# print(bool(np.isnan(lt[0])))
# print(type(np.isnan(lt[0])))
if np.isnan(lt[0]) == False:
df_list_tuple.append(
(date, float(lt[0]), float(lt[1]), float(lt[2]),
float(lt[3]), float(lt[4])))
# print(df_list_tuple)
df_tuple_tuple = tuple(df_list_tuple)
# print(df_tuple_tuple)
flag = False
try:
flag = self.us_stock_service.insert_one_stock_all_daily_batch(
symbol, df_tuple_tuple)
print(symbol, flag)
except Exception as ex:
print("insert_stock_daily_batch exception, reason:", ex)
if flag is True:
us_all_stock_daily_count += 1
us_all_stock_daily_str += symbol + ","
# 更新港股历史行情所有港股代码字符串
self.update_us_stock_daily_exist(us_all_stock_daily_str,
'us_stock_daily',
us_all_stock_daily_count)
end_time = datetime.now()
time = (end_time - start_time)
print("get_us_all_stock_daily 获取每一只美股的所有历史行情，不包含今天 end_time:",
end_time, "耗时:", time)