Speed up excel webservice() formula

[Rulenine]

Hi everyone,

I currently have a stock list with various information for each stock (8 columns) being retrieved using WebService(). The problem is that my stock list contains 2000 stocks. This often takes a very long time in excel. I am wondering whether there is a VBA method of speeding this up? Would it be faster to call WorksheeetFunction in VBA for webservice than it is to act on it within Excel?

I am also open to some other forms of retrieving data. I have a fair amount of familiarity with VBA (enough to get by and use google when I can't) and a mild amount of awareness of web scraping.

Any ideas? Even cutting the time by 25% would be reasonable, just to reduce strain on computers with less CPU power.

Thanks!

 

[John_w]

How many =WEBSERVICE() formulas are there? 8 x 2000 = 16,000? It would be faster to call WorksheetFunction.WebService in VBA, but only if you can reduce the number of WebService calls, for example if a single WebService call can return the data for all 8 fields (columns) for a stock, maybe with a bit of parsing to extract each field.

Another approach is to retrieve the data using XMLhttpRequest, again retrieving multiple fields with a single request if possible. Even faster, and building on XMLhttpRequest, you can write code which creates multiple threads (processes) each of which retrieve the data for one or multiple URLs and write the data to the same master workbook. This technique is described at http://www.excelhero.com/blog/2010/05/multi-threaded-vba.html.