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