def serialize_and_pack_portfolio_details(game_id: int, user_id: int):
out_dict = dict(data=[], headers=list(PORTFOLIO_DETAIL_MAPPINGS.values()))
balances = get_active_balances(game_id, user_id)
if balances.empty:
s3_cache.set(f"{game_id}/{user_id}/{CURRENT_BALANCES_PREFIX}", json.dumps(out_dict))
return
cash_balance = get_current_game_cash_balance(user_id, game_id)
symbols = balances["symbol"].unique()
prices = get_most_recent_prices(symbols)
df = balances.groupby("symbol", as_index=False).aggregate({"balance": "last", "clear_price": "last"})
df = df.merge(prices, on="symbol", how="left")
df["Value"] = df["balance"] * df["price"]
total_portfolio_value = df["Value"].sum() + cash_balance
df["Portfolio %"] = (df["Value"] / total_portfolio_value)
close_prices = get_last_close_prices(symbols)
df = df.merge(close_prices, how="left")
df["Change since last close"] = ((df["price"] - df["close_price"]) / df["close_price"])
del df["close_price"]
symbols_colors = assign_colors(symbols)
df["color"] = df["symbol"].apply(lambda x: symbols_colors[x])
df.rename(columns=PORTFOLIO_DETAIL_MAPPINGS, inplace=True)
df.fillna(NA_TEXT_SYMBOL, inplace=True)
records = df.to_dict(orient="records")
out_dict["data"] = records
s3_cache.set(f"{game_id}/{user_id}/{CURRENT_BALANCES_PREFIX}", json.dumps(out_dict))
def process_order(order_id: int):
timestamp = time.time()
if get_order_expiration_status(order_id):
add_row("order_status",
order_id=order_id,
timestamp=timestamp,
status="expired",
clear_price=None)
return
order_ticket = query_to_dict("SELECT * FROM orders WHERE id = %s",
order_id)[0]
symbol = order_ticket["symbol"]
game_id = order_ticket["game_id"]
user_id = order_ticket["user_id"]
buy_or_sell = order_ticket["buy_or_sell"]
quantity = order_ticket["quantity"]
order_type = order_ticket["order_type"]
market_price, _ = fetch_price(symbol)
# Only process active outstanding orders during trading day
cash_balance = get_current_game_cash_balance(user_id, game_id)
current_holding = get_current_stock_holding(user_id, game_id, symbol)
if during_trading_day():
if execute_order(buy_or_sell, order_type, market_price,
order_ticket["price"], cash_balance, current_holding,
quantity):
order_status_id = add_row("order_status",
order_id=order_id,
timestamp=timestamp,
status="fulfilled",
clear_price=market_price)
update_balances(user_id, game_id, order_status_id, timestamp,
buy_or_sell, cash_balance, current_holding,
market_price, quantity, symbol)
serialize_and_pack_pending_orders(
game_id, user_id) # refresh the pending orders table
add_fulfilled_order_entry(
game_id, user_id,
order_id) # add the new fulfilled orders entry to the table
serialize_and_pack_portfolio_details(game_id, user_id)
else:
# if a market order was placed after hours, there may not be enough cash on hand to clear it at the new
# market price. If this happens, cancel the order and recalculate the purchase quantity with the new price
if order_type == "market":
cancel_order(order_id)
updated_quantity = cash_balance // market_price
if updated_quantity <= 0:
return
place_order(user_id, game_id, symbol, buy_or_sell,
cash_balance, current_holding, order_type,
"Shares", market_price, updated_quantity,
order_ticket["time_in_force"])
serialize_and_pack_portfolio_details(game_id, user_id)
def add_virtual_cash(game_id: int, user_id: int, dividend_id: int,
amount: float):
current_cash = get_current_game_cash_balance(user_id, game_id) + amount
now = datetime_to_posix(dt.now())
add_row("game_balances",
user_id=user_id,
game_id=game_id,
timestamp=now,
balance_type='virtual_cash',
balance=current_cash,
dividend_id=dividend_id,
transaction_type="stock_dividend")
def get_user_portfolio_value(game_id: int, user_id: int, cutoff_time: float = None) -> float:
"""This works slightly differently than the method currently used to generates the leaderboard and charts, which
depends on the dataframe that make_historical_balances_and_prices_table produces, using pandas.merge_asof. this
minor discrepancy could result in leaderboard orders in the game panel not precisely matching how they are
calculated internally here."""
cash_balance = get_current_game_cash_balance(user_id, game_id)
balances = get_active_balances(game_id, user_id)
symbols = balances["symbol"].unique()
if len(symbols) == 0:
return cash_balance
prices = get_most_recent_prices(symbols, cutoff_time)
df = balances[["symbol", "balance"]].merge(prices, how="left", on="symbol")
df["value"] = df["balance"] * df["price"]
return df["value"].sum() + cash_balance
def apply_stock_splits(start_time: float = None, end_time: float = None):
splits = get_splits(start_time, end_time)
if splits.empty:
return
last_prices = get_most_recent_prices(splits["symbol"].to_list())
for i, row in splits.iterrows():
symbol = row["symbol"]
numerator = row["numerator"]
denominator = row["denominator"]
exec_date = row["exec_date"]
df = get_active_holdings_for_games(symbol)
if df.empty:
continue
# make order status updates here. implement dask or spark here when the job getting to heavy to run locally
# (e.g. see https://docs.dask.org/en/latest/dataframe-api.html#dask.dataframe.read_sql_table)
df["transaction_type"] = "stock_split"
df["order_status_id"] = None
df["timestamp"] = exec_date
df["fractional_balance"] = df["balance"] * numerator / denominator
df["balance"] = df["balance"] * numerator // denominator
df["fractional_balance"] -= df["balance"]
# identify any fractional shares that need to be converted to cash
mask = df["fractional_balance"] > 0
fractional_df = df[mask]
last_price = float(last_prices.loc[last_prices["symbol"] == symbol,
"price"].iloc[0])
for _, fractional_row in fractional_df.iterrows():
game_id = fractional_row["game_id"]
user_id = fractional_row["user_id"]
cash_balance = get_current_game_cash_balance(user_id, game_id)
add_row("game_balances",
user_id=user_id,
game_id=game_id,
timestamp=exec_date,
balance_type="virtual_cash",
balance=cash_balance +
fractional_row["fractional_balance"] * last_price,
transaction_type="stock_split")
# write updated balances
del df["fractional_balance"]
with engine.connect() as conn:
df.to_sql("game_balances", conn, index=False, if_exists="append")
def compile_and_pack_player_leaderboard(game_id: int, start_time: float = None, end_time: float = None):
user_ids = get_active_game_user_ids(game_id)
usernames = get_game_users(game_id)
user_colors = assign_colors(usernames)
records = []
for user_id in user_ids:
user_info = get_user_information(user_id) # this is where username and profile pic get added in
cash_balance = get_current_game_cash_balance(user_id, game_id)
balances = get_active_balances(game_id, user_id)
stocks_held = list(balances["symbol"].unique())
portfolio_value = get_user_portfolio_value(game_id, user_id)
stat_info = make_stat_entry(color=user_colors[user_info["username"]],
cash_balance=cash_balance,
portfolio_value=portfolio_value,
stocks_held=stocks_held,
return_ratio=rds.get(f"{RETURN_RATIO_PREFIX}_{game_id}_{user_id}"),
sharpe_ratio=rds.get(f"{SHARPE_RATIO_PREFIX}_{game_id}_{user_id}"))
records.append({**user_info, **stat_info})
if check_single_player_mode(game_id):
for index in TRACKED_INDEXES:
index_info = query_to_dict("""
SELECT name as username, avatar AS profile_pic
FROM index_metadata WHERE symbol = %s""", index)[0]
portfolio_value = get_index_portfolio_value(game_id, index, start_time, end_time)
stat_info = make_stat_entry(color=user_colors[index_info["username"]],
cash_balance=None,
portfolio_value=portfolio_value,
stocks_held=[],
return_ratio=rds.get(f"{RETURN_RATIO_PREFIX}_{game_id}_{index}"),
sharpe_ratio=rds.get(f"{SHARPE_RATIO_PREFIX}_{game_id}_{index}"))
records.append({**index_info, **stat_info})
benchmark = get_game_info(game_id)["benchmark"] # get game benchmark and use it to sort leaderboard
records = sorted(records, key=lambda x: -x[benchmark])
output = dict(days_left=_days_left(game_id), records=records)
s3_cache.set(f"{game_id}/{LEADERBOARD_PREFIX}", json.dumps(output))
def test_apply_dividends_to_games(self):
user_id = 1
date = datetime_to_posix(dt.now().replace(hour=0,
minute=0,
second=0,
microsecond=0))
amzn_dividend = 10
tsla_dividend = 20
envidia_dividend = 15
fake_dividends = ([['AMZN', 'Amazon INC', amzn_dividend, date],
['TSLA', 'Tesla Motors', tsla_dividend, date],
['NVDA', 'Envidia SA', envidia_dividend, date]])
fake_dividends = pd.DataFrame(
fake_dividends,
columns=['symbol', 'company', 'amount', 'exec_date'])
insert_dividends_to_db(fake_dividends)
user_1_game_8_balance = get_current_game_cash_balance(user_id, 8)
user_1_game_3_balance = get_current_game_cash_balance(user_id, 3)
should_remain_1_000_000 = get_current_game_cash_balance(user_id, 6)
apply_dividends_to_stocks()
# user 1, game 8 is holding NVDA. we should see their holding * the dividend in their updated cash balance
envidia_holding = get_current_stock_holding(user_id, 8, "NVDA")
self.assertEqual(
get_current_game_cash_balance(user_id, 8) - user_1_game_8_balance,
envidia_holding * envidia_dividend)
# user 1, game 3 is holding AMZN, TSLA, and NVDA. Their change in cash balance should be equal to the sum of
# each position * its corresponding dividend
active_balances = get_active_balances(3, user_id)
merged_table = fake_dividends.merge(active_balances,
how="left",
on="symbol")
merged_table[
"payout"] = merged_table["amount"] * merged_table["balance"]
total_dividend = merged_table["payout"].sum()
self.assertEqual(
get_current_game_cash_balance(user_id, 3) - user_1_game_3_balance,
total_dividend)
# user 1 isn't holding any dividend-paying shares in game 6. they should have no cash balance change
self.assertEqual(get_current_game_cash_balance(user_id, 6),
should_remain_1_000_000)
def test_internal_splits_logic(self):
"""test_harvest_stock_splits_external_integration ensures that our external integration is working. this test
verifies that once we have splits they are appropriately applied to users balances. we'll cover a straight-
forward split, a split that leaves fractional shares, and a reverse split that leaves fractional shares"""
game_id = 3
user_id = 1
exec_date = simulation_end_time + 1
splits = pd.DataFrame([
{
"symbol": "AMZN",
"numerator": 5,
"denominator": 1,
"exec_date": exec_date
},
{
"symbol": "TSLA",
"numerator": 5,
"denominator": 2,
"exec_date": exec_date
},
{
"symbol": "SPXU",
"numerator": 2.22,
"denominator": 3.45,
"exec_date": exec_date
},
])
cash_balance_pre = get_current_game_cash_balance(user_id, game_id)
balances_pre = get_active_balances(game_id, user_id)
with patch("backend.logic.stock_data.get_splits") as db_splits_mock:
db_splits_mock.return_value = splits
apply_stock_splits()
cash_balance_post = get_current_game_cash_balance(user_id, game_id)
balances_post = get_active_balances(game_id, user_id)
pre_amzn = balances_pre[balances_pre["symbol"] ==
"AMZN"].iloc[0]["balance"]
post_amzn = balances_post[balances_post["symbol"] ==
"AMZN"].iloc[0]["balance"]
self.assertEqual(pre_amzn * 5 / 1, post_amzn)
pre_tsla = balances_pre[balances_pre["symbol"] ==
"TSLA"].iloc[0]["balance"]
post_tsla = balances_post[balances_post["symbol"] ==
"TSLA"].iloc[0]["balance"]
self.assertEqual(pre_tsla * 5 // 2, post_tsla)
pre_spxu = balances_pre[balances_pre["symbol"] ==
"SPXU"].iloc[0]["balance"]
post_spxu = balances_post[balances_post["symbol"] ==
"SPXU"].iloc[0]["balance"]
self.assertEqual(pre_spxu * 2.22 // 3.45, post_spxu)
last_prices = get_most_recent_prices(["AMZN", "TSLA", "SPXU"])
last_tsla_price = last_prices[last_prices["symbol"] ==
"TSLA"].iloc[0]["price"]
last_spxu_price = last_prices[last_prices["symbol"] ==
"SPXU"].iloc[0]["price"]
self.assertAlmostEqual(
cash_balance_pre +
(pre_tsla * 5 / 2 - post_tsla) * last_tsla_price +
(pre_spxu * 2.22 / 3.45 - post_spxu) * last_spxu_price,
cash_balance_post, 3)