Table of contents

• Introduction
• Create a lookup table
• Create a drop down list
• Change web query parameter
• Add dynamic ranges to stock chart

Introduction

I have previously posted how to create  a dynamic stock chart in excel. It is really easy to use:

• Select Company
• Click "Update chart"

In this post I have added a new feature. You can now select a date range in a drop down list and the stock chart uses the new date range.

• Select Company
• Select date range
• Click "Update chart"

I am working with the attached file in this post:
Create a dynamic stock chart using a web query and a drop down list in excel
or download the complete tutorial file for this post here:
dynamic_stock_chart_change_date_range.xls

Create a lookup table

Select sheet "Data".

Formula in B1:=TODAY() + ENTER
Formula in B2:=YEAR(B1)+ENTER
Formula in B3:=MONTH(B1) + ENTER
Formula in B4:=DAY(B1)
Formula in B5:=INDEX(Overview!C3:C33, MATCH(Overview!E3, Overview!B3:B33, 0)) + ENTER

Formula in E2:=INDEX(G3:K3, MATCH(Overview!$E$4, Data!$G$1:$K$1, 0)) + ENTER
Formula in E3:=INDEX(G4:K4, MATCH(Overview!$E$4, Data!$G$1:$K$1, 0)) + ENTER
Formula in E4:=INDEX(G5:K5, MATCH(Overview!$E$4, Data!$G$1:$K$1, 0)) + ENTER

Formula in G2: =DATE(YEAR($B$1), MONTH($B$1)-3, DAY($B$1)) + ENTER
Formula in G3:=YEAR(G2) + ENTER
Formula in G4:=MONTH(G2)-1 + ENTER
Formula in G5:=DAY(G2) + ENTER

Formula in H2:=DATE(YEAR($B$1), MONTH($B$1)-6, DAY($B$1)) + ENTER
Formula in H3:=YEAR(H2) + ENTER
Formula in H4:=MONTH(H2)-1 + ENTER
Formula in H5:=DAY(H2) + ENTER

Formula in I2:=DATE(YEAR($B$1), MONTH($B$1)-12, DAY($B$1)) + ENTER
Formula in I3:=YEAR(I2) + ENTER
Formula in I4:=MONTH(I2)-1 + ENTER
Formula in I5:=DAY(I2) + ENTER

Formula in J2:=DATE(YEAR($B$1), MONTH($B$1)-24, DAY($B$1)) + ENTER
Formula in J3:=YEAR(J2) + ENTER
Formula in J4:=MONTH(J2)-1 + ENTER
Formula in J5:=DAY(J2) + ENTER

Formula in K2:=DATE(YEAR($B$1), MONTH($B$1)-60, DAY($B$1)) + ENTER
Formula in K3:=YEAR(J2) + ENTER
Formula in K4:=MONTH(J2)-1 + ENTER
Formula in K5:=DAY(J2) + ENTER

Create a drop down list

1. Select sheet "Data"
2. Type "3 months" in cell M1
3. Type "6 months" in cell M2
4. Type "1 year" in cell M3
5. Type "2 years" in cell M4
6. Type "5 years" in cell M5
7. Select M1:M5 and create a named range. I named it "daterange".
8. Select sheet "Overview"
9. Select cell E4
10. Select tab "Data"
11. Click "Data validation"
12. Click "Data validation..."
13. Select List
14. Type "=daterange" in source.
15. Click ok!

Change web query parameter

I have changed web query parameter "ticker" to cell reference:B5. Remaining parameters.

Add dynamic ranges to stock chart

Dynamic ranges

1. Click tab "Formulas"
2. Click "Name Manager"
3. Click "New.."
4. Name it "High"
5. Copy and paste this formula into "Refers to:": =OFFSET(Data!$C$11, 0, 0, COUNTA(Data!$C$10:$C$10000)-1, 1)
6. Click OK!
7. Click "New.."
8. Name it "Low"
9. Copy and paste this formula into "Refers to:": =OFFSET(Data!$D$11, 0, 0, COUNTA(Data!$D$10:$D$10000)-1, 1)
10. Click OK!
11. Click "New.."
12. Name it "Close"
13. Copy and paste this formula into "Refers to:":=OFFSET(Data!$E$11, 0, 0, COUNTA(Data!$E$10:$E$10000)-1, 1)
14. Click OK!

Update stock chart with dynamic ranges

1. Right click on stock chart
2. Click "Select Data"
3. Select "High"
4. Click "Edit"
5. Copy and paste this into "Series values:" =dynamic_stock_chart_change_date_range.xls!High
6. Click OK
7. Select "Low"
8. Click "Edit"
9. Copy and paste this into "Series values:" =dynamic_stock_chart_change_date_range.xls!Low
10. Select "Close"
11. Click "Edit"
12. Copy and paste this into "Series values:" =dynamic_stock_chart_change_date_range.xls!Close
13. Click OK!
14. Click OK!

Download excel tutorial file

dynamic_stock_chart_change_date_range.xls
(Excel 97-2003 Workbook *.xls)

Related posts:

Create a dynamic stock chart using a web query and a drop down list in excel

Add buy and sell points to a stock chart

Adjust stock chart axis automatically

Excel charts: Use dynamic ranges to add new values to both chart and drop down list

Learn how to create a stock chart in excel