I would like to know how web queries are set up on the server side (if,
indeed, there is any setup involved), in other words, how do they work?

When I do a web query, am I requesting information from a database on the
web server, or am I just looking for tables on a particular web page?

To ask with an example, can I submit a web query to this symbol lookup page
on Yahoo Finance?
http://finance.yahoo.com/l or would I have to query the webpage that
returns results for a typed-in request, for example, this page that is
returned from typing in "Intel" and pressing "Look Up":
http://finance.yahoo.com/l?s=intel&t=S&m=US

If this is a complex issue, I'd appreciate links to articles.

Shockley

A web query just fetches an HTML page from a web server: that page could be
either static (hardcoded) or dynamic.
Dynamic pages accept input parameters as part of the page address, as in
your example, or from a web form posted to the page. Dynamic pages can be
coded in a number of different languages (PERL, vbscript, javascript, java,
etc) on a number of platforms. Basic mechanism is the same though - the
supplied parameters are usually converted into a [SQL] query which is then
run against a datasource (usually a database of some sort). The query
results are converted to a form which can be included in a web page: this is
constructed on the fly and sent back to the requestor. The dynamic parts of
the page might included tables and/or graphics.

A large topic really. Many introductions to this kind of thing can be found
online.

Random links
Intro to CGI: http://hoohoo.ncsa.uiuc.edu/cgi/intro.html
ASP: http://www.devguru.com/Technologies/...asp_intro.html
ColdFusion: http://tech.irt.org/articles/js123/



Tim.

"shockley" wrote in message
...
I would like to know how web queries are set up on the server side (if,
indeed, there is any setup involved), in other words, how do they work?

When I do a web query, am I requesting information from a database on the
web server, or am I just looking for tables on a particular web page?

To ask with an example, can I submit a web query to this symbol lookup
page
on Yahoo Finance?
http://finance.yahoo.com/l or would I have to query the webpage that
returns results for a typed-in request, for example, this page that is
returned from typing in "Intel" and pressing "Look Up":
http://finance.yahoo.com/l?s=intel&t=S&m=US

If this is a complex issue, I'd appreciate links to articles.

Shockley

steel Enter partial company name in cell a1 & touch ENTER key
Symbol Name [up] Market Industry


Just right click on sheet tabview codeinsert thissave.


Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" Then 'GETINFO
Application.ScreenUpdating = False
Rows("3:1000").Delete
ActiveWorkbook.Save
myurl = "http://finance.yahoo.com/l?s=" & [CoName] & "&t=S&m=US"
With ActiveWorkbook.Worksheets("GetSymbols").QueryTable s.Add( _
Connection:="URL;" & myurl, Destination:=[GetSymbols!a5])
.TablesOnlyFromHTML = False
.Refresh BackgroundQuery:=False
End With
Rows("3:19").EntireRow.Delete
Columns("e:i").Delete
DeleteNames
Application.ScreenUpdating = True
End If
End Sub
Private Sub DeleteNames()
On Error Resume Next
For Each nm In ThisWorkbook.Names
If nm.Name Like "*ExternalData*" Then nm.Delete
Next nm
End Sub


--
Don Guillett
SalesAid Software

"shockley" wrote in message
...
I would like to know how web queries are set up on the server side (if,
indeed, there is any setup involved), in other words, how do they work?

When I do a web query, am I requesting information from a database on the
web server, or am I just looking for tables on a particular web page?

To ask with an example, can I submit a web query to this symbol lookup
page
on Yahoo Finance?
http://finance.yahoo.com/l or would I have to query the webpage that
returns results for a typed-in request, for example, this page that is
returned from typing in "Intel" and pressing "Look Up":
http://finance.yahoo.com/l?s=intel&t=S&m=US

If this is a complex issue, I'd appreciate links to articles.

Shockley

Tim, Hi!

You're the guy that gave me that "SaveImg" macro that now forms the basis of
most of my downloading programs (~50K files per day!). It was way over my
head at the time, but I've come to kind of understand it now <g. A real
workhorse and timesaver... thanks again!

