How to get a web query to refresh into the next column?

[arronaje]

In Microsoft Excel 2010 how can you get a web query to refresh into the next column instead of the same cell?

I'm trying to track the price of a share of stock by date and would like the fields to update themselves every single day. I've tried different things including writing out a seemingly never ending IF function with references to an updating query but with no success.. I always end up creating a circular reference. Please Help!

 

[John_w]

I think you'll need a VBA procedure to control the web query, either by changing its Destination range to the next cell (in Excel 2003 the web query must be deleted and recreated in order to change the Destination property, I don't know about 2010), or by having the web query on one sheet and copying the required data to the next cell on your main data sheet. You could also do it with the BeforeRefresh or AfterRefresh events. I would start by creating the web query with the macro recorder to generate the initial VBA code.

 

[arronaje]

Thanks!
I don't know how to write VBA but this is still very helpful. Do you have any more specific instructions on what i might need to do to the VBA code in order to get it to function the way i want it to?

 

[John_w]

Specific instructions will depend on the web site and the data you want to retrieve.

Another technique for keeping the retrieved data is by using the Worksheet_Change event to copy the data to another sheet every time the web query refreshes. The code below works with a Yahoo Finance stock quote web query (e.g. http://finance.yahoo.com/q?s=MSFT&ql=1), but should be easily adaptable for similar web sites.

First, create a manual web query on Sheet2, importing the table of data which starts 'Last Trade:' and ends '1y Target Est:' to cell A1.

Put the following code in the Sheet2 module:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    
    Dim qt As QueryTable
    Dim nextColumn As Long
    
    If QueryTables.Count = 0 Then
        MsgBox "There isn't a web query defined on the '" & Sheet2.Name & "' sheet"
        Exit Sub
    End If
    
    Set qt = QueryTables(1)
        
    If Not Intersect(qt.Destination, Target) Is Nothing Then
        
        'All data for current web query has been retrieved, so copy it to Sheet1
       
        With Sheet1
            If .Range("A1").Value = "" Then
            
                'Copy web data field names (in column A) to column A on Sheet1
                       
                Range(Cells(qt.ResultRange.row, qt.ResultRange.Column), _
                    Cells(qt.ResultRange.row + qt.ResultRange.Rows.Count, qt.ResultRange.Column)).Copy .Range("A1")
            End If
            
            'Determine next available column by looking in row 1
        
            nextColumn = .Cells(1, Columns.Count).End(xlToLeft).Column + 1

            'Copy web data column B to next column on Sheet1
                       
            qt.ResultRange.Offset(, 1).Copy .Cells(1, nextColumn)
        End With

    End If

End Sub

Every time the web query refreshes (manually or automatically), the data will be copied to the next column on Sheet1.

 

[arronaje]

John W,
Thank you so much for helping me along this far but i still think i have just a couple more questions. How would i restructure that code if i were wanting to refresh more than 1 query out of column A into sheet 1 column A? Also, if i were interested in learning how to write my own VBA code, for excel use, is there any readings you would recommend me?

 

[John_w]

Again, it all depends on the web site. Without knowing the URL and the exact data you want to retrieve and keep it's impossible to give specific advice. Here is the Worksheet_Change routine modified to handle multiple web queries from Yahoo Finance on the sheet:

Option Explicit

'Handle multiple query tables on this sheet

Private Sub Worksheet_Change(ByVal Target As Range)
    
    Dim qt As QueryTable
    Dim nextColumn As Long, dataRow As Long
    Dim i As Integer
    
    If QueryTables.Count = 0 Then
        MsgBox "There isn't a web query defined on the '" & Sheet2.Name & "' sheet"
        Exit Sub
    End If
    
    Debug.Print "Target " & Target.Address
    
    'Determine which web query caused the change
    
    Set qt = Nothing
    i = 1
    While i <= QueryTables.Count And qt Is Nothing
        If Not Intersect(QueryTables(i).Destination, Target) Is Nothing Then
            Set qt = QueryTables(i)
            Debug.Print "Matched "; i, QueryTables(i).Destination.Address
        End If
        i = i + 1
    Wend
            
    If Not qt Is Nothing Then
        
        Debug.Print "Copy "; qt.Destination.Address
        
        'Row to copy the data to on Sheet1 is the same as the web query destination row
        
        dataRow = qt.Destination.row
        
        With Sheet1
            If .Cells(dataRow, 1).Value = "" Then
            
                'Copy web data field names (in column A) to column A on Sheet1
                       
                Range(Cells(qt.ResultRange.row, qt.ResultRange.Column), _
                    Cells(qt.ResultRange.row + qt.ResultRange.Rows.Count, qt.ResultRange.Column)).Copy .Cells(dataRow, 1)
            End If
            
            'Determine next available column in dataRow
        
            nextColumn = .Cells(dataRow, Columns.Count).End(xlToLeft).Column + 1

            'Copy web data column B to dataRow and next column on Sheet1
                       
            qt.ResultRange.Offset(, 1).Copy .Cells(dataRow, nextColumn)
        End With

    End If
    
End Sub


Private Sub CommandButton1_Click()
    Dim i As Integer
    If QueryTables.Count > 0 Then
        For i = 1 To QueryTables.Count
            If Not QueryTables(i).Refreshing Then
                Debug.Print "Refresh "; i
                QueryTables(i).Refresh BackgroundQuery:=True
                DoEvents
            End If
        Next
    End If
End Sub

I've added a command button to the sheet to refresh the web queries.

The Worksheet_Change method might not be the best method you, particularly if there are multiple web queries on the sheet. You might be better refreshing the queries with VBA code using Refresh BackgroundQuery:=False, and then copy the data retrieved.

For learning VBA, someone published a list of web sites etc. on this forum. The best way is to experiment and create simple macros with the macro recorder, edit the code, add debug.print statements, step through the code in the VB editor, set breakpoints, etc. Also, in the VB Editor, make sure Tools - Options - Editor tab - 'Require variable declaration' is ticked. This automatically puts Option Explicit at the top of code modules and forces you to declare variables of the appropriate data type, rather than relying on Excel's default type of Variant, and can save a lot of development time.

 

[arronaje]

Alright well lets say i wanted to do company stocks such as Microsoft, Google, and Apple all at the same time? How do i alter the VBA code to make it copy all of those company prices into their respectful places on sheet1?

http://finance.yahoo.com/q?s=MSFT
http://finance.yahoo.com/q?s=goog
http://finance.yahoo.com/q?s=AAPL

 

[John_w]

In that case I wouldn't use the Worksheet_Change method, as I said. I'd have a single web query, change its Connection property for each URL (stock quote) and call Refresh BackgroundQuery:=False, and then copy the data retrieved to where you want to keep it. There should be a few examples of this technique on this forum. Start by recording a web query with the macro recorder.

 

[arronaje]

When I make my macro do i record it to reload all my web queries? And when im done am i going to be able to copy all the data if i had lets say over 100 stock prices to keep track of? Is there a way to make it automatic?

I'm obviously a newb when it comes to using excel, for that I am sorry. Also thanks again for the help you've already given me.