Results 1 to 5 of 5
  1. #1
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    MSN StockQuotes & Sorting (Excel XP)

    (If there is a better forum, moderator please move me ...)

    I've an Excel XP workbook uses the MSN stockquote functions and also grabs corporate data from Money Central for a slew of companies, builds a summary block, one row of a worksheet per company, calculates a home-grown score, sorts the rows in descending sequence on the Score ("Check out these companies first ...") and then saves the workbook.

    Watching it run, I see the rows being built, I see the rows sorted into descending sequence, I see the workbook saved, and then the rows appear out of sequence.
    If I place a manual pause (using a MsgBox) before the Save, the sort seems to stick.
    Who wants to ship an application with a MsgBox "Er, I can't fathom what's happening, but if you click OK it'll all come out in the wash", or similar, right?

    I have now moved the call to my Sort routine to a point after the Save, and that seems to work around the problem, but for the life of me I can't comprehend why a Save of a workbook might un-do a completed sort.

    This is not urgent, but it leaves me wondering what I might be doing wrong.
    Anyone else out there using MSN? Or have you all gone to Google Finance?

  2. #2
    3 Star Lounger MacroAlan's Avatar
    Join Date
    Feb 2003
    Location
    St Louis, Missouri, USA
    Posts
    254
    Thanks
    6
    Thanked 1 Time in 1 Post

    Re: MSN StockQuotes & Sorting (Excel XP)

    I have had some people hit a limit on the number of stocks that can be tracked at one time with MSN.StockQuote. It may have been bombing on the recalc.
    Alan

  3. #3
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: MSN StockQuotes & Sorting (Excel XP)

    > It may have been bombing on the recalc.
    Thanks Allan.
    I'll try to isolate or iterate slowly the claims to the network.
    I have a horrible feeling that it's an all-or-nothing thing, though.
    Typically the client summary workbook has fifty to 500 rows of data, each row linked to a separate (50-500) corporate workbook, each corporate workbook grabbing data from the web.
    My final sort-and-rank is supposed to be done without access to the web.

    If all else fails I'll try the old trick of building a summary workbook with only three rows of data.

  4. #4
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: MSN StockQuotes & Sorting (Excel XP)

    You do know that info pulled from the web may be asynchonous?
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  5. #5
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: MSN StockQuotes & Sorting (Excel XP)

    > You do know that info pulled from the web may be asynchonous?
    Er, yes.
    Also that MoneyCentral is perhaps the worst source of this type of data; it is the one upon which client stumbled, and we'll probably switch to Google Finance when he gets back from vacation.
    I suspect the problem lies within the MS code that scours the workbook for all possible update avenues, bundles them into a single transmission, and then tries to sort out the responses and put them all in place.
    I hate to say this, but my code is working, inasmuch as it sorts the block of cells, and can be seen to sort them, and they stay sorted when I single-step through the update process.
    Sorting the block of cells after the update process we can see them with correct values, and sorted, but running in batch mode the sorting gets undone.

    I'm guessing now that the Async process has "remembered" the block of data, and when that async process is complete, it restores the block of data as it remembered it, but with updated values (which generally haven't changed, since we are looking at each quarter's results, that is, they change about once every 3 months.

Posting Permissions

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