I think I should have been clearer in my question, that I was referring to
Excel Web Queries rather than how servers return info to typed-in requests
on a webpage, which latter is what I think you answered--for which I
thankyou
for the explanation and the links. But for now I'm interested in how the
Excel Web Queries work. In the example I gave of the Yahoo Finance Symbol
Lookup page, what I do presently is have my program send the url

http://finance.yahoo.com/l?s=*&t=S&m=US

which returns the page you would get by typing "*" into the box and hitting
"Look Up"

This page lists the first 50 of over 30,000 US/Canada stock symbols. I then
cycle through the 68 pages, retrieving all the symbol information.

I'm wondering if there is a way, using the Excel Web Query, to get the
server to just spit out the entire 30k+ list in one shot, saving me 67 web
accesses (I'm guessing not!).

Regards,
Shockley



"Tim Williams" <saxifrax@pacbell*dot*net wrote in message
...
A web query just fetches an HTML page from a web server: that page could
be
either static (hardcoded) or dynamic.
Dynamic pages accept input parameters as part of the page address, as in
your example, or from a web form posted to the page. Dynamic pages can be
coded in a number of different languages (PERL, vbscript, javascript,
java,
etc) on a number of platforms. Basic mechanism is the same though - the
supplied parameters are usually converted into a [SQL] query which is
then
run against a datasource (usually a database of some sort). The query
results are converted to a form which can be included in a web page: this
is
constructed on the fly and sent back to the requestor. The dynamic parts
of
the page might included tables and/or graphics.

A large topic really. Many introductions to this kind of thing can be
found
online.

Random links
Intro to CGI: http://hoohoo.ncsa.uiuc.edu/cgi/intro.html
ASP: http://www.devguru.com/Technologies/...asp_intro.html
ColdFusion: http://tech.irt.org/articles/js123/



Tim.

"shockley" wrote in message
...
I would like to know how web queries are set up on the server side (if,
indeed, there is any setup involved), in other words, how do they work?

When I do a web query, am I requesting information from a database on
the
web server, or am I just looking for tables on a particular web page?

To ask with an example, can I submit a web query to this symbol lookup
page
on Yahoo Finance?
http://finance.yahoo.com/l or would I have to query the webpage that
returns results for a typed-in request, for example, this page that is
returned from typing in "Intel" and pressing "Look Up":
http://finance.yahoo.com/l?s=intel&t=S&m=US

If this is a complex issue, I'd appreciate links to articles.

Shockley

Don,

Nice piece of code--thanks! As I noted in my response to Tim, I am able to
get a list of up to 50 symbols per web access with my usual programmed
routine, i.e.,

Sub saveImg(sURL)
Dim oXHTTP As New MSXML2.XMLHTTP

oXHTTP.Open "GET", sURL, False
oXHTTP.send
sTest = oXHTTP.ResponseText

'Parse out info

Set oXHTTP = Nothing
End Sub

But, as the info I want comes back in 68 pages, I would like to get the
server to just stream me the whole load with one access. It would really be
much easier for both of us <g.

Regards,
Shockley


"Don Guillett" wrote in message
...
steel Enter partial company name in cell a1 & touch ENTER key
Symbol Name [up] Market Industry


Just right click on sheet tabview codeinsert thissave.


Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" Then 'GETINFO
Application.ScreenUpdating = False
Rows("3:1000").Delete
ActiveWorkbook.Save
myurl = "http://finance.yahoo.com/l?s=" & [CoName] & "&t=S&m=US"
With ActiveWorkbook.Worksheets("GetSymbols").QueryTable s.Add( _
Connection:="URL;" & myurl, Destination:=[GetSymbols!a5])
.TablesOnlyFromHTML = False
.Refresh BackgroundQuery:=False
End With
Rows("3:19").EntireRow.Delete
Columns("e:i").Delete
DeleteNames
Application.ScreenUpdating = True
End If
End Sub
Private Sub DeleteNames()
On Error Resume Next
For Each nm In ThisWorkbook.Names
If nm.Name Like "*ExternalData*" Then nm.Delete
Next nm
End Sub


--
Don Guillett
SalesAid Software

"shockley" wrote in message
...
I would like to know how web queries are set up on the server side (if,
indeed, there is any setup involved), in other words, how do they work?

When I do a web query, am I requesting information from a database on
the
web server, or am I just looking for tables on a particular web page?

To ask with an example, can I submit a web query to this symbol lookup
page
on Yahoo Finance?
http://finance.yahoo.com/l or would I have to query the webpage that
returns results for a typed-in request, for example, this page that is
returned from typing in "Intel" and pressing "Look Up":
http://finance.yahoo.com/l?s=intel&t=S&m=US

If this is a complex issue, I'd appreciate links to articles.

Shockley

The problem comes in when you type in a partial name like "steel" where you
would get a multiple list of possibles from which you have to pick. So you
would have to have a loop to go through each and then probably make a
worksheet for each with the requested title in the list as the name of the
worksheet. Simpler to get one at a time but, if desired, I can custom
program such an animal. Send me what you have to the address below.

--
Don Guillett
SalesAid Software

"shockley" wrote in message
...
Don,

Nice piece of code--thanks! As I noted in my response to Tim, I am able
to
get a list of up to 50 symbols per web access with my usual programmed
routine, i.e.,

Sub saveImg(sURL)
Dim oXHTTP As New MSXML2.XMLHTTP

oXHTTP.Open "GET", sURL, False
oXHTTP.send
sTest = oXHTTP.ResponseText

'Parse out info

Set oXHTTP = Nothing
End Sub

But, as the info I want comes back in 68 pages, I would like to get the
server to just stream me the whole load with one access. It would really
be
much easier for both of us <g.

Regards,
Shockley


"Don Guillett" wrote in message
...
steel Enter partial company name in cell a1 & touch ENTER key
Symbol Name [up] Market Industry


Just right click on sheet tabview codeinsert thissave.


Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" Then 'GETINFO
Application.ScreenUpdating = False
Rows("3:1000").Delete
ActiveWorkbook.Save
myurl = "http://finance.yahoo.com/l?s=" & [CoName] & "&t=S&m=US"
With ActiveWorkbook.Worksheets("GetSymbols").QueryTable s.Add( _
Connection:="URL;" & myurl, Destination:=[GetSymbols!a5])
.TablesOnlyFromHTML = False
.Refresh BackgroundQuery:=False
End With
Rows("3:19").EntireRow.Delete
Columns("e:i").Delete
DeleteNames
Application.ScreenUpdating = True
End If
End Sub
Private Sub DeleteNames()
On Error Resume Next
For Each nm In ThisWorkbook.Names
If nm.Name Like "*ExternalData*" Then nm.Delete
Next nm
End Sub


--
Don Guillett
SalesAid Software

"shockley" wrote in message
...
I would like to know how web queries are set up on the server side
(if,
indeed, there is any setup involved), in other words, how do they
work?

When I do a web query, am I requesting information from a database on
the
web server, or am I just looking for tables on a particular web page?

To ask with an example, can I submit a web query to this symbol lookup
page
on Yahoo Finance?
http://finance.yahoo.com/l or would I have to query the webpage that
returns results for a typed-in request, for example, this page that is
returned from typing in "Intel" and pressing "Look Up":
http://finance.yahoo.com/l?s=intel&t=S&m=US

If this is a complex issue, I'd appreciate links to articles.

Shockley

I looked again. Are you saying that you want a list of ALL 34,380? If so,
this can be done with a loop that adds 50 to the end of the url and
consolidates to the end of a list on another page. But , WHY? The loop would
take awhile since Yahoo is only giving 50 at a time. I can custom design.

--
Don Guillett
SalesAid Software

"shockley" wrote in message
...
Don,

Nice piece of code--thanks! As I noted in my response to Tim, I am able
to
get a list of up to 50 symbols per web access with my usual programmed
routine, i.e.,

Sub saveImg(sURL)
Dim oXHTTP As New MSXML2.XMLHTTP

oXHTTP.Open "GET", sURL, False
oXHTTP.send
sTest = oXHTTP.ResponseText

'Parse out info

Set oXHTTP = Nothing
End Sub

But, as the info I want comes back in 68 pages, I would like to get the
server to just stream me the whole load with one access. It would really
be
much easier for both of us <g.

Regards,
Shockley


"Don Guillett" wrote in message
...
steel Enter partial company name in cell a1 & touch ENTER key
Symbol Name [up] Market Industry


Just right click on sheet tabview codeinsert thissave.


Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" Then 'GETINFO
Application.ScreenUpdating = False
Rows("3:1000").Delete
ActiveWorkbook.Save
myurl = "http://finance.yahoo.com/l?s=" & [CoName] & "&t=S&m=US"
With ActiveWorkbook.Worksheets("GetSymbols").QueryTable s.Add( _
Connection:="URL;" & myurl, Destination:=[GetSymbols!a5])
.TablesOnlyFromHTML = False
.Refresh BackgroundQuery:=False
End With
Rows("3:19").EntireRow.Delete
Columns("e:i").Delete
DeleteNames
Application.ScreenUpdating = True
End If
End Sub
Private Sub DeleteNames()
On Error Resume Next
For Each nm In ThisWorkbook.Names
If nm.Name Like "*ExternalData*" Then nm.Delete
Next nm
End Sub


--
Don Guillett
SalesAid Software

"shockley" wrote in message
...
I would like to know how web queries are set up on the server side
(if,
indeed, there is any setup involved), in other words, how do they
work?

When I do a web query, am I requesting information from a database on
the
web server, or am I just looking for tables on a particular web page?

To ask with an example, can I submit a web query to this symbol lookup
page
on Yahoo Finance?
http://finance.yahoo.com/l or would I have to query the webpage that
returns results for a typed-in request, for example, this page that is
returned from typing in "Intel" and pressing "Look Up":
http://finance.yahoo.com/l?s=intel&t=S&m=US

If this is a complex issue, I'd appreciate links to articles.

Shockley

this can be done with a loop that adds 50 to the end of the url and
consolidates to the end of a list on another page.<

That's what I have now--it runs in about 10 minutes during the speedier
parts of the day, which is OK, but I'd prefer a single stream that comes in
about 1-3 minutes <g.

WHY?<

I'm building a stock data database on the 3 US major markets that requires
keeping the symbols updated. I can get the symbols daily from the Nasdaq
website in 3 files (8000 symbols in about 30 seconds), but the list is kind
of flakey -- go figure. Yahoo does a wonderful job with their data!

Thanks for the help,
Regards,
Shockley


"Don Guillett" wrote in message
...
I looked again. Are you saying that you want a list of ALL 34,380? If so,
this can be done with a loop that adds 50 to the end of the url and
consolidates to the end of a list on another page. But , WHY? The loop
would
take awhile since Yahoo is only giving 50 at a time. I can custom design.

--
Don Guillett
SalesAid Software

"shockley" wrote in message
...
Don,

Nice piece of code--thanks! As I noted in my response to Tim, I am able
to
get a list of up to 50 symbols per web access with my usual programmed
routine, i.e.,

Sub saveImg(sURL)
Dim oXHTTP As New MSXML2.XMLHTTP

oXHTTP.Open "GET", sURL, False
oXHTTP.send
sTest = oXHTTP.ResponseText

'Parse out info

Set oXHTTP = Nothing
End Sub

But, as the info I want comes back in 68 pages, I would like to get the
server to just stream me the whole load with one access. It would really
be
much easier for both of us <g.

Regards,
Shockley


"Don Guillett" wrote in message
...
steel Enter partial company name in cell a1 & touch ENTER key
Symbol Name [up] Market Industry


Just right click on sheet tabview codeinsert thissave.


Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" Then 'GETINFO
Application.ScreenUpdating = False
Rows("3:1000").Delete
ActiveWorkbook.Save
myurl = "http://finance.yahoo.com/l?s=" & [CoName] & "&t=S&m=US"
With ActiveWorkbook.Worksheets("GetSymbols").QueryTable s.Add( _
Connection:="URL;" & myurl, Destination:=[GetSymbols!a5])
.TablesOnlyFromHTML = False
.Refresh BackgroundQuery:=False
End With
Rows("3:19").EntireRow.Delete
Columns("e:i").Delete
DeleteNames
Application.ScreenUpdating = True
End If
End Sub
Private Sub DeleteNames()
On Error Resume Next
For Each nm In ThisWorkbook.Names
If nm.Name Like "*ExternalData*" Then nm.Delete
Next nm
End Sub


--
Don Guillett
SalesAid Software

"shockley" wrote in message
...
I would like to know how web queries are set up on the server side
(if,
indeed, there is any setup involved), in other words, how do they
work?

When I do a web query, am I requesting information from a database
on
the
web server, or am I just looking for tables on a particular web
page?

To ask with an example, can I submit a web query to this symbol
lookup
page
on Yahoo Finance?
http://finance.yahoo.com/l or would I have to query the webpage
that
returns results for a typed-in request, for example, this page that
is
returned from typing in "Intel" and pressing "Look Up":
http://finance.yahoo.com/l?s=intel&t=S&m=US

If this is a complex issue, I'd appreciate links to articles.

Shockley

If you would care to send me the whole thing, maybe?? I can tweak to make it
faster.

--
Don Guillett
SalesAid Software

"shockley" wrote in message
...

this can be done with a loop that adds 50 to the end of the url and
consolidates to the end of a list on another page.<

That's what I have now--it runs in about 10 minutes during the speedier
parts of the day, which is OK, but I'd prefer a single stream that comes
in
about 1-3 minutes <g.

WHY?<

I'm building a stock data database on the 3 US major markets that requires
keeping the symbols updated. I can get the symbols daily from the Nasdaq
website in 3 files (8000 symbols in about 30 seconds), but the list is
kind
of flakey -- go figure. Yahoo does a wonderful job with their data!

Thanks for the help,
Regards,
Shockley


"Don Guillett" wrote in message
...
I looked again. Are you saying that you want a list of ALL 34,380? If
so,
this can be done with a loop that adds 50 to the end of the url and
consolidates to the end of a list on another page. But , WHY? The loop
would
take awhile since Yahoo is only giving 50 at a time. I can custom
design.

--
Don Guillett
SalesAid Software

"shockley" wrote in message
...
Don,

Nice piece of code--thanks! As I noted in my response to Tim, I am
able
to
get a list of up to 50 symbols per web access with my usual programmed
routine, i.e.,

Sub saveImg(sURL)
Dim oXHTTP As New MSXML2.XMLHTTP

oXHTTP.Open "GET", sURL, False
oXHTTP.send
sTest = oXHTTP.ResponseText

'Parse out info

Set oXHTTP = Nothing
End Sub

But, as the info I want comes back in 68 pages, I would like to get
the
server to just stream me the whole load with one access. It would
really
be
much easier for both of us <g.

Regards,
Shockley


"Don Guillett" wrote in message
...
steel Enter partial company name in cell a1 & touch ENTER key
Symbol Name [up] Market Industry


Just right click on sheet tabview codeinsert thissave.


Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" Then 'GETINFO
Application.ScreenUpdating = False
Rows("3:1000").Delete
ActiveWorkbook.Save
myurl = "http://finance.yahoo.com/l?s=" & [CoName] & "&t=S&m=US"
With ActiveWorkbook.Worksheets("GetSymbols").QueryTable s.Add( _
Connection:="URL;" & myurl, Destination:=[GetSymbols!a5])
.TablesOnlyFromHTML = False
.Refresh BackgroundQuery:=False
End With
Rows("3:19").EntireRow.Delete
Columns("e:i").Delete
DeleteNames
Application.ScreenUpdating = True
End If
End Sub
Private Sub DeleteNames()
On Error Resume Next
For Each nm In ThisWorkbook.Names
If nm.Name Like "*ExternalData*" Then nm.Delete
Next nm
End Sub


--
Don Guillett
SalesAid Software

"shockley" wrote in message
...
I would like to know how web queries are set up on the server side
(if,
indeed, there is any setup involved), in other words, how do they
work?

When I do a web query, am I requesting information from a database
on
the
web server, or am I just looking for tables on a particular web
page?

To ask with an example, can I submit a web query to this symbol
lookup
page
on Yahoo Finance?
http://finance.yahoo.com/l or would I have to query the webpage
that
returns results for a typed-in request, for example, this page
that
is
returned from typing in "Intel" and pressing "Look Up":
http://finance.yahoo.com/l?s=intel&t=S&m=US

If this is a complex issue, I'd appreciate links to articles.

Shockley