# Dragonfly Doji Candle Google Sheets Formula

Last Updated on 08/04/2024 by Ndanileka

## What is a Dragonfly Doji Candle

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.
3. Sign of Reversal: It often signals a potential reversal, especially if it occurs after a downtrend.

## 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"))``

1.2 Calculate 95% of the High.

``=GOOGLEFINANCE(B1,"high")*0.95``

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``

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")``

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``

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``

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 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.