web query and yahoo finance stock quotes

**addingle** · 10-21-2011, 09:37 AM

Newby here. I'm tracking stocks from finance.yahoo.com, and I need to get the adjusted closing price for yesterday. I can do this by using a web query, pasting the results into a separate worksheet, and then linking the desired cell into another worksheet. The problem is that I want to track a large number of stocks, and I really don't want to have 100 worksheets. Is there a way to do a web query for a specific item in a table?

Thanks in advance for any ideas.......

**6StringJazzer** · 10-21-2011, 12:07 PM

I'm a little confused by what you're describing as the layout of your data. If you do a web query, why do you need to copy & paste the data to another worksheet? Why not just link directly to the data provided by the web query?

You can do as many queries as you want on a single worksheet, then link the particular cells you're interested in on another. I don't see a need for more than two worksheets.

My understanding of how web queries work is that you can only identify a table to import, based on Excel's analysis of the HTML code. You cannot identify a single cell in the table to import. There may be such a way to do something like that using VBA but I have not used VBA to try to retrieve HTML from a URL.

**addingle** · 10-21-2011, 01:26 PM

Thanks for the reply. I am using web query to import, from yahoo finance, yesterday's adjusted closing stock price (as a table). As you suggest, I link the particular cell to another worksheet, which updates every morning, calculates ytd return, and so forth. But I also have to reference older historical prices for 3, 5, and 10 year returns. I do this by importing the same table, in its entirety, via excel, and delete the web query portion. Because I am doing this for about 100 securities, I have a worksheet for each security. I was simply hoping that I could avoid the need to create multiple worksheets and simply import specific cells. Sounds like it is not possible.