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