Using MSXML2.XMLHTTP in Excel VBA to extract large amounts of text data from website
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
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.