def backfill_returns(ticker_symbol, start, end):
"""
Looks for investment returns for the ticker between the given start and
end date. If some are present, checks the last time it was backfilled
to see if there is a gap. If so, backfills either of these gaps. If none
are present, backfills starting with the given start date.
@param [string] ticker_symbol Ticker symbol of the stock
@param [Date] start Start date of the range (inclusive) of desired data
@param [Date] end End date of the range (inclusive) of desired data
@raise [pandas_datareader._utils.RemoteDataError] If Yahoo API response is not 200
@raise [requests.exceptions.ConnectionError] If unable to connect to the Yahoo API
"""
session = Session()
query = session.query(InvestmentReturn).\
filter(InvestmentReturn.ticker_symbol == ticker_symbol).\
filter(InvestmentReturn.occurred_at >= start).\
filter(InvestmentReturn.occurred_at <= end)
if session.query(query.exists()).scalar():
max_occurred_at = session.query(func.max(InvestmentReturn.occurred_at)).filter(InvestmentReturn.ticker_symbol == ticker_symbol).scalar()
if max_occurred_at < end:
print(f'Ticker price data for ({ticker_symbol}, {max_occurred_at}, {end}) not found in the DB, backfilling it from the Yahoo API')
new_start = date(max_occurred_at.year, max_occurred_at.month, 1)
percentage_change_data = InvestmentReturns.get_percentage_change_data(ticker_symbol, new_start, end)
if percentage_change_data.empty:
print(f'WARNING: No new ticker price data found for ({ticker_symbol}, {max_occurred_at}, {end}) in the Yahoo API')
session.add_all([InvestmentReturn(**row) for _, row in percentage_change_data.iterrows()])
session.commit()
else:
print(f'Ticker price data for ({ticker_symbol}, {start}, {end}) not found in the DB, backfilling it from the Yahoo API')
one_month_ago = date.today() + pd.offsets.DateOffset(months=-1)
_, last_day = monthrange(one_month_ago.year, one_month_ago.month)
end = date(one_month_ago.year, one_month_ago.month, last_day)
percentage_change_data = InvestmentReturns.get_percentage_change_data(ticker_symbol, start, end)
session.add_all([InvestmentReturn(**row) for _, row in percentage_change_data.iterrows()])
session.commit()
def write(market_type_name, data_frame):
session = Session()
factor_returns = FactorReturn.query_by_market_type_name(market_type_name)
if not session.query(factor_returns.exists()).scalar():
market_type = session.query(MarketType).filter(MarketType.name == market_type_name).one()
data_frame['market_type_id'] = market_type.id
session.add_all([FactorReturn(**row) for _, row in data_frame.iterrows()])
session.commit()
def fetch(ticker_symbol, start, end):
"""
@param [string] ticker_symbol Ticker symbol of the stock
@param [Date] start Start date of the range (inclusive) of desired data
@param [Date] end End date of the range (inclusive) of desired data
@raise [pandas_datareader._utils.RemoteDataError] If Yahoo API response is not 200
@raise [requests.exceptions.ConnectionError] If unable to connect to the Yahoo API
@return [pandas.core.frame.DataFrame]
"""
session = Session()
query = session.query(InvestmentReturn).\
filter(InvestmentReturn.ticker_symbol == ticker_symbol).\
filter(InvestmentReturn.occurred_at >= start).\
filter(InvestmentReturn.occurred_at <= end)
return pd.read_sql(query.statement, query.session.bind)
def fetch(market_type_name, force_refresh=False):
"""
@param [String] market_type_name The market type, e.g. Emerging
@param [Boolean] force_refresh If True, will wipe data and reimport it.
@raise [???] If file can't be found
@raise [???] If the file isn't a CSV
@return [pandas.core.frame.DataFrame]
"""
session = Session()
# If force_refresh, delete the data so that the rest of the code here
# picks up on the need to re-download and write it.
if force_refresh:
FactorReturn.delete_by_market_type_name(market_type_name)
factor_returns = FactorReturn.query_by_market_type_name(market_type_name)
if not session.query(factor_returns.exists()).scalar():
FactorReturns.download_and_write_data()
return pd.read_sql(factor_returns.statement, factor_returns.session.bind)
def getUserCountWithMoreMessages(count):
ses = Session()
return ses.query(User).filter(User.message_count > count).count()
def getUsers(count=20, page=0):
ses = Session()
return ses.query(User).filter(User.message_count > 0).order_by(
desc(User.message_count)).offset(page * count).limit(count)
def get_all_assets():
session = Session()
return session.query(Asset).all()
def get_all_categories():
session = Session()
return session.query(Category).all()