"""Rank gold's half-hour bins by MEAN M5 high-low range, the statistic
quant-research-mt5's comparison used, on the same data my median-minute
ranking came from. 2026-09-16."""
import pandas as pd
df = pd.read_csv("/home/vesper/data/bars/XAUUSD_mt5_m1.csv", parse_dates=["minute"])
df["ny"] = df["minute"].dt.tz_convert("America/New_York")
df = df[df.ny.dt.dayofweek < 5]
# broker break: drop 17:00-17:59 NY (bins exist on 15 days only)
df = df[~df.ny.dt.hour.isin([17])]
df["m5"] = df.ny.dt.floor("5min")
g = df.groupby("m5").agg(hi=("bid_high","max"), lo=("bid_low","min"))
g["rng"] = g.hi - g.lo
g = g.reset_index()
g["bin"] = g.m5.dt.strftime("%H:") + (g.m5.dt.minute // 30 * 30).map("{:02d}".format)
mean_rank = g.groupby("bin")["rng"].mean().sort_values(ascending=False)
med_rank  = g.groupby("bin")["rng"].median().sort_values(ascending=False)
n = len(mean_rank)
print("bins:", n, " M5 bars:", len(g), " days:", df.ny.dt.date.nunique())
print("\nMEAN M5 range, top 12:")
for i,(b,v) in enumerate(mean_rank.head(12).items(), 1): print("  %2d %s %.3f" % (i,b,v))
print("  21:00 rank by MEAN  : %d of %d (%.3f)" % (list(mean_rank.index).index("21:00")+1, n, mean_rank["21:00"]))
print("\nMEDIAN M5 range, top 12:")
for i,(b,v) in enumerate(med_rank.head(12).items(), 1): print("  %2d %s %.3f" % (i,b,v))
print("  21:00 rank by MEDIAN: %d of %d (%.3f)" % (list(med_rank.index).index("21:00")+1, n, med_rank["21:00"]))