Loop breaks at different points

**nhandal** · 04-28-2009, 04:40 PM

Hi,

I have a sheet named SymbolList in which I list stock symbols in the first column, and have row headings in the first row. I use the following sub to get stock quotes from yahoo. I developed a loop that works in steps of 200 because yahoo has a limit of 200 stock symbols that can be acquired in each request. The loop should go through the whole list in loops of 200 until the last row or if by mistake there is an empty row. The problem I face is that the loop does not work in a consistent manner, for example if I use 200 as the step limit, the sub skips all loops and only gets the last one, if I use a limit of 100 the sub skips the first loop and then proceeds, it only works well at a limit of 50.

I hope someone can help me figure out what is wrong with my loop.

Sub UpdateQuotes()
  
    Dim WSW As Worksheet
    Dim Qt As QueryTable
    Dim ConnectionString As String, StockFlags As String
    Dim i As Integer, iLoopLimit As Integer, LastRow As Integer
    
           
    StockFlags = "&f=" + "d1t1l1ve1"
    
        
    DisableUpdate
    
        
    Set WSW = Worksheets("SymbolList")
    
       
    LastRow = Range("A65536").End(xlUp).Row
    
    
    For iLoopLimit = 0 To LastRow Step 200
    
        
        ConnectionString = "URL;http://download.finance.yahoo.com/d/quotes.csv?s="
        i = iLoopLimit + 2
        ConnectionString = ConnectionString + Trim(SymbolList.Cells(i, 1).Text)
        'i = i + 1

        For i = (iLoopLimit + 3) To (iLoopLimit + 51) Step 1
          If Cells(i, 1).Text = "" Then
            Exit For
          Else
            ConnectionString = ConnectionString + "+" + Trim(SymbolList.Cells(i, 1).Text)
          End If
        Next i
       
        
        ConnectionString = ConnectionString + StockFlags
           
        
        
        Set Qt = WSW.QueryTables.Add(Connection:=ConnectionString, Destination:=WSW.Range("H2"))
        
            With Qt
                .FieldNames = True
                .RowNumbers = False
                .FillAdjacentFormulas = False
                .PreserveFormatting = True
                .RefreshOnFileOpen = False
                .BackgroundQuery = True
                .RefreshStyle = xlInsertDeleteCells
                .SavePassword = False
                .SaveData = True
                .AdjustColumnWidth = True
                .RefreshPeriod = 0
                .WebSelectionType = xlSpecifiedTables
                .WebFormatting = xlWebFormattingNone
                .WebPreFormattedTextToColumns = True
                .WebConsecutiveDelimitersAsOne = True
                .WebSingleBlockTextImport = False
                .WebDisableDateRecognition = False
                .WebDisableRedirections = False
                .TablesOnlyFromHTML = False
            End With
 
            With Application
                On Error Resume Next
                Qt.Refresh BackgroundQuery:=False
            End With
 
            WSW.Range("H2:H200").Select
            Selection.TextToColumns Destination:=WSW.Range("H2"), DataType:=xlDelimited, _
                TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
                Semicolon:=False, Comma:=True, Space:=False, Other:=False, FieldInfo _
                :=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1))
 
            WSW.Range("H2:M200").Select
            Selection.Copy
            SymbolList.Cells(iLoopLimit + 2, 2).Select
            Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
 
            
    Next iLoopLimit
    
  
    
    EnableUpdate
    
       
End Sub

**JBeaucaire** · 04-28-2009, 06:40 PM

Without looking deeply, I suggest you stop using "Integer"'...use Long in your declarations.

**TonyS** · 04-28-2009, 10:04 PM

Hi,
Give this a try!

Public Sub test()
Dim WSW As Worksheet
    Dim Qt As QueryTable
    Dim ConnectionString As String, StockFlags As String
    Dim i As Long, iLoopLimit As Long, LastRow As Long
    
           
    StockFlags = "&f=" + "d1t1l1ve1"
    
        
