Creating a Candlestick Stock chart

Candlestick chart is a style of bar-chart used primarily to describe price movements of a security, derivative, or currency over time. It is a combination of a line-chart and a bar-chart, in that each bar represents the range of price movement over a given time interval. It is most often used in technical analysis of equity and currency price patterns.
Candlestick chart Excel 2007

To create a Candlestick chart in Excel 2007, do the following:

    1.    Depending on the type of stock chart you want to create, you must include a specific combination of data series in your worksheet - and put the data series in order:

  • High-Low-Close:
    • High price
    • Low price
    • Closing price
  • Open-High-Low-Close:
    • Opening price
    • High price
    • Low price
    • Closing price
  • Volume-High-Low-Close:
    • Volume traded
    • High price
    • Low price
    • Closing price
  • Volume-Open-High-Low-Close:
    • Volume traded
    • Opening price
    • High price
    • Low price
    • Closing price

    2.    Select the data range (in this example C5:F26, the data from Google Finance):

Candlestick chart data in Excel 2007

    3.    On the Insert tab, in the Charts group, choose the Other Charts button and then select the Stock group:

Charts in Excel 2007

Choose Open-High-Low-Close.

    4.    Right-click in the chart area, in the popup menu select Select Data...:

popup menu in Excel 2007

    5.    In the Select Data Source dialog box, in the Horizontal (Category) Axis Labels group, click the Edit button:

Select Data Source in Excel 2007

    6.    In the Axis Labels dialog box, select the data range (in this example A5:A26):

Data range in Excel 2007

    7.    Click OK twice.

    8.    Right-click one of the years along the category axis. In the popup menu select Format Axis...:

Format Axis in Excel 2007

In the Format Axis dialog box:

  • Choose the Axis Options tab, under the Axis Type label, click the field Text axis:
    Text Format in Excel 2007
  • Choose the Number tab, change the field Format Code (in example to show only days) and click the Add button:
    Add Format Code in Excel 2007

You can then make any other adjustments to get the look you desire.

Also see Creating a Candlestick Stock chart with volume.