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 save(self):
session = Session()
session.add(self)
try:
session.commit()
return True
except InvalidRequestError:
return False
def saveServer(serverId: str, channelId: str = None):
session = Session()
server = getServerById(serverId, session)
if server is None:
server = Server(id=serverId, role_reaction_channel_id=channelId)
session.add(server)
elif channelId is not None:
setattr(server, 'role_reaction_channel_id', channelId)
session.commit()
def populate_db():
session = Session()
categories = [
'gold',
'toman',
]
session.add_all([Category(name=name) for name in categories])
session.commit()
def saveKarmaReaction(serverId: str, reaction: str, change: int):
session = Session()
karmaReaction = getKarmaReactionByServerAndReaction(
serverId, reaction, session)
if karmaReaction is None:
karmaReaction = KarmaReaction(reaction=reaction,
server_id=serverId,
karmaChange=change)
session.add(karmaReaction)
else:
karmaReaction.karmaChange = change
session.commit()
def incUserMsgCount(user):
ses = Session()
dbUser = getUserById(str(user.id), ses)
if dbUser is None:
dbUser = User(message_count=1,
karma=0,
name=str(user),
id=str(user.id))
ses.add(dbUser)
else:
dbUser.message_count += 1
ses.commit()
def update_amount(category_id, amount, delta):
"""Updates asset amount and returns the new amount"""
session = Session()
asset = _get_or_create_asset(
session=session,
category_id=category_id,
)
if delta:
amount = asset.amount + amount
if amount < 0:
raise NotEnoughAssetAmount()
asset.amount = amount
if asset.amount == 0:
session.delete(asset)
session.commit()
def removeKarmaReaction(serverId: str, kr: str):
ses = Session()
kr = getKarmaReactionByServerAndReaction(serverId, kr, ses)
ses.delete(kr)
ses.commit()