Plot Candlestick Chart Excel

      2 Comments on Plot Candlestick Chart Excel

* Update – Trade and investing models that were designed earlier using Yahoo! Finance API have been rewritten to retrieve quotes from Bloomberg Markets. At this time only US stocks and ETFs can be retrieved from the RSI model plot. See setup instructions.

A primer on Candlestick charts

Candlestick charts originated in Japan during the 18th century by a Japanese rice trader, Munehisa Homma to analyze the price of rice contracts. A candlestick chart shows daily movements of stocks and shares. Candlestick charts are an intuitive way to perform technical analysis on a stock. A technical analyst can ask “how are the other traders viewing this stock, and how will that affect the price in the immediate future”.  One of the greatest benefit of the candlestick chart is the ability to study market sentiment regarding a stock. It uses four series of inputs : open, high, low, and close.

Plot- Candlestick-Chart-Excel

The thick part of the line represents the day’s open and close prices. If the main body is red, it means that the close of the day was lower than the open. If the main body of the stick is green, it means that the close of the day was higher than the open. The thin lines above and below the main body are shadows that represent the day’s price extremes. The top of the shadow is the high of the session, and the bottom part represents the low of the session. The above chart is an example of Yahoo (Symbol: YHOO) between Sep-2-2014 and Oct-1-2014.

How To Plot a Candlestick Chart in Excel

You can create candlestick charts through few clicks. VBA goes a step further by easing the process of chart creation. In this sample, the spreadsheet assumes:

1. The starting cell is A1 and the ordering of data fields are Date,Open,High,Low, Close as per the Quote Web service.

2. The historical stock prices are on the sheet called “Data”.

3. The plot will be rendered on a sheet named “Chart”

The VBA code accompanied with the spreadsheet is flexible and allows you to do more by setting additional properties; such as setting the title, color, axis labels, and border.

Plot-Candlestick-Chart-Excel

The VBA behind this spreadsheet downloads historical stock prices from Yahoo Finance Web Service, and constructs a candlestick chart. The spreadsheet is quite simple to use. Just input the ticker, from and to dates, and VBA does the rest.

Plot-Candlestick-Chart-Excel

The spreadsheet model retrieves quote information from Bloomberg OpenMarkets through a lightweight data-interchange format called JSON or JavaScript Object Notification. If this is your first time programming JSON in Excel, you must add certain COM+ objects to start utilizing Bloomberg Markets Web Service through VBA.

Under Developer->Visual Basic->Tools->References, add the highlighted objects.

VBA-JSON-References

2 thoughts on “Plot Candlestick Chart Excel

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.