In Excel development, very often we need to import data from an online source, sometimes we will need to import from hundreds or thousands of online source. Good news is that with XMLHttpRequest object, you can achieve multi-session asynchronous Http call on VBA. Let see how it works:
1. Open VBA Editor -> Select “Tools” -> Select “References”.
2. Add a reference to Microsolt XML 6.0.
3. We can start to code.
Option Explicit Sub main() Dim httprequest As New MSXML2.XMLHTTP60 httprequest.Open "GET", "https://finance.yahoo.com/", False httprequest.send Debug.Print httprequest.responseText End Sub
Let’s take a look what we can get from this four lines of codes!
httprequest.responseText retrieves the response body as a string, in this case, the html code of the front page of https://finance.yahoo.com/. So what if we want to get some stock data from yahoo finance api? Easy! We can simply change “https://finance.yahoo.com/” to “http://ichart.finance.yahoo.com/table.csv?s=0001.hk” then we are good to go.
At this point, we can further develop to save the http respond to csv file. According to description, XMLHttpRequest.respondbody return unassigned byte, we can leverage Saving Data in binary format to save any file download from XMLHttprequest.
Option Explicit Sub main() Dim httprequest As New MSXML2.XMLHTTP60 Dim b() As Byte Dim filename As String httprequest.Open "GET", "http://ichart.finance.yahoo.com/table.csv?s=0001.hk", False httprequest.send b = httprequest.responseBody filename = "C:\TEMP\stock.csv" Debug.Print httprequest.responseBody Open filename For Binary As #1 Put #1, , b Close #1 End Sub