locked
Excel Web Query returns no data RRS feed

Answers

  • I've had another look at the page in question, in comparison with a working web page.

    The only thing I have spotted is related to the Div tags.  In the working one the all the referencable tables have div tags with an id like this

          <div id="yfi_market_info"><div id="yfi_market_summary">

    However I have noticed that although the page in question uses this

          <div class="panel" id="portfoliopanel" style="clear:both; padding:6px; z-index:1; margin:0 10px">

    it is nested inside another <div> with no id.  It appears that this also repeated elsewhere and may explain why the first div with an id doesn't return what you were expecting. 

    It may also be why the Web Query tool cannot identify blocks that appear to be coded correctly.  Pure speculation but if the divs are not structured with ids the query will not look inside the div thus making any divs nested inside it and it's content invisible.


    G North MCT
    Saturday, October 8, 2011 1:00 PM

All replies

  • I see the same error in EXCEL 2007.

     

    Friday, October 7, 2011 7:23 AM
  • Hi

    You appear to be at the mercy of the web sites developers, you are not alone.  In this thread right at the very end another user makes comments similar to yours.  Unfortunately there appears not to be a solution.

    Although the yellow arrow is at the top of the page, leading you to think that you will receive the entire page, the structure of the page prevents the query retrieving it.  I'm no HTML guru but I would suspect it has something to do with how the page uses <div>s and the fact the content you really want is to be found inside nested <div> tags.

    It might be worth bringing your problems up with the web site in question.

    Depending on your configuration you may be able to retrieve the data manually by editing the page with Excel, see your browsers menus (varies with browser and version).


    G North MCT
    Friday, October 7, 2011 8:07 AM
  • Hi

     

    Thank you for using Microsoft Office for IT Professionals Forums.

     

    From your description, I realize this problem might be caused by Website locked this data.

     

    I have try to use Excel Web Query from following website Microsoft, Yahoo & Google, it will download whole data from Website (2007/2010). Also there has Yellow arrow for detailed list let me choice.

    For Portfolio, it did not have yellow arrow for your choice.

    http://office.microsoft.com/en-us/excel-help/results.aspx?qu=excel+query+from+web&ex=1&origin=CH006252840

    http://finance.yahoo.com/indices?e=dow_jones

    http://www.google.com/finance

     

     

     

    Please take your time to try the suggestions and let me know the results at your earliest convenience. If anything is unclear or if there is anything I can do for you, please feel free to let me know.

     

    Best Regards, 

     

    William Zhou

    Forum Support

    -----------------------------------------------------------------------------------------

    Please remember to mark the replies as answers if they help and unmark them if they provide no help. If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.
    Friday, October 7, 2011 8:31 AM
  • Thanks for the reply. I was thinking similar, i.e. no tables only Divs but like you I am no HTMP expert either. Still I don't think it is the web sites fault as such since tables are now discouraged in favour of Divs. I guess MS need to upgrade this in Excel or it will simply become an unusable facility in time.

     

    Friday, October 7, 2011 12:20 PM
  • There are tables <table></table> in both pages. I was comparing your link to a yahoo finance page and the tables seemed to contain the revant properties.

    It may be the use of the Divs that is the issue, possbly the nesting.

    It's only speculation as the machanics of how it works from an Excel Web Query point of view, and what tag usage may be confusion the Web query, are possibly questions web developers could answer.

    Maybe William could find someone to contribute.

    What HTML structues does the Web query identify to position the yellow arrows?

    If the code structures exist what could prevent the query from seeing them?

    Is there a techncal blog containing this information?

     


    G North MCT
    Friday, October 7, 2011 12:40 PM
  • I take it you had a look at the "source" of the web page. I will be interested if anything comes from William or anybody else. It does seem that the data is all there and it is Excel that is failing to return the table data. Why not is the big question. I opened the page in Expression Web but my knowledge of this HTML stuff is not up to interpreting anything this complex.
    Friday, October 7, 2011 1:37 PM
  •  It does seem that the data is all there and it is Excel that is failing to return the table data

    If the above is the case then why do other pages load into EXCEL successfully?

    Friday, October 7, 2011 1:56 PM
  • That is the question I am asking.
    Friday, October 7, 2011 3:57 PM
  • I've had another look at the page in question, in comparison with a working web page.

    The only thing I have spotted is related to the Div tags.  In the working one the all the referencable tables have div tags with an id like this

          <div id="yfi_market_info"><div id="yfi_market_summary">

    However I have noticed that although the page in question uses this

          <div class="panel" id="portfoliopanel" style="clear:both; padding:6px; z-index:1; margin:0 10px">

    it is nested inside another <div> with no id.  It appears that this also repeated elsewhere and may explain why the first div with an id doesn't return what you were expecting. 

    It may also be why the Web Query tool cannot identify blocks that appear to be coded correctly.  Pure speculation but if the divs are not structured with ids the query will not look inside the div thus making any divs nested inside it and it's content invisible.


    G North MCT
    Saturday, October 8, 2011 1:00 PM
  • I guess you derserve the answer for this. When I have a while I will grab the page and modify the /Div to have an ID then publish it to a test site and try out Excel.

    Thanks for the effort.

    Still leaves the question open as to if/how Excel can be modified to to not ignore such structures.

    Friday, October 14, 2011 5:00 AM