Querytables in Loop

JuanPa

New Member
Joined
Jun 9, 2010
Messages
32
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.
Code:
[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 ---
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,224,352
Messages
6,178,069
Members
452,822
Latest member
MtC

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top