Using MSXML2.XMLHTTP in Excel VBA to extract large amounts of text data from website

Question

I am trying to download historical stock price data from finance.yahoo.com for 1000s of stocks. The website only displays 60 days of data on a single page so I have to loop through the time period that I am downloading for along with the loop for different securities. I have used the following code to access the relevant page.

Set httpObject = CreateObject("MSXML2.XMLHTTP")
httpObject.Open "GET", url, False
httpObject.send
webObject.body.innerHTML = httpObject.responseText

This works like a charm for 99% of the data that I access. But every now and then, the object returned by the website turns out to be empty, even though the exact same URL will show me the correct data in Internet Explorer. If I rerun the code for just that URL, it may or may not fail the next time.

I would like to know if there is a more stable/consistent way of using the above method. I had previously implemented the InternetExplorer.Application method to download data but found that to be much slower and cumbersome.

Answer 1

Check the readystate.

httpObject.ReadyState = 4

that means it's done loading. Then you can check the status

httpObject.Status = 200

that means you didn't mess up the URL. If readystate is 4 and status is 200, then you can read the responseText.

Answer 2

I had the same issue, getting an empty repsonse, with .Status = 0 (i.e. Ok). My issue it turned out was that the http when you go in the browser redirects to https. When I changed it in my request it was fine.

For a generic function, you could try http and if you get .Status = 0 and .responseText = "", then repeat but with https.