Create a dynamic stock chart using a web query and a drop down list in excel
In this blog post I´ll show you how to update a stock chart in excel using a drop down menu and a web query connection.
- Create list of companies and corresponding tickers
- Create a dynamic range
- Create a drop down list
- Calculate dates
- Create a web query
- Create a macro to refresh web query and automate "text to columns"
- Create a stock chart
- Create a button
Here is a picture of the final result:
At the end of this post is an attached excel tutorial file.
Create list of companies and corresponding tickers
In this tutorial I have two sheets, Overview and Data. On sheet Overview I created a small list of companies and corresponding ticker. You can search tickers at http://finance.yahoo.com/
Create a dynamic range
As I add new companies and tickers to the list I want the drop down list automatically updated. To accomplish this I need to create a dynamic named range.
- Click tab "Formulas"
- Click "Named ranges"
- Click "New"
- Name your range (formula in this tutorial).
I named it "Dynrange". - Type in "Refers to:" =OFFSET(Overview!$B$3, 0, 0, COUNTA(Overview!$B:$B)-1, 1)
- Click OK!
Create a drop down list
- Select cell E3
- Click "Data" tab
- Click "Data validation"
- Click "Data validation..."
- Select Allow: List
- Type in "Source:" =Dynrange
- Click OK!
Calculate dates
To download the latest three months of historical data we need to calculate the the date three months back.
I did this calculation on sheet "Data". The formulas use the current date today automatically.
Cell H1 matches the corresponding ticker to the selected company in the drop down list.
See formula details on the attached file at the bottom of this post.
Create a web query
- Click "Data" tab
- Click "From web"
- Paste this into address field:http://table.finance.yahoo.com/table.csv?a=["fm","fm"]&b=["fd","fd"]&c=["fy","fy"]&d=["tm","tm"]&e=["td","td"]&f=["ty","ty"]&s=["ticker", "ticker"]&y=0&g=d&ignore=.csv
- Click "Go"
- Click "Import"
- Select cell "A10" on sheet "Data"
- Click OK
- Select cell E3 (fm) . Enable "Use this value/reference for future refreshes". Click OK.
- Select cell E4 (fd) . Enable "Use this value/reference for future refreshes". Click OK.
- Select cell E2 (fy) . Enable "Use this value/reference for future refreshes". Click OK.
- Select cell B3 (tm) . Enable "Use this value/reference for future refreshes". Click OK.
- Select cell B4 (td) . Enable "Use this value/reference for future refreshes". Click OK.
- Select cell B2 (ty) . Enable "Use this value/reference for future refreshes". Click OK.
- Select cell H1 (ticker) . Enable "Use this value/reference for future refreshes". Click OK.
VBA code to refresh web query and automate "text to columns".
- Click "Developer" tab
How to show the Developer tab or run in developer mode - Click "Visual Basic"
- Create a "Module" for your workbook
How to Copy Excel VBA Code to a Regular Module - Copy this vba code into module:
Sub Refreshwebq()
Set qryTableStocks = ThisWorkbook.Worksheets("Data").QueryTables(1)
With qryTableStocks
.BackgroundQuery = False
End With
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Sheets("Data").Select
Range("A10").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.ClearContents
ActiveWorkbook.Connections("Connection").Refresh
Sheets("Data").Select
Range("A10").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.TextToColumns Destination:=Range("A10"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
Semicolon:=False, Comma:=True, Space:=False, Other:=False, FieldInfo _
:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), _
Array(7, 1)), TrailingMinusNumbers:=True
Sheets("Overview").Select
End Sub
Create a stock chart
- Select High, Low and Close columns on sheet "Data"
- Click "Insert" tab
- Click "Other charts"
- Click High-Low-Close stock chart
- Cut and paste stock chart to sheet "Overview"
- Right click on x-axis values
- Click "Select data"
- Click "Edit" in Horizontal (Category) axis labels
- Select Dates on sheet "Data", =Data!$A$11:$A$71
- Click OK!
- Click OK!
- Right click on x-axis values
- Click "Format axis"
- Click Axis Options
- Click Axis Type: Text axis
- Click Categories in reverse order
- Click Vertical axis crosses: At maximum category

- Click Close
Create a button and assign macro Refreshwebq()
How to Run an Excel Macro With a Worksheet Button
Download excel tutorial file
dynamic stock chart using web query.xls
(Excel 97-2003 Workbook *.xls)
Related posts:
- Dynamic stock chart in excel – Add date ranges
- Stock alerts in excel
- Create a dynamic border to your list using excel conditional formatting
- How to create excel macro to color every second row
- Create a drop down list containing only unique distinct alphabetically sorted text values using excel array formula
- Extract dates using a drop down list in excel
- Color every second row using dynamic conditional formatting in excel
- Create a unique distinct list of a long list without sacrificing performance using vba in excel
- Scan stock markets in excel
- How to create a unique list using conditional formatting in excel 2007





















=$D6<=$E6 in formula field
























February 21st, 2010 at 9:53 am
Oscar, your blogs do stretch make my Excel understanding. I have learnt a lot, especially in the usage of these powerful Array formulas. Thanks
In this case its your results would be great for producing a filter list of the criteria mentioned. However if you looking for just a price based on the criteria mentioned, this formula would be more simpler.
=INDEX($D$3:$D$19,MATCH($G$3&$G$4,$B$3:$B$19&$C$3:$C$19,0))
February 21st, 2010 at 9:59 am
But I guess, the above formula does not work for multiple items of the same criteria....... sorry, I long way to go for me:)
February 24th, 2010 at 9:59 pm
Problem description (simplified of course):
I have a list of employees (by ID number) and date (by yr & mon) of when they were assigned a certain duty (task). This is in a Work book, on a TAB. Each TAB is a separate month (first is Jan, 2nd is Feb, etc.). I have 12 tabs (12 worksheets) in workbook. Each TAB, a single month, has a list of ID numbers. Some IDs may repeat on different worksheets, that is, some may be in multiple months and some may be in just two or three months or just one month. An ID number will shown only once in a month for a single task (duty). Abbreviated example is below.
Is it possible to combine the data, by function, or formula, or VBasic) to a 13th worksheet automatically and:
1. Show a list of all ID numbers in order (without repeating).
2. Show Jan data in col B, Feb data in col C, etc., and some columns will be blank because the ID had no assignment that month, and will not be on the worksheet for that month.
Is there a formula, or function, or does it have to be done in VBasic? (Is it even possible?)
I have the workbook with 12 tabs in it, and now have to manually put the ID columns side by side and copy and slide down one side on the other to get them to match, and repeat the process 12 times to get the yearly data on one worksheet.
Ex:
For Jan:
ID Duty Asgn.
01 C
05 F
09 D
15 X
23 P
For Feb:
ID Duty Asgn.
02 M
05 Q
08 A
12 R
20 W
Combing Jan and Feb would be:
ID Duty Asgn.
01 C
02 M
05 F Q
08 A
09 D
12 R
15 X
20 W
23 P
This would be repeated for each month to build all 12 col months.
Very Respectfully,
Dave Bonar
(504) 697-2395
February 26th, 2010 at 10:18 pm
Dave Bonar,
Yes, I believe this can be automated using vba. Some of the actions required can also be automated using excel formulas.
Very interesting questions! I´ll try to answer your questions as soon as possible here on my website.
/Oscar
February 28th, 2010 at 9:51 pm
Dave Bonar,
See this post: http://www.get-digital-help.com/2010/02/28/combine-data-from-multiple-sheets-in-excel/
/Oscar