Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using Webquery via VBA
First, if this has been done a million times before, I apologize.
I have a worksheet that has one column containing stock tickers. Another column tells us what exchange it trades on, or whether it's off the board or delisted. I'd like to create another worksheet that would take the stock ticker values, for example, and submit a webquery to pcquote.com (http://webservices.pcquote.com/cgi-bin/excel.exe) and outputting the tabular result from the resulting webpage. I'd obviously need to build an array I suppose of the stock tickers and then pass that to the webquery code. But I want to condtitionally add to the array based on the column that tells us what exchange it belongs to (I only want to run this for the cheap penny stocks...nothing on Amex or Nasdaq). Is this very difficult in Excel 2000 or 2002 ? I know a little about VBA (done some Outlook stuff) but I don't know the Excel Object model. Is there a good book that would help me out. I'm sure there are tons of Excel books, but I would want something that is more VBA and Webquerying focused. Thanks ! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using Webquery via VBA
Thanks, Don.
I have another question to ask you. Is there a way to break out the tab- delimted results to separate cells ? If you can just point me in the general direction that would be great. "Don Guillett" wrote in news:OKmUgLGuDHA.2408 @tk2msftngp13.phx.gbl: This may give you some idea X = [quotes!a65536].End(xlUp).Row For Each c In Sheets("quotes").Range(Cells(5, 1), Cells(X, 1)) symbols = symbols & "+" & c Next URLAddress = "http://finance.yahoo.com/d/quotes.csv?s=" mystringend = symbols & "&f=snd1t1l1ohgpv&e=.csv" qurl = URLAddress + mystringend With Sheets("Data").QueryTables.Add(Connection:="URL;" & qurl, _ Destination:=Sheets("Data").Range("b2")) .BackgroundQuery = True .TablesOnlyFromHTML = False .Refresh BackgroundQuery:=False .SaveData = True End With |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using Webquery via VBA
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Webquery from other browser | Excel Discussion (Misc queries) | |||
Webquery | Excel Worksheet Functions | |||
webquery and solver macros | Excel Discussion (Misc queries) | |||
Webquery fieldnaming... | Links and Linking in Excel | |||
Webquery | Excel Discussion (Misc queries) |