How to Create a Candlestick Chart in Google Sheets

Published on

A Candlestick chart is similar to a column chart but provides more detailed information and is used to visualize price movements.

To create a Candlestick chart in Google Sheets, you need data organized into five columns: Labels, Open, High, Low, and Close.

You can use the output of the GOOGLEFINANCE function to plot a Candlestick chart. When using GOOGLEFINANCE historical data, ensure that the first column (which contains timestamps) is formatted as plain text, as it will be used for the X-axis labels.

Creating a Candlestick Chart in Google Sheets

In Google Sheets Candlestick charts, if the closing value is greater than the opening value, the candle will be filled. Otherwise, it will be hollow.

  • Filled candles represent a bullish trend (Close > Open).
  • Hollow candles represent a bearish trend (Close < Open).
Bullish and Bearish Trends in a Candlestick Chart

Let’s create a Candlestick chart using the following data in Google Sheets.

Sample Data:

OpenHighLowClose
Mon10015075120
Tue1001105070
Wed1001507085

Steps:

  1. Enter the sample data into cells B2:F5.
  2. Select the range B2:F5.
  3. Click Insert > Chart. This will open the Chart Editor sidebar panel.
  4. Select Candlestick chart under the Chart type.
  5. In the Chart Editor, within the Setup tab, adjust the following settings:
    • Under Low, click on the existing field (likely “Open”) and select “Low.”
    • Under Open, select “Open.”
    • Under Close, select “Close.”
    • Under High, select “High.”
  6. Click the Customize tab in the Chart Editor.
  7. Under the Vertical axis, set the min and max values according to your data. For the sample data, you may set the min to 0 and the max to 150.
Candlestick Chart Series Settings in the Chart Editor

This way, you can create a Candlestick chart in Google Sheets.

Using GOOGLEFINANCE Historical Data to Create Candlestick Charts

You can use the GOOGLEFINANCE function to fetch historical stock information such as the open, high, low, and close prices of securities, and use this data to create a Candlestick chart in Google Sheets.

The syntax of the GOOGLEFINANCE function for this purpose is:

GOOGLEFINANCE(ticker, "all", start_date, end_date|num_days)

For example, you can enter the following formula in cell B1 of an empty sheet:

=GOOGLEFINANCE("NASDAQ:GOOG" , "all" , "01/08/2018" , "31/08/2018" )

This will retrieve historical stock data for the ticker symbol “GOOG” (Alphabet Inc.) from the NASDAQ stock exchange for the specified date range.

Candlestick Chart Created Using GOOGLEFINANCE Data

The output will be in the format: Date, Open, High, Low, Close, Volume

Before creating the Candlestick Chart, you need to convert the date values in column B into a text format for the X-axis labels. Enter the following formula in cell A2:

=ArrayFormula(IFERROR(TEXT(DATEVALUE(B2:B), "DD-MM-YY")))

Steps to Create the Candlestick Chart:

  1. Select the range A1:F (excluding the “Volume” column).
  2. Click Insert > Chart.
  3. In the Chart Editor, select Candlestick Chart under the Chart type.
  4. Assign the correct data to each series:
    • Select “Low” under Low.
    • Select “Open” under Open.
    • Select “Close” under Close.
    • Select “High” under High.
  5. Under the Vertical Axis, set the Min and Max values based on your data.

That’s it! This is how you can create a Candlestick chart using the historical data fetched from GOOGLEFINANCE.

Get Your Candlestick Chart Template

Resources

Prashanth KV
Prashanth KV
Your Trusted Google Sheets and Excel Expert Prashanth KV is a Diamond Product Expert in Google Sheets, officially recognized by Google for his contributions to the Docs Editors Help Community and featured in the Google Product Experts Directory. Explore his blog to learn advanced formulas, automation tips, and problem-solving techniques to elevate your spreadsheet skills.

Mode of Comma-Separated Numbers in Excel (Dynamic Array)

There is no dedicated function in Excel to directly find the mode of comma-separated...

How to Find Mode of Comma-Separated Numbers in Google Sheets

Working with comma-separated numbers inside a single cell is a common scenario in Google...

How to Count Merged Cells in Google Sheets (and Get Their Size)

Sometimes, you may have blocks of merged cells in a column or row. But...

Count Consecutive Workday Absences in Google Sheets

This tutorial offers a powerful formula-based solution to count consecutive workday absences in Google...

More like this

How to Find Mode of Comma-Separated Numbers in Google Sheets

Working with comma-separated numbers inside a single cell is a common scenario in Google...

How to Count Merged Cells in Google Sheets (and Get Their Size)

Sometimes, you may have blocks of merged cells in a column or row. But...

Count Consecutive Workday Absences in Google Sheets

This tutorial offers a powerful formula-based solution to count consecutive workday absences in Google...

7 COMMENTS

  1. Hi, Your formula helped me solve the issue as described. However, can you modify the formula further to ensure that the table so generated has the latest values on top and older below (Basically, invert the present data)?

    Thanks

  2. Unfortunately, the google’s candlestick chart only allows the use of text on the x-axis (no dates or numbers) making it useless for anything more than trivial use cases.

LEAVE A REPLY

Please enter your comment!
Please enter your name here

This site uses Akismet to reduce spam. Learn how your comment data is processed.