Stock charts in Excel help present your stock’s data in a much simpler and easy to read manner.

There are 4 types of stock charts that you can create in Excel: –

  1. Open-High-Low-Close
  2. High-Low-Close
  3. Volume-High-Low-Close
  4. Volume-Open-High-Low-Close

As the names suggest, that is the data which is required for each of the types. Also the columns need to be in the exact order as mentioned in the name.

Just some basics of what is this data

  • Open price – The price at which the stock started trading at the beginning of that day.
  • High price – The highest price that the stock went to in that particular day.
  • Low price – The lowest price that the stock went to in that particular day.
  • Close price – The price at which the stock traded at the last moment in that day.
  • Volume – Number of shares that were traded in that particular day, of that stock.

 

To explain how to create, we will be taking an example of Reliance Industries Limited (RIL)’s stock prices from 5th October to 9th October, 2015 (source of data – www.BSEIndia.com).

Open-High-Low-Close (OHLC)

Data needs to be in the below shown order only.

Date Open High Low Close
5-Oct-15 865 889.9 862.5 886.25
6-Oct-15 893.1 899 885.55 895.9
7-Oct-15 895.8 917 890.05 913.85
8-Oct-15 914.15 915 886.6 889.15
9-Oct-15 896.85 904.9 882.1 886.35

Steps to create the chart

  1. Select the data.
  2. Go to Insert Tab, Other Charts, and select Open-High-Low-Close.
  3. Chart will be created as below.
Step 1, Select the data

Step 1, Select the data

Step 2, Select Open-High-Low-Close chart

Step 2, Select Open-High-Low-Close chart

Step 3, Chart created

Step 3, Chart created

 

How do you read this chart?

  • White Bars – It means the price has gone UP during the day.
  • Black Bars – It means the price has gone DOWN during the day.
    • Thus, Open and Close price indication depend on the color of the bar, price may have gone Up or Down.
  • High Price – The top-most point of the chart is the High Price.
  • Low Price – The bottom-most point of the chart is the Low Price.
OHLC Chart Explained

OHLC Chart Explained

 

Similarly, you can create the below charts based on what data you intend to show in the chart.

High-Low-Close (HLC)

High-Low-Close

High-Low-Close

Volume-High-Low-Close (VHLC)

Volume-High-Low-Close

Volume-High-Low-Close

Volume-Open-High-Low-Close (VOHLC)

Volume-Open-High-Low-Close

Volume-Open-High-Low-Close