Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
September 30 2018 - Get Data From an API with VBA

Getting Data from an API with VBA

The other day I had to use VBA in order to retrive data from the stockwatch.com. StockWatch offers a fairly extensive stock API with a daily changing API key. StockWatch allows up to 100 stocks to be queried via a 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 function/"sub" 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 seperated by a comma. For example:

17.3,1462455,1738250300,19691231
1.66,26608794,5872261500,20180810
0.48,555772,22531200,20180913

So we will 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 charater/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 & "&region=" & 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`. It should not return a 4 - This apparently means our response has come completely back from the server (data returned).
        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! Now let's parse it!
        Dim lines() As String
        ' `vbCrLf` represents a carriage return 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 -- maybe pass these values off to another Sub/Function
                '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

' This sub exists soley as an 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. 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.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.