+ Reply to Thread
Results 1 to 12 of 12

Loop breaks at different points

  1. 04-28-2009, 04:40 PM #1
    nhandal
    nhandal is offline
    Registered User
    Join Date
    04-28-2009
    Location
    Jerusalem
    MS-Off Ver
    Excel 2021 Professional
    Posts
    19

    Loop breaks at different points

    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.

    Please Login or Register  to view this content.
    Last edited by nhandal; 05-11-2009 at 12:48 AM.
    Reply With QuoteRegister To Reply
  2. 04-28-2009, 06:40 PM #2
    JBeaucaire
    JBeaucaire is offline
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Loop breaks at different points

    Without looking deeply, I suggest you stop using "Integer"'...use Long in your declarations.
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)
    Reply With QuoteRegister To Reply
  3. 04-28-2009, 10:04 PM #3
    TonyS
    TonyS is offline
    Forum Contributor
    Join Date
    10-08-2006
    Location
    Walnut, CA
    MS-Off Ver
    2003,2010, Office 365
    Posts
    114

    Re: Loop breaks at different points

    Hi,
    Give this a try!

    Please Login or Register  to view this content.
    Tony
    Reply With QuoteRegister To Reply
  4. 04-29-2009, 09:50 AM #4
    nhandal
    nhandal is offline
    Registered User
    Join Date
    04-28-2009
    Location
    Jerusalem
    MS-Off Ver
    Excel 2021 Professional
    Posts
    19

    Re: Loop breaks at different points

    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.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  5. 04-29-2009, 01:14 PM #5
    TonyS
    TonyS is offline
    Forum Contributor
    Join Date
    10-08-2006
    Location
    Walnut, CA
    MS-Off Ver
    2003,2010, Office 365
    Posts
    114

    Re: Loop breaks at different points

    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.


    Please Login or Register  to view this content.
    Tony
    Reply With QuoteRegister To Reply
  6. 04-29-2009, 02:43 PM #6
    nhandal
    nhandal is offline
    Registered User
    Join Date
    04-28-2009
    Location
    Jerusalem
    MS-Off Ver
    Excel 2021 Professional
    Posts
    19

    Re: Loop breaks at different points

    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.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  7. 04-30-2009, 03:15 PM #7
    nhandal
    nhandal is offline
    Registered User
    Join Date
    04-28-2009
    Location
    Jerusalem
    MS-Off Ver
    Excel 2021 Professional
    Posts
    19

    Re: Loop breaks at different points

    Hi TonyS,

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

    Thanks.
    Reply With QuoteRegister To Reply
  8. 05-01-2009, 05:33 PM #8
    TonyS
    TonyS is offline
    Forum Contributor
    Join Date
    10-08-2006
    Location
    Walnut, CA
    MS-Off Ver
    2003,2010, Office 365
    Posts
    114

    Re: Loop breaks at different points

    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
    Please Login or Register  to view this content.
    In sum, you can query up to 200 symbols with Len(ConnectString)<=1037.
    Good Luck,
    Tony
    Reply With QuoteRegister To Reply
  9. 05-02-2009, 03:25 PM #9
    nhandal
    nhandal is offline
    Registered User
    Join Date
    04-28-2009
    Location
    Jerusalem
    MS-Off Ver
    Excel 2021 Professional
    Posts
    19

    Re: Loop breaks at different points

    Hi TonyS,

    Thanks a lot. I really appreciate your work on this.
    Reply With QuoteRegister To Reply
  10. 05-02-2009, 04:00 PM #10
    nhandal
    nhandal is offline
    Registered User
    Join Date
    04-28-2009
    Location
    Jerusalem
    MS-Off Ver
    Excel 2021 Professional
    Posts
    19

    Re: Loop breaks at different points

    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.
    Reply With QuoteRegister To Reply
  11. 05-03-2009, 11:16 AM #11
    TonyS
    TonyS is offline
    Forum Contributor
    Join Date
    10-08-2006
    Location
    Walnut, CA
    MS-Off Ver
    2003,2010, Office 365
    Posts
    114

    Re: Loop breaks at different points

    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
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  12. 05-04-2009, 01:26 PM #12
    nhandal
    nhandal is offline
    Registered User
    Join Date
    04-28-2009
    Location
    Jerusalem
    MS-Off Ver
    Excel 2021 Professional
    Posts
    19

    Re: Loop breaks at different points

    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.
    Last edited by nhandal; 05-04-2009 at 01:27 PM. Reason: missing the word you in "Thank you"
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1