Creating Japanese Candle Stick Charts in Excel

Thebullishtrends: If you use historical pricing data in your analytical models one of the best ways to display this information is with a Japanese candlestick chart. To create a candlestick chart you need the open, high, low and close for an instrument. You can display the same information with a standard bar chart but candlestick charts are more visually appealing and help you spot trend changes.


With a bar chart you need to really look closely at the price action to see if the close was higher or lower than the open. Candlestick charts are color coded. A black candle indicates the close was lower than the opening. A white candle indicates the close was higher than the opening. With Excel you can apply a custom color code the candlesticks. For example, you can have a blue color indicate the closing price to be higher than the opening price and a red color to indicate the closing price to be lower than the opening price.


In addition to the color coding there are thin vertical lines above and below the body of the candle which are called upper and lower shadows. The shadows represent high and low price extremes. The bodies of the candlestick along with the shadow lines create formations which indicate various types of patterns. An entire trading system has been developed around candlestick price charts.


Our focus in this Willow Tip is to use historical pricing information to create Japanese candlestick charts. Several books have been written on Japanese candlestick charting. An in depth explanation of how to interpret candlestick patterns and formations can be found in:
Japanese Candlestick Charting Techniques: A Contemporary Guide to the Ancient Investment Techniques of the Far East by Steve Nison.


There are also many web sites which offer explanations and summaries of candlestick charts and formations. One of the best for a quick summary is:
http://www.altavest.com/candlesticks.html


Use Excel’s Chart Wizard :
A candlestick chart is one of Excel’s standard chart types. Before creating your chart you need to arrange your historical pricing data in the order of date open, high, low. Select the data you want to chart including the column headings. Select Chart… from Excel’s Insert menu.

This action launches the chart wizard. In Step One select the Stock chart type and click on the graphic of the candlestick chart under chart sub-type. Continue through the steps of the wizard to create your candlestick chart.


Charting Tips and Ticks :
The chart that you get when you have finished with the wizard does not look that impressive. You need to add apply some customize formatting to your chart to make into a visually appealing analytical tool.


Adjust the Scales on the X and Y Axis :
Right click on the Y axis, choose Format Axis… and select the Scale tab. The Excel chart wizard usually assigns zero as the minimum value. This leaves a great deal of wasted space below the price chart. Change the minimum value and the maximum value to reflect the high and low points reached for the period you are trying to display. You can also click on the Number and Font tabs to customize the way you want your fonts and numbers to appear on the vertical axis.


Once you have customized the Y axis follow the same procedure to customize the X axis. Usually the dates displayed are bunched together and the spacing between then need to be widened. Right click on the X axis, choose Format Axis… and select the Scale tab. You can adjust the spacing of the dates by increasing the major and minor units. You can also change the date format by clicking on the Number tab and changing the way the dates are displayed.

Customize the Up and Down Bars :
In order to be better able to recognize the patterns in a candlestick chart you need to adjust the appearance of the up and down bars. Unlike the standard bar chart the candlestick is composed of high low lines and these can be up or down bars depending on the price movement. Right click on the one of the down bars on the chart in order to format its appearance.

Use the style color and weight drop downs to customize the appearance of the down bars. In our case we selected the color red to identify the down bars.
Follow the same procedure to customize the appearance of the up bars.