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.
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.
- The stock price values in the dataset should be in Financial format. Select the values. Choose Format -> Number -> Financial.
- The date entries in the dataset should be in Plain text format. Select the date column and choose Format -> Number -> Plain text.
Creating Candlestick Charts in Google Sheets
- Select the dataset.
- Click Insert-> Chart.
- 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.
- 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.
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.
- 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 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.
- 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.
- 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.
- 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.
- 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.
See Also
- Gantt Charts in Google Sheets: Learn how to create Gantt Charts in Google Sheets.
- Tree Map Charts in Google Sheets: An easy guide to help you create Tree Map Charts in Google Sheets.
- Radial Bar Charts in Google Sheets: More on how to generate Radial Bar Charts in Google Sheets.
- Waterfall Charts in Google Sheets: Quick guide to teach you how to create Waterfall Charts in Google Sheets.