I have what I think is a pretty simple question.
I have a column (A) of stock symbols (IBM, WMT, CAT). As the first part of this project, all I want to be able to do is to make a query to yahoo finance to get the name.
What I do is this.
1.Click on cell B1 and go to Data -> From Web
2. the web query window comes up and in the address I type http://download.finance.yahoo.com/d/quotes?s=IBM&f=n
- I import this into B1 and I get the correct name "International Bus" in column B1
3. I now click B1 and right click and choose "Edit Query".
4. I substitute IBM for ["CellToIncludeHere"] so the query now reads
http://download.finance.yahoo.com/d/quotes?s=["CellToIncludeHere"]&f=n
5. I click on import and I get a prompt with the label CellToIncludeHere as expected. I enter a Cell Value of $A1 (note that I hope the column is fixed and the row is not)
6. I enable both "Use this value/reference for future refreshes" and "Refresh automatically when cell value changes"
7. Ok - so that didn't work (as expected). I get text '$A1' (without the 's) in the cell B1.
8. .. but now I can right-click on cell B1 and I now get the option to "Parameters" so I click on that
9. In this window I check "Get the value from the following cell:" and enter "=$A1" (without quotes) again, and enable "Refresh automatically when cell value changes". Click OK
10. This seems to work, it gets the IBM stock ticker from A1 and uses that in the query. I can change A1 to WMT and the name changes to "Wal-Mart Stores"
NOW for the tricky part
11. I enter another stock symbol in A2 (XOM - Exxon)
12. I select B1:B2 and hit Ctrl-D (copy down)
13. This does NOT pick up XOM but instead is still the same query as B1 with the parameter CellToIncludeHere and that parameter points to $A1 which is IBM.
So the question is - How do I get the query string to get the value from column A and the corresponding row and simply use that value in the query without the named parameter (CellToIncludeHere) being there? It seems like a simple thing but I'm not getting it!
Thanks for your help!
I have a column (A) of stock symbols (IBM, WMT, CAT). As the first part of this project, all I want to be able to do is to make a query to yahoo finance to get the name.
What I do is this.
1.Click on cell B1 and go to Data -> From Web
2. the web query window comes up and in the address I type http://download.finance.yahoo.com/d/quotes?s=IBM&f=n
- I import this into B1 and I get the correct name "International Bus" in column B1
3. I now click B1 and right click and choose "Edit Query".
4. I substitute IBM for ["CellToIncludeHere"] so the query now reads
http://download.finance.yahoo.com/d/quotes?s=["CellToIncludeHere"]&f=n
5. I click on import and I get a prompt with the label CellToIncludeHere as expected. I enter a Cell Value of $A1 (note that I hope the column is fixed and the row is not)
6. I enable both "Use this value/reference for future refreshes" and "Refresh automatically when cell value changes"
7. Ok - so that didn't work (as expected). I get text '$A1' (without the 's) in the cell B1.
8. .. but now I can right-click on cell B1 and I now get the option to "Parameters" so I click on that
9. In this window I check "Get the value from the following cell:" and enter "=$A1" (without quotes) again, and enable "Refresh automatically when cell value changes". Click OK
10. This seems to work, it gets the IBM stock ticker from A1 and uses that in the query. I can change A1 to WMT and the name changes to "Wal-Mart Stores"
NOW for the tricky part
11. I enter another stock symbol in A2 (XOM - Exxon)
12. I select B1:B2 and hit Ctrl-D (copy down)
13. This does NOT pick up XOM but instead is still the same query as B1 with the parameter CellToIncludeHere and that parameter points to $A1 which is IBM.
So the question is - How do I get the query string to get the value from column A and the corresponding row and simply use that value in the query without the named parameter (CellToIncludeHere) being there? It seems like a simple thing but I'm not getting it!
Thanks for your help!
