# Doji Candle Google Sheets Formula

Last Updated on 08/04/2024 by Ndanileka

## Understanding the Doji Candlestick

A Doji candlestick pattern is characterized by the opening and closing prices being very close or nearly identical, resulting in a small or non-existent body.
Such price action typically signifies a state of indecision among market participants, where there is no clear direction of price movement. Instead, the price fluctuates up and down throughout the trading session, ultimately returning to its starting point (the open).

Conversely, the “indecision” may suggest that the market participants are in agreement on the current price level. In this context, indecision can be interpreted as a form of decision-making. By maintaining the current price level, the market signifies a collective decision until new information or valuation factors introduce a potential disagreement and prompt a shift to a new equilibrium.

Indecision = Decision

In essence, the equation “indecision = decision” encapsulates the idea that the lack of a distinct price direction, as exemplified by a Doji, constitutes a form of decision within the market. This implies that, in a state of indecision, the market collectively decides to uphold the existing price level until external factors motivate a change.

## Identifying Doji Candles

To identify a doji candle, we need to compare the length of the candle’s body with the length of the entire candle. If the body is less than the predefined threshold of 10% of the candle’s length, we consider it a doji.

The candle’s body is determined by calculating the absolute value of the difference between the open and close prices. We express it as Abs(Open – Close).

Our 10% threshold, calculated as 10% of the difference between the high and low prices (H – L), establishes the benchmark against which we assess the body’s length to determine if it falls within an acceptable range for identifying a doji

Once we have both of these values, we compare the body size with the 10% length threshold, and if the body is smaller or equal, a doji is recognized.

Abs(Open – Close) <= ((High – Low) * 0.1)

Example Candle Values:

Open: 656.18
Close: 656.01
High: 659.65
Low: 652.24

Condition:
Abs(656.18 – 656.01) <= ((659.65 – 652.24) * 0.1)
0.17 <= 0.741

Comparing these values, since the absolute difference of the Open and Close (0.17) is less than 10% of the High-Low range (0.741), we identify this example as a Doji candle.

## Doji Candle Google Sheets Formula

In the below example, Cell B1 = “COST ” ( Costco Wholesale Corporation )

Step 1: Gathering Current Open and Current Price

1.1 Request Current Open:

``=GOOGLEFINANCE(B1,"priceopen")``

1.2 Request Current Price:

``=GOOGLEFINANCE(B1,"price")``

1.3 Calculate the Absolute Difference:
Find the absolute value of the difference between the current open and current price.

``=ABS(GOOGLEFINANCE(B1,"priceopen")-GOOGLEFINANCE(B1,"price"))``

Step 2: Calculate the Threshold Value From the High and Low

2.1 Request Current High:

``GOOGLEFINANCE(B1,"high")``

2.2 Request Current Low:

``=GOOGLEFINANCE(B1,"low")``

2.3 Calculate 10% of High and Low:
Compute 10% of the difference between the current high and low.

``=(GOOGLEFINANCE(B1,"high")-GOOGLEFINANCE(B1,"low"))*0.1``

Step 3: Doji Identification Complete formula

This formula compares the values from Step 1.3 and Step 2.3.
If the value in Step 1.3 is less than or equal to the value in Step 2.3, it returns “Doji”; otherwise, it returns nothing (an empty cell).
=IF(Step1.3 <= Step2.3, “Doji”, “”)

``=IF(ABS(GOOGLEFINANCE(B1,"priceopen")-GOOGLEFINANCE(B1,"price"))<=(GOOGLEFINANCE(B1,"high")-GOOGLEFINANCE(B1,"low"))*0.1,"Doji","")``

## Doji Candle Scan

The Doji scan spreadsheet employs the formula to identify potential Doji candles within a specified watchlist.
In our template, we scan stocks listed on the S&P100.

Not every Doji candle is tradable. This is an inherent characteristic of markets, as Doji’s can occur at various points in price action. However, tradable Dojis are often found at extreme or inflection points, signifying moments when prices appear exhausted or when they take a “pause”.
These key points include the top or bottom of a range, the end of a trend, or at significant support or resistance levels.

Given that Dojis represent a “pause” in price direction, they provide ideal entry points for both reversals and continuations. The nature of Dojis suggests that price is temporarily at a standstill, offering traders an opportunity to assess market sentiment.

Continuing with our discussion on doji candles, in upcoming articles, we will focus on the detailed formulas for three various namely:
Gravestone, Dragonfly, Long-legged.
Each type has unique characteristics and can provide valuable insights into the underlying price action, which can help us better understand their significance in trading.