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.

1. Click tab "Formulas"
2. Click "Named ranges"
3. Click "New"
4. Name your range (formula in this tutorial).
   I named it "Dynrange".
5. Type in "Refers to:" =OFFSET(Overview!$B$3, 0, 0, COUNTA(Overview!$B:$B)-1, 1)
6. Click OK!

Create a drop down list

1. Select cell E3
2. Click "Data" tab
3. Click "Data validation"
4. Click "Data validation..."
5. Select Allow: List
6. Type in "Source:" =Dynrange
7. 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

1. Click "Data" tab
2. Click "From web"
3. 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
4. Click "Go"
5. Click "Import"
6. Select cell "A10" on sheet "Data"
7. Click OK
   
8. Select cell E3 (fm) . Enable "Use this value/reference for future refreshes". Click OK.
9. Select cell E4 (fd) . Enable "Use this value/reference for future refreshes". Click OK.
10. Select cell E2 (fy) . Enable "Use this value/reference for future refreshes". Click OK.
11. Select cell B3 (tm) . Enable "Use this value/reference for future refreshes". Click OK.
12. Select cell B4 (td) . Enable "Use this value/reference for future refreshes". Click OK.
13. Select cell B2 (ty) . Enable "Use this value/reference for future refreshes". Click OK.
14. 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".

1. Click "Developer" tab
   How to show the Developer tab or run in developer mode
2. Click "Visual Basic"
3. Create a "Module" for your workbook
   How to Copy Excel VBA Code to a Regular Module
4. 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

1. Select High, Low and Close columns on sheet "Data"
2. Click "Insert" tab
3. Click "Other charts"
4. Click High-Low-Close stock chart
5. Cut and paste stock chart to sheet "Overview"
6. Right click on x-axis values
7. Click "Select data"
8. Click "Edit" in Horizontal (Category) axis labels
9. Select Dates on sheet "Data", =Data!$A$11:$A$71
10. Click OK!
11. Click OK!
12. Right click on x-axis values
13. Click "Format axis"
14. Click Axis Options
15. Click Axis Type: Text axis
16. Click Categories in reverse order
17. Click Vertical axis crosses: At maximum category
18. 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:

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

Dynamic stock chart in excel – Add date ranges

Excel 2007: How to create a dynamic chart

Excel: Dynamic charting with drop list