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

  • Entering a stock ticker symbol to pull in up to 5 years of price history
  • Allows controlling the period displayed on the chart (5 days, Month To date, 3 Months, 6 Months, etc.)
  • Allows controlling the interval of price data – Daily, Weekly or Monthly
  • In addition, the price is displayed not as one price line – instead it is represented in this shape which is called as candlestick chart.

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
  • A candle is colored red when the closing price is less than Open price. For example, on 16th March, Open price is 703.35 and the Closing price is 676.88.
  • A candle is colored green when the closing price is more than the Open price. For example, on 17th March, the Open price is 656.87 and the Closing price is 701.81.
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

7 Comments

  • Is it possible to get the Candlestick Chart password so I can learn how to make a Candlestick Chart.

    Reply
  • Hello Indzara team,

    I tested the Stock Price Chart Template and the Candlestick Chart Template today. Both templates work really well, but not for all stocks.

    If, for example, Chalice Mining Limited (XASX: CHN), period of 5 years, daily is entered, then the evaluation of various non-trading days ensures a faulty presentation. Is there a way to disregard these trading days in the charts?

    Many greetings from Germany
    Volker Schnabel

    Reply
    • Thank you for showing interest in our template and pin-pointing the issue.

      We have updated the template to filter non-trading days. Please download a fresh copy of the template to fix the issue.

      Best wishes.

      Reply
      • Hello Indzara team,

        Thank you for the updates. Perfect!

        Many greetings from Germany
        Volker Schnabel

        Reply
  • Hello Sir, Very useful excel templates. Is it possible to add EMA’s to candlestick pattern & then also setup alerts if certain EMA is touched? Looking forward to your response on this 🙂

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *