Degree Days

Degree Days

Weather Data for Energy Saving

Access the Degree Days.net API using VBA

VBA (Visual Basic for Applications) has long been used for scripting Microsoft Office applications like Excel. This page has some sample code showing how to use VBA to specify a JSON request, send it to our API servers (past the security system), and process the JSON response that comes back.

This sample will currently only work on Windows. It could potentially be modified to run on Mac as well, but it might be better to start with our Office Add-in sample code instead (programmed in JavaScript), as that will automatically work on Office for Windows, Office for Mac, and Office running in a web browser.

To use this VBA sample code, first you will need to install .NET 3.5 by following these instructions from Microsoft. Unfortunately, even though later versions of .NET come pre-installed on modern Windows, VBA can only use version 3.5 or below, so you'll have to install it. If this will make deploying your code a pain, you might want to consider using our Office Add-in sample code instead of VBA.

Next, open the VBA Editor (from Excel on Windows just press Alt+F11) and:

This is not a full client library (like we have for Java, .NET, and Python), but it should be pretty easy to adapt it to suit your needs.

See the JSON API docs for more about specifying the data you want in the JSON request. There are lots of options, and the request included in the VBA code below is just a simple example. The JSON docs also explain more about the data you can expect back in the response.

You might also find the JSON API test tool useful for testing different JSON requests and seeing the JSON responses that come back.

' This code needs Windows with .NET 3.5 installed on it, and VBA with the
' VBA-JSON library and a reference to "Microsoft Scripting Runtime".  Please
' see www.degreedays.net/api/vba for instructions.

Option Explicit

' The test API access keys are described at www.degreedays.net/api/test
' They will let you access data from the Cape Cod area only.
' To fetch data from locations worldwide, sign up for a proper API account at
' www.degreedays.net/api/ and copy your API access keys here.
Private Const accountKey As String = "test-test-test"
Private Const securityKey As String = _
    "test-test-test-test-test-test-test-test-test-test-test-test-test"

' You can call the API over HTTP using http://apiv1.degreedays.net/json or
' over HTTPS using https://apiv1.degreedays.net/json - set the endpoint URL
' below as appropriate.
Private Const endpoint As String = "http://apiv1.degreedays.net/json"



' Run this Sub to test this example code.
Sub RunDegreeDaysExample()
    ' STEP 1:
    Dim requestJson As String
    requestJson = CreateRequestJson()
    ' STEP 2:
    Dim fullResponse As Dictionary
    Set fullResponse = SendRequestToApi(requestJson)
    ' STEP 3:
    ProcessResponse fullResponse
End Sub



' ************* STEP 1: Create the request **********************************
' First we create a JSON request that specifies what we want from the API.
' See www.degreedays.net/api/json#request for more on this.
Private Function CreateRequestJson() As String
    ' You can fetch data from a station ID, a longitude/latitude position, or a
    ' postal/zip code.
    Dim location As Dictionary
    Set location = New Dictionary
    With location
        .Add "type", "PostalCodeLocation"
        .Add "postalCode", "02532"
        .Add "countryCode", "US"
    End With
    ' In this example we fetch both HDD and CDD, using the same breakdown (daily
    ' data covering the last 7 days) for both. For more breakdown options see
    ' www.degreedays.net/api/json#breakdown
    Dim breakdown As Dictionary
    Set breakdown = New Dictionary
    With breakdown
        .Add "type", "DailyBreakdown"
        .Add "period", New Dictionary
        With .Item("period")
            .Add "type", "LatestValuesPeriod"
            .Add "numberOfValues", 7
        End With
    End With
    Dim locationDataRequest As Dictionary
    Set locationDataRequest = New Dictionary
    With locationDataRequest
        .Add "type", "LocationDataRequest"
        .Add "location", location
        .Add "dataSpecs", New Dictionary
        With .Item("dataSpecs")
            ' Here we specify 2 DataSpec items: one for HDD and one for CDD.
            ' You can specify up to 100 DataSpec items in one request (e.g. to
            ' fetch data in lots of base temperatures).  With an API Standard+
            ' account you can have a DataSpec for hourly temperature data too.
            ' Give each DataSpec a unique name so you can get the corresponding
            ' DataSet from the response.
            .Add "myHdd", New Dictionary
            With .Item("myHdd")
                .Add "type", "DatedDataSpec"
                .Add "calculation", New Dictionary
                With .Item("calculation")
                    .Add "type", "HeatingDegreeDaysCalculation"
                    .Add "baseTemperature", New Dictionary
                    With .Item("baseTemperature")
                        .Add "unit", "F"
                        .Add "value", 60
                    End With
                End With
                .Add "breakdown", breakdown
            End With
            .Add "myCdd", New Dictionary
            With .Item("myCdd")
                .Add "type", "DatedDataSpec"
                .Add "calculation", New Dictionary
                With .Item("calculation")
                    .Add "type", "CoolingDegreeDaysCalculation"
                    .Add "baseTemperature", New Dictionary
                    With .Item("baseTemperature")
                        .Add "unit", "F"
                        .Add "value", 70
                    End With
                End With
                .Add "breakdown", breakdown
            End With
        End With
    End With
    Dim fullRequest As Dictionary
    Set fullRequest = New Dictionary
    With fullRequest
        .Add "securityInfo", New Dictionary
        With .Item("securityInfo")
            .Add "endpoint", endpoint
            .Add "accountKey", accountKey
            Dim dt As Object, utcTimestamp As Date
            Set dt = CreateObject("WbemScripting.SWbemDateTime")
            dt.SetVarDate Now()
            utcTimestamp = dt.GetVarDate(False)
            .Add "timestamp", Format$(utcTimestamp, "yyyy-mm-ddTHH:mm:ssZ")
            .Add "random", CStr(Rnd())
        End With
        .Add "request", locationDataRequest
    End With
    Dim fullRequestJson As String
    ' JsonConverter is part of the VBA-JSON project, which you will have to
    ' install.  See www.degreedays.net/api/vba for more.
    fullRequestJson = JsonConverter.ConvertToJson(fullRequest)
    ' Now our JSON request is ready.  Uncomment the line below to see the JSON:
    'Debug.Print fullRequestJson
    CreateRequestJson = fullRequestJson
