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 populate_db():
session = Session()
categories = [
'gold',
'toman',
]
session.add_all([Category(name=name) for name in categories])
session.commit()