Dragonfly Doji Candle Google Sheets Formula

Last Updated on 05/02/2024 by Ndanileka

What is a Dragonfly Doji Candle

dragonfly doji candle
Dragonfly Doji Chart Example

A Dragonfly Doji is a candlestick pattern in technical analysis that forms when the open, close, and high prices are all the same or very close. It looks like a T-shape, resembling a dragonfly. This pattern suggests a potential reversal in the market. The long lower “tail” indicates that sellers pushed the price down during the trading session, but by the end of the session, buyers were able to bring it back to the opening level.

The key characteristics of a Dragonfly Doji include:

  1. Open, Close, and High: The open, close, and high prices are very close or identical.
  2. Long Lower Shadow: The lower shadow (tail) is significantly longer than the upper shadow.
  3. Sign of Reversal: It often signals a potential reversal, especially if it occurs after a downtrend.

Dragonfly Doji Identification

Dragonfly Doji Identification
Dragonfly Doji Identification

To identify a dragonfly doji candle, you need to check for two conditions:

Condition 1:
Checks if the higher of the open or close prices is within 95% of the day’s high.
This condition is expressed as MAX(Open, Close) > High * 0.95.
If this condition is met, it means that the price is very close to the day’s high.

Condition 2:
Check if the candle’s lower tail is greater than 75% of the candle’s length.
The tail is calculated as the difference between the lower of the open or close prices and the low price and is given by the formula Tail = MIN(Open, Close) – Low.
If this tail is greater than 75% of the entire candle’s length, expressed as MIN(Open, Close) – Low > (High – Low) * 0.75, then the condition is satisfied.

Note that the percentages utilized to define the dragonfly doji are subjective and flexible, resembling an art rather than a precise science. Adjustments can be made based on the desired sensitivity of your trading system.


Dragonfly Doji Google Sheets Formula

When working in Google Sheets, we use the current price instead of the close price. This is because we scan the pattern for the current day, and when the market is closed, the current price reflects the last traded price until the next trading day.

Googlefinance(“symbol”,”price”) = Close Price, at E.O.D

In the below example, Cell B1 = “WMT ” ( Walmart Inc)

Step 1 – Verify if the greater of the open and current price is within 95% of the high.

1.1 Find the maximum value between the Open and the Current price.

=MAX(GOOGLEFINANCE(B1,"priceopen"),GOOGLEFINANCE(B1,"price"))
Google sheets greater value between open and current price 
Greater Value between Open and Current Price Formula
Greater Value between Open and Current Price Formula

1.2 Calculate 95% of the High.

=GOOGLEFINANCE(B1,"high")*0.95
google sheets 95% of the high formula
95% of the High Formula

1.3 Confirm if the condition is satisfied.
A TRUE is returned if the Open or Current Price exceeds 95% of the High. Otherwise, a FALSE is returned.

=MAX(GOOGLEFINANCE(B1,"priceopen"),GOOGLEFINANCE(B1,"price")) > GOOGLEFINANCE(B1,"high")*0.95
Google sheets formula to check if the 
Open or Current Price is within 95% of the high.
Formula to Verify if the Open or Current Price exceeds 95% of the High.

Step 2 – Assess if the lower tail is greater than 75% of the candle’s length.

2.1 Determine the lesser value between the Open and Current Price, then subtract the low to find the length of the lower tail.

=MIN(GOOGLEFINANCE(B1,"priceopen"),GOOGLEFINANCE(B1,"price")) -GOOGLEFINANCE(B1,"low")
Google Sheets Lower tail is greater than 75% formula
Length of the Lower Tail formula.

2.2 Calculate 75% of the candle’s length by subtracting the low from the high and multiplying by 0.75.

=(GOOGLEFINANCE(B1,"high")-GOOGLEFINANCE(B1,"low"))*0.75
75% of Candle Length Google Sheets Formula.
75% of Candle Length Formula.

2.3 Check if the condition is met.

A True is returned if the tail is greater than 75%; otherwise, a FALSE is Returned.

=MIN(GOOGLEFINANCE(B1,"priceopen"),GOOGLEFINANCE(B1,"price")) -GOOGLEFINANCE(B1,"low")>(GOOGLEFINANCE(B1,"high")-GOOGLEFINANCE(B1,"low"))*0.75
Formula to Verify if the Lower Tail is Greater Than 75% of the Candle Length.

Step 3 – Finalize the formula by combining both conditions into a single IF statement using the AND() function.
The AND function verifies if both conditions are fulfilled.
If true, it returns TRUE; otherwise, it returns FALSE.
The result is then passed to the IF() function:
If TRUE, “Dragonfly” is returned; If FALSE, a blank cell is returned.

=IF(AND(MAX(GOOGLEFINANCE(B1,"priceopen"),GOOGLEFINANCE(B1,"price")) > GOOGLEFINANCE(B1,"high")*0.95,MIN(GOOGLEFINANCE(B1,"priceopen"),GOOGLEFINANCE(B1,"price"))- GOOGLEFINANCE(B1,"low")  > (GOOGLEFINANCE(B1,"high") - GOOGLEFINANCE(B1,"low"))*0.75),"Dragonfly","")
Dragonfly Doji Candle Formula

Dragonfly Doji Scan

The spreadsheet is designed to automate identifying occurrences of Dragonfly Doji that appear daily within the S&P100 listed stocks. It can be used as the foundation for a trading system by simplifying the process of scanning for potential Dragonfly trades. Moreover, the spreadsheet serves as a template that can be customized for other stock watchlists.

Google Sheets Dragonfly Doji Scan
Dragonfly Doji Spreadsheet Scan

Happy Trading!

Leave a Reply