End Function



' ************* STEP 2: Send the request to the API *************************
' Next we sign the JSON request and package everything together into an HTTP
' request which we send to the Degree Days.net API.  This follows the spec at
' www.degreedays.net/api/json#send
Private Function SendRequestToApi(requestJson As String) As Dictionary
    ' NB the Dictionary type requires a reference to "Microsoft Scripting
    ' Runtime".  See www.degreedays.net/api/vba for instructions.
    Dim requestBytes() As Byte
    requestBytes = StrConv(requestJson, vbFromUnicode)
    Dim httpObj As Variant
    Set httpObj = CreateObject("MSXML2.XMLHTTP")
    With httpObj
        .Open "POST", endpoint, False
        .SetRequestHeader "Content-type", "application/x-www-form-urlencoded"
        .SetRequestHeader "Accept-Encoding", "gzip"
        .Send "request_encoding=base64url" & _
            "&signature_method=HmacSHA256" & _
            "&signature_encoding=base64url" & _
            "&encoded_request=" & Base64UrlEncode(requestBytes) & _
            "&encoded_signature=" & Base64UrlEncode( _
                   HmacSha256(requestBytes, StrConv(securityKey, vbFromUnicode)))
    End With
    ' Uncomment the line below to see the JSON response:
    'Debug.Print httpObj.responseText
    ' JsonConverter is part of the VBA-JSON project, which you will have to
    ' install.  See www.degreedays.net/api/vba for more.
    Set SendRequestToApi = JsonConverter.ParseJson(httpObj.responseText)
End Function

' The API requires the JSON request and the signature to be base64url encoded.
Private Function Base64UrlEncode(ByRef byteArray() As Byte) As String
    Dim xmlDocument As Variant
    Set xmlDocument = CreateObject("MSXML2.DOMDocument")
    Dim node As Variant
    Set node = xmlDocument.createElement("b64")
    node.dataType = "bin.base64"
    node.nodeTypedValue = byteArray
    Dim s As String
    s = node.text
    s = Replace(s, "+", "-")
    s = Replace(s, "/", "_")
    s = Replace(s, "=", "")
    Base64UrlEncode = s
End Function

' We need this function to generate the signature (for security).
Private Function HmacSha256(ByRef byteArray() As Byte, _
        ByRef secretByteArray() As Byte) As String
    Dim hmac As Variant
    ' If this gives you an "Automation Error", you'll need to install .NET 3.5
    ' as described at www.degreedays.net/api/vba
    Set hmac = CreateObject("System.Security.Cryptography.HMACSHA256")
    hmac.Key = secretByteArray
    HmacSha256 = hmac.ComputeHash_2(byteArray)
End Function



' ************* STEP 3: Process the response from the API *********************
' The JSON response is explained at www.degreedays.net/api/json#response
Private Sub ProcessResponse(fullResponse As Dictionary)
    Dim response As Dictionary
    Set response = fullResponse("response")
    If response("type") = "Failure" Then
        Debug.Print "Request Failure: " & response("code") & " - " & _
            response("message")
    Else
        Debug.Print "Station ID: " & response("stationId")
        Dim v As Dictionary
        Dim hddData As Dictionary
        Set hddData = response("dataSets")("myHdd")
        If hddData("type") = "Failure" Then
            Debug.Print "Failure for HDD DataSet: " & hddData("code") & _
                " - " & hddData("message")
        Else
            Debug.Print "HDD data:"
            For Each v In hddData("values")
                Debug.Print v("d") & ": " & v("v")
            Next v
        End If
        Dim cddData As Dictionary
        Set cddData = response("dataSets")("myCdd")
        If cddData("type") = "Failure" Then
            Debug.Print "Failure for CDD DataSet: " & cddData("code") & _
                " - " & cddData("message")
        Else
            Debug.Print "CDD data:"
            For Each v In cddData("values")
                Debug.Print v("d") & ": " & v("v")
            Next v
        End If
    End If
End Sub

Problems?

Hopefully the example code above, the JSON API docs, and the JSON API test tool will be enough to get you going, but please feel free to email us if you'd like more help.

Higher level integration details

It is also worth reading the higher-level integration guide for tips on the various approaches to integrating with the API. We have helped a lot of businesses integrate their software with our API so we are very familiar with the patterns that work well for common use cases.

Choose your Plan and Sign Up Today!

© 2008–2022 BizEE Software – About | Contact | Privacy | Free Website | API | Integration Guide | API FAQ | API Sign-Up