dynamic stock chart

This stock chart in excel allows you to change the date range and the chart is instantly refreshed.

Here is how to build this chart.

Create a date range

I entered "Start:" and "End:" in cell range B16:B17.

stock chart - dynamic date range

Copy data

I found stock price data at the yahoo finance website, here is a link to S&P 500 historical prices.

  1. Click "Download to spreadsheet" link to download file, you can find the link below historical data
  2. Open downloaded file in excel
  3. Copy data from downloaded file
  4. Paste to cell A20 on your worksheet

stock chart - historical data

The data starts from the 1950's but I can't show all data, for obvious reasons.

Build dynamic named ranges

We want the chart to change depending on the start and end date in cell range B16:B17. "Named ranges" is what we are looking for, it can return a cell range of variable size.

  1. Go to "Formulas" on the ribbon
  2. Click "Named Ranges"
  3. Click "New" button
  4. Name it "Date"
  5. Refers to: =INDEX(Sheet1!$A$21:$A$810, MATCH(Sheet1!$C$16,Sheet1!$A$21:$A$810,-1)):INDEX(Sheet1!$A$21:$A$810, MATCH(Sheet1!$C$17, Sheet1!$A$21:$A$810, -1))
    stock chart - named ranges
  6. Click "OK"
  7. Click "New" button
  8. Name it "High"
  9. Refers to: =INDEX(Sheet1!$C$21:$C$810, MATCH(Sheet1!$C$16,Sheet1!$A$21:$A$810, -1)):INDEX(Sheet1!$C$21:$C$810, MATCH(Sheet1!$C$17, Sheet1!$A$21:$A$810, -1))
  10. Click OK
  11. Click "New"
  12. Name it "Low"
  13. Refers to: =INDEX(Sheet1!$D$21:$D$810, MATCH(Sheet1!$C$16, Sheet1!$A$21:$A$810, -1)):INDEX(Sheet1!$D$21:$D$810, MATCH(Sheet1!$C$17, Sheet1!$A$21:$A$810, -1))
  14. Click OK
  15. Click "New"
  16. Name it "Close"
  17. Refers to: =INDEX(Sheet1!$E$21:$E$810, MATCH(Sheet1!$C$16, Sheet1!$A$21:$A$810, -1)):INDEX(Sheet1!$E$21:$E$810, MATCH(Sheet1!$C$17, Sheet1!$A$21:$A$810, -1))
  18. Click OK

You have now created 4 different named ranges which are to be used in the stock chart. You can find a formula explanation later in this post. But first, lets insert a stock chart above the date range.

Insert a stock chart

  1. Select cell range C21:E29, column C:E contain high, low and close values.
  2. Go to tab "Insert" on the ribbon
  3. Click "Stock chart" button

stock chart

The values are in an incorrect order, they need to be reversed.

  1. Right click on x axis
  2. Select "Format Axis..."
  3. Enable option "Categories in reverse order"

stock chart1

Change series values

It is now time to use the dynamic named ranges we created earlier. They help us quickly change the cell range we want to be shown in the chart.

  1. Right click on chart
  2. Click "Select Data..."
  3. Select Series1
  4. Click "Edit" button
  5. Series values: =Sheet1!High (Don´t forget to type the sheet name before the named range.)
    stock chart - edit series
  6. Click OK
  7. Select Series2
  8. Click "Edit" button
  9. Series values: =Sheet1!Low
  10. Click OK
  11. Select Series3
  12. Click "Edit" button
  13. Series values: =Sheet1!Close
  14. Click OK
  15. Click "Edit" button below Horizontal (Category) Axis Labels
  16. Axis label range: =Sheet1!Dates

Explaining the named range (Date) formula

Step 1 - Find the position of value cell C16 in cell range A21:A810




and returns 2. Read more about MATCH function.

Step 2 - Calculate the first cell ref in the cell range

INDEX(Sheet1!$A$21:$A$810, MATCH(Sheet1!$C$16,Sheet1!$A$21:$A$810,-1))


INDEX(Sheet1!$A$21:$A$810, 2)

and returns cell ref A22. Read more about INDEX function.

Step 3 - Find the position of value cell C17 in cell range A21:A810




and returns 130.

Step 4 - Calculate the second cell ref in the cell range

INDEX(Sheet1!$A$21:$A$810, MATCH(Sheet1!$C$17, Sheet1!$A$21:$A$810, -1))


INDEX(Sheet1!$A$21:$A$810, 130)

and returns cell ref A151.

Step 5 - Combine cell refs to a cell range

=INDEX(Sheet1!$A$21:$A$810, MATCH(Sheet1!$C$16,Sheet1!$A$21:$A$810,-1)):INDEX(Sheet1!$A$21:$A$810, MATCH(Sheet1!$C$17, Sheet1!$A$21:$A$810, -1))

returns A22:A151

Download excel *.xlsx file

Dynamic stock chart.xlsx

How to make the chart even more dynamic

This user defined function allows you to fetch past data for any quote on yahoo finance. You don't have to go to the yahoo finance website and copy/paste the data, this udf does it for you automatically. Just type the stock quote in cell D17 and press enter. See picture below.

stock chart - udf

If you are looking for a particular company, index or commodity go to yahoo finance and use their "Quote lookup" to find the quote you are looking for.

Yes, you still need to manually adjust the y axis values on chart every time you change the date range or stock quote, unless you check out this post.

Download excel *.xlsm file

Dynamic stock chart with udf.xlsm