Getting chunks of 200 items for stock query
 

I am using Office 2003 on Windows XP.

I have a list of +/- 2000 rows of stock symbols in a sheet. There is legacy
code in place that loops through these symbols and runs a couple different
web queries on each one against Yahoo.com to retrieve a variety of
information on each stock. As you might suspect this takes a long time to run.

1. I know that Yahoo can handle up to 200 stock symbols at a time, so I
thought one way to speed things up is to query 200 at a time instead of one
at a time. For this I need the best practice for getting successive chunks of
200 items at a time until all symbols are queried - using an array perhaps?
--- But then how do you neatly get successive chunks of 200 items from an
array...Unless someone can help me with #2 below:

2. Does anyone know of a good web site that I can tap using a web query to
get this many stock quotes (~2000 or more) at one time specifically or in
general other good sources of stock information related to pricing, volume,
% of company owned by institutionals, daily history, and the toughy: how long
a company has been publicly traded?

Sorry for the length, but I think it was needed for adequate explanation.
Thanks a lot in advance for this complex question. All help or suggestions
greatly appreciated.

Getting chunks of 200 items for stock query
 

quartz,

In general, any time you want help modifying code, it is best to post the
code that needs modification.

HTH,
Bernie
MS Excel MVP


"quartz" wrote in message
...
I am using Office 2003 on Windows XP.

I have a list of +/- 2000 rows of stock symbols in a sheet. There is
legacy
code in place that loops through these symbols and runs a couple different
web queries on each one against Yahoo.com to retrieve a variety of
information on each stock. As you might suspect this takes a long time to
run.

1. I know that Yahoo can handle up to 200 stock symbols at a time, so I
thought one way to speed things up is to query 200 at a time instead of
one
at a time. For this I need the best practice for getting successive chunks
of
200 items at a time until all symbols are queried - using an array
perhaps?
--- But then how do you neatly get successive chunks of 200 items from an
array...Unless someone can help me with #2 below:

2. Does anyone know of a good web site that I can tap using a web query to
get this many stock quotes (~2000 or more) at one time specifically or in
general other good sources of stock information related to pricing,
volume,
% of company owned by institutionals, daily history, and the toughy: how
long
a company has been publicly traded?

Sorry for the length, but I think it was needed for adequate explanation.
Thanks a lot in advance for this complex question. All help or suggestions
greatly appreciated.

Getting chunks of 200 items for stock query
 

for i = 1 to 2000 step 200
set rng = cells(i,1).Resize(200,1)
varr = rng.Value
' whatever'
Next

--
Regards,
Tom Ogilvy

"quartz" wrote in message
...
I am using Office 2003 on Windows XP.

I have a list of +/- 2000 rows of stock symbols in a sheet. There is
legacy
code in place that loops through these symbols and runs a couple different
web queries on each one against Yahoo.com to retrieve a variety of
information on each stock. As you might suspect this takes a long time to
run.

1. I know that Yahoo can handle up to 200 stock symbols at a time, so I
thought one way to speed things up is to query 200 at a time instead of
one
at a time. For this I need the best practice for getting successive chunks
of
200 items at a time until all symbols are queried - using an array
perhaps?
--- But then how do you neatly get successive chunks of 200 items from an
array...Unless someone can help me with #2 below:

2. Does anyone know of a good web site that I can tap using a web query to
get this many stock quotes (~2000 or more) at one time specifically or in
general other good sources of stock information related to pricing,
volume,
% of company owned by institutionals, daily history, and the toughy: how
long
a company has been publicly traded?

Sorry for the length, but I think it was needed for adequate explanation.
Thanks a lot in advance for this complex question. All help or suggestions
greatly appreciated.

Getting chunks of 200 items for stock query
 

Thanks Tom, I didn't think of this approach.


"Tom Ogilvy" wrote:

for i = 1 to 2000 step 200
set rng = cells(i,1).Resize(200,1)
varr = rng.Value
' whatever'
Next

--
Regards,
Tom Ogilvy

"quartz" wrote in message
...
I am using Office 2003 on Windows XP.

I have a list of +/- 2000 rows of stock symbols in a sheet. There is
legacy
code in place that loops through these symbols and runs a couple different
web queries on each one against Yahoo.com to retrieve a variety of
information on each stock. As you might suspect this takes a long time to
run.

1. I know that Yahoo can handle up to 200 stock symbols at a time, so I
thought one way to speed things up is to query 200 at a time instead of
one
at a time. For this I need the best practice for getting successive chunks
of
200 items at a time until all symbols are queried - using an array
perhaps?
--- But then how do you neatly get successive chunks of 200 items from an
array...Unless someone can help me with #2 below:

2. Does anyone know of a good web site that I can tap using a web query to
get this many stock quotes (~2000 or more) at one time specifically or in
general other good sources of stock information related to pricing,
volume,
% of company owned by institutionals, daily history, and the toughy: how
long
a company has been publicly traded?

Sorry for the length, but I think it was needed for adequate explanation.
Thanks a lot in advance for this complex question. All help or suggestions
greatly appreciated.

Getting chunks of 200 items for stock query
 

Yahoo asks for a + in between symbols in the string
ibm+fe+T+etc

--
Don Guillett
SalesAid Software

"Tom Ogilvy" wrote in message
...
for i = 1 to 2000 step 200
set rng = cells(i,1).Resize(200,1)
varr = rng.Value
' whatever'
Next

--
Regards,
Tom Ogilvy

"quartz" wrote in message
...
I am using Office 2003 on Windows XP.

I have a list of +/- 2000 rows of stock symbols in a sheet. There is
legacy
code in place that loops through these symbols and runs a couple
different
web queries on each one against Yahoo.com to retrieve a variety of
information on each stock. As you might suspect this takes a long time
to
run.

1. I know that Yahoo can handle up to 200 stock symbols at a time, so I
thought one way to speed things up is to query 200 at a time instead of
one
at a time. For this I need the best practice for getting successive
chunks
of
200 items at a time until all symbols are queried - using an array
perhaps?
--- But then how do you neatly get successive chunks of 200 items from
an
array...Unless someone can help me with #2 below:

2. Does anyone know of a good web site that I can tap using a web query
to
get this many stock quotes (~2000 or more) at one time specifically or
in
general other good sources of stock information related to pricing,
volume,
% of company owned by institutionals, daily history, and the toughy: how
long
a company has been publicly traded?

Sorry for the length, but I think it was needed for adequate
explanation.
Thanks a lot in advance for this complex question. All help or
suggestions
greatly appreciated.