Getting Data from a 3rd Party API with VBA
The other day I had to use VBA in order to retrive data from the stockwatch.com API. StockWatch offers a fairly extensive stock API with a daily changing API key. StockWatch allows up to 100 stocks to be queried via a single query string in each call.
getStockDataString() takes: an array of strings which represent each stock symbol, the market or “region” where the symbols are located, and the daily API/authorization key (provided to you by StockWatch).
I wrote the subroutine (function) in Excel 2010 and the HTTP
GET request uses the
MSXML2 library and the
XMLHTTP method. I don’t know if this library is available in later versions of Excel, but I’m sure the process for making HTTP requests would be similar.
The StockWatch API returns each stock on each line of the response and each requested field separated by a comma. For example a response may look like:
17.3,1462455,1738250300,19691231 1.66,26608794,5872261500,20180810 0.48,555772,22531200,20180913
So we will need to parse each line of the response and for each line, print each value (seperated by a comma) onto the current active sheet in Excel, starting at the top left.
Sub getStockDataString(stockSymbols() As Variant, region As String, authCode As String) ' Construct our string of stock symbols (that we will pass to the URL query string) Dim stockSymbolString As String Dim index As Integer index = 0 Dim arrLength As Integer arrLength = UBound(stockSymbols) + 1 For Each symbol In stockSymbols stockSymbolString = stockSymbolString & region & ":" & symbol If index + 1 < arrLength Then stockSymbolString = stockSymbolString & "," End If index = index + 1 Next symbol ' Field values that we will loop over later Dim requestedFieldsStr As String ' The requested StockWatch fields. See the StockWatch documentation. requestedFieldsStr = "CVWx" ' Add each requested field to an array (`requestedFieldsArr`) Dim requestedFieldsArr() As String ReDim requestedFieldsArr(Len(requestedFieldsStr) - 1) For tmp = 1 To Len(requestedFieldsStr) requestedFieldsArr(tmp - 1) = Mid$(requestedFieldsStr, tmp, 1) Next ' Construct our URL Dim Url As String Url = "http://www.stockwatch.com/Quote/WebQuery.aspx?what=quote&format=comma&fields=" & requestedFieldsStr & "&header=N&symbols=" & stockSymbolString & "®ion=" & region & "&auth=" & authCode ' Variables for our request Dim oXMLHTTP As Object Dim i As Long Dim vFF As Long Dim oResp() As Byte Dim bodyResponse As String Dim pos As Integer Set oXMLHTTP = CreateObject("MSXML2.XMLHTTP") ' Open socket to GET the URL oXMLHTTP.Open "GET", Url, False ' Send request oXMLHTTP.Send ' Check if server returned an OK code If oXMLHTTP.Status = 200 Then ' Wait for the response `readyState`. To be honest with you, ' I am uncertain why 4 is a magic number here. Do While oXMLHTTP.readyState <> 4 DoEvents Loop ' The response will originally be returned as a byte array oResp = oXMLHTTP.responseBody ' Convert the returned byte array to a string bodyResponse = StrConv(oResp, vbUnicode) ' Check if our auth code is correct pos = InStr(bodyResponse, "Invalid auth parameter") If pos > 0 Then MsgBox "The auth code supplied is incorrect." Exit Sub End If ' Valid data. Let's parse it. Dim lines() As String ' `vbCrLf` represents a line feed in VBA lines = Split(bodyResponse, vbCrLf) ' Loop over each line in the returned string Dim lineIndex As Integer lineIndex = 1 For Each l In lines ' TODO: Handle each line in the response 'Debug.Print l ' Loop over each data point in the line Dim lineData() As String lineData = Split(l, ",") Dim dataPointIndex As Integer dataPointIndex = 0 For Each dataPoint In lineData ' TODO: Handle each data point in a line 'Debug.Print "Field " & requestedFieldsArr(dataPointIndex) & " Value: " & dataPoint ' Where the magic happens. Print the data to the current sheet. ActiveSheet.Cells(lineIndex, dataPointIndex + 1).Value = dataPoint ' Increment our pointer so that we know where to write the next datapoint on the sheet dataPointIndex = dataPointIndex + 1 Next dataPoint ' Increment the row lineIndex = lineIndex + 1 Next l Else ' TODO: Handle the case when the web server does not return an OK code MsgBox "Cannot get stock data from the web server. The server may be offline or unable to process this request." Exit Sub End If 'Debug.Print "Request sent to: " & Url 'Debug.Print bodyResponse ' Garbage collection Set oXMLHTTP = Nothing End Sub ' Example of how to call `getStockDataString()` Sub exampleCaller() ' Arrays in VBA should be declared as Variants first and then populated: https://stackoverflow.com/a/26492994/1171790 ' Why this is so? I have not a clue.. Dim stockSymbols() As Variant ' Make array <= 100 items long in production stockSymbols = Array("GOOGL", "AAPL", "BCE", "SYMC", "ADM") Dim region As String region = "U" Dim authCode As String authCode = "99969581,999155,999718,user123" ' Example call to getStockDataString() ' Pass an array of 100 symbols in production Call getStockDataString(stockSymbols, region, authCode) End Sub
Hopefully someone out there finds this useful. To be honest, I was really surprised at the number of Microsoft libraries available in VBA. There are at least two fairly large ones for making HTTP requests. If you need any help with the code, feel free to reach out. I would also be interested knowing if the
MSXML2 library/object works in later versions of Excel.