Candlestick Charts in Google Sheets | Easy stock price tracking 📈

Candlestick Charts in Google Sheets | Easy stock price tracking 📈
Reading Time: 4 minutes
Depicting data in the  form of a Candlestick Chart in Google Sheets

How to create Candlestick Chart in Google Sheets

Track stock price movements with day-high and day-low prices by creating Candlestick Charts in Google Sheets.

What is a Candlestick Chart?

Candlestick Charts are used often by investors to have a full-picture view of a stock price’s movements. They are especially useful to show four stock price points – high, low, open, and close.

Source: Image by Julie Bang, Investopedia

Candlestick Charts in Google Sheets allow you to follow changes in the value of a variable over a period of time. Finance organizations frequently utilize Candlestick Charts to show price swings in a company’s stock value. Google sheets candlestick chart is shown as a filled box and/or a hollow box. A filled box illustrates a gain in stock value whereas a hollow box depicts a loss in stock value.

Trivia- Candlestick Charts originated in Japan in the early 1700s to track price movement of rice.

Preparing the data set

  • The data set below depicts the stock prices of a company over a week. Before starting we need to ensure that the columns are in the Low-Open-Close-High format. 
Data set depicting the stock prices of a company over a week
Fig 1: Dataset for Candlestick Charts In Google Sheets

 

  • The stock price values in the dataset should be in Financial format. Select the values. Choose Format -> Number -> Financial.
Stock Price values in Financial format 
Fig 2: Stock Price values in Financial format | Candlestick Charts In Google Sheets
  • The date entries in the dataset should be in Plain text format. Select the date column and choose Format ->  Number -> Plain text.
Date values in Plain text format 
Fig 3: Date values in Plain text format | Candlestick Charts In Google Sheets

Creating Candlestick Charts in Google Sheets

  • Select the dataset. 
  • Click Insert-> Chart
Inserting Candlestick chart in Google Sheets
Fig 4: Insert Candlestick Chart in Google Sheets
  • Google Sheets will create a Candlestick Chart as shown below by default and a Chart editor sidebar will open on the screen’s right hand side.
Candlestick Chart in Google Sheets
Fig 5: Visual for Candlestick Charts In Google Sheets 
  • In case the Candlestick Chart is not displayed, click on the Setup tab in the Chart editor and click on the drop down menu under Chart type. From the list of available options, select ‘Candlestick chart’. Your screen should now display a Candlestick Chart. 
Adding Candlestick chart using Setup Tab in Google Sheets
Fig 6: Add Candlestick Charts in Google Sheets using Setup Tab in Chart Editor

Customizing Candlestick Charts in Google Sheets

The Customize tab of the chart editor allows you to modify Candlestick Charts in Google Sheets according to your wish. 

Customizing Candlestick Chart using Customize Tab in Google Sheets
Fig 7: Customize Tab 
  1. Changing the chart style
  • This option allows you to change the font style, border color, background color, and size of your chart. 
  • For instance, here the background color of the chart has been to yellow by selecting yellow as the suitable background color. 
Changing the background color of Candlestick chart in Google Sheets
Fig 8: Change the background color of Candlestick Chart 
  1. Changing chart and axis titles
  • This option allows you to add title to the chart as well as the horizontal and vertical axes. 
  • For instance, here I have changed the chart title to ‘Week 1- Stock Prices’ by adding the appropriate title in the Title text input box.
Add a title to a Candlestick chart in Google Sheets
Fig 9: Add a title to Candlestick Chart 
  • I have also added the title ‘Stock Prices’ to the vertical axis by selecting the Vertical axis title option from the dropdown menu and then typing Stock Prices as the title text.
Adding a title to the vertical axis of a Candlestick Chart in Google Sheets
Fig 10: Add a title to the vertical axis 
  1. Formatting horizontal and vertical axes
  • Here, I have used this option to change the range of the vertical axis values by setting the min value and max value for the vertical axis as 5 and 50 respectively. 
Changing range of vertical axis of a Candlestick Chart in Google Sheets
Fig 11: Change range of vertical axis 
  • Apart from this, the various other formatting options available under this category allow you to change the color, style and size of the horizontal and vertical axes font. 
  1. Adding gridlines and ticks
  • This option allows you to format the axes of the Candlestick Chart by adding major and minor tick marks to the chart, customizing tick mark position and style, etc. 
  • For instance, in the example shown below, I have added both Major and Minor ticks to the Candlestick Chart and set their position as Cross
Adding Tick marks to the axis of a Candlestick Chart in Google Sheets
Fig 12: Add Tick marks to the axis

See Also

One response to “Candlestick Charts in Google Sheets | Easy stock price tracking 📈”

Leave a Reply