Financial & Accounting
Candlestick (OHLC) Chart Formula: Real Body, Upper & Lower Wicks & Doji Body Ratio
Validate Open-High-Low-Close (OHLC) price bars and compute Candlestick Real Body = Close - Open, Upper Shadow = High - MAX(Open, Close), Lower Shadow = MIN(Open, Close) - Low, and Body-to-Range ratio.
Every Japanese candlestick decomposes into 3 exact non-negative geometry segments plus direction!
Whether plotting a native Open-High-Low-Close stock chart or building a custom stacked-column candlestick chart, corrupted OHLC feeds (where High < MAX(Open, Close) or Low > MIN(Open, Close)) break wick rendering! Validating High >= MAX(Open, Close) and Low <= MIN(Open, Close) first ensures geometric consistency.
Once validated, each daily bar splits cleanly into: Signed Real Body = Close - Open (Bullish > 0, Bearish < 0), Upper Shadow (Wick) = High - MAX(Open, Close), Lower Shadow (Tail) = MIN(Open, Close) - Low, and dimensionless Body-to-Range Ratio = ABS(Close - Open) / (High - Low)—where a ratio <= 0.10 identifies an indecision Doji candle!
| Candlestick Component (Open=142.5, High=148.2, Low=140.1, Close=146.5) | Worksheet Formula | Value & Pattern |
|---|---|---|
| Signed Real Body (Close - Open) | =E2 - B2 | +4.00 (Bullish Hollow/Green Body) |
| Upper Wick / Shadow | =C2 - MAX(B2, E2) | 1.70 (148.20 - 146.50) |
| Lower Wick / Tail | =MIN(B2, E2) - D2 | 2.40 (142.50 - 140.10) |
| Body-to-Range Ratio | =ABS(E2 - B2) / (C2 - D2) | 0.4938 (4.00 / 8.10 Directional Bar) |
Validated Upper Shadow (Wick) Height = High - MAX(Open, Close)
Verifies OHLC ordering (High >= MAX(Open, Close) and Low <= MIN(Open, Close)) before computing upper wick height.
=IF(OR(C2 < MAX(B2, E2), D2 > MIN(B2, E2), C2 <= D2), "Invalid OHLC Bar", ROUND(C2 - MAX(B2, E2), 4))Candlestick Body-to-Range Ratio & Doji Classifier = ABS(Close - Open) / (High - Low)
Computes the fraction of total High-Low range occupied by the real Open-Close body.
=IF(OR(C2 < MAX(B2, E2), D2 > MIN(B2, E2), C2 <= D2), "Invalid OHLC Bar", ROUND(ABS(E2 - B2) / (C2 - D2), 4))Try the example
Use the original values or change them to test your understanding.