Author: Oscar Cronquist Article last updated on September 23, 2018

The high-low-close stock chart displays the high, low and closing price for a given date range. Each line represents a day, week, month or year determined by your data values. The image above shows a monthly stock chart from year 2009 to 2018.

The highest/smallest value is the value that the stock was traded during that particular day/week/month or year. The close price is the marker shown above, it indicates the price the last trade for that given day/week/month or year was traded at.

How to build

  1. Select data. It must be arranged High-Low-Close.
  2. Go to tab "Insert" on the ribbon.
  3. Click the "Insert Waterfall, Funnel, Stock, Surface, or Radar Chart" button.
  4. Click the "High-Low-Close" button.
  5. To change the color of the marker double click on the data series, this opens the task pane.
  6. Click "Fill & Line".
  7. Click "Marker".
  8. Pick a "Fill" and "Border" marker color, I chose black.

Add dates to x-axis

  1. Right-click on chart.
  2. Click "Select Data...".
  3. Click "Edit" button.
  4. Select your date values.
  5. Click OK button.
  6. Click OK button.

Table of Contents

  1. Create a stock chart in excel 2003
  2. Create a stock chart in excel 2007/2010

 

Create a stock chart in excel 2003

This tutorial will show you how to create stock charts in excel 2003.

How to copy historical stock prices to excel

Search for a company in Yahoo Finance and go to historical prices. Select and copy historical prices.

Go back to excel. Paste the values to a sheet.

Insert the stock chart

Select the data below "High", "Low" and "Close" in your excel sheet.

Go to "Insert" and click "Chart..." in the top menu in Excel.

Press "Finish".

Chart settings
To change the gray background to white, right click with mouse button on the grey area and select "Format plot area". Select a background color.

Create a stock chart in excel 2007/2010

To make things more interesting than copying historical prices from yahoo I am going to use a modified version of the user defined function in this post:
Excel udf: Import historical stock prices from yahoo – added features

The user defined function makes it easy to import historical prices from yahoo finance.

This chart is a mess.

Change the vertical axis minimum and maximum values

  1. Select vertical axis
  2. Right click on vertical axis
  3. Click "Format Axis..."
  4. Click Minimum: Fixed and type 24
  5. Click Maximum: Fixed and type 32
  6. Click Close

Add months to stock chart

In cell A1 type : Months

In cell A2:

=TEXT(B2, "MMM")

In cell A3:

=IF(TEXT(B3, "MMM")=TEXT(B2, "MMM"), "", TEXT(B3, "MMM"))

Copy cell A3 and paste down as far as needed.

  1. Select stock chart
  2. Right click on stock chart
  3. Click "Select Data"
  4. Click "Edit" button
  5. Select cell range A2:A113
  6. Click OK
  7. Click OK
  8. Select horizontal axis
  9. Right click on horizontal axis
  10. Click "Format Axis..."
  11. Go to "Alignment"
  12. Change custom angle to 1
  13. Click Close