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.

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" <quartz@discussions.microsoft.com> wrote in message
news:46287EEF-6EAF-48B2-8877-5F6B85C2D0CB@microsoft.com...
> 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.

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

--
Regards,
Tom Ogilvy

"quartz" <quartz@discussions.microsoft.com> wrote in message
news:46287EEF-6EAF-48B2-8877-5F6B85C2D0CB@microsoft.com...
> 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.

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" <quartz@discussions.microsoft.com> wrote in message
> news:46287EEF-6EAF-48B2-8877-5F6B85C2D0CB@microsoft.com...
> > 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.
>
>
>