Excel WinHttpRequest takes too long.... suggestions please?

[markw996]

Hi,

I have a workbook set up to retreive information from several websites using VBAs WinHttp.WinnHttpRequest5.1 and then to compare it with the Betfair data provided by BA. Then the whole lot is calculated using formulas placed in the cells of the spreadsheet. I'm getting my web data in the same way as Gary's http://www.gruss-software.co.uk/Excel/Racing_Post_Tissue.xls example.

Currently everytime BA refreshes the data in Excel my code runs and refreshes my data from the web.

My problem is that my code takes approx 10 secs to visit the websites, gather the source code, retreive the information which provides URLS to the relevant webpages, then to visit these webpages and retreive the important data, and finally to populate several worksheets (each containing up to 400 cells) so that the formulas can act on the information.

The result is that I must set BA to refresh once every 30 secs or so to stop my workbook from freezing as all the time this is happening I am unable to use Excel at all

The only solution I can see is to try to build an application in VB.NET that scrapes the websites and logs the data to Excel in realtime, similar to the way BA does it

Before I embark on my slow and painful course of action, can anyone suggest another way to resolve my issues

Thanks,
Mark.

[Ian]

What type of data are you gathering from the other websites ? Does it need to be refreshed on every BA refresh ? If so, could you split the coding up so that you could refesh BA, say, every 5 secs and cycle around the other refeshes ie. do one website on the first refesh, another on the second etc. and eventually back to the first website.

[markw996]

Hi Ian, that's a good idea and one I hadn't thought of. I'm basically scraping an odds comparison site so I want the data to be as fresh as possible. I'll play around with the code and see if I can streamline it a bit and also implement your suggestion. I still don't think I'll be able to get it to work to my satisfaction so I've already started developing a scraper in VB.NET as that should free up Excel completely.

[Ian]

Hi Mark. Are you getting the "calculating 100%" error ? I've got a spreadsheet that keeps getting that error when I'm doing a web query to Bestbetting using VBA. I only do the web query once when the market changes, and it will often work for a few markets, and then crash. Never have gone to the bottom of it.

Btw I think there is a market for an bookie odds to excel for horse racing, football etc. I actually suggested it to Bestbetting some years ago.

[GaryRussell]

Have you considered making the WinHttp.WinnHttpRequest5.1 request asynchronous? ie. you should be able to make the calls run in the background and call back your functions to process the data only when the data is available therefore waiting for a response will not block Excel.

[markw996]

Hi Gary, your talking to someone who can barely spell asynchronous let alone implement it in their code

Can you please briefly explain the concept using the code from your tissue price example?

[GaryRussell]

Sorry for the jargon, I just love the word asynchronous

I'll try to put an example together.

[GaryRussell]

I have uploaded the tissue price example using asynchronous requests.
Download it here http://www.gruss-software.co.uk/Excel/Racing_Post_Tissue_Async.xls

I have moved the code to the worksheet module so that the data received events can be captured (events cannot be captured in normal modules, they require class modules and the worksheet module is a class module). The data capture is kicked off as soon as the workbook opens.

If you look at requestTissueURLS for example you will see that it requests the web page, but there is no code to process the data after it is received. This is because web_OnResponseFinished() is called as soon as the data is available. This means no code is running while you are waiting for a response, it could take several seconds, but Excel will not freeze.

To enable capturing the OnResponseFinished event you need to add a reference to Microsoft WinHTTP Services. Select Tools|References from the menu to see this. If it says 'reference missing' you will need to add the reference yourself as it will not work without this.

[markw996]

Ok now I'm starting to get somewhere! But I have another problem

I've modified my VBA code so that the WinHttpRequest5.1 runs asynchronously but it appears my destination sourcecode cannot
be read and hence converted into a string due to a problem possibly with the charset

The error message I'm receiving in response to the request is "No Mapping for the Unicode character exists in the target multi-byte code page"

however the HTML page does contain:

Code: Select all

<meta xmlns="" http-equiv="content-type" content="text/html; charset=UTF-8" />

which I assume should have been ok as my WinHttpRequest5.1 specifies

Code: Select all

    web.SetRequestHeader "REFERER", strUrl
    web.SetRequestHeader "User-Agent", "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1)"
    web.SetRequestHeader "Accept", "text/xml,application/xml,application/xhtml+xml,text/html;q=0.9,text/plain;q=0.8,image/png,*/*;q=0.5"
    web.SetRequestHeader "Accept-Language", "en-us,en;q=0.5"
    web.SetRequestHeader "Accept-Charset", "ISO-8859-1,utf-8;q=0.7,*;q=0.7"

Can Gary or anyone else shed any light on this as it would be a disaster if I had to adapt all my code to work with another URL

[markw996]

Scratch that, it's working after a reboot