Web Query general question
 

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

Web Query general question
 

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

Web Query general question
 

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

Web Query general question
 

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

Web Query general question
 

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

Web Query general question
 

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

Web Query general question
 

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

Web Query general question
 

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

Web Query general question
 

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