Better method to downdload data into sheet?

The following is a segment of code that pulls stock quote information from Yahoo.
The character string "sl1va2f6t8r5rr6r7j2s7enqd1" pulls 15 different quote properties. (s=symbol, l1=last value, v=volume, etc, see below)

Column A contains stock symbols
Column B will contain the .cvs data that is returned when the query is completed.


I found this "With" construct that creates a QueryTable online.
Immediately upon using the QueryTable command a "table" is created.
Unfortunately one of the byproducts of using the QueryTable command is the table that is created cannot be modified therefore, every time the iteration increments to the next symbol in column A, it shoves column B to the right so as not to damage the already created querytable.

Afters 10 symbols, the previous data is shifted 10 columns to the right.

Also, every time it runs it inputs a row of "noise" before dropping to the following row and returning the "good" data.
so, I end up getting two rows of data for each query.

What I like about this command is it is VERY fast and runs in the background.

Is there a similar way to execute what I am trying to do that is fast, runs in the background and keeps things in a single column.

Thanks,
dustin

        qurl = "http://download.finance.yahoo.com/d/quotes.csv?s=%40%5EDJI," _
            & Symbol & "&f=sl1va2f6t8r5rr6r7j2s7enqd1&e=.csv"
            
QueryQuote:

        With Sheets("Data2").QueryTables.Add _
            (Connection:="URL;" & qurl, _
            Destination:=Sheets("Data2").Range("B" & i))
                .BackgroundQuery = True                 'execute the query in the background
                .TablesOnlyFromHTML = False
                .Refresh BackgroundQuery:=False     'execute the query
                .SaveData = True
        End With

for the curious, these links can give you some background information about retrieving Yahoo quote information.

Current Quotes download:
https://code.google.com/p/yahoo-fina...QuotesDownload

current quote decoder ring:
https://code.google.com/p/yahoo-fina...mQuoteProperty

I think something like this will work for you, assuming you want the downloaded CSV data to be split (via TextToColumns) into separate columns. In this example, the ticker symbol list starts in row 5. I download the data in sets of 50 ticker symbols, since I found that Yahoo chokes (or cuts you off) if you try to download data for like 200 tickers at once. The QueryTable delete at the end removes the data connection (otherwise you end up with an ever-growing list of open data connections):

Sub Download Stock Data()
'
' Download stock data CSV table for list of tickers
'
    Dim LastRow As Integer, StartRow As Integer, StopRow As Integer, rowcounter As Integer
    Dim AllTickers As String, URLString1 As String

    Application.ScreenUpdating = False
    Application.DisplayAlerts = False

    LastRow = Worksheets("Data2").Cells(Rows.Count, "A").End(xlUp).Row
    StartRow = 5
    StopRow = Application.WorksheetFunction.Min(StartRow + 49, LastRow)
    
    Do While StartRow < LastRow
    
    ' Assemble StockTickers string containing all tickers, comma separated
        AllTickers = ""
        For rowcounter = StartRow To StopRow
            AllTickers = AllTickers & Worksheets("Data2").Range("$A$" & rowcounter) & ","
        Next rowcounter
        
    ' Download basic data for all tickers
        URLString1 = "URL;http://download.finance.yahoo.com/d/quotes.csv?s=" + AllTickers + "&f=sl1va2f6t8r5rr6r7j2s7enqd1&e=.csv"
        
        With Worksheets("Data2").QueryTables.Add(Connection:=URLString1, Destination:=Range("$B$" & StartRow))
            .FieldNames = True
            .RowNumbers = False
            .FillAdjacentFormulas = False
            .RefreshOnFileOpen = False
            .BackgroundQuery = False
            .RefreshStyle = xlOverwriteCells
            .SavePassword = False
            .SaveData = True
            .AdjustColumnWidth = False
            .Refresh
        End With
        
        StartRow = StopRow + 1
        StopRow = Application.WorksheetFunction.Min(StartRow + 49, LastRow)
        
    Loop
    
    Worksheets("Data2").Range("$B$5:$B$" & LastRow).TextToColumns Destination:=Range("$B$5"), DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, Comma:=True
    Worksheets("Data2").QueryTables(1).Delete

    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
    
End Sub

Also, forgot to mention: If you don't want to expand the downloaded CSV data, just delete the TextToColumns line (7th line from the bottom)