Abdi-Ranaldo Close-High-Low (CHL) Bid-Ask Spread Formula

Combine Roll’s close bounce with daily mid-range reference prices eta_t = (h_t + l_t) / 2

Roll’s (1984) close-to-close covariance spread estimator ignores intraday high-low range information, while Corwin and Schultz’s (2012) high-low estimator ignores closing trade bounce! Abdi and Ranaldo (2017) unified both using log High h_t, log Low l_t, log mid-range eta_t = (h_t + l_t) / 2, and log Close c_t across consecutive days (t, t+1): E[4 * (c_t - eta_t) * (c_t - eta_{t+1})] = s^2, with finite-sample two-day zero-floored spread s_AR_t = 2 * SQRT(MAX(0, (c_t - eta_t) * (c_t - eta_{t+1}))).

Audit 5 trading days of log OHLC bars (B2:D6, forming 4 adjacent two-day pairs in E2:E5): Pair 1 (+0.006 * +0.006) gives 2 * 0.006 = 0.0120; Pair 2 (-0.004 * -0.004) gives 2 * 0.004 = 0.0080; Pair 3 (+0.005 * -0.005 = -0.000025) zero-floors cleanly to 0.0000; and Pair 4 (+0.008 * +0.008) gives 0.0160 — averaging 0.0090 (90.00 basis points)!

Two-Day Zero-Floored Abdi-Ranaldo Spread s_AR_t Formula

Validate OHLC ordering (l_t <= c_t <= h_t and l_{t+1} <= h_{t+1}) before evaluating 2 * SQRT(MAX(0, (c_t - eta_t) * (c_t - eta_{t+1}))).

=LET(h_1, B2, l_1, C2, c_1, D2, h_2, B3, l_2, C3, IF(OR(h_1 < l_1, c_1 < l_1, c_1 > h_1, h_2 < l_2), "Invalid OHLC Bar", LET(eta_1, 0.5 * (h_1 + l_1), eta_2, 0.5 * (h_2 + l_2), prod_c, (c_1 - eta_1) * (c_1 - eta_2), ROUND(2 * SQRT(MAX(0, prod_c)), 4))))

Mean Effective Spread in Basis Points (10,000 * AVERAGE) Formula

Average the two-day zero-floored estimates across the window and multiply by 10,000 for institutional execution-cost reporting in basis points.

=IF(COUNTIF(E2:E5, "Invalid OHLC Bar") > 0, "Invalid OHLC Bar", ROUND(10000 * AVERAGE(E2:E5), 2))

Further reading

• Arte Sheets Lesson — Corwin-Schultz High-Low Two-Day Bid-Ask Spread Estimator
• Microsoft Support — MAX function