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 !

Using Webquery via VBA
 

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
SalesAid Software

"Richard Winston" wrote in message
...
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 !

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

Using Webquery via VBA
 

Use datatext to columns

--
Don Guillett
SalesAid Software

"Richard Winston" wrote in message
...
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