'    DisableUpdate
    
        
    Set WSW = Worksheets("SymbolList")
    
       
    LastRow = Range("A65536").End(xlUp).Row
    
    
    For iLoopLimit = 0 To LastRow Step 200
    
        
        ConnectionString = "URL;http://download.finance.yahoo.com/d/quotes.csv?s="
        i = iLoopLimit + 2
        ConnectionString = ConnectionString + Trim(Cells(i, 1).Text)
        'i = i + 1

        For i = (iLoopLimit + 3) To (iLoopLimit + 201)
          If Cells(i, 1).Text = "" Then
            Exit For
          Else
            ConnectionString = ConnectionString + "+" + Trim(Cells(i, 1).Text)
          End If
        Next i
       
        
        ConnectionString = ConnectionString + StockFlags
           
           
        Set Qt = WSW.QueryTables.Add(Connection:=ConnectionString, Destination:=WSW.Range("H2"))
        
            With Qt
                .FieldNames = True
                .RowNumbers = False
                .FillAdjacentFormulas = False
                .PreserveFormatting = True
                .RefreshOnFileOpen = False
                .BackgroundQuery = True
                .RefreshStyle = xlInsertDeleteCells
                .SavePassword = False
                .SaveData = True
                .AdjustColumnWidth = True
                .RefreshPeriod = 0
                .WebSelectionType = xlSpecifiedTables
                .WebFormatting = xlWebFormattingNone
                .WebPreFormattedTextToColumns = True
                .WebConsecutiveDelimitersAsOne = True
                .WebSingleBlockTextImport = False
                .WebDisableDateRecognition = False
                .WebDisableRedirections = False
                .TablesOnlyFromHTML = False
            End With
 
            With Application
                On Error Resume Next
                Qt.Refresh BackgroundQuery:=False
            End With
 
            WSW.Range("H2:H201").Select
            Selection.TextToColumns Destination:=WSW.Range("H2"), DataType:=xlDelimited, _
                TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
                Semicolon:=False, Comma:=True, Space:=False, Other:=False, FieldInfo _
                :=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1))
 
            WSW.Range("H2:M201").Select
            Selection.Copy
            Cells(iLoopLimit + 2, 2).Select
            Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
            WSW.Range("H2:M201").ClearContents
  
    Next iLoopLimit
    
  
    
'    EnableUpdate
    

End Sub

Tony

**nhandal** · 04-29-2009, 09:50 AM

Dear JBeaucaire and TonyS,

Thank you for your follow up. I tried the suggested changes, but still the same problem. I have attached an excel sheet with a number of symbols to make it easy for you to try the code.

What surprises me is the inconsistency in the way the code works, if you test the sub with different step values, 50, 90, 100, 190, 200 it works differently every time (remember the maximum is 200 per Yahoo's limit).

One thing I noticed: I commented out the "On Error Resume Next" line in the query refresh block, then took the value of the ConnectionString variable when the code stops and used it in IE (in the URL line), the data was returned but IE reports an error and closes. This happened at the 200 step, but at 190 IE does not report an error but the Loop still works strangely.

I am inclined to think there is a problem with the way the ConnectionString variable is passed to IE.

**TonyS** · 04-29-2009, 01:14 PM

Hi,

Try the following procedure. I've tried to set the query into 50/batch and it seems to work fine. You can experiment by changing the value of the variable Counter to a bigger number. I do know 200 is too big though.
I suspect although you can do 200 per call but there's probably a limit for the length of the ConnectionString.

Public Sub TS()
Dim WSW As Worksheet
    Dim Qt As QueryTable
    Dim ConnectionString As String, StockFlags As String
    Dim i As Long, iLoopLimit As Long, LastRow As Long
    Dim Counter As Long
    
    Counter = 50
    StockFlags = "&f=" + "d1t1l1ve1"
    Application.DisplayAlerts = False
    
        
'    DisableUpdate
    
        
    Set WSW = Worksheets("SymbolList")
    
       
    LastRow = Range("A65536").End(xlUp).Row
    
    
    For iLoopLimit = 0 To LastRow Step Counter
    
        
        ConnectionString = "URL;http://download.finance.yahoo.com/d/quotes.csv?s="
        i = iLoopLimit + 2
        ConnectionString = ConnectionString + Trim(Cells(i, 1).Text)
        'i = i + 1

        For i = (iLoopLimit + 3) To (iLoopLimit + 1 + Counter)
          If Cells(i, 1).Text = "" Then
            Exit For
          Else
            ConnectionString = ConnectionString + "+" + Trim(Cells(i, 1).Text)
          End If
        Next i
       
        
        ConnectionString = ConnectionString + StockFlags
           
        Set Qt = WSW.QueryTables.Add(Connection:=ConnectionString, Destination:=WSW.Range("H2"))
        
            With Qt
                .FieldNames = True
                .RowNumbers = False
                .FillAdjacentFormulas = False
                .PreserveFormatting = True
                .RefreshOnFileOpen = False
                .BackgroundQuery = True
                .RefreshStyle = xlInsertDeleteCells
                .SavePassword = False
                .SaveData = True
                .AdjustColumnWidth = True
                .RefreshPeriod = 0
                .WebSelectionType = xlSpecifiedTables
                .WebFormatting = xlWebFormattingNone
                .WebPreFormattedTextToColumns = True
                .WebConsecutiveDelimitersAsOne = True
                .WebSingleBlockTextImport = False
                .WebDisableDateRecognition = False
                .WebDisableRedirections = False
                .TablesOnlyFromHTML = False
            End With
 
            With Application
                On Error Resume Next
                Qt.Refresh BackgroundQuery:=False
            End With
 
             WSW.Range("H2:H" & (Counter + 1)).Select
            Selection.TextToColumns Destination:=WSW.Range("H2"), DataType:=xlDelimited, _
                TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
                Semicolon:=False, Comma:=True, Space:=False, Other:=False, FieldInfo _
                :=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1))
 
            WSW.Range("H2:M" & (Counter + 1)).Select
            Selection.Copy
            Cells(iLoopLimit + 2, 2).Select
            Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
             WSW.Range("H2:M" & (Counter + 1)).ClearContents
     Next iLoopLimit
    
  
  Application.DisplayAlerts = True
