Querytables in Loop

[JuanPa]

Hi, everyone.

I want to download a list of the components of a stock Index from Yahoo! Finance (i.e.: http://finance.yahoo.com/q/cp?s=%5EIXIC+Components).
In order to get the list in a .csv file, it is possible to get a web address, but it's limited to 50 records (i.e.: http://download.finance.yahoo.com/d/quotes.csv?&s=@^IXIC&f=s&h=1), so the next logical step is to create a loop which downloads every .cvs into a the same spreadsheet.

I'm using a QueryTable object which connection is the .csv web address. Then, the QueryTable deposits the data in column A (1) of my spreadsheet. The procedure is repeated using a While..Wend till all of the batched have been downloaded. Each new instance of the QueryTable is pasted at the button of the previous one. My code looks as follows.

[FONT=Courier New][SIZE=1][COLOR=DarkSlateBlue]Option Explicit[/COLOR]
[COLOR=DarkSlateBlue]Private Sub [/COLOR]TESTING()
    [COLOR=Green]'Call Components_Download("^DJA") 'Dow Jones Composite Average (65 Components)[/COLOR]
    Call Components_Download("^IXIC") 'Nasdaq Composite (2605 Components)
[COLOR=DarkSlateBlue]End Sub
Private Sub [/COLOR]Components_Download(Index_Symbol As [COLOR=DarkSlateBlue]String[/COLOR])
    With ThisWorkbook.Worksheets("Components")
        [COLOR=Green]'Variables definition[/COLOR]
        Dim Temporal_Table [COLOR=DarkSlateBlue]As QueryTable[/COLOR], _
            File_Address [COLOR=DarkSlateBlue]As String[/COLOR], _
            File_Address_Root [COLOR=DarkSlateBlue]As String[/COLOR], _
            Row_Number [COLOR=DarkSlateBlue]As Integer[/COLOR], _
            Batch_Number [COLOR=DarkSlateBlue]As Integer[/COLOR], _
            Download_Finished [COLOR=DarkSlateBlue]As Boolean[/COLOR]
        [COLOR=Green]'Default values[/COLOR]
            File_Address_Root = "http://download.finance.yahoo.com/d/quotes.csv?"
            Row_Number = 2
            Batch_Number = 0
            Download_Finished = [COLOR=DarkSlateBlue]False[/COLOR]
        .Cells.Clear
        [COLOR=Green]'Loop[/COLOR]
        [COLOR=DarkSlateBlue]While [/COLOR]Download_Finished = [COLOR=DarkSlateBlue]False[/COLOR]
            [COLOR=Green]'Fill web address for the batch[/COLOR]
            File_Address = File_Address_Root & "&s=" & Replace(Index_Symbol, "^", "@%5E") & _
                                               "&f=sn" & _
                                               "&h=" & (Batch_Number * 50) + 1
            [COLOR=Green]'Creates a querytable based on the batch web address[/COLOR]
            [COLOR=DarkSlateBlue]Set [/COLOR]Temporal_Table = .QueryTables.Add(Connection:="URL;" & File_Address, _
                                                  Destination:=.Cells(Row_Number, 1))
            [COLOR=DarkSlateBlue]With [/COLOR]Temporal_Table
                .BackgroundQuery = [COLOR=DarkSlateBlue]True[/COLOR]
                .TablesOnlyFromHTML = [COLOR=DarkSlateBlue]False[/COLOR]
                .Refresh BackgroundQuery:=[COLOR=DarkSlateBlue]False[/COLOR]
                .SaveData = [COLOR=DarkSlateBlue]True[/COLOR]
                .Delete
            [COLOR=DarkSlateBlue]End With[/COLOR]
            [COLOR=Green]'Checks if the last row is empty in order to stop querying[/COLOR]
            [COLOR=DarkSlateBlue]If [/COLOR].Cells(Row_Number + 50, 1) = "" Then
                Download_Finished = [COLOR=DarkSlateBlue]True[/COLOR]
            [COLOR=DarkSlateBlue]Else[/COLOR]
                Row_Number = Row_Number + 50
                Batch_Number = Batch_Number + 1
            [COLOR=DarkSlateBlue]End If[/COLOR]
        [COLOR=DarkSlateBlue]Wend[/COLOR]
        [COLOR=Green]'Housekeeping[/COLOR]
        .Cells(1, 1) = "Symbol"
        .Cells(1, 2) = "Name"
        Row_Number = Cells(1, 1).End(xlDown).Row
        .Range(Cells(2, 1), Cells(Row_Number, 1)).TextToColumns Destination:=Cells(2, 1), _
                                                                DataType:=xlDelimited, _
                                                                TextQualifier:=xlDoubleQuote, _
                                                                Comma:=[COLOR=DarkSlateBlue]True[/COLOR], _
                                                                FieldInfo:=Array(Array(1, 1), Array(2, 1))
        .Range(Cells(1, 1), Cells(Row_Number, 2)).RemoveDuplicates Columns:=Array(1, 2), _
                                                                   Header:=xlYes
    End With
End Sub[/SIZE][/FONT]

My problem? Every new QueryTable is creating a new column to the left, so every batch end is a different column. Printscreen below (loop 2).

-- removed inline image ---

 

[b.downey]

The Shift of the Data Set (Batchs) is happening when you get to the line ".Refresh BackgroundQuery:=False". I am not sure why.

I did find that is you change the line Row_Number = Row_Number + 50 to Row_Number = Row_Number + 53, the shift does not occur per batch, but it does leave you with a couple of extra rows between batchs. You can always remove the extra lines via a macro.

Clearly this is not an elegant solution, but it may help until a better answer is provided.

 

[John_w]

Before the .Refresh, try putting:

.RefreshStyle = xlOverwriteCells

Alternatively, instead of adding and deleting the web query, create one web query on a separate sheet and modify its Connection property for each URL and copy the retrieved data to your main sheet. Another way without a web query is Workbooks.Open URL, which downloads the data to a separate workbook (quotes.csv) and then copy that data to your main sheet.

For me, your Row_Number calculation causes the last row of the previous web query data to be overwritten.