INDZARA

Candlestick Chart in Excel – Stock Market Analysis

You can use this template to build candlestick chart of price history of any stock.

Forex Rate Chart in Excel for Currency Pairs

In this article, I will go over the following.

1) Candlestick chart template and its features
2) Introduction to the Candlestick

Download


Video Demo


How to create a Candlestick chart in Excel?

A very common thing you see in the charts of stock market related sites is the Candlestick chart.

Candlestick Chart Excel Template

Using our free template, you can see those charts in Excel itself for any stock you are interested in. The template allows

What is a CandleStick chart?

In order to explain how to interpret a candlestick chart, let’s choose a shorter date range. Let’s use a 5 day price trend.

Now, the chart should look like this. By default the data labels do not appear.

Candlestick chart for 5 days – without data labels

Enable the data labels using the steps shown below.

Display data labels on Chart

Tip: Showing the data labels for a lot of data points will be very cluttered and not useful.  (Use the same steps and choose None for data labels, in order to remove data labels).

There are four series of values plotted on the chart. They represent the Open, High, Low and Close values of the stock for the day or the period.

You can right click on each and choose Format Data Labels.

Format data labels for Chart
Enable Series Name

Optionally you can change the separator and the label position as needed.

Implement on all 4 series

After implementing the data labels for all the four series, the chart should look like below.

Chart with labels
Candlestick Explanation

The candlestick also represents two more data points about each day/period. They are called the wicks or shadows.  They represent the High and Low of the day.

How to refresh?

To refresh the price and other information, just use the regular Excel refresh in the Data ribbon.

Refresh stocks data

Resources

The foundation of this is the StockHistory function.

Please read more on StockHistory function and how we use to extract the price history of a stock or currency.

Here is the link to the article from Microsoft which shows which exchanges are available and the delay in data refresh for each.

Requirements


Microsoft has introduced a new feature in Excel called Data types. One of the data types is ‘Stocks’. That’s what we will be using for these templates. This feature is available in Microsoft 365 Subscription Plans for PC and Web

For all the templates in this ‘Excel for Stock Market’ series, please visit indzara.com/stock-market-templates

Feedback

Please share your experience. I expect this to be different with different exchanges and countries. Is it working for you? Is it working differently? I would love to hear from you.

Related Templates

Exit mobile version