Using Webquery via VBA

[Richard Winston]

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 !

 

[Don Guillett]

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

 

[Richard Winston]

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.

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

 

[Don Guillett]

Use data>text to columns

--
Don Guillett
SalesAid Software
(e-mail address removed)

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.

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