Oleg Sheynin
1288 lines
38 KiB
Python
1288 lines
38 KiB
Python
import sqlite3
|
|
from pathlib import Path
|
|
|
|
import pandas as pd
|
|
import pytest
|
|
|
|
from scripts.spbt_day import (
|
|
ANALYZE_BUTTON_COLUMN,
|
|
PAIR_NAME_VALUE_COLUMN,
|
|
add_total_pnl,
|
|
calculate_pair_theo_executions,
|
|
calculate_pair_theo_ret,
|
|
calculate_ranked_pairs_theo_ret,
|
|
create_pair_name_dropdown,
|
|
create_pair_theo_ret_analyze_grid,
|
|
create_pair_trades_market_plot,
|
|
create_selected_pair_executions_grid,
|
|
connect_sqlite_read_only,
|
|
create_total_pnl_histogram,
|
|
find_repo_root,
|
|
format_pair_name_for_display,
|
|
format_pair_names_for_display,
|
|
format_pair_theo_ret_for_analyze_grid,
|
|
infer_trading_day_start_ns,
|
|
list_candidate_files,
|
|
load_pair_market_data,
|
|
load_selector_pair_rankings,
|
|
load_trading_instructions,
|
|
normalize_directory,
|
|
pair_assets_and_quote,
|
|
parse_selector_instrument,
|
|
parse_mr_score_final,
|
|
rank_selector_pairs,
|
|
read_only_sqlite_uri,
|
|
show_interactive_dataframe,
|
|
sorted_pair_names,
|
|
)
|
|
|
|
|
|
@pytest.mark.parametrize(
|
|
("raw_score", "expected_score", "expected_status"),
|
|
[
|
|
('{"final":"0.75"}', 0.75, "ok"),
|
|
('{"final":0.5}', 0.5, "ok"),
|
|
(None, None, "missing_mr_score"),
|
|
("not-json", None, "malformed_json"),
|
|
("[]", None, "unexpected_json_type"),
|
|
('{"other": "0.1"}', None, "missing_final"),
|
|
('{"final": ""}', None, "missing_final"),
|
|
('{"final": true}', None, "non_numeric_final"),
|
|
('{"final": "abc"}', None, "non_numeric_final"),
|
|
('{"final": "NaN"}', None, "non_finite_final"),
|
|
],
|
|
)
|
|
def test_parse_mr_score_final(raw_score, expected_score, expected_status):
|
|
assert parse_mr_score_final(raw_score) == (expected_score, expected_status)
|
|
|
|
|
|
def test_rank_selector_pairs_uses_dense_descending_rank_and_preserves_bad_rows():
|
|
selector_pairs = pd.DataFrame(
|
|
{
|
|
"pair_name": ["PAIR_C", "PAIR_A", "PAIR_B", "PAIR_BAD"],
|
|
"mr_score": [
|
|
'{"final":"0.7"}',
|
|
'{"final":"0.9"}',
|
|
'{"final":"0.7"}',
|
|
'{"final":"bad"}',
|
|
],
|
|
}
|
|
)
|
|
|
|
ranked = rank_selector_pairs(selector_pairs)
|
|
|
|
assert ranked["pair_name"].tolist() == ["PAIR_A", "PAIR_B", "PAIR_C", "PAIR_BAD"]
|
|
assert ranked["pair_rank"].iloc[:3].tolist() == [1, 2, 2]
|
|
assert pd.isna(ranked["pair_rank"].iloc[3])
|
|
assert ranked["mr_score_parse_status"].tolist() == [
|
|
"ok",
|
|
"ok",
|
|
"ok",
|
|
"non_numeric_final",
|
|
]
|
|
|
|
|
|
def test_load_selector_pair_rankings_validates_required_table():
|
|
conn = sqlite3.connect(":memory:")
|
|
|
|
with pytest.raises(ValueError, match="missing required table: selector_pairs"):
|
|
load_selector_pair_rankings(conn)
|
|
|
|
|
|
def test_load_selector_pair_rankings_reads_sqlite_table():
|
|
conn = sqlite3.connect(":memory:")
|
|
conn.execute("CREATE TABLE selector_pairs (pair_name TEXT, mr_score TEXT)")
|
|
conn.executemany(
|
|
"INSERT INTO selector_pairs (pair_name, mr_score) VALUES (?, ?)",
|
|
[
|
|
("PAIR_A", '{"final":"0.1"}'),
|
|
("PAIR_B", '{"final":"0.2"}'),
|
|
],
|
|
)
|
|
|
|
ranked = load_selector_pair_rankings(conn)
|
|
|
|
assert ranked[["pair_rank", "pair_name", "mr_score_final"]].to_dict("records") == [
|
|
{"pair_rank": 1, "pair_name": "PAIR_B", "mr_score_final": 0.2},
|
|
{"pair_rank": 2, "pair_name": "PAIR_A", "mr_score_final": 0.1},
|
|
]
|
|
|
|
|
|
def test_pair_assets_and_quote_parses_two_leg_pair():
|
|
assert pair_assets_and_quote("ADA:USD-BTC:USD") == (("ADA", "BTC"), "USD")
|
|
|
|
|
|
def test_infer_trading_day_start_ns_uses_utc_midnight():
|
|
trd_inst_df = pd.DataFrame(
|
|
{
|
|
"time_ns": [
|
|
pd.Timestamp("2026-06-17T02:25:00Z").value,
|
|
pd.Timestamp("2026-06-17T00:01:00Z").value,
|
|
]
|
|
}
|
|
)
|
|
|
|
assert infer_trading_day_start_ns(trd_inst_df) == pd.Timestamp(
|
|
"2026-06-17T00:00:00Z"
|
|
).value
|
|
|
|
|
|
def test_parse_selector_instrument_splits_exchange_account_and_instrument_id():
|
|
assert parse_selector_instrument("COINBASE_AT:PAIR-ADA-USD") == (
|
|
"COINBASE_AT",
|
|
"PAIR-ADA-USD",
|
|
)
|
|
|
|
|
|
def test_load_pair_market_data_maps_selector_instruments_and_relative_close():
|
|
trading_day_start_ns = 10
|
|
conn = sqlite3.connect(":memory:")
|
|
conn.execute(
|
|
"""
|
|
CREATE TABLE selector_pairs (
|
|
pair_name TEXT,
|
|
instrument_a TEXT,
|
|
instrument_b TEXT
|
|
)
|
|
"""
|
|
)
|
|
conn.execute(
|
|
"""
|
|
CREATE TABLE market (
|
|
tstamp TEXT,
|
|
tstamp_ns INTEGER,
|
|
exch_acct TEXT,
|
|
instrument_id TEXT,
|
|
close REAL
|
|
)
|
|
"""
|
|
)
|
|
conn.execute(
|
|
"INSERT INTO selector_pairs VALUES (?, ?, ?)",
|
|
(
|
|
"AAA:USD-BBB:USD",
|
|
"EXCH_A:PAIR-AAA-USD",
|
|
"EXCH_B:PAIR-BBB-USD",
|
|
),
|
|
)
|
|
conn.executemany(
|
|
"INSERT INTO market VALUES (?, ?, ?, ?, ?)",
|
|
[
|
|
("pre", 9, "EXCH_A", "PAIR-AAA-USD", 90.0),
|
|
("t0", 10, "EXCH_A", "PAIR-AAA-USD", 100.0),
|
|
("t1", 11, "EXCH_A", "PAIR-AAA-USD", 110.0),
|
|
("pre", 9, "EXCH_B", "PAIR-BBB-USD", 55.0),
|
|
("t0", 10, "EXCH_B", "PAIR-BBB-USD", 50.0),
|
|
("t1", 11, "EXCH_B", "PAIR-BBB-USD", 45.0),
|
|
("t0", 10, "OTHER", "PAIR-AAA-USD", 999.0),
|
|
],
|
|
)
|
|
|
|
market_data = load_pair_market_data(
|
|
conn,
|
|
"AAA:USD-BBB:USD",
|
|
trading_day_start_ns=trading_day_start_ns,
|
|
)
|
|
|
|
assert market_data[
|
|
["asset", "exch_acct", "instrument_id", "close", "initial_close"]
|
|
].to_dict("records") == [
|
|
{
|
|
"asset": "AAA",
|
|
"exch_acct": "EXCH_A",
|
|
"instrument_id": "PAIR-AAA-USD",
|
|
"close": 100.0,
|
|
"initial_close": 100.0,
|
|
},
|
|
{
|
|
"asset": "AAA",
|
|
"exch_acct": "EXCH_A",
|
|
"instrument_id": "PAIR-AAA-USD",
|
|
"close": 110.0,
|
|
"initial_close": 100.0,
|
|
},
|
|
{
|
|
"asset": "BBB",
|
|
"exch_acct": "EXCH_B",
|
|
"instrument_id": "PAIR-BBB-USD",
|
|
"close": 50.0,
|
|
"initial_close": 50.0,
|
|
},
|
|
{
|
|
"asset": "BBB",
|
|
"exch_acct": "EXCH_B",
|
|
"instrument_id": "PAIR-BBB-USD",
|
|
"close": 45.0,
|
|
"initial_close": 50.0,
|
|
},
|
|
]
|
|
assert market_data["time_ns"].tolist() == [10, 11, 10, 11]
|
|
assert market_data["relative_close"].tolist() == [
|
|
0.0,
|
|
pytest.approx(0.1),
|
|
0.0,
|
|
pytest.approx(-0.1),
|
|
]
|
|
|
|
|
|
def test_load_pair_market_data_requires_market_rows_for_both_assets():
|
|
trading_day_start_ns = 1
|
|
conn = sqlite3.connect(":memory:")
|
|
conn.execute(
|
|
"""
|
|
CREATE TABLE selector_pairs (
|
|
pair_name TEXT,
|
|
instrument_a TEXT,
|
|
instrument_b TEXT
|
|
)
|
|
"""
|
|
)
|
|
conn.execute(
|
|
"""
|
|
CREATE TABLE market (
|
|
tstamp TEXT,
|
|
tstamp_ns INTEGER,
|
|
exch_acct TEXT,
|
|
instrument_id TEXT,
|
|
close REAL
|
|
)
|
|
"""
|
|
)
|
|
conn.execute(
|
|
"INSERT INTO selector_pairs VALUES (?, ?, ?)",
|
|
(
|
|
"AAA:USD-BBB:USD",
|
|
"EXCH_A:PAIR-AAA-USD",
|
|
"EXCH_B:PAIR-BBB-USD",
|
|
),
|
|
)
|
|
conn.execute(
|
|
"INSERT INTO market VALUES (?, ?, ?, ?, ?)",
|
|
("t1", 1, "EXCH_A", "PAIR-AAA-USD", 100.0),
|
|
)
|
|
|
|
with pytest.raises(
|
|
ValueError,
|
|
match=r"market does not contain data for asset\(s\): BBB",
|
|
):
|
|
load_pair_market_data(
|
|
conn,
|
|
"AAA:USD-BBB:USD",
|
|
trading_day_start_ns=trading_day_start_ns,
|
|
)
|
|
|
|
|
|
def test_load_pair_market_data_requires_time_zero_close_for_each_asset():
|
|
trading_day_start_ns = 1
|
|
conn = sqlite3.connect(":memory:")
|
|
conn.execute(
|
|
"""
|
|
CREATE TABLE selector_pairs (
|
|
pair_name TEXT,
|
|
instrument_a TEXT,
|
|
instrument_b TEXT
|
|
)
|
|
"""
|
|
)
|
|
conn.execute(
|
|
"""
|
|
CREATE TABLE market (
|
|
tstamp TEXT,
|
|
tstamp_ns INTEGER,
|
|
exch_acct TEXT,
|
|
instrument_id TEXT,
|
|
close REAL
|
|
)
|
|
"""
|
|
)
|
|
conn.execute(
|
|
"INSERT INTO selector_pairs VALUES (?, ?, ?)",
|
|
(
|
|
"AAA:USD-BBB:USD",
|
|
"EXCH_A:PAIR-AAA-USD",
|
|
"EXCH_B:PAIR-BBB-USD",
|
|
),
|
|
)
|
|
conn.executemany(
|
|
"INSERT INTO market VALUES (?, ?, ?, ?, ?)",
|
|
[
|
|
("t1", 1, "EXCH_A", "PAIR-AAA-USD", None),
|
|
("t2", 2, "EXCH_A", "PAIR-AAA-USD", 110.0),
|
|
("t1", 1, "EXCH_B", "PAIR-BBB-USD", 50.0),
|
|
],
|
|
)
|
|
|
|
with pytest.raises(
|
|
ValueError,
|
|
match=r"market initial close must be positive for asset\(s\): AAA",
|
|
):
|
|
load_pair_market_data(
|
|
conn,
|
|
"AAA:USD-BBB:USD",
|
|
trading_day_start_ns=trading_day_start_ns,
|
|
)
|
|
|
|
|
|
def test_load_pair_market_data_requires_exact_trading_day_start_row():
|
|
trading_day_start_ns = 10
|
|
conn = sqlite3.connect(":memory:")
|
|
conn.execute(
|
|
"""
|
|
CREATE TABLE selector_pairs (
|
|
pair_name TEXT,
|
|
instrument_a TEXT,
|
|
instrument_b TEXT
|
|
)
|
|
"""
|
|
)
|
|
conn.execute(
|
|
"""
|
|
CREATE TABLE market (
|
|
tstamp TEXT,
|
|
tstamp_ns INTEGER,
|
|
exch_acct TEXT,
|
|
instrument_id TEXT,
|
|
close REAL
|
|
)
|
|
"""
|
|
)
|
|
conn.execute(
|
|
"INSERT INTO selector_pairs VALUES (?, ?, ?)",
|
|
(
|
|
"AAA:USD-BBB:USD",
|
|
"EXCH_A:PAIR-AAA-USD",
|
|
"EXCH_B:PAIR-BBB-USD",
|
|
),
|
|
)
|
|
conn.executemany(
|
|
"INSERT INTO market VALUES (?, ?, ?, ?, ?)",
|
|
[
|
|
("t1", 11, "EXCH_A", "PAIR-AAA-USD", 110.0),
|
|
("t0", 10, "EXCH_B", "PAIR-BBB-USD", 50.0),
|
|
],
|
|
)
|
|
|
|
with pytest.raises(
|
|
ValueError,
|
|
match=(
|
|
"market does not contain trading-day start close for "
|
|
r"asset\(s\): AAA"
|
|
),
|
|
):
|
|
load_pair_market_data(
|
|
conn,
|
|
"AAA:USD-BBB:USD",
|
|
trading_day_start_ns=trading_day_start_ns,
|
|
)
|
|
|
|
|
|
def test_create_pair_trades_market_plot_adds_relative_lines_and_trade_markers():
|
|
market_data = pd.DataFrame(
|
|
{
|
|
"pair_name": ["AAA:USD-BBB:USD"] * 4,
|
|
"asset": ["AAA", "AAA", "BBB", "BBB"],
|
|
"time": ["t1", "t2", "t1", "t2"],
|
|
"time_ns": [1, 2, 1, 2],
|
|
"close": [100.0, 110.0, 50.0, 45.0],
|
|
"initial_close": [100.0, 100.0, 50.0, 50.0],
|
|
"relative_close": [0.0, 0.1, 0.0, -0.1],
|
|
}
|
|
)
|
|
theo_executions = pd.DataFrame(
|
|
{
|
|
"time": ["t1.5", "t2.5"],
|
|
"time_ns": [15, 25],
|
|
"asset": ["AAA", "BBB"],
|
|
"action": ["TARGET", "CLOSE"],
|
|
"side": ["BUY", "SELL"],
|
|
"size": [2.0, -3.0],
|
|
"price": [105.0, 40.0],
|
|
}
|
|
)
|
|
|
|
figure = create_pair_trades_market_plot(
|
|
"AAA:USD-BBB:USD",
|
|
market_data,
|
|
theo_executions,
|
|
)
|
|
|
|
assert [trace.name for trace in figure.data] == [
|
|
"AAA close",
|
|
"AAA BUY",
|
|
"BBB close",
|
|
"BBB SELL",
|
|
]
|
|
assert figure.data[0].y.tolist() == [0.0, 0.1]
|
|
assert figure.data[1].marker.symbol == "triangle-up"
|
|
assert figure.data[1].marker.color == "darkgreen"
|
|
assert figure.data[1].x.tolist() == ["t1.5"]
|
|
assert figure.data[1].y.tolist() == [pytest.approx(0.05)]
|
|
assert figure.data[3].marker.symbol == "triangle-down"
|
|
assert figure.data[3].marker.color == "darkred"
|
|
assert figure.data[3].x.tolist() == ["t2.5"]
|
|
assert figure.data[3].y.tolist() == [pytest.approx(-0.2)]
|
|
assert figure.layout.xaxis.range == ("t1", "t2")
|
|
|
|
|
|
def test_calculate_pair_theo_ret_replaces_targets_and_closes_open_position():
|
|
trd_inst_df = pd.DataFrame(
|
|
{
|
|
"time_ns": [1, 2, 3],
|
|
"tstamp": ["t1", "t2", "t3"],
|
|
"data": [
|
|
(
|
|
'{"action":"TARGET","quote_asset":"USD","assets":'
|
|
'{"AAA":{"reference_price":"100","strength":"0.01"},'
|
|
'"BBB":{"reference_price":"50","strength":"-0.02"}}}'
|
|
),
|
|
(
|
|
'{"action":"TARGET","quote_asset":"USD","assets":'
|
|
'{"AAA":{"reference_price":"120","strength":"0.01"},'
|
|
'"BBB":{"reference_price":"60","strength":"-0.02"}}}'
|
|
),
|
|
(
|
|
'{"action":"CLOSE","quote_asset":"USD","assets":'
|
|
'{"AAA":{"reference_price":"132"},'
|
|
'"BBB":{"reference_price":"54"}}}'
|
|
),
|
|
],
|
|
}
|
|
)
|
|
|
|
theo_ret = calculate_pair_theo_ret("AAA:USD-BBB:USD", trd_inst_df)
|
|
|
|
assert theo_ret == {
|
|
"pair_name": "AAA:USD-BBB:USD",
|
|
"num_trades": 6,
|
|
"realized_pnl": pytest.approx(0.1),
|
|
"unrealized_pnl": 0.0,
|
|
}
|
|
|
|
|
|
def test_calculate_pair_theo_executions_uses_target_deltas_and_signed_cash():
|
|
trd_inst_df = pd.DataFrame(
|
|
{
|
|
"time_ns": [1, 2, 3, 4],
|
|
"tstamp": ["t1", "t2", "t3", "t4"],
|
|
"data": [
|
|
(
|
|
'{"action":"CLOSE","quote_asset":"USD","assets":'
|
|
'{"AAA":{"reference_price":"95"},'
|
|
'"BBB":{"reference_price":"55"}}}'
|
|
),
|
|
(
|
|
'{"action":"TARGET","quote_asset":"USD","assets":'
|
|
'{"AAA":{"reference_price":"100","strength":"0.01"},'
|
|
'"BBB":{"reference_price":"50","strength":"-0.02"}}}'
|
|
),
|
|
(
|
|
'{"action":"TARGET","quote_asset":"USD","assets":'
|
|
'{"AAA":{"reference_price":"110","strength":"0.015"},'
|
|
'"BBB":{"reference_price":"45","strength":"-0.01"}}}'
|
|
),
|
|
(
|
|
'{"action":"CLOSE","quote_asset":"USD","assets":'
|
|
'{"AAA":{"reference_price":"120"},'
|
|
'"BBB":{"reference_price":"40"}}}'
|
|
),
|
|
],
|
|
}
|
|
)
|
|
|
|
executions = calculate_pair_theo_executions("AAA:USD-BBB:USD", trd_inst_df)
|
|
|
|
display_columns = [
|
|
"time",
|
|
"asset",
|
|
"action",
|
|
"side",
|
|
"strength",
|
|
"size",
|
|
"price",
|
|
"usd_value",
|
|
]
|
|
execution_rows = executions[display_columns].to_dict("records")
|
|
|
|
assert execution_rows[:4] == [
|
|
{
|
|
"time": "t2",
|
|
"asset": "AAA",
|
|
"action": "TARGET",
|
|
"side": "BUY",
|
|
"strength": 0.01,
|
|
"size": 1.0,
|
|
"price": 100.0,
|
|
"usd_value": -100.0,
|
|
},
|
|
{
|
|
"time": "t2",
|
|
"asset": "BBB",
|
|
"action": "TARGET",
|
|
"side": "SELL",
|
|
"strength": -0.02,
|
|
"size": -4.0,
|
|
"price": 50.0,
|
|
"usd_value": 200.0,
|
|
},
|
|
{
|
|
"time": "t3",
|
|
"asset": "AAA",
|
|
"action": "TARGET",
|
|
"side": "BUY",
|
|
"strength": 0.015,
|
|
"size": pytest.approx(0.36363636363636365),
|
|
"price": 110.0,
|
|
"usd_value": pytest.approx(-40.0),
|
|
},
|
|
{
|
|
"time": "t3",
|
|
"asset": "BBB",
|
|
"action": "TARGET",
|
|
"side": "BUY",
|
|
"strength": -0.01,
|
|
"size": pytest.approx(1.7777777777777777),
|
|
"price": 45.0,
|
|
"usd_value": pytest.approx(-80.0),
|
|
},
|
|
]
|
|
assert execution_rows[4] | {"strength": None} == {
|
|
"time": "t4",
|
|
"asset": "AAA",
|
|
"action": "CLOSE",
|
|
"side": "SELL",
|
|
"strength": None,
|
|
"size": pytest.approx(-1.3636363636363638),
|
|
"price": 120.0,
|
|
"usd_value": pytest.approx(163.63636363636365),
|
|
}
|
|
assert execution_rows[5] | {"strength": None} == {
|
|
"time": "t4",
|
|
"asset": "BBB",
|
|
"action": "CLOSE",
|
|
"side": "BUY",
|
|
"strength": None,
|
|
"size": pytest.approx(2.2222222222222223),
|
|
"price": 40.0,
|
|
"usd_value": pytest.approx(-88.88888888888889),
|
|
}
|
|
assert executions["strength"].iloc[:4].tolist() == [0.01, -0.02, 0.015, -0.01]
|
|
assert executions["strength"].iloc[4:].isna().all()
|
|
|
|
|
|
def test_calculate_pair_theo_executions_skips_small_target_strength_changes():
|
|
trd_inst_df = pd.DataFrame(
|
|
{
|
|
"time_ns": [1, 2, 3, 4],
|
|
"tstamp": ["t1", "t2", "t3", "t4"],
|
|
"data": [
|
|
(
|
|
'{"action":"TARGET","quote_asset":"USD","assets":'
|
|
'{"AAA":{"reference_price":"100","strength":"0.5"},'
|
|
'"BBB":{"reference_price":"50","strength":"-0.5"}}}'
|
|
),
|
|
(
|
|
'{"action":"TARGET","quote_asset":"USD","assets":'
|
|
'{"AAA":{"reference_price":"100","strength":"0.53"},'
|
|
'"BBB":{"reference_price":"50","strength":"-0.47"}}}'
|
|
),
|
|
(
|
|
'{"action":"TARGET","quote_asset":"USD","assets":'
|
|
'{"AAA":{"reference_price":"100","strength":"0.7"},'
|
|
'"BBB":{"reference_price":"50","strength":"-0.7"}}}'
|
|
),
|
|
(
|
|
'{"action":"CLOSE","quote_asset":"USD","assets":'
|
|
'{"AAA":{"reference_price":"100"},'
|
|
'"BBB":{"reference_price":"50"}}}'
|
|
),
|
|
],
|
|
}
|
|
)
|
|
|
|
executions = calculate_pair_theo_executions(
|
|
"AAA:USD-BBB:USD",
|
|
trd_inst_df,
|
|
min_pctg_change=25,
|
|
)
|
|
|
|
execution_rows = executions[["time", "asset", "action", "strength", "size"]]
|
|
|
|
assert execution_rows.iloc[:4].to_dict("records") == [
|
|
{
|
|
"time": "t1",
|
|
"asset": "AAA",
|
|
"action": "TARGET",
|
|
"strength": 0.5,
|
|
"size": 50.0,
|
|
},
|
|
{
|
|
"time": "t1",
|
|
"asset": "BBB",
|
|
"action": "TARGET",
|
|
"strength": -0.5,
|
|
"size": -100.0,
|
|
},
|
|
{
|
|
"time": "t3",
|
|
"asset": "AAA",
|
|
"action": "TARGET",
|
|
"strength": 0.7,
|
|
"size": 20.0,
|
|
},
|
|
{
|
|
"time": "t3",
|
|
"asset": "BBB",
|
|
"action": "TARGET",
|
|
"strength": -0.7,
|
|
"size": -40.0,
|
|
},
|
|
]
|
|
assert execution_rows.iloc[4].to_dict() | {"strength": None} == {
|
|
"time": "t4",
|
|
"asset": "AAA",
|
|
"action": "CLOSE",
|
|
"strength": None,
|
|
"size": -70.0,
|
|
}
|
|
assert execution_rows.iloc[5].to_dict() | {"strength": None} == {
|
|
"time": "t4",
|
|
"asset": "BBB",
|
|
"action": "CLOSE",
|
|
"strength": None,
|
|
"size": 140.0,
|
|
}
|
|
|
|
|
|
def test_calculate_pair_theo_executions_trades_threshold_boundary_and_zero_crossing():
|
|
trd_inst_df = pd.DataFrame(
|
|
{
|
|
"time_ns": [1, 2, 3, 4],
|
|
"tstamp": ["t1", "t2", "t3", "t4"],
|
|
"data": [
|
|
(
|
|
'{"action":"TARGET","quote_asset":"USD","assets":'
|
|
'{"AAA":{"reference_price":"100","strength":"0.4"},'
|
|
'"BBB":{"reference_price":"50","strength":"0"}}}'
|
|
),
|
|
(
|
|
'{"action":"TARGET","quote_asset":"USD","assets":'
|
|
'{"AAA":{"reference_price":"100","strength":"0.5"},'
|
|
'"BBB":{"reference_price":"50","strength":"0"}}}'
|
|
),
|
|
(
|
|
'{"action":"TARGET","quote_asset":"USD","assets":'
|
|
'{"AAA":{"reference_price":"100","strength":"0.51"},'
|
|
'"BBB":{"reference_price":"50","strength":"0.1"}}}'
|
|
),
|
|
(
|
|
'{"action":"CLOSE","quote_asset":"USD","assets":'
|
|
'{"AAA":{"reference_price":"100"},'
|
|
'"BBB":{"reference_price":"50"}}}'
|
|
),
|
|
],
|
|
}
|
|
)
|
|
|
|
executions = calculate_pair_theo_executions(
|
|
"AAA:USD-BBB:USD",
|
|
trd_inst_df,
|
|
min_pctg_change=25,
|
|
)
|
|
|
|
assert executions[["time", "asset", "action", "strength", "size"]].iloc[
|
|
:3
|
|
].to_dict("records") == [
|
|
{
|
|
"time": "t1",
|
|
"asset": "AAA",
|
|
"action": "TARGET",
|
|
"strength": 0.4,
|
|
"size": 40.0,
|
|
},
|
|
{
|
|
"time": "t2",
|
|
"asset": "AAA",
|
|
"action": "TARGET",
|
|
"strength": 0.5,
|
|
"size": 10.0,
|
|
},
|
|
{
|
|
"time": "t3",
|
|
"asset": "BBB",
|
|
"action": "TARGET",
|
|
"strength": 0.1,
|
|
"size": 20.0,
|
|
},
|
|
]
|
|
|
|
|
|
def test_calculate_pair_theo_ret_uses_execution_cash_flows():
|
|
trd_inst_df = pd.DataFrame(
|
|
{
|
|
"time_ns": [1, 2, 3],
|
|
"data": [
|
|
(
|
|
'{"action":"TARGET","quote_asset":"USD","assets":'
|
|
'{"AAA":{"reference_price":"100","strength":"0.01"},'
|
|
'"BBB":{"reference_price":"50","strength":"-0.02"}}}'
|
|
),
|
|
(
|
|
'{"action":"TARGET","quote_asset":"USD","assets":'
|
|
'{"AAA":{"reference_price":"110","strength":"0.015"},'
|
|
'"BBB":{"reference_price":"45","strength":"-0.01"}}}'
|
|
),
|
|
(
|
|
'{"action":"CLOSE","quote_asset":"USD","assets":'
|
|
'{"AAA":{"reference_price":"120"},'
|
|
'"BBB":{"reference_price":"40"}}}'
|
|
),
|
|
],
|
|
}
|
|
)
|
|
|
|
assert calculate_pair_theo_ret("AAA:USD-BBB:USD", trd_inst_df) == {
|
|
"pair_name": "AAA:USD-BBB:USD",
|
|
"num_trades": 6,
|
|
"realized_pnl": pytest.approx(0.5474747474747474),
|
|
"unrealized_pnl": 0.0,
|
|
}
|
|
|
|
|
|
def test_calculate_pair_theo_ret_ignores_unmatched_quote_and_close_without_target():
|
|
trd_inst_df = pd.DataFrame(
|
|
{
|
|
"time_ns": [1, 2],
|
|
"data": [
|
|
(
|
|
'{"action":"TARGET","quote_asset":"EUR","assets":'
|
|
'{"AAA":{"reference_price":"100","strength":"0.01"},'
|
|
'"BBB":{"reference_price":"50","strength":"-0.02"}}}'
|
|
),
|
|
(
|
|
'{"action":"CLOSE","quote_asset":"USD","assets":'
|
|
'{"AAA":{"reference_price":"110"},'
|
|
'"BBB":{"reference_price":"45"}}}'
|
|
),
|
|
],
|
|
}
|
|
)
|
|
|
|
assert calculate_pair_theo_ret("AAA:USD-BBB:USD", trd_inst_df) == {
|
|
"pair_name": "AAA:USD-BBB:USD",
|
|
"num_trades": 0,
|
|
"realized_pnl": 0.0,
|
|
"unrealized_pnl": 0.0,
|
|
}
|
|
|
|
|
|
def test_calculate_ranked_pairs_theo_ret_preserves_pairs_without_instructions():
|
|
rankings = pd.DataFrame(
|
|
{
|
|
"pair_name": ["AAA:USD-BBB:USD", "CCC:USD-DDD:USD"],
|
|
"pair_rank": pd.Series([1, 2], dtype="Int64"),
|
|
}
|
|
)
|
|
trd_inst_df = pd.DataFrame(
|
|
{
|
|
"time_ns": [1, 2],
|
|
"data": [
|
|
(
|
|
'{"action":"TARGET","quote_asset":"USD","assets":'
|
|
'{"AAA":{"reference_price":"100","strength":"0.01"},'
|
|
'"BBB":{"reference_price":"50","strength":"-0.02"}}}'
|
|
),
|
|
(
|
|
'{"action":"CLOSE","quote_asset":"USD","assets":'
|
|
'{"AAA":{"reference_price":"110"},'
|
|
'"BBB":{"reference_price":"45"}}}'
|
|
),
|
|
],
|
|
}
|
|
)
|
|
|
|
result = calculate_ranked_pairs_theo_ret(rankings, trd_inst_df)
|
|
|
|
assert result.to_dict("records") == [
|
|
{
|
|
"pair_name": "AAA:USD-BBB:USD",
|
|
"mr_ranking": 1,
|
|
"num_trades": 4,
|
|
"realized_pnl": 0.3,
|
|
"unrealized_pnl": 0.0,
|
|
},
|
|
{
|
|
"pair_name": "CCC:USD-DDD:USD",
|
|
"mr_ranking": 2,
|
|
"num_trades": 0,
|
|
"realized_pnl": 0.0,
|
|
"unrealized_pnl": 0.0,
|
|
},
|
|
]
|
|
|
|
|
|
def test_calculate_ranked_pairs_theo_ret_applies_min_pctg_change():
|
|
rankings = pd.DataFrame(
|
|
{
|
|
"pair_name": ["AAA:USD-BBB:USD"],
|
|
"pair_rank": pd.Series([1], dtype="Int64"),
|
|
}
|
|
)
|
|
trd_inst_df = pd.DataFrame(
|
|
{
|
|
"time_ns": [1, 2, 3],
|
|
"data": [
|
|
(
|
|
'{"action":"TARGET","quote_asset":"USD","assets":'
|
|
'{"AAA":{"reference_price":"100","strength":"0.5"},'
|
|
'"BBB":{"reference_price":"50","strength":"-0.5"}}}'
|
|
),
|
|
(
|
|
'{"action":"TARGET","quote_asset":"USD","assets":'
|
|
'{"AAA":{"reference_price":"100","strength":"0.53"},'
|
|
'"BBB":{"reference_price":"50","strength":"-0.47"}}}'
|
|
),
|
|
(
|
|
'{"action":"CLOSE","quote_asset":"USD","assets":'
|
|
'{"AAA":{"reference_price":"100"},'
|
|
'"BBB":{"reference_price":"50"}}}'
|
|
),
|
|
],
|
|
}
|
|
)
|
|
|
|
result = calculate_ranked_pairs_theo_ret(
|
|
rankings,
|
|
trd_inst_df,
|
|
min_pctg_change=25,
|
|
)
|
|
|
|
assert result.to_dict("records") == [
|
|
{
|
|
"pair_name": "AAA:USD-BBB:USD",
|
|
"mr_ranking": 1,
|
|
"num_trades": 4,
|
|
"realized_pnl": 0.0,
|
|
"unrealized_pnl": 0.0,
|
|
}
|
|
]
|
|
|
|
|
|
def test_calculate_ranked_pairs_theo_ret_validates_min_pctg_change():
|
|
rankings = pd.DataFrame(
|
|
{
|
|
"pair_name": ["AAA:USD-BBB:USD"],
|
|
"pair_rank": pd.Series([1], dtype="Int64"),
|
|
}
|
|
)
|
|
trd_inst_df = pd.DataFrame({"time_ns": [], "data": []})
|
|
|
|
with pytest.raises(ValueError, match="min_pctg_change must be non-negative"):
|
|
calculate_ranked_pairs_theo_ret(
|
|
rankings,
|
|
trd_inst_df,
|
|
min_pctg_change=-1,
|
|
)
|
|
|
|
|
|
def test_load_trading_instructions_validates_required_table():
|
|
conn = sqlite3.connect(":memory:")
|
|
|
|
with pytest.raises(ValueError, match="missing required table: trading_instructions"):
|
|
load_trading_instructions(conn)
|
|
|
|
|
|
def test_load_trading_instructions_requires_sp_quant_metric_columns():
|
|
conn = sqlite3.connect(":memory:")
|
|
conn.execute(
|
|
"""
|
|
CREATE TABLE trading_instructions (
|
|
tstamp TEXT,
|
|
tstamp_ns INTEGER,
|
|
action TEXT,
|
|
quote_asset TEXT,
|
|
assets TEXT
|
|
)
|
|
"""
|
|
)
|
|
|
|
with pytest.raises(
|
|
ValueError,
|
|
match=r"SP Quant column\(s\) \[beta, scaled_disequilibrium\]",
|
|
):
|
|
load_trading_instructions(conn)
|
|
|
|
|
|
def test_load_trading_instructions_reads_sp_quant_schema_with_time_alias():
|
|
conn = sqlite3.connect(":memory:")
|
|
conn.execute(
|
|
"""
|
|
CREATE TABLE trading_instructions (
|
|
tstamp TEXT,
|
|
tstamp_ns INTEGER,
|
|
type TEXT,
|
|
book_id TEXT,
|
|
strategy_id TEXT,
|
|
action TEXT,
|
|
quote_asset TEXT,
|
|
assets TEXT,
|
|
scaled_disequilibrium REAL,
|
|
beta REAL
|
|
)
|
|
"""
|
|
)
|
|
conn.executemany(
|
|
"INSERT INTO trading_instructions VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)",
|
|
[
|
|
(
|
|
"t2",
|
|
2,
|
|
"CLOSE_POSITION",
|
|
"book",
|
|
"strategy",
|
|
"CLOSE",
|
|
"USD",
|
|
'{"AAA":{"reference_price":"110"},"BBB":{"reference_price":"45"}}',
|
|
-0.5,
|
|
0.75,
|
|
),
|
|
(
|
|
"t1",
|
|
1,
|
|
"TARGET_POSITION",
|
|
"book",
|
|
"strategy",
|
|
"TARGET",
|
|
"USD",
|
|
(
|
|
'{"AAA":{"reference_price":"100","strength":"0.5"},'
|
|
'"BBB":{"reference_price":"50","strength":"-0.5"}}'
|
|
),
|
|
-1.25,
|
|
0.75,
|
|
),
|
|
],
|
|
)
|
|
|
|
trading_instructions = load_trading_instructions(conn)
|
|
|
|
assert trading_instructions["tstamp_ns"].tolist() == [1, 2]
|
|
assert trading_instructions["time_ns"].tolist() == [1, 2]
|
|
assert trading_instructions["action"].tolist() == ["TARGET", "CLOSE"]
|
|
assert trading_instructions["scaled_disequilibrium"].tolist() == [-1.25, -0.5]
|
|
assert trading_instructions["beta"].tolist() == [0.75, 0.75]
|
|
|
|
|
|
def test_calculate_pair_theo_executions_reads_sp_quant_instruction_columns():
|
|
trd_inst_df = pd.DataFrame(
|
|
{
|
|
"tstamp_ns": [1, 2],
|
|
"time_ns": [1, 2],
|
|
"tstamp": ["t1", "t2"],
|
|
"action": ["TARGET", "CLOSE"],
|
|
"quote_asset": ["USD", "USD"],
|
|
"assets": [
|
|
(
|
|
'{"AAA":{"reference_price":"100","strength":"0.5"},'
|
|
'"BBB":{"reference_price":"50","strength":"-0.5"}}'
|
|
),
|
|
'{"AAA":{"reference_price":"110"},"BBB":{"reference_price":"45"}}',
|
|
],
|
|
"scaled_disequilibrium": [-1.25, -0.5],
|
|
"beta": [0.75, 0.75],
|
|
}
|
|
)
|
|
|
|
executions = calculate_pair_theo_executions("AAA:USD-BBB:USD", trd_inst_df)
|
|
|
|
assert executions[
|
|
["time", "asset", "action", "scaled_disequilibrium", "beta"]
|
|
].to_dict("records") == [
|
|
{
|
|
"time": "t1",
|
|
"asset": "AAA",
|
|
"action": "TARGET",
|
|
"scaled_disequilibrium": -1.25,
|
|
"beta": 0.75,
|
|
},
|
|
{
|
|
"time": "t1",
|
|
"asset": "BBB",
|
|
"action": "TARGET",
|
|
"scaled_disequilibrium": -1.25,
|
|
"beta": 0.75,
|
|
},
|
|
{
|
|
"time": "t2",
|
|
"asset": "AAA",
|
|
"action": "CLOSE",
|
|
"scaled_disequilibrium": -0.5,
|
|
"beta": 0.75,
|
|
},
|
|
{
|
|
"time": "t2",
|
|
"asset": "BBB",
|
|
"action": "CLOSE",
|
|
"scaled_disequilibrium": -0.5,
|
|
"beta": 0.75,
|
|
},
|
|
]
|
|
|
|
|
|
def test_find_repo_root_and_normalize_directory():
|
|
repo_root = find_repo_root(Path("notebooks").resolve())
|
|
|
|
assert repo_root.name == "stat_pairs_backtest"
|
|
assert normalize_directory("data", repo_root) == (repo_root / "data").resolve()
|
|
|
|
|
|
def test_list_candidate_files_prefers_result_databases(tmp_path):
|
|
names = [
|
|
"20260617.spbt_md.db",
|
|
"20260617.spbt_results.db",
|
|
"20260617.spbt_selector_results.db",
|
|
"notes.txt",
|
|
]
|
|
for name in names:
|
|
(tmp_path / name).write_text("", encoding="utf-8")
|
|
|
|
assert [path.name for path in list_candidate_files(tmp_path)] == [
|
|
"20260617.spbt_results.db",
|
|
"20260617.spbt_selector_results.db",
|
|
"20260617.spbt_md.db",
|
|
]
|
|
assert [path.name for path in list_candidate_files(tmp_path, show_all=True)] == [
|
|
"20260617.spbt_results.db",
|
|
"20260617.spbt_selector_results.db",
|
|
"20260617.spbt_md.db",
|
|
"notes.txt",
|
|
]
|
|
|
|
|
|
def test_connect_sqlite_read_only_uses_read_only_uri(tmp_path):
|
|
db_path = tmp_path / "example name.sqlite"
|
|
conn = sqlite3.connect(db_path)
|
|
conn.execute("CREATE TABLE sample (value INTEGER)")
|
|
conn.execute("INSERT INTO sample (value) VALUES (1)")
|
|
conn.commit()
|
|
conn.close()
|
|
|
|
assert read_only_sqlite_uri(db_path).endswith("?mode=ro")
|
|
|
|
read_only_conn = connect_sqlite_read_only(db_path)
|
|
try:
|
|
assert read_only_conn.execute("SELECT value FROM sample").fetchone() == (1,)
|
|
with pytest.raises(sqlite3.OperationalError, match="readonly"):
|
|
read_only_conn.execute("INSERT INTO sample (value) VALUES (2)")
|
|
finally:
|
|
read_only_conn.close()
|
|
|
|
|
|
def test_add_total_pnl_and_histogram_builder():
|
|
pair_theo_ret = pd.DataFrame(
|
|
{
|
|
"pair_name": ["AAA:USD-BBB:USD", "PAIR_B"],
|
|
"mr_ranking": [1, 2],
|
|
"realized_pnl": [1.5, -0.5],
|
|
"unrealized_pnl": [0.25, 0.0],
|
|
}
|
|
)
|
|
|
|
with_total = add_total_pnl(pair_theo_ret)
|
|
histogram = create_total_pnl_histogram(pair_theo_ret)
|
|
|
|
assert with_total["total_pnl"].tolist() == [1.75, -0.5]
|
|
assert histogram.data[0].type == "histogram"
|
|
assert histogram.data[0].x.tolist() == [1.75, -0.5]
|
|
assert histogram.data[0].xbins.start is None
|
|
assert histogram.data[0].xbins.size is None
|
|
|
|
|
|
def test_format_pair_names_for_display_removes_usd_suffix_without_mutating_source():
|
|
pair_theo_ret = pd.DataFrame(
|
|
{
|
|
"pair_name": [
|
|
"AAA:USD-BBB:USD",
|
|
"CCC:EUR-DDD:EUR",
|
|
"AAA:USDT-BBB:USDT",
|
|
None,
|
|
],
|
|
"realized_pnl": [1.0, 2.0, 3.0, 4.0],
|
|
}
|
|
)
|
|
|
|
formatted = format_pair_names_for_display(pair_theo_ret)
|
|
|
|
assert format_pair_name_for_display("AAA:USD-BBB:USD") == "AAA-BBB"
|
|
assert formatted["pair_name"].tolist() == [
|
|
"AAA-BBB",
|
|
"CCC:EUR-DDD:EUR",
|
|
"AAA:USDT-BBB:USDT",
|
|
None,
|
|
]
|
|
assert pair_theo_ret["pair_name"].tolist() == [
|
|
"AAA:USD-BBB:USD",
|
|
"CCC:EUR-DDD:EUR",
|
|
"AAA:USDT-BBB:USDT",
|
|
None,
|
|
]
|
|
|
|
|
|
def test_format_pair_theo_ret_for_analyze_grid_preserves_full_pair_name():
|
|
pair_theo_ret = pd.DataFrame(
|
|
{
|
|
"pair_name": ["AAA:USD-BBB:USD"],
|
|
"mr_ranking": [1],
|
|
"realized_pnl": [1.0],
|
|
"unrealized_pnl": [0.0],
|
|
}
|
|
)
|
|
|
|
formatted = format_pair_theo_ret_for_analyze_grid(pair_theo_ret)
|
|
|
|
assert formatted["pair_name"].tolist() == ["AAA-BBB"]
|
|
assert formatted[PAIR_NAME_VALUE_COLUMN].tolist() == ["AAA:USD-BBB:USD"]
|
|
assert pair_theo_ret["pair_name"].tolist() == ["AAA:USD-BBB:USD"]
|
|
|
|
|
|
def test_create_panel_grids_use_analyze_button_and_hidden_pair_column():
|
|
pair_grid = create_pair_theo_ret_analyze_grid(
|
|
pd.DataFrame(
|
|
{
|
|
"pair_name": ["AAA-BBB"],
|
|
PAIR_NAME_VALUE_COLUMN: ["AAA:USD-BBB:USD"],
|
|
}
|
|
)
|
|
)
|
|
executions_grid = create_selected_pair_executions_grid()
|
|
|
|
assert pair_grid.buttons.keys() == {ANALYZE_BUTTON_COLUMN}
|
|
assert pair_grid.hidden_columns == [PAIR_NAME_VALUE_COLUMN]
|
|
assert pair_grid.disabled is False
|
|
assert pair_grid.pagination is None
|
|
assert pair_grid.layout == "fit_data_table"
|
|
assert pair_grid.sizing_mode is None
|
|
assert executions_grid.value.columns.tolist() == [
|
|
"time",
|
|
"asset",
|
|
"action",
|
|
"side",
|
|
"strength",
|
|
"scaled_disequilibrium",
|
|
"beta",
|
|
"size",
|
|
"price",
|
|
"usd_value",
|
|
]
|
|
|
|
|
|
def test_show_interactive_dataframe_uses_sortable_grid_defaults(monkeypatch):
|
|
calls = []
|
|
|
|
def fake_show(dataframe, **kwargs):
|
|
calls.append((dataframe, kwargs))
|
|
|
|
import itables
|
|
|
|
monkeypatch.setattr(itables, "show", fake_show)
|
|
dataframe = pd.DataFrame({"pair_name": ["AAA-BBB"], "num_trades": [2]})
|
|
|
|
show_interactive_dataframe(
|
|
dataframe,
|
|
table_id="pair-theo-ret-grid",
|
|
pageLength=50,
|
|
)
|
|
|
|
assert len(calls) == 1
|
|
assert calls[0][0] is dataframe
|
|
assert calls[0][1] == {
|
|
"paging": True,
|
|
"pageLength": 50,
|
|
"scrollX": True,
|
|
"ordering": True,
|
|
"showIndex": False,
|
|
"maxBytes": "8MB",
|
|
"classes": "display compact stripe hover",
|
|
"css": calls[0][1]["css"],
|
|
"table_id": "pair-theo-ret-grid",
|
|
}
|
|
assert "background-color: #ffffff" in calls[0][1]["css"]
|
|
assert "color: #000000" in calls[0][1]["css"]
|
|
|
|
|
|
def test_sorted_pair_names_and_dropdown_use_alphabetical_unique_pairs():
|
|
selector_pair_rankings = pd.DataFrame(
|
|
{
|
|
"pair_name": [
|
|
"BTC:USD-ETH:USD",
|
|
"ADA:USD-BTC:USD",
|
|
"BTC:USD-ETH:USD",
|
|
]
|
|
}
|
|
)
|
|
|
|
assert sorted_pair_names(selector_pair_rankings) == [
|
|
"ADA:USD-BTC:USD",
|
|
"BTC:USD-ETH:USD",
|
|
]
|
|
|
|
dropdown = create_pair_name_dropdown(selector_pair_rankings)
|
|
|
|
assert dropdown.options == (
|
|
("ADA-BTC", "ADA:USD-BTC:USD"),
|
|
("BTC-ETH", "BTC:USD-ETH:USD"),
|
|
)
|
|
assert dropdown.value == "ADA:USD-BTC:USD"
|
|
|
|
|
|
def test_example_database_pair_theo_executions_include_strength():
|
|
db_path = Path("data/20260617.spbt_results.db")
|
|
if not db_path.exists():
|
|
pytest.skip(f"example database not available: {db_path}")
|
|
|
|
conn = connect_sqlite_read_only(db_path)
|
|
try:
|
|
rankings = load_selector_pair_rankings(conn)
|
|
trading_instructions = load_trading_instructions(conn)
|
|
finally:
|
|
conn.close()
|
|
|
|
empty_pair_executions = calculate_pair_theo_executions(
|
|
"ADA:USD-BNB:USD",
|
|
trading_instructions,
|
|
)
|
|
non_empty_pair_executions = calculate_pair_theo_executions(
|
|
"ADA:USD-BTC:USD",
|
|
trading_instructions,
|
|
)
|
|
|
|
assert "strength" in empty_pair_executions.columns
|
|
assert "strength" in non_empty_pair_executions.columns
|
|
assert len(rankings) == 78
|
|
assert len(non_empty_pair_executions) > 0
|
|
assert (
|
|
non_empty_pair_executions.loc[
|
|
non_empty_pair_executions["action"] == "TARGET",
|
|
"strength",
|
|
]
|
|
.notna()
|
|
.all()
|
|
)
|
|
assert (
|
|
non_empty_pair_executions.loc[
|
|
non_empty_pair_executions["action"] == "CLOSE",
|
|
"strength",
|
|
]
|
|
.isna()
|
|
.all()
|
|
)
|
|
|
|
first_target_execution = non_empty_pair_executions[
|
|
non_empty_pair_executions["action"] == "TARGET"
|
|
].iloc[0]
|
|
assert first_target_execution["size"] == pytest.approx(
|
|
10_000 * first_target_execution["strength"] / first_target_execution["price"]
|
|
)
|
|
assert first_target_execution["usd_value"] == pytest.approx(
|
|
-first_target_execution["size"] * first_target_execution["price"]
|
|
)
|