There is not much documentation available, or I could not find one, on how to use Candlestick Chart in Google Sheets.

Even an experienced Google Sheet’s user may face issue with data formatting for Candlestick Chart.

Your conventional dataset for the Candlestick may not work in Google Sheets correctly. With this tutorial, I hope I can shed some light on Google Sheets Candlestick Chart and data formatting for it.

How the Candlestick Chart in Google Sheets Reflects Stock Value Behavior?

Candlestick chart is popular in the financial world and using mainly for showing price movements, i.e. changes in the value of stocks.

In Candlestick chart;

1. The gains are drawn as filled boxes. (items where the opening value is less than the closing value)

2. The losses are drawn as hollow/empty boxes (items where the opening value is more than the closing value)

See the image below.

How To Format Data for Google Sheets Candlestick Chart?

Open – High – Low – Close (OHLC)

Many users ask why Google Sheets Candlestick Chart uses a different formatting.

The standard order is Open – High – Low – Close aka OHLC but Google Sheets prefers Low – Open – Close – High aka LOCH and I don’t know why?

Low – Open – Close – High (LOCH)

I will tell you how to format data for Candlestick in the LOCH format.

In my above Candlestick Chart example, the source data is in the range A5: E8. It’s manually entered in the correct format that is in LOCH.

You can use the GoogleFinance function to populate historical stock prices for similar charts. But there is one issue. What’s that?

The populated data using the GoogleFinance function would be in OHLC. Fortunately, you can use Query to format this data to LOCH.

I have explained this under the below title. You can learn below how to format data for Candlestick chart in Google Sheets.

How to Plot a Candlestick Chart in Google Sheets Using GoogleFinance Historical Stock Price Data

I am using the following GoogleFinance formula to populate the historical price of the stock HDFC (NSE India).

Syntax:

=GOOGLEFINANCE( "symbol" ; "all" ; "start_date" ; "end_date" )

Formula 1:

=googlefinance( "NSE:HDFC" , "all" , "01/08/2018" , "31/08/2018" )

See the top part of the data below. It shows the default column order and that is not suitable to plot the Candlestick Graph.

I have marked the required column order and how to format it for the Candlestick Graph in Google Sheets.

In this, the first column should be formatted to text and re-order the other columns as marked on the image.

Manipulate GoogleFinance Output for Candlestick

Use Query to format OHLC Data to LOCH Data. Just modify the Formula 1 as below. Don’t get panic seeing this lengthy formula. You can modify this formula easily that even without knowing Query. See the explanation below.

Formula 2:

={ArrayFormula(text({"Date";int(query(query(googlefinance( "NSE:HDFC" , "all" , "01/08/2018" , "31/08/2018" ) ,"Select Col1",1),"offset 1",0))},"DD-MM-YY")),query(googlefinance( "NSE:HDFC" , "all" , "01/08/2018" , "31/08/2018" ) ,"Select Col4,Col2,Col5,Col3",1)}

With the help of Query, I have rearranged the column order and used the function Text to format the first column that contains timestamps to text.

Candlestick graph doesn’t support dates or numbers in the first column. The first column is category axis and which should be formatted as text in Candlestick Chart.

In this Formula 2, you can see the Formula 1 repeated twice. So for any reason, if you want to change the ticker symbol or period, you only want to change the Formula 1 parameter but twice.

Final Steps to Create the Candlestick Chart

Once you have properly formatted your data, the next step is chart creation. Here are that simple steps involved.

Select the data in the range A1: E (considering the above Query formula in A1). Then go to the menu Insert > Chart and choose Candlestick Chart.

The finished Candlestick chart would look like as below. There is only limited customization available for this chart type.

Conclusion

I hope you can now create Candlestick charts in Google Sheets on your own. For any reason, if you fail to rearrange the data using Query, don’t hesitate to contact me.

If you like this tutorial, please do share on social sites and comment. Thanks.