Updating web query

[normanbox]

I am stuck and don’t know where to even begin and am not even sure if what I want to do is even possible, but then again, I really don’t know VBA. I’ll be as specific as possible. I’m looking for a solution to the following problem, likely VBA code.

I have a workbook with “Sheet 1” and “Sheet 2.” Sheet 1 has three web queries from Yahoo Finance:

http://finance.yahoo.com/q/is?s=A+Income+Statement&annual
http://finance.yahoo.com/q/bs?s=A+Balance+Sheet&annual
http://finance.yahoo.com/q/cf?s=A+Cash+Flow&annual

In each of the above web addresses, the address points to a stock by using a ticker symbol, in this case “A”. In addition, because I don’t want the whole page, I have “checked” only the table next to “Period ending” to be imported. The tables from the query are then imported into Sheet 1 starting at B3, I3, and P3 respectively.

I have a list of stock ticker symbols in Sheet 2. Cell A1 currently has the symbol “A”, but I would like to update the ticker symbol to another symbol, i.e. MSFT and have the queries in “Sheet 1” all update to the new symbol, including only importing the same selected. The result would be the following:

http://finance.yahoo.com/q/is?s=MSFT+Income+Statement&annual
http://finance.yahoo.com/q/bs?s=MSFT+Balance+Sheet&annual
http://finance.yahoo.com/q/cf?s=MSFT+Cash+Flow&annual

Automating this process is very important to me because I have a lot of stock ticker symbols I’d like to review. Your help will be extremely appreciated!

 

[John_w]

I have a list of stock ticker symbols in Sheet 2. Cell A1 currently has the symbol “A”, but I would like to update the ticker symbol to another symbol, i.e. MSFT and have the queries in “Sheet 1” all update to the new symbol, including only importing the same selected.

It sounds like you need a dynamic web query. See MS Excel Web Query Secrets Revealed. You can also create a dynamic web query in VBA without the need for the .iqy file that the first link uses - see WebQuery.

 

[normanbox]

That worked, except I can't seem to get the address to query the one table, but I can work around querying the whole page.

Thank you,