Multi-session / asynchronous web request with VBA and XMLHttpRequest object – Part 1

Standard

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”.

Screen Shot 2016-09-27 at 8.16.07 PM.png

2. Add a reference to Microsolt XML 6.0.

Screen Shot 2016-09-27 at 8.19.23 PM.png

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!

Screen Shot 2016-09-27 at 8.32.58 PM.png

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.

Screen Shot 2016-09-27 at 8.38.08 PM.png

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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s