'    EnableUpdate
    

End Sub

Tony

**nhandal** · 04-29-2009, 02:43 PM

Hi,

The limit is not the problem, I have attached a file from which I got the original idea, it works for a step of 200 with no problem (just to be clear the string length is not the problem). Click on the button tittled Download Data on the sheet "Yahoo".

My code is structured a bit different, but I could not figure out what is causing the problem.

Thanks again.

**nhandal** · 04-30-2009, 03:15 PM

Hi TonyS,

Just checking if you had the opportunity to review the file I attached yesterday.

Thanks.

**TonyS** · 05-01-2009, 05:33 PM

Hi,
The problem is the length of the ConnectionString. I've made several tests and the length of the ConnectionString must <= 1037 for this web query to work, otherwise the results can be unpredicted.
You can use the procedure from my previous posting to adjust the counter value.
Also, you can add the following line to the procedure

Debug.Print "ConnectionString Len=" & Len(ConnectionString)

In sum, you can query up to 200 symbols with Len(ConnectString)<=1037.
Good Luck,
Tony

**nhandal** · 05-02-2009, 03:25 PM

Hi TonyS,

Thanks a lot. I really appreciate your work on this.

**nhandal** · 05-02-2009, 04:00 PM

One more question which still confuses me. At Counter value 100 the length of the ConnectionString is in the range of 600 which is below the 1037 limit you stated earlier, but the first loop is always skipped while the data for the rest of the symbols is acquired. Do you have an explanation for this strange behavior.

Thanks.

**TonyS** · 05-03-2009, 11:16 AM

Hi,
I don't know why it didn't work for you.
Counter=100 works fine with me.
Did you change anything?
Try the attached file and let me know.
Tony

**nhandal** · 05-04-2009, 01:26 PM

Hi,

I did not change anything in the code, I checked it again with your file, it is exactly the same code. What is totally strange is the inconsistency with how the code works.

My file works until counter value=80, beyond this point it starts to have problems.
Your file works fine until counter value=180
The file I posted earlier works fine up to counter value=200 (which Yahoo states is the limit on the number of stock symbols in one batch).

I can't find an any logical explanation for this and hope that I can find one, because it really makes a big difference for me when the code loops in batches of 200 symbols as it uses less than half the time of batches of 80 to update the file frequently during the day.

TonyS, I really appreciate the time you have put in this, and want to thank you very much.