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 serialize_and_pack_order_performance_table(df: pd.DataFrame, game_id: int, user_id: int):
if df.empty:
no_fulfilled_orders_table(game_id, user_id)
return
apply_validation(df, order_performance_schema, True)
agg_rules = {"symbol": "first", "quantity": "first", "clear_price": "first", "timestamp": "first",
"fifo_balance": "last", "basis": "first", "realized_pl": "sum", "unrealized_pl": "last",
"total_pct_sold": "last", "event_type": "first"}
tab = df.groupby("order_label", as_index=False).agg(agg_rules)
recent_prices = get_most_recent_prices(tab["symbol"].unique())
recent_prices.rename(columns={"price": "Market price", "timestamp": "as of"}, inplace=True)
tab = tab.merge(recent_prices, how="left")
tab.sort_values(["order_label", "timestamp"], inplace=True)
label_colors = assign_colors(tab["order_label"].to_list())
tab["unrealized_pl"] = tab["fifo_balance"] * tab["Market price"] - (1 - tab["total_pct_sold"]) * tab["basis"]
del tab["total_pct_sold"]
tab["color"] = tab["order_label"].apply(lambda x: label_colors.get(x))
# tack on sold orders
sold_columns = ["symbol", "timestamp", "quantity", "clear_price", "basis", "event_type"]
sold_df = df.loc[df["event_type"] == "sell", sold_columns]
sold_df["basis"] = -1 * sold_df["basis"]
tab = pd.concat([tab, sold_df], axis=0)
tab.sort_values("timestamp", inplace=True)
tab["realized_pl_percent"] = tab["realized_pl"] / tab["basis"]
tab["unrealized_pl_percent"] = tab["unrealized_pl"] / tab["basis"]
tab.rename(columns=FULFILLED_ORDER_MAPPINGS, inplace=True)
tab.fillna(NA_NUMERIC_VAL, inplace=True)
fulfilled_order_table = dict(data=tab.to_dict(orient="records"), headers=list(FULFILLED_ORDER_MAPPINGS.values()))
s3_cache.set(f"{game_id}/{user_id}/{FULFILLED_ORDER_PREFIX}", json.dumps(fulfilled_order_table))
def serialize_and_pack_balances_chart(df: pd.DataFrame, game_id: int, user_id: int):
chart_json = make_null_chart("Cash")
if df.shape[0] > 1: # a dataframe with a single row means that this user just got started and is only holding cash
df.sort_values("timestamp", inplace=True)
apply_validation(df, balances_chart_schema)
chart_json = make_chart_json(df, "symbol", "value")
s3_cache.set(f"{game_id}/{user_id}/{BALANCES_CHART_PREFIX}", json.dumps(chart_json))
def add_fulfilled_order_entry(game_id: int, user_id: int, order_id: int):
"""Add a fulfilled order to the fulfilled orders table without rebuilding the entire asset"""
order_status_entry = query_to_dict("""
SELECT * FROM order_status WHERE order_id = %s ORDER BY id DESC LIMIT 0, 1""", order_id)[0]
if order_status_entry["status"] == "fulfilled":
order_entry = query_to_dict("SELECT * FROM orders WHERE id = %s;", order_id)[0]
symbol = order_entry['symbol']
timestamp = order_status_entry["timestamp"]
clear_price = order_status_entry["clear_price"]
quantity = order_entry["quantity"]
order_label = f"{symbol}/{int(quantity)} @ {USD_FORMAT.format(clear_price)}/{format_posix_time(timestamp)}"
buy_or_sell = order_entry["buy_or_sell"]
new_entry = {
"order_label": order_label,
"event_type": buy_or_sell,
"Symbol": symbol,
"Cleared on": timestamp,
"Quantity": quantity,
"Clear price": clear_price,
"Basis": quantity * clear_price if buy_or_sell == "buy" else NA_TEXT_SYMBOL,
"Balance (FIFO)": quantity if buy_or_sell == "buy" else NA_NUMERIC_VAL,
"Realized P&L": 0 if buy_or_sell == "buy" else NA_NUMERIC_VAL,
"Realized P&L (%)": 0 if buy_or_sell == "buy" else NA_NUMERIC_VAL,
"Unrealized P&L": 0 if buy_or_sell == "buy" else NA_NUMERIC_VAL,
"Unrealized P&L (%)": 0 if buy_or_sell == "buy" else NA_NUMERIC_VAL,
"Market price": clear_price,
"as of": timestamp,
"color": NULL_RGBA
}
assert set(FULFILLED_ORDER_MAPPINGS.values()) - set(new_entry.keys()) == set()
fulfilled_order_table = s3_cache.unpack_s3_json(f"{game_id}/{user_id}/{FULFILLED_ORDER_PREFIX}")
fulfilled_order_table["data"] = [new_entry] + fulfilled_order_table["data"]
s3_cache.set(f"{game_id}/{user_id}/{FULFILLED_ORDER_PREFIX}", json.dumps(fulfilled_order_table))
def serialize_and_pack_order_performance_chart(df: pd.DataFrame, game_id: int, user_id: int):
if df.empty:
chart_json = make_null_chart("Waiting for orders...")
else:
apply_validation(df, order_performance_schema, True)
plot_df = add_bookends(df, group_var="order_label", condition_var="fifo_balance")
plot_df["cum_pl"] = plot_df.groupby("order_label")["realized_pl"].cumsum()
plot_df["timestamp"] = plot_df["timestamp"].apply(lambda x: posix_to_datetime(x))
plot_df.set_index("timestamp", inplace=True)
plot_df = plot_df.groupby("order_label", as_index=False).resample(f"{RESAMPLING_INTERVAL}T").last().ffill()
plot_df = plot_df.reset_index(level=1)
plot_df = filter_for_trade_time(plot_df)
plot_df = append_price_data_to_balance_histories(plot_df)
plot_df.sort_values(["order_label", "timestamp"], inplace=True)
plot_df = add_time_labels(plot_df)
plot_df = plot_df.groupby(["order_label", "t_index"], as_index=False).agg("last")
plot_df["label"] = plot_df["timestamp"].apply(lambda x: datetime_to_posix(x)).astype(float)
plot_df.sort_values("timestamp", inplace=True)
plot_df["total_pl"] = plot_df["cum_pl"] + plot_df["fifo_balance"] * plot_df["price"] - (
1 - plot_df["total_pct_sold"]) * plot_df["basis"]
plot_df["return"] = 100 * plot_df["total_pl"] / plot_df["basis"]
label_colors = assign_colors(plot_df["order_label"].unique())
plot_df["color"] = plot_df["order_label"].apply(lambda x: label_colors.get(x))
chart_json = make_chart_json(plot_df, "order_label", "return", "label", colors=plot_df["color"].unique())
s3_cache.set(f"{game_id}/{user_id}/{ORDER_PERF_CHART_PREFIX}", json.dumps(chart_json))
def serialize_and_pack_pending_orders(game_id: int, user_id: int):
df = get_order_details(game_id, user_id)
df = df[df["status"] == "pending"]
if df.empty:
no_pending_orders_table(game_id, user_id)
return
df["time_in_force"] = df["time_in_force"].apply(lambda x: "Day" if x == "day" else "Until cancelled")
df = add_market_prices_to_order_details(df)
df.fillna(NA_TEXT_SYMBOL, inplace=True)
mapped_columns_to_drop = ["clear_price_fulfilled", "timestamp_fulfilled"]
df = df.drop(mapped_columns_to_drop, axis=1)
df = df.rename(columns=PENDING_ORDER_MAPPINGS)
df = df[df["status"] == "pending"]
pending_order_records = dict(data=df.to_dict(orient="records"), headers=list(PENDING_ORDER_MAPPINGS.values()))
s3_cache.set(f"{game_id}/{user_id}/{PENDING_ORDERS_PREFIX}", json.dumps(pending_order_records))
def serialize_and_pack_winners_table(game_id: int):
"""this function serializes the winners data that has already been saved to DB and fills in any missing rows."""
game_start, game_end, start_dt, end_dt, benchmark, side_bets_perc, stakes, offset = get_winners_meta_data(game_id)
# pull winners data from DB
with engine.connect() as conn:
winners_df = pd.read_sql("SELECT * FROM winners WHERE game_id = %s ORDER BY id", conn, params=[game_id])
# Where are we at in the current game?
game_finished = False
if winners_df.empty:
last_observed_win = start_dt
else:
last_observed_win = posix_to_datetime(winners_df["timestamp"].max())
if "overall" in winners_df["type"].to_list():
game_finished = True
data = []
if side_bets_perc:
payout = get_sidebet_payout(game_id, side_bets_perc, offset, stakes)
expected_sidebet_dates = get_expected_sidebets_payout_dates(start_dt, end_dt, side_bets_perc, offset)
for _, row in winners_df.iterrows():
if row["type"] == "sidebet":
winner = get_usernames([row["winner_id"]])
data.append(
make_payout_table_entry(posix_to_datetime(row["start_time"]), posix_to_datetime(row["end_time"]),
winner, payout, "Sidebet", benchmark, row["score"]))
dates_to_fill_in = [x for x in expected_sidebet_dates if x > last_observed_win]
last_date = last_observed_win
for payout_date in dates_to_fill_in:
data.append(make_payout_table_entry(last_date, payout_date, "???", payout, "Sidebet"))
last_date = payout_date
payout = get_overall_payout(game_id, side_bets_perc, stakes)
if not game_finished:
final_entry = make_payout_table_entry(start_dt, end_dt, "???", payout, "Overall")
else:
winner_row = winners_df.loc[winners_df["type"] == "overall"].iloc[0]
winner = get_usernames([int(winner_row["winner_id"])])[0]
final_entry = make_payout_table_entry(start_dt, end_dt, winner, payout, "Overall", benchmark,
winner_row["score"])
data.append(final_entry)
out_dict = dict(data=data, headers=list(data[0].keys()))
s3_cache.set(f"{game_id}/{PAYOUTS_PREFIX}", json.dumps(out_dict))
def make_redis_mocks():
for i, _ in enumerate(MOCK_DATA["users"]):
user_id = int(i + 1)
rds.set(f"{PLAYER_RANK_PREFIX}_{user_id}", STARTING_ELO_SCORE)
rds.set(f"{THREE_MONTH_RETURN_PREFIX}_{user_id}", 0)
serialize_and_pack_rankings()
game_ids = [3, 6, 7, 8]
for game_id in game_ids:
init_game_assets(game_id)
serialize_and_pack_games_per_user_chart()
# TODO: This is a quick hack to get the admin panel working in dev. Fix at some point
df = make_games_per_user_data()
chart_json = make_chart_json(df, "cohort", "percentage", "game_count")
s3_cache.set(f"{ORDERS_PER_ACTIVE_USER_PREFIX}", json.dumps(chart_json))
def serialize_and_pack_portfolio_comps_chart(df: pd.DataFrame, game_id: int):
usernames = get_game_users(game_id)
user_colors = assign_colors(usernames)
datasets = []
if df["username"].nunique() == df.shape[0]:
# if our portfolio dataframe only has as many rows as there are users in the game, this means that we've just
# started the game, and can post a null chart to the field
for username, color in user_colors.items():
null_chart = make_null_chart(username)
datasets.append(null_chart["datasets"][0])
labels = null_chart["labels"]
chart_json = dict(labels=list(labels), datasets=datasets)
else:
colors = []
for username in df["username"].unique():
colors.append(user_colors[username])
chart_json = make_chart_json(df, "username", "value", colors=colors)
leaderboard = s3_cache.unpack_s3_json(f"{game_id}/{LEADERBOARD_PREFIX}")
chart_json["leaderboard"] = leaderboard["records"]
s3_cache.set(f"{game_id}/{FIELD_CHART_PREFIX}", json.dumps(chart_json))
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 removing_pending_order(game_id: int, user_id: int, order_id: int):
fn = f"{game_id}/{user_id}/{PENDING_ORDERS_PREFIX}"
order_json = s3_cache.unpack_s3_json(fn)
order_json["data"] = [entry for entry in order_json["data"] if entry["order_id"] != order_id]
s3_cache.set(fn, json.dumps(order_json))
def no_pending_orders_table(game_id: int, user_id: int):
init_pending_json = dict(data=[], headers=list(PENDING_ORDER_MAPPINGS.values()))
s3_cache.set(f"{game_id}/{user_id}/{PENDING_ORDERS_PREFIX}", json.dumps(init_pending_json))
def no_fulfilled_orders_table(game_id: int, user_id: int):
init_fufilled_json = dict(data=[], headers=list(FULFILLED_ORDER_MAPPINGS.values()))
s3_cache.set(f"{game_id}/{user_id}/{FULFILLED_ORDER_PREFIX}", json.dumps(init_fufilled_json))
def serialize_and_pack_games_per_user_chart():
df = make_games_per_user_data()
chart_json = make_chart_json(df, "cohort", "percentage", "game_count")
s3_cache.set(f"{GAMES_PER_USER_PREFIX}", json.dumps(chart_json))
def serialize_and_pack_orders_per_active_user():
df = make_orders_per_active_user()
df["series_label"] = "Orders per active user"
chart_json = make_chart_json(df, "series_label", "orders_per_users",
"timestamp")
s3_cache.set(f"{ORDERS_PER_ACTIVE_USER_PREFIX}", json.dumps(chart_json))