def get_single_stock_data(root_path, symbol, dates_range):
df, lastUpdateTime = queryStock(root_path, "DB_STOCK", "SHEET_CHN_DAILY",
symbol)
if df.empty: return pd.DataFrame()
df.index = pd.to_datetime(df.index)
df = df[df.index.isin(dates_range)].sort_index()
df.loc[:, 'Close_Shift_1'] = df.loc[:, 'adj_close'].shift(1)
df.loc[:,
'Return'] = df.loc[:, 'adj_close'] / df.loc[:, 'Close_Shift_1'] - 1
return df
def updateSingleStockData(root_path, symbol, force_check):
startTime = time.time()
message = ""
if len(symbol) == 0: return startTime, message
till_date = (datetime.datetime.now()).strftime("%Y-%m-%d")
end_date = pd.Timestamp(till_date)
stockData, lastUpdateTime = queryStock(root_path, "DB_STOCK",
"SHEET_CHN_DAILY", symbol)
if stockData.empty:
stockData, message = getSingleStock(symbol)
if stockData.empty == False:
storeStock(root_path, "DB_STOCK", "SHEET_CHN_DAILY", symbol,
stockData)
return startTime, message
modified = False
first_date = pd.Timestamp(stockData.index[0])
last_date = pd.Timestamp(stockData.index[-1])
updateOnce = end_date > lastUpdateTime
if end_date > last_date and (updateOnce or force_check):
to_date = (last_date + datetime.timedelta(days=1)).strftime("%Y-%m-%d")
if judgeNeedPostDownload(to_date, till_date):
message = message + ", download post data from " + to_date + " to " + till_date
moreStockData, tempMessage = getSingleStockByTime(
symbol, to_date, till_date)
message = message + tempMessage
if len(moreStockData) > 0:
if isinstance(moreStockData.index, pd.DatetimeIndex):
moreStockData.index = moreStockData.index.strftime(
"%Y-%m-%d")
modified = True
stockData = pd.concat([stockData, moreStockData])
stockData.index.name = 'date'
if modified:
stockData = stockData[~stockData.index.duplicated(keep='first')]
storeStock(root_path, "DB_STOCK", "SHEET_CHN_DAILY", symbol, stockData)
elif updateOnce:
stockData = stockData[~stockData.index.duplicated(keep='first')]
storeStock(root_path, "DB_STOCK", "SHEET_CHN_DAILY", symbol, stockData)
message = message + ", nothing updated"
else:
message = ""
return startTime, message
def get_single_stock_data(root_path, symbol, dates_range):
df, lastUpdateTime = queryStock(root_path, "DB_STOCK", "SHEET_US_DAILY",
symbol)
if df.empty: return pd.DataFrame()
df.index = pd.to_datetime(df.index)
#df = pd.read_csv(filename, index_col=["Date"], parse_dates=['Date'], usecols=['Date', 'Adj Close'])
df = df[df.index.isin(dates_range)].sort_index()
df.loc[:, 'Close_Shift_1'] = df.loc[:, 'Adj Close'].shift(1)
df.loc[:,
'Return'] = df.loc[:, 'Adj Close'] / df.loc[:, 'Close_Shift_1'] - 1
return df
def get_single_stock_data_weekly(root_path, symbol):
'''
All data is from quandl wiki dataset
Feature set: [Open High Low Close Volume Ex-Dividend Split Ratio Adj. Open Adj. High Adj. Low
Adj. Close Adj. Volume]
'''
df, lastUpdateTime = queryStock(root_path, "DB_STOCK", "SHEET_CHN", "_WEEKLY", symbol, "weekly_update")
df.index = pd.to_datetime(df.index)
if df.empty:
return df
return df
def get_single_stock_data(root_path, symbol):
'''
All data is from quandl wiki dataset
Feature set: [Open High Low Close Volume Ex-Dividend Split Ratio Adj. Open Adj. High Adj. Low
Adj. Close Adj. Volume]
'''
# file_name = stock_folder + ticker + '.csv'
# COLUMNS = ['Date', 'Open', 'High', 'Low', 'Close', 'Volume']
# RENAME_COLUMNS = ['date', 'open', 'high', 'low', 'close', 'volume']
# if os.path.exists(file_name) == False:
# print("get stock: " + ticker + " failed")
# return pd.DataFrame()
# df = pd.read_csv(
# file_name,
# #names=COLUMNS,
# skipinitialspace=True,
# engine='python',
# index_col=['Date'],
# #usecols=COLUMNS,
# parse_dates=['Date'],
# #skiprows=1,
# memory_map=True,
# #chunksize=300,
# ).sort_index()
df, lastUpdateTime = queryStock(root_path, "DB_STOCK", "SHEET_CHN",
"_DAILY", symbol, "daily_update")
df.index = pd.to_datetime(df.index)
suspended_day = pd.Timestamp(
(datetime.datetime.now() -
datetime.timedelta(days=3)).strftime("%Y-%m-%d"))
if df.empty:
#print("stock delisted", symbol)
return df
if df.index[-1] < suspended_day:
#print("stock suspended", symbol)
return pd.DataFrame()
if 'adj_close' in df:
df = df.drop('close', 1)
df = df.rename(columns={'adj_close': 'close'})
return df
def get_single_stock_data_monthly(root_path, symbol):
'''
All data is from quandl wiki dataset
Feature set: [Open High Low Close Volume Ex-Dividend Split Ratio Adj. Open Adj. High Adj. Low
Adj. Close Adj. Volume]
'''
df, lastUpdateTime = queryStock(root_path, "DB_STOCK", "SHEET_US", "_MONTHLY", symbol, "monthly_update")
df.index = pd.to_datetime(df.index)
if df.empty:
print("monthly empty df", symbol)
return df
if 'adj_close' in df:
df = df.drop('close', 1)
df = df.rename(columns = {'adj_close':'close'})
return df
def get_single_stock_data_monthly(root_path, symbol):
'''
All data is from quandl wiki dataset
Feature set: [Open High Low Close Volume Ex-Dividend Split Ratio Adj. Open Adj. High Adj. Low
Adj. Close Adj. Volume]
'''
df, lastUpdateTime = queryStock(root_path, "DB_STOCK", "SHEET_CHN", "_MONTHLY", symbol, "monthly_update")
df.index = pd.to_datetime(df.index)
#suspended_day = pd.Timestamp((datetime.datetime.now() - datetime.timedelta(days=3)).strftime("%Y-%m-%d"))
if df.empty:
return df
return df
def get_single_stock_data(root_path, symbol):
'''
All data is from quandl wiki dataset
Feature set: [Open High Low Close Volume Ex-Dividend Split Ratio Adj. Open Adj. High Adj. Low
Adj. Close Adj. Volume]
'''
# file_name = stock_folder + ticker + '.csv'
# COLUMNS = ['Date', 'Open', 'High', 'Low', 'Close', 'Volume']
# RENAME_COLUMNS = ['date', 'open', 'high', 'low', 'close', 'volume']
# if os.path.exists(file_name) == False:
# print("get stock: " + ticker + " failed")
# return pd.DataFrame()
# df = pd.read_csv(
# file_name,
# #names=COLUMNS,
# skipinitialspace=True,
# engine='python',
# index_col=['Date'],
# #usecols=COLUMNS,
# parse_dates=['Date'],
# #skiprows=1,
# memory_map=True,
# #chunksize=300,
# ).sort_index()
df, lastUpdateTime = queryStock(root_path, "DB_STOCK", "SHEET_US_DAILY",
symbol)
df.index = pd.to_datetime(df.index)
if df.empty:
print("empty df", symbol)
return df
# if 'Adj Close' in df:
# close = 'Adj Close'
# else:
# close = 'Close'
#df=df.rename(columns = {'Date':'date', 'Open':'open', 'High':'high', 'Low':'low', close:'close', 'Volume':'volume'})
return df
def get_single_stock_data_weekly(root_path, symbol):
'''
All data is from quandl wiki dataset
Feature set: [Open High Low Close Volume Ex-Dividend Split Ratio Adj. Open Adj. High Adj. Low
Adj. Close Adj. Volume]
'''
df, lastUpdateTime = queryStock(root_path, "DB_STOCK", "SHEET_CHN",
"_WEEKLY", symbol, "weekly_update")
df.index = pd.to_datetime(df.index)
#suspended_day = pd.Timestamp((datetime.datetime.now() - datetime.timedelta(days=3)).strftime("%Y-%m-%d"))
if df.empty:
#print("stock delisted", symbol)
return df
# if df.index[-1] < suspended_day:
# #print("stock suspended", symbol)
# return pd.DataFrame()
return df
def get_data(symbols, dates_range, update):
df = pd.DataFrame()
symbols.append('SPY')
symbols.append('^VIX')
for symbol in symbols:
data, lastUpdateTime = queryStock(root_path, "DB_STOCK", "SHEET_US",
"_DAILY", symbol, 'daily_update')
data.index = pd.to_datetime(data.index)
if df.empty:
if 'close' in data:
df[symbol] = data['close']
else:
if 'close' in data:
df[symbol] = data['close']
df = df[df.index.isin(dates_range)].fillna(0).sort_index()
#print("NaN Columns:", df[df.isnull().any(axis=1)])
return df
def updateSingleStockData(root_path, symbol, from_date, till_date,
force_check):
startTime = time.time()
message = ""
if len(symbol) == 0: return startTime, message
now_date = pd.Timestamp((datetime.datetime.now()).strftime("%Y-%m-%d"))
start_date = pd.Timestamp(from_date)
end_date = pd.Timestamp(till_date)
if end_date == now_date:
end_date = end_date - datetime.timedelta(days=1)
stockData, lastUpdateTime = queryStock(root_path, "DB_STOCK", "SHEET_US",
"_WEEKLY", symbol, "weekly_update")
if stockData.empty:
stockData, message = getSingleStock(symbol, from_date, till_date)
if stockData.empty == False:
storeStock(root_path, "DB_STOCK", "SHEET_US", "_WEEKLY", symbol,
stockData, "weekly_update")
first_date = pd.Timestamp(stockData.index[0])
to_date = (first_date -
datetime.timedelta(days=1)).strftime("%Y-%m-%d")
if judgeNeedPreDownload(root_path, symbol, first_date, from_date,
to_date):
storePublishDay(root_path, "DB_STOCK", "SHEET_US", symbol,
first_date.strftime("%Y-%m-%d"))
message = message + ", database updated"
else:
print("get stock from network failed", symbol)
return startTime, message
modified = False
savePublishDay = False
first_date = pd.Timestamp(stockData.index[0])
last_date = pd.Timestamp(stockData.index[-1])
if start_date < first_date:
to_date = (first_date -
datetime.timedelta(days=1)).strftime("%Y-%m-%d")
if judgeNeedPreDownload(root_path, symbol, first_date, from_date,
to_date):
message = message + ", download pre data from " + from_date + " to " + to_date
moreStockData, tempMessage = getSingleStock(
symbol, from_date, to_date)
message = message + tempMessage
if len(moreStockData) > 0:
if isinstance(moreStockData.index, pd.DatetimeIndex):
moreStockData.index = moreStockData.index.strftime(
"%Y-%m-%d")
modified = True
stockData = pd.concat([moreStockData, stockData])
stockData.index.name = 'date'
else:
savePublishDay = True
storePublishDay(root_path, "DB_STOCK", "SHEET_US", symbol,
first_date.strftime("%Y-%m-%d"))
message = message + ", save stock publish(IPO) day, next time won't check it again"
updateOnce = now_date > lastUpdateTime
if (end_date > last_date) and (updateOnce or force_check):
to_date = (last_date + datetime.timedelta(days=1)).strftime("%Y-%m-%d")
if judgeNeedPostDownload(now_date, to_date, till_date):
message = message + ", download post data from " + to_date + " to " + till_date
moreStockData, tempMessage = getSingleStock(
symbol, to_date, till_date)
message = message + tempMessage
if len(moreStockData) > 0:
if isinstance(moreStockData.index, pd.DatetimeIndex):
moreStockData.index = moreStockData.index.strftime(
"%Y-%m-%d")
modified = True
stockData = pd.concat([stockData, moreStockData])
stockData.index.name = 'date'
if modified:
stockData = stockData[~stockData.index.duplicated(keep='first')]
storeStock(root_path, "DB_STOCK", "SHEET_US", "_WEEKLY", symbol,
stockData, "weekly_update")
elif updateOnce:
now_date = datetime.datetime.now().strftime("%Y-%m-%d")
stockList = queryStockList(root_path, "DB_STOCK", "SHEET_US")
if stockList[stockList.index == symbol]['daily_update'][0] != now_date:
stockList.set_value(symbol, 'weekly_update', now_date)
storeStockList(root_path, "DB_STOCK", "SHEET_US", stockList,
symbol)
elif savePublishDay == False:
message = ""
return startTime, message
