def test_single_player_start(self):
user_id = 1
title = "jugando solit@"
add_game(user_id, title, "single_player", 365, "return_ratio")
# confirm that a single player game was registered successfully
game_entry = query_to_dict("SELECT * FROM games WHERE title = %s;",
title)[0]
game_id = game_entry["id"]
self.assertEqual(game_entry["game_mode"], "single_player")
self.assertEqual(game_entry["benchmark"], "return_ratio")
# confirm that user is registered as joined
invite_entry = query_to_dict(
"SELECT * FROM game_invites WHERE game_id = %s", game_id)[0]
self.assertEqual(invite_entry["status"], "joined")
# confirm that all expected redis cache assets exist
# --------------------------------------------------
# these assets are specific to the user
for prefix in [
CURRENT_BALANCES_PREFIX, PENDING_ORDERS_PREFIX,
FULFILLED_ORDER_PREFIX, BALANCES_CHART_PREFIX,
ORDER_PERF_CHART_PREFIX
]:
self.assertIn(f"{game_id}/{user_id}/{prefix}", s3_cache.keys())
# these assets exist for both the user and the index users
for _id in [user_id] + TRACKED_INDEXES:
self.assertIn(f"{SHARPE_RATIO_PREFIX}_{game_id}_{_id}", rds.keys())
# and check that the leaderboard exists on the game level
self.assertIn(f"{game_id}/{LEADERBOARD_PREFIX}", s3_cache.keys())
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 suggest_symbols(game_id: int, user_id: int, text: str, buy_or_sell: str):
if buy_or_sell == "buy":
to_match = f"{text.upper()}%"
symbol_suggestions = query_to_dict(
"""
SELECT * FROM symbols
WHERE symbol LIKE %s OR name LIKE %s;""", to_match, to_match)
if buy_or_sell == "sell":
balances = get_active_balances(game_id, user_id)
symbols = list(balances["symbol"].unique())
to_match = f"{text.upper()}%"
params_list = [to_match] * 2 + symbols
symbol_suggestions = query_to_dict(
f"""
SELECT * FROM symbols
WHERE (symbol LIKE %s OR name LIKE %s) AND symbol IN ({','.join(['%s'] * len(symbols))});""",
params_list)
suggestions = [{
"symbol": entry["symbol"],
"label": f"{entry['symbol']} ({entry['name']})",
"dist": hamming(text, entry['symbol'])
} for entry in symbol_suggestions]
# sort suggestions by hamming distance between text and ticker entry
return sorted(suggestions, key=lambda i: i["dist"])
def get_game_info(game_id: int):
info = query_to_dict("SELECT * FROM games WHERE id = %s;", game_id)[0]
creator_id = info["creator_id"]
info["creator_username"] = get_usernames([creator_id])[0]
info["creator_profile_pic"] = query_to_dict(
"SELECT * FROM users WHERE id = %s", creator_id)[0]["profile_pic"]
info["benchmark_formatted"] = info["benchmark"].upper().replace("_", " ")
info["game_status"] = get_current_game_status(game_id)
info["start_time"] = info["end_time"] = None
if info["game_status"] in ["active", "finished"]:
info["start_time"], info["end_time"] = get_game_start_and_end(game_id)
return info
def get_rating_info(player_id: Union[int, str]):
"""if player_id is passed as an int get_rating will interpret it as a user_id. if passed as a string it will interpret
it as a n index"""
assert type(player_id) in [int, str]
rating_column = "index_symbol" if type(player_id) == str else "user_id"
return query_to_dict(f"""SELECT n_games, basis, total_return, rating FROM stockbets_rating
WHERE {rating_column} = %s ORDER BY id DESC LIMIT 0, 1;""", player_id)[0]
def change_user():
username = request.json.get("username")
entry = query_to_dict("SELECT * FROM users WHERE username = %s", username)[0]
session_token = make_session_token_from_uuid(resource_uuid=entry["resource_uuid"])
resp = make_response()
resp.set_cookie("session_token", session_token, httponly=True, samesite=None, secure=True)
return resp
def get_index_portfolio_value_data(game_id: int,
symbol: str,
start_time: float = None,
end_time: float = None) -> pd.DataFrame:
"""In single-player mode a player competes against the indexes. This function just normalizes a dataframe of index
values by the starting value for when the game began
"""
start_time, end_time = get_time_defaults(game_id, start_time, end_time)
base_value = get_index_reference(game_id, symbol)
with engine.connect() as conn:
df = pd.read_sql("""
SELECT timestamp, `value` FROM indexes
WHERE symbol = %s AND timestamp >= %s AND timestamp <= %s;""",
conn,
params=[symbol, start_time, end_time])
index_info = query_to_dict(
"SELECT * FROM index_metadata WHERE symbol = %s", symbol)[0]
# normalizes index to the same starting scale as the user
df["value"] = STARTING_VIRTUAL_CASH * df["value"] / base_value
df["username"] = index_info["name"]
# When a game kicks off, it will generally be that case that there won't be an index data point at exactly that
# time. We solve this here, create a synthetic "anchor" data point that starts at the same time at the game
trade_start = make_index_start_time(start_time)
return pd.concat([
pd.DataFrame(
dict(username=index_info["name"],
timestamp=[trade_start],
value=[STARTING_VIRTUAL_CASH])), df
])
def cancel_order(order_id: int):
order_ticket = query_to_dict("SELECT * FROM orders WHERE id = %s",
order_id)[0]
add_row("order_status",
order_id=order_id,
timestamp=time.time(),
status="cancelled")
removing_pending_order(order_ticket["game_id"], order_ticket["user_id"],
order_id)
def close_open_game(game_id, update_time, close_status="expired"):
game_status_entry = query_to_dict(
"SELECT * FROM game_status WHERE game_id = %s", game_id)[0]
add_row("game_status",
game_id=game_id,
status=close_status,
users=json.loads(game_status_entry["users"]),
timestamp=update_time)
mark_invites_expired(game_id, ["invited"], update_time)
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 get_winners_meta_data(game_id: int):
game_info = query_to_dict("SELECT * FROM games WHERE id = %s", game_id)[0]
side_bets_perc = game_info.get("side_bets_perc")
benchmark = game_info["benchmark"]
stakes = game_info["stakes"]
game_start, game_end = get_game_start_and_end(game_id)
offset = make_date_offset(game_info["side_bets_period"])
start_dt = posix_to_datetime(game_start)
end_dt = posix_to_datetime(game_end)
return game_start, game_end, start_dt, end_dt, benchmark, side_bets_perc, stakes, offset
def get_user_invite_status_for_game(game_id: int, user_id: int):
sql = """
SELECT gi.status
FROM game_invites gi
INNER JOIN
(SELECT game_id, user_id, max(id) as max_id
FROM game_invites
GROUP BY game_id, user_id) grouped_gi
ON
gi.id = grouped_gi.max_id
WHERE gi.game_id = %s AND gi.user_id = %s;
"""
entry = query_to_dict(sql, game_id, user_id)
if entry:
return entry[0]["status"]
game_mode = query_to_dict("SELECT game_mode FROM games WHERE id = %s;",
game_id)[0]["game_mode"]
assert game_mode == "public"
return "invited"
def close_finished_game_with_context(**context):
game_id = context_parser(context, "game_id")[0]
_, game_end = get_game_start_and_end(game_id)
current_time = time.time()
if current_time >= game_end:
user_ids = get_active_game_user_ids(game_id)
last_status_entry = query_to_dict("""SELECT * FROM game_status
WHERE game_id = %s ORDER BY id DESC LIMIT 0, 1""", game_id)[0]
if last_status_entry["status"] == "active":
# close game and update ratings
add_row("game_status", game_id=game_id, status="finished", users=user_ids, timestamp=current_time)
update_ratings(game_id)
def get_payment_profile_uuids(user_ids: List[int], processor="paypal"):
profile_entries = query_to_dict(
f"""
SELECT * FROM payment_profiles p
INNER JOIN (
SELECT user_id, MAX(id) AS max_id
FROM payment_profiles
WHERE user_id IN ({",".join(["%s"] * len(user_ids))}) AND processor = %s
GROUP BY user_id) grouped_profiles
ON p.id = grouped_profiles.max_id;""", user_ids + [processor])
if len(profile_entries) != len(user_ids):
raise MissingProfiles
return profile_entries
def scrape_stock_splits():
driver = get_web_driver()
_included_symbols = query_to_dict("SELECT symbol FROM symbols")
included_symbols = [x["symbol"] for x in _included_symbols]
nasdaq_raw_splits = retrieve_nasdaq_splits(driver, included_symbols)
nasdaq_splits = parse_nasdaq_splits(nasdaq_raw_splits)
nasdaq_symbols = nasdaq_splits["symbol"].to_list()
yahoo_raw_splits = retrieve_yahoo_splits(driver, included_symbols)
yahoo_splits = parse_yahoo_splits(yahoo_raw_splits, nasdaq_symbols)
df = pd.concat([nasdaq_splits, yahoo_splits])
if not df.empty:
with engine.connect() as conn:
df.to_sql("stock_splits", conn, if_exists="append", index=False)
def respond_to_game_invite(game_id: int, user_id: int, decision: str,
response_time: float):
add_row("game_invites",
game_id=game_id,
user_id=user_id,
status=decision,
timestamp=response_time)
game_info = query_to_dict("SELECT * FROM games WHERE id = %s", game_id)[0]
if game_info["game_mode"] in ["single_player", "multi_player"]:
update_external_invites(game_id, user_id, decision)
update_game_if_all_invites_responded(game_id)
if game_info["game_mode"] == "public" and decision == "joined":
handle_public_game_acceptance(game_id)
def get_pending_external_game_invites(invited_email: str):
"""Returns external game invites whose most recent status is 'invited'
"""
return query_to_dict(
"""
SELECT *
FROM external_invites ex
INNER JOIN
(SELECT LOWER(REPLACE(invited_email, '.', '')) as formatted_email, MAX(id) as max_id
FROM external_invites
WHERE type = 'game'
GROUP BY requester_id, type, game_id, formatted_email) grouped_ex
ON ex.id = grouped_ex.max_id
WHERE LOWER(REPLACE(ex.invited_email, '.', '')) = %s AND ex.status = 'invited';
""", standardize_email(invited_email))
def add_external_game_invites(email: str, user_id: int):
# is this user already invited to any games?
external_game_invites = get_pending_external_game_invites(email)
current_time = time.time()
for invite_entry in external_game_invites:
game_id = invite_entry["game_id"]
gs_entry = query_to_dict(
"SELECT * FROM game_status WHERE game_id = %s ORDER BY id DESC LIMIT 0, 1",
game_id)[0]
if gs_entry["status"] == "pending":
add_row("game_invites",
game_id=game_id,
user_id=user_id,
status="invited",
timestamp=current_time)
def update_external_invites(game_id: int, user_id: int, decision: str):
if decision == "joined":
decision = "accepted"
# check if the user has an external invite for this game. if they do, mark the external invite as accepted
user_email = get_user_information(user_id)["email"]
external_invite_entries = query_to_dict(
"""
SELECT * FROM external_invites WHERE
game_id = %s AND LOWER(REPLACE(invited_email, '.', '')) = %s;""",
game_id, standardize_email(user_email))
for entry in external_invite_entries:
add_row("external_invites",
requester_id=entry["requester_id"],
invited_email=user_email,
status=decision,
timestamp=time.time(),
game_id=game_id,
type="game")
def check_payment_profile(user_id: int, processor: str, uuid: str,
payer_email: str):
"""Check to see if a payment profile entry exists for a user. If not create profile. Return profile_id
"""
profile_entry = query_to_dict(
"""
SELECT * FROM payment_profiles
WHERE user_id = %s AND uuid = %s AND processor = %s AND payer_email = %s
ORDER BY timestamp DESC LIMIT 1;""", user_id, uuid, processor,
payer_email)
if profile_entry:
return profile_entry[0]["id"]
return add_row("payment_profiles",
user_id=user_id,
processor=processor,
uuid=uuid,
payer_email=payer_email,
timestamp=time.time())
def test_single_player_visuals(self, mock_base_time, mock_game_time):
mock_base_time.time.return_value = mock_game_time.time.return_value = simulation_end_time
game_id = 8
user_id = 1
serialize_and_pack_pending_orders(game_id, user_id)
serialize_and_pack_order_performance_assets(game_id, user_id)
pending_orders_table = s3_cache.unpack_s3_json(
f"{game_id}/{user_id}/{PENDING_ORDERS_PREFIX}")
fulfilled_orders_table = s3_cache.unpack_s3_json(
f"{game_id}/{user_id}/{FULFILLED_ORDER_PREFIX}")
self.assertEqual(len(pending_orders_table["data"]), 0)
self.assertEqual(len(fulfilled_orders_table["data"]), 2)
self.assertEqual(
set([x["Symbol"] for x in fulfilled_orders_table["data"]]),
{"NVDA", "NKE"})
serialize_and_pack_order_performance_assets(game_id, user_id)
self.assertIn(f"{game_id}/{user_id}/{ORDER_PERF_CHART_PREFIX}",
s3_cache.keys())
op_chart = s3_cache.unpack_s3_json(
f"{game_id}/{user_id}/{ORDER_PERF_CHART_PREFIX}")
chart_stocks = set(
[x["label"].split("/")[0] for x in op_chart["datasets"]])
expected_stocks = {"NKE", "NVDA"}
self.assertEqual(chart_stocks, expected_stocks)
# balances chart
df = make_user_balances_chart_data(game_id, user_id)
serialize_and_pack_balances_chart(df, game_id, user_id)
balances_chart = s3_cache.unpack_s3_json(
f"{game_id}/{user_id}/{BALANCES_CHART_PREFIX}")
self.assertEqual(set([x["label"] for x in balances_chart["datasets"]]),
{"NVDA", "NKE", "Cash"})
# leaderboard and field charts
compile_and_pack_player_leaderboard(game_id)
make_the_field_charts(game_id)
field_chart = s3_cache.unpack_s3_json(
f"{game_id}/{FIELD_CHART_PREFIX}")
_index_names = query_to_dict("SELECT * FROM index_metadata")
_index_names = [x["name"] for x in _index_names]
self.assertEqual(set([x["label"] for x in field_chart["datasets"]]),
set(["cheetos"] + _index_names))
def test_db_helpers(self):
dummy_symbol = "ACME"
dummy_name = "ACME CORP"
symbol_id = add_row("symbols", symbol=dummy_symbol, name=dummy_name)
# There's nothing special about primary key #27. If we update the mocks this will need to update, too.
self.assertEqual(symbol_id, 27)
acme_entry = query_to_dict("SELECT * FROM symbols WHERE symbol = %s",
dummy_symbol)[0]
self.assertEqual(acme_entry["symbol"], dummy_symbol)
self.assertEqual(acme_entry["name"], dummy_name)
user_id = add_row("users",
name="diane browne",
email="*****@*****.**",
profile_pic="private",
username="******",
created_at=time.time(),
provider="twitter",
resource_uuid="aaa")
rds.set(f"{PLAYER_RANK_PREFIX}_{user_id}", STARTING_ELO_SCORE)
rds.set(f"{THREE_MONTH_RETURN_PREFIX}_{user_id}", 0)
new_entry = get_user_information(user_id)
self.assertEqual(new_entry["name"], "diane browne")
game_id = add_row("games",
creator_id=1,
title="db test",
game_mode="multi_player",
duration=1_000,
buy_in=0,
benchmark="return_ratio",
side_bets_perc=0,
invite_window=time.time() + 1_000_000_000_000)
add_row("game_status",
game_id=game_id,
status="pending",
users=[1, 1],
timestamp=time.time())
new_entry = get_game_info(game_id)
self.assertEqual(new_entry["title"], "db test")
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 make_test_token_from_email(email: str):
user_entry = query_to_dict("SELECT * FROM users WHERE email = %s", email)[0]
return create_jwt(user_entry["email"], user_entry["id"], user_entry["username"])
def login():
"""Following a successful login, this allows us to create a new users. If the user already exists in the DB send
back a SetCookie to allow for seamless interaction with the API. token_id comes from response.tokenId where the
response is the returned value from the React-Google-Login component.
"""
current_time = time.time()
login_data = request.json # in the case of a different platform provider, this is just the oauth response
is_sign_up = login_data.get("is_sign_up")
provider = login_data.get("provider")
password = login_data.get("password")
name = login_data.get("name")
email = login_data.get("email")
if provider not in ["google", "facebook", "twitter", "stockbets"]:
return make_response(INVALID_OAUTH_PROVIDER_MSG, 411)
status_code = 400
profile_pic = None
resource_uuid = None
if provider == "google":
response = verify_google_oauth(login_data["tokenId"])
resource_uuid = login_data.get("googleId")
status_code = response.status_code
if status_code == 200:
verification_json = response.json()
email = verification_json["email"]
if is_sign_up:
name = verification_json["given_name"]
profile_pic = upload_image_from_url_to_s3(verification_json["picture"], resource_uuid)
if provider == "facebook":
response = verify_facebook_oauth(login_data["accessToken"])
status_code = response.status_code
if status_code == 200:
resource_uuid = login_data["userID"]
email = login_data["email"]
if is_sign_up:
name = login_data["name"]
profile_pic = upload_image_from_url_to_s3(login_data["picture"]["data"]["url"], resource_uuid)
if provider == "stockbets":
status_code = 200
if is_sign_up:
resource_uuid = hashlib.sha224(bytes(email, encoding='utf-8')).hexdigest()
url = make_avatar_url(email)
profile_pic = upload_image_from_url_to_s3(url, resource_uuid)
if provider == "twitter":
pass
if status_code is not 200:
return make_response(OAUTH_ERROR_MSG, status_code)
if is_sign_up:
db_entry = query_to_dict("SELECT * FROM users WHERE LOWER(REPLACE(email, '.', '')) = %s",
standardize_email(email))
if db_entry:
return make_response(EMAIL_ALREADY_LOGGED_MSG, 403)
user_id = setup_new_user(name, email, profile_pic, current_time, provider, resource_uuid, password)
add_external_game_invites(email, user_id)
else:
db_entry = query_to_dict("SELECT * FROM users WHERE LOWER(REPLACE(email, '.', '')) = %s",
standardize_email(email))
if not db_entry:
return make_response(EMAIL_NOT_FOUND_MSG, 403)
resource_uuid = db_entry[0]["resource_uuid"]
session_token = make_session_token_from_uuid(resource_uuid)
resp = make_response()
resp.set_cookie("session_token", session_token, httponly=True, samesite=None, secure=True)
return resp
def get_dividends_for_date(date: dt = None) -> pd.DataFrame:
if date is None:
date = dt.now().replace(hour=0, minute=0, second=0, microsecond=0)
posix = datetime_to_posix(date)
return pd.DataFrame(
query_to_dict("SELECT * FROM dividends WHERE exec_date=%s", posix))
def make_order_performance_table(game_id: int, user_id: int, start_time: float = None, end_time: float = None):
"""the order performance table provides the basis for the order performance chart and the order performance table
in the UI. it looks at each purchase order as a separate entity, and then iterates over subsequent sale and stock
split events to iteratively 'unwind' the P&L associated with that buy order. the function handles multiple buy
orders for the same stock by considering the buy order events and the split/sale events to each be their own queue.
as the function iterates over the buy orders it "consumes" split/sell events from the queue. the sales event portion
of the queue is unique to each stock symbol -- this part of the queue is built at the outermost loop, and sales
events are "consumed" and removed from the queue as the function iterates over the different buy events. stock
splits, on the other hand can apply to multiple buy orders and aren't dropped from the queue in the same way that
sales events are. this portion of the queue is therefore reconstructed for each buy order iteration before being
blended with the remaining sales events in the queue. """
# get historical order details
order_df = get_order_details(game_id, user_id, start_time, end_time)
order_df = order_df[(order_df["status"] == "fulfilled") & (order_df["symbol"] != "Cash")]
if order_df.empty:
return order_df
order_df = make_order_labels(order_df)
order_details_columns = ["symbol", "order_id", "order_status_id", "order_label", "buy_or_sell", "quantity",
"clear_price_fulfilled", "timestamp_fulfilled"]
orders = order_df[order_details_columns]
balances = get_game_balances(game_id, user_id, start_time, end_time)
df = balances.merge(orders, on=["symbol", "order_status_id"], how="left")
buys_df = df[df["buy_or_sell"] == "buy"]
sales_df = df[df["transaction_type"] == "stock_sale"]
splits_df = df[df["transaction_type"] == "stock_split"]
performance_records = []
for symbol in df["symbol"].unique():
# look up price ranged need to calculating unrealized p&l for split events
min_time = float(df[df["symbol"] == symbol]["timestamp"].min())
max_time = time.time()
price_df = get_price_histories([symbol], min_time, max_time)
# iterate over buy and sales queues
buys_subset = buys_df[buys_df["symbol"] == symbol]
sales_queue = sales_df[sales_df["symbol"] == symbol].to_dict(orient="records")
for _, buy_order in buys_subset.iterrows():
order_label = buy_order["order_label"]
buy_quantity = buy_order["quantity"]
clear_price = buy_order["clear_price_fulfilled"]
basis = buy_quantity * clear_price
performance_records.append(dict(
symbol=symbol,
order_id=buy_order["order_id"],
order_label=order_label,
basis=basis,
quantity=buy_quantity,
clear_price=clear_price,
event_type="buy",
fifo_balance=buy_quantity,
timestamp=buy_order["timestamp_fulfilled"],
realized_pl=0,
unrealized_pl=0,
total_pct_sold=0
))
# instantiate variable for balances and sold percentages
fifo_balance = buy_order["quantity"]
total_pct_sold = 0
# reconstruct the events queue with splits refreshed each time
mask = (splits_df["symbol"] == symbol) & (splits_df["timestamp"] >= buy_order["timestamp"])
splits_queue = splits_df[mask].to_dict(orient="records")
events_queue = splits_queue + sales_queue
events_queue = sorted(events_queue, key=lambda k: k['timestamp'])
while fifo_balance > 0 and len(events_queue) > 0:
event = events_queue[0] # 'consume' events from the queue as we unwind P&L
if event["transaction_type"] == "stock_split":
split_entry = query_to_dict("SELECT * FROM stock_splits WHERE id = %s", event["stock_split_id"])[0]
fifo_balance *= split_entry["numerator"] / split_entry["denominator"]
price = price_df[price_df["timestamp"] >= event["timestamp"]].iloc[0]["price"]
performance_records.append(dict(
symbol=symbol,
order_label=order_label,
order_id=None,
basis=basis,
quantity=None,
clear_price=None,
event_type="split",
fifo_balance=fifo_balance,
timestamp=split_entry["exec_date"],
realized_pl=0,
unrealized_pl=fifo_balance * price - (1 - total_pct_sold) * basis,
total_pct_sold=total_pct_sold
))
events_queue.pop(0)
if event["transaction_type"] == "stock_sale":
sale_price = event["clear_price_fulfilled"]
quantity_sold = order_amount = event["quantity"]
if fifo_balance - quantity_sold < 0:
quantity_sold = fifo_balance
event["quantity"] -= quantity_sold
assert not event["quantity"] < 0
if event["quantity"] == 0:
sales_queue.pop(0)
events_queue.pop(0)
sale_pct = (1 - total_pct_sold) * quantity_sold / fifo_balance
total_pct_sold += sale_pct
fifo_balance -= quantity_sold
performance_records.append(dict(
symbol=symbol,
order_label=order_label,
order_id=event["order_id"],
basis=basis,
quantity=order_amount,
clear_price=sale_price,
event_type="sell",
fifo_balance=fifo_balance,
timestamp=event["timestamp_fulfilled"],
realized_pl=quantity_sold * sale_price - sale_pct * basis,
unrealized_pl=fifo_balance * sale_price - (1 - total_pct_sold) * basis,
total_pct_sold=total_pct_sold
))
return pd.DataFrame(performance_records)
def get_user_information(user_id: int):
sql = "SELECT id, name, email, profile_pic, username, created_at FROM users WHERE id = %s"
info = query_to_dict(sql, user_id)[0]
info["rating"] = float(rds.get(f"{PLAYER_RANK_PREFIX}_{user_id}"))
info["three_month_return"] = float(rds.get(f"{THREE_MONTH_RETURN_PREFIX}_{user_id}"))
return info
def test_play_game_tasks(self):
start_time = time.time()
game_title = "lucky few"
creator_id = 1
mock_game = {
"creator_id": creator_id,
"title": game_title,
"game_mode": "multi_player",
"duration": 180,
"buy_in": 100,
"benchmark": "return_ratio",
"side_bets_perc": 50,
"side_bets_period": "weekly",
"invitees": ["miguel", "murcitdev", "toofast"],
"invite_window": DEFAULT_INVITE_OPEN_WINDOW,
"stakes": "monopoly"
}
game_id = add_game(
mock_game["creator_id"],
mock_game["title"],
mock_game["game_mode"],
mock_game["duration"],
mock_game["benchmark"],
mock_game["stakes"],
mock_game["buy_in"],
mock_game["side_bets_perc"],
mock_game["side_bets_period"],
mock_game["invitees"],
mock_game["invite_window"]
)
game_entry = query_to_dict("SELECT * FROM games WHERE title = %s", game_title)[0]
# Check the game entry table
# OK for these results to shift with the test fixtures
self.assertEqual(game_entry["id"], game_id)
for k, v in mock_game.items():
if k == "invitees":
continue
if k == "duration":
continue
if k == "invite_window":
self.assertAlmostEqual(game_entry[k], start_time + DEFAULT_INVITE_OPEN_WINDOW * SECONDS_IN_A_DAY, 3)
continue
self.assertAlmostEqual(game_entry[k], v, 1)
# Confirm that game status was updated as expected
# ------------------------------------------------
game_status_entry = query_to_dict("SELECT * FROM game_status WHERE game_id = %s", game_id)[0]
self.assertEqual(game_status_entry["id"], 17)
self.assertEqual(game_status_entry["game_id"], game_id)
self.assertEqual(game_status_entry["status"], "pending")
users_from_db = json.loads(game_status_entry["users"])
self.assertEqual(set(users_from_db), {3, 4, 5, 1})
# and that the game invites table is working as well
# --------------------------------------------------
with self.engine.connect() as conn:
game_invites_df = pd.read_sql("SELECT * FROM game_invites WHERE game_id = %s", conn, params=[game_id])
self.assertEqual(game_invites_df.shape, (4, 5))
for _, row in game_invites_df.iterrows():
self.assertIn(row["user_id"], users_from_db)
status = "invited"
if row["user_id"] == creator_id:
status = "joined"
self.assertEqual(row["status"], status)
# less than a two-second difference between when we sent the data and when it was logged. If the local
# celery worked is gummed up and not working properly this can fail
self.assertTrue(row["timestamp"] - start_time < 2)
# we'll mock in a time value for the current game in a moment, but first check that async_service_open_games is
# working as expected
with self.engine.connect() as conn:
gi_count_pre = conn.execute("SELECT COUNT(*) FROM game_invites;").fetchone()[0]
open_game_ids = get_open_game_ids_past_window()
for _id in open_game_ids:
service_open_game(_id)
with self.engine.connect() as conn:
gi_count_post = conn.execute("SELECT COUNT(*) FROM game_invites;").fetchone()[0]
self.assertEqual(gi_count_post - gi_count_pre, 4)
with self.engine.connect() as conn:
df = pd.read_sql("SELECT game_id, user_id, status FROM game_invites WHERE game_id in (1, 2)", conn)
self.assertEqual(df[df["user_id"] == 5]["status"].to_list(), ["invited", "expired"])
self.assertEqual(df[(df["user_id"] == 3) & (df["game_id"] == 2)]["status"].to_list(), ["joined"])
# murcitdev is going to decline to play, toofast and miguel will play and receive their virtual cash balances
# -----------------------------------------------------------------------------------------------------------
for user_id in [3, 4]:
respond_to_game_invite(game_id, user_id, "joined", time.time())
respond_to_game_invite(game_id, 5, "declined", time.time())
# So far so good. Pretend that we're now past the invite open window and it's time to play
# ----------------------------------------------------------------------------------------
game_start_time = time.time() + DEFAULT_INVITE_OPEN_WINDOW * SECONDS_IN_A_DAY + 1
with patch("backend.logic.games.time") as mock_time:
# users have joined, and we're past the invite window
mock_time.time.return_value = game_start_time
open_game_ids = get_open_game_ids_past_window()
for _id in open_game_ids:
service_open_game(_id)
with self.engine.connect() as conn:
# Verify game updated to active status and active players
game_status = conn.execute(
"SELECT status, users FROM game_status WHERE game_id = %s ORDER BY id DESC LIMIT 0, 1",
game_id).fetchone()
self.assertEqual(game_status[0], "active")
self.assertEqual(len(set(json.loads(game_status[1])) - {1, 3, 4}), 0)
# Verify that we have three plays for game 5 with $1,000,000 virtual cash balances
res = conn.execute(
"SELECT balance FROM game_balances WHERE game_id = %s AND balance_type = 'virtual_cash';",
game_id).fetchall()
balances = [x[0] for x in res]
self.assertIs(len(balances), 3)
self.assertTrue(all([x == STARTING_VIRTUAL_CASH for x in balances]))
# For now I've tried to keep things simple and divorce the ordering part of the integration test from game
# startup. May need to close the loop on this later when expanding the test to cover payouts
game_start_time = 1590508896
# Place two market orders and a buy limit order
with patch("backend.logic.games.time") as mock_game_time, patch(
"backend.logic.base.time") as mock_data_time:
mock_game_time.time.return_value = mock_data_time.time.return_value = game_start_time + 300
# Everything working as expected. Place a couple buy orders to get things started
stock_pick = "AMZN"
user_id = 1
order_quantity = 500_000
amzn_price, _ = fetch_price(stock_pick)
cash_balance = get_current_game_cash_balance(user_id, game_id)
current_holding = get_current_stock_holding(user_id, game_id, stock_pick)
place_order(
user_id=user_id,
game_id=game_id,
symbol=stock_pick,
buy_or_sell="buy",
cash_balance=cash_balance,
current_holding=current_holding,
order_type="market",
quantity_type="USD",
market_price=amzn_price,
amount=order_quantity,
time_in_force="day"
)
original_amzn_holding = get_current_stock_holding(user_id, game_id, stock_pick)
updated_cash = get_current_game_cash_balance(user_id, game_id)
expected_quantity = order_quantity // amzn_price
expected_cost = expected_quantity * amzn_price
self.assertEqual(original_amzn_holding, expected_quantity)
test_user_original_cash = STARTING_VIRTUAL_CASH - expected_cost
self.assertAlmostEqual(updated_cash, test_user_original_cash, 2)
stock_pick = "MELI"
user_id = 4
order_quantity = 600
meli_price = 600
cash_balance = get_current_game_cash_balance(user_id, game_id)
current_holding = get_current_stock_holding(user_id, game_id, stock_pick)
place_order(
user_id=user_id,
game_id=game_id,
symbol=stock_pick,
buy_or_sell="buy",
cash_balance=cash_balance,
current_holding=current_holding,
order_type="market",
quantity_type="Shares",
market_price=meli_price,
amount=order_quantity,
time_in_force="day"
)
original_meli_holding = get_current_stock_holding(user_id, game_id, stock_pick)
original_miguel_cash = get_current_game_cash_balance(user_id, game_id)
self.assertEqual(original_meli_holding, order_quantity)
miguel_cash = STARTING_VIRTUAL_CASH - order_quantity * meli_price
self.assertAlmostEqual(original_miguel_cash, miguel_cash, 2)
stock_pick = "NVDA"
user_id = 3
order_quantity = 1420
nvda_limit_ratio = 0.95
nvda_price = 350
stop_limit_price = nvda_price * nvda_limit_ratio
cash_balance = get_current_game_cash_balance(user_id, game_id)
current_holding = get_current_stock_holding(user_id, game_id, stock_pick)
place_order(
user_id=user_id,
game_id=game_id,
symbol=stock_pick,
buy_or_sell="buy",
cash_balance=cash_balance,
current_holding=current_holding,
order_type="limit",
quantity_type="Shares",
market_price=nvda_price,
amount=order_quantity,
time_in_force="until_cancelled",
stop_limit_price=stop_limit_price
)
updated_holding = get_current_stock_holding(user_id, game_id, stock_pick)
updated_cash = get_current_game_cash_balance(user_id, game_id)
self.assertEqual(updated_holding, 0)
self.assertEqual(updated_cash, STARTING_VIRTUAL_CASH)
with patch("backend.logic.games.fetch_price") as mock_price_fetch, patch(
"backend.logic.base.time") as mock_base_time, patch("backend.logic.games.time") as mock_game_time:
order_clear_price = stop_limit_price - 5
amzn_stop_ratio = 0.9
meli_limit_ratio = 1.1
mock_price_fetch.side_effect = [
(order_clear_price, None),
(amzn_stop_ratio * amzn_price - 1, None),
(meli_limit_ratio * meli_price + 1, None),
]
mock_game_time.time.side_effect = [
game_start_time + 24 * 60 * 60, # NVDA order from above
game_start_time + 24 * 60 * 60,
game_start_time + 24 * 60 * 60,
game_start_time + 24 * 60 * 60,
game_start_time + 24 * 60 * 60,
game_start_time + 24 * 60 * 60 + 1000, # AMZN order needs to clear on the same day
game_start_time + 48 * 60 * 60, # MELI order is open until being cancelled
game_start_time + 48 * 60 * 60,
game_start_time + 48 * 60 * 60,
game_start_time + 48 * 60 * 60,
game_start_time + 48 * 60 * 60,
]
mock_base_time.time.side_effect = [
game_start_time + 24 * 60 * 60,
game_start_time + 24 * 60 * 60,
game_start_time + 24 * 60 * 60,
game_start_time + 24 * 60 * 60 + 1000,
game_start_time + 24 * 60 * 60 + 1000,
game_start_time + 24 * 60 * 60 + 1000,
game_start_time + 48 * 60 * 60,
game_start_time + 48 * 60 * 60,
game_start_time + 48 * 60 * 60,
game_start_time + 48 * 60 * 60,
]
# First let's go ahead and clear that last transaction that we had above
with self.engine.connect() as conn:
open_order_id = conn.execute("""
SELECT id
FROM orders
WHERE user_id = %s AND game_id = %s AND symbol = %s;""",
user_id, game_id, stock_pick).fetchone()[0]
process_order(open_order_id)
updated_holding = get_current_stock_holding(user_id, game_id, stock_pick)
updated_cash = get_current_game_cash_balance(user_id, game_id)
self.assertEqual(updated_holding, order_quantity)
self.assertAlmostEqual(updated_cash, STARTING_VIRTUAL_CASH - order_clear_price * order_quantity, 3)
# Now let's go ahead and place stop-loss and stop-limit orders against existing positions
stock_pick = "AMZN"
user_id = 1
order_quantity = 250_000
cash_balance = get_current_game_cash_balance(user_id, game_id)
current_holding = get_current_stock_holding(user_id, game_id, stock_pick)
stop_limit_price = amzn_stop_ratio * amzn_price
place_order(
user_id=user_id,
game_id=game_id,
symbol=stock_pick,
buy_or_sell="sell",
cash_balance=cash_balance,
current_holding=current_holding,
order_type="stop",
quantity_type="USD",
market_price=stop_limit_price + 10,
amount=order_quantity,
time_in_force="day",
stop_limit_price=stop_limit_price
)
amzn_sales_entry = query_to_dict("""
SELECT id, price, quantity
FROM orders
WHERE user_id = %s AND game_id = %s AND symbol = %s
ORDER BY id DESC LIMIT 0, 1;
""", user_id, game_id, stock_pick)[0]
stock_pick = "MELI"
user_id = 4
miguel_order_quantity = 300
cash_balance = get_current_game_cash_balance(user_id, game_id)
current_holding = get_current_stock_holding(user_id, game_id, stock_pick)
place_order(
user_id=user_id,
game_id=game_id,
symbol=stock_pick,
buy_or_sell="sell",
cash_balance=cash_balance,
current_holding=current_holding,
order_type="limit",
quantity_type="Shares",
market_price=meli_limit_ratio * meli_price - 10,
amount=miguel_order_quantity,
time_in_force="until_cancelled",
stop_limit_price=meli_limit_ratio * meli_price
)
with self.engine.connect() as conn:
meli_open_order_id = conn.execute("""
SELECT id
FROM orders
WHERE user_id = %s AND game_id = %s AND symbol = %s
ORDER BY id DESC LIMIT 0, 1;""",
user_id, game_id, stock_pick).fetchone()[0]
process_order(amzn_sales_entry["id"])
process_order(meli_open_order_id)
with self.engine.connect() as conn:
query = """
SELECT o.user_id, o.id, o.symbol, os.clear_price
FROM orders o
INNER JOIN
order_status os
ON
o.id = os.order_id
WHERE
os.status = 'fulfilled' AND
game_id = %s;
"""
df = pd.read_sql(query, conn, params=[game_id])
test_user_id = 1
test_user_stock = "AMZN"
updated_holding = get_current_stock_holding(test_user_id, game_id, test_user_stock)
updated_cash = get_current_game_cash_balance(test_user_id, game_id)
amzn_clear_price = df[df["id"] == amzn_sales_entry["id"]].iloc[0]["clear_price"]
# This test is a little bit awkward because of how we are handling floating point for prices and
# doing integer round here. This may need to become more precise in the future
self.assertEqual(updated_holding, original_amzn_holding - amzn_sales_entry["quantity"])
self.assertAlmostEqual(updated_cash,
test_user_original_cash + amzn_sales_entry["quantity"] * amzn_clear_price, 2)
test_user_id = 4
test_user_stock = "MELI"
updated_holding = get_current_stock_holding(test_user_id, game_id, test_user_stock)
updated_cash = get_current_game_cash_balance(test_user_id, game_id)
meli_clear_price = df[df["id"] == meli_open_order_id].iloc[0]["clear_price"]
shares_sold = miguel_order_quantity
self.assertEqual(updated_holding, original_meli_holding - shares_sold)
self.assertAlmostEqual(updated_cash, original_miguel_cash + shares_sold * meli_clear_price, 2)
# if all users leave at the end of a game, that game should shut down
leave_game(game_id, 1)
leave_game(game_id, 3)
leave_game(game_id, 4)
game_status_entry = query_to_dict(
"SELECT * FROM game_status WHERE game_id = %s ORDER BY id DESC LIMIT 0, 1;", game_id)[0]
self.assertEqual(game_status_entry["status"], "cancelled")
self.assertEqual(json.loads(game_status_entry["users"]), [])
game_invites_entries = query_to_dict(
"SELECT * FROM game_invites WHERE game_id = %s ORDER BY id DESC LIMIT 0, 3;", game_id)
for entry in game_invites_entries:
self.assertEqual(entry["status"], "left")
def get_game_users(game_id: int):
usernames = get_all_game_usernames(game_id)
if check_single_player_mode(game_id):
index_names = query_to_dict("SELECT `name` FROM index_metadata")
usernames += [x["name"] for x in index_names]
return usernames
