Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Getting chunks of 200 items for stock query
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel query for a stock sheet. | Excel Discussion (Misc queries) | |||
adding stock items to accounting reports | Excel Discussion (Misc queries) | |||
use a check box to dermine shipping charges of non-stock items | Excel Discussion (Misc queries) | |||
Query to MSN Stock Scouter | Excel Worksheet Functions | |||
Web Query With Multiple Stock Symbols & Stock Scouter | Excel Worksheet Functions |