def get_mktsymbol_list(): """get a list of symbols to lookup on AlphaVantage""" tbl = 'stockindex_app_stock' stock = SqlConnection(tbl) stmt = select([stock.table.c.marketsymbol]).where(stock.table.c.inactive == 0) df = stock.select_query(stmt) symbol_list = df['marketsymbol'].tolist() return symbol_list
def get_stock_fk(self): """get the foreign keys for each symbol in the observations DataFrame""" tbl = 'stockindex_app_stock' stock = SqlConnection(tbl) stmt = select([stock.table.c.marketsymbol, stock.table.c.id]).where(stock.table.c.inactive == 0) df_symbol = stock.select_query(stmt) self.df = pd.merge(df_symbol, self.df, on='marketsymbol') self.df.drop(columns='marketsymbol', inplace=True) self.df.rename(columns={'id': 'stock_id'}, inplace=True)
def update_52_week_highlow(self): """compute the 52 week high/low price of a stock""" tbl = 'stockindex_app_observations' observation = SqlConnection(tbl) highest_price = func.max(observation.table.c.high_price).label('high_price_52_weeks') lowest_price = func.min(observation.table.c.low_price).label('low_price_52_weeks') stmt = select([observation.table.c.stock_id, highest_price, lowest_price]) stmt = stmt.where(observation.table.c.observation_date >= datetime.datetime.now()+datetime.timedelta(weeks=-52)) stmt = stmt.group_by(observation.table.c.stock_id) df_highlow = observation.select_query(stmt) self.df.drop(['high_price_52_weeks', 'low_price_52_weeks'], axis=1, inplace=True) self.df = pd.merge(self.df, df_highlow, how='inner', left_on='id', right_on='stock_id') self.df.drop('stock_id', axis=1, inplace=True)
def update_52_week_highlow(self): """compute the 52 week high/low value of an index""" tbl = 'stockindex_app_indexobservations' index_obs = SqlConnection(tbl) highest_value = func.max(index_obs.table.c.high_value).label('high_value_52_weeks') lowest_value = func.min(index_obs.table.c.low_value).label('low_value_52_weeks') stmt = select([index_obs.table.c.index_id, highest_value, lowest_value]) stmt = stmt.where(index_obs.table.c.observation_date >= datetime.datetime.now()+datetime.timedelta(weeks=-52)) df_index_obs = index_obs.select_query(stmt) self.df.drop(['high_value_52_weeks', 'low_value_52_weeks'], axis=1, inplace=True) self.df = pd.merge(self.df, df_index_obs, how='inner', left_on='id', right_on='index_id') self.df.rename(columns={'close_value': 'current_value'}, inplace=True) self.df = self.df[['id', 'current_value', 'prior_close_value', 'change_value', 'high_value', 'low_value', 'high_value_52_weeks', 'low_value_52_weeks']]
def write(self): """update the index information in the SQL database""" tbl = 'stockindex_app_index' index = SqlConnection(tbl) values_list = [x for x in self.df.T.to_dict().values()] for value in values_list: stmt = update(index.table).values( current_value=value['current_value'], prior_close_value=value['prior_close_value'], change_value=value['change_value'], high_value=value['high_value'], low_value=value['low_value'], high_value_52_weeks=value['high_value_52_weeks'], low_value_52_weeks=value['low_value_52_weeks'], ).where( index.table.c.id == value['id'] ) index.update_query(stmt)
def write(self): """update the stock information in the SQL database""" tbl = 'stockindex_app_stock' stock = SqlConnection(tbl) values_list = [x for x in self.df.T.to_dict().values()] for value in values_list: stmt = update(stock.table).values( current_price=value['current_price'], prior_close_price=value['prior_close_price'], change_price=value['change_price'], market_cap=value['market_cap'], high_market_cap=value['high_market_cap'], low_market_cap=value['low_market_cap'], high_price=value['high_price'], low_price=value['low_price'], high_price_52_weeks=value['high_price_52_weeks'], low_price_52_weeks=value['low_price_52_weeks'], shares_outstanding=value['shares_outstanding'] ).where( stock.table.c.id == value['id'] ) stock.update_query(stmt)
def get_observation(self): """update the current, prior, high, low and change value of the index""" tbl = 'stockindex_app_stocksindexed' stocksindexed = SqlConnection(tbl) stmt = select([stocksindexed.table]) df_stocksindexed = stocksindexed.select_query(stmt) df_update = pd.merge(self.df, df_stocksindexed, how='inner', left_on='id', right_on='index_id') df_update = df_update[['index_id', 'stock_id']] tbl = 'stockindex_app_stock' stock = SqlConnection(tbl) stmt = select([stock.table.c.id, stock.table.c.marketsymbol, stock.table.c.market_cap, stock.table.c.high_market_cap, stock.table.c.low_market_cap, ]).where(stock.table.c.inactive == 0) df_stock = stock.select_query(stmt) df_update = pd.merge(df_update, df_stock, how='inner', left_on='stock_id', right_on='id') df_update.drop(['stock_id', 'id'], axis=1, inplace=True) df_update['market_cap'] = (df_update['market_cap']/1000000).astype('int64') df_update['high_market_cap'] = (df_update['high_market_cap'] / 1000000).astype('int64') df_update['low_market_cap'] = (df_update['low_market_cap'] / 1000000).astype('int64') df_update = df_update.groupby('index_id')['market_cap', 'high_market_cap', 'low_market_cap'].sum() df_update.rename(columns={'market_cap': 'current_value', 'high_market_cap': 'high_value', 'low_market_cap': 'low_value'}, inplace=True) self.df['open_value'] = self.df['current_value'] self.df.drop(['current_value', 'high_value', 'low_value'], axis=1, inplace=True) self.df = pd.merge(self.df, df_update, how='inner', left_on='id', right_on='index_id') self.df['observation_date'] = datetime.datetime.strptime( (datetime.datetime.now() + datetime.timedelta(days=-1)).strftime('%Y-%m-%d'), '%Y-%m-%d') self.df.rename(columns={'id': 'index_id', 'current_value': 'close_value'}, inplace=True) self.df = self.df[['observation_date', 'open_value', 'high_value', 'low_value', 'close_value', 'index_id']]
def __init__(self): tbl = 'stockindex_app_stock' stock = SqlConnection(tbl) stmt = select([stock.table]).where(stock.table.c.inactive == 0) self.df = stock.select_query(stmt)
def write(self): """insert new observations into the SQL database""" tbl = 'stockindex_app_observations' observation = SqlConnection(tbl) stmt = insert(observation.table) observation.insert_query(stmt, self.df)
def __init__(self): tbl = 'stockindex_app_index' index = SqlConnection(tbl) stmt = select([index.table]).where(index.table.c.inactive == 0) self.df = index.select_query(stmt)