Graphing Web Data

You can bring data in from web through a web query, but that doesn’t allow you to see the data change over time. With a macro and a chart, you can see how that piece of data changes.

In this example, my web query will be the time. I know you don’t need a graph to see how the time changes, but it gives a nice example that’s updated frequently. Something like a stock price or the current temperature would be more interesting, but they would take too long for my level of patience. Also, I couldn’t find a decent web page from which to query the temperature.

Start by creating a web query that gets the time and put it on sheet named WebQuery:


Next, change the properties to refresh every minute:


Now we’ll need to create a class module with QueryTable Events to save the data before it’s refreshed and lost forever. In a standard module:

Public clsQueryEvent As CQtEvent

In the ThisWorkbook module

Private Sub Workbook_Open()

    Set clsQueryEvent = New CQtEvent
    Set clsQueryEvent.qtTime = WQuery.QueryTables(1)
End Sub

In a Class module named CQtEvent:

Public WithEvents qtTime As QueryTable

Private Sub qtTime_AfterRefresh(ByVal Success As Boolean)

    WData.Range(“a” & WData.Rows.Count).End(xlUp).Offset(1, 0).Value = _
End Sub

Range(“B3?) is where the time ends up when I put the query in A1. Note that I have changed the code names of my two worksheets (F4 to show the properties window in the VBE) to WData and WQuery.

Next we need a defined name that will be the source for our chart, call it GraphData and set it up like this:


Create a bar chart on a worksheet name Data, but instead of using the Data Range textbox, switch over to the Series tab and create a series with your newly defined name as the Data.


After a few refreshes, the chart should look something like this


Now you know how long it takes to make a post.

Posted in Uncategorized

8 thoughts on “Graphing Web Data

  1. Dick, I swear I have never been happier. I was first impressed with the concept of tracking internet data through a web query – I hadn’t gotten into that before. I was carefully reading your post for that reason, because I have a few applications for that sort of thing.

    But I was blown away when I got to your name definition — it gives you a variable-named-range . . . and it’s so SIMPLE!

    Thanks for such a healthy daily dose. You just made my day better!

  2. On a related note, are Add-Ins and Web Queries incompatible?

    I tried making a toolbar with a textbox that would display the current temperature.

    The only way I could get it to work was if I set my IsAddIn property to False.

    Otherwise, since the sheet that the query is on is not visible, the query is not called, so nothing based on that query will work. Is there another way?

  3. I know that dick has an addin that looks at a dictionary site, but I don’t know if this is a web query? – the sheets on this were hidden.

    I’m going to try and write a little addin for xl97, that will allow you to run web queries with out having to pop out and manually save a text file. Should be very easy, if I can save a text file with the .iqy extension. Give me a few hours and see what I come up with.

  4. can you help me to do this:

    i have an excel book.
    in it are sheets with web queries from some websites. these websites wants ‘user login’ & ‘password’.
    so then i open this excel book, it cant update data in web queries.
    how to automate this process, that an excel book at opening would connect to these sites?

    i would very happy for your answer.

  5. how can I download a stock chart directly from the Web as an image without downloading the data and making the chart in Excel?


  6. right click the mouse as pick as image as, or press the “Print Screen” Button on you key board, go to paint and press ctrl+V (ther ctrl button and V at the same time)

Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.