Degree Days
Weather Data for Energy Saving
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:
Tools (at the top), then References, and add a reference to Microsoft Scripting Runtime. Our sample code needs the Dictionary class that that runtime provides.VBA-JSON. This is essentially a VBA module to import into your project. Installation instructions are on GitHub.Insert and Module), then copy/paste in the sample code below.RunDegreeDaysExample near the top (click it and hit F5), then edit/experiment from there.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 for the Cape Cod area only.
' To fetch data for 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, as explained at www.degreedays.net/api/json#location
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 120 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
The sample code above will hopefully get you fetching degree days, but the JSON API docs also explain other options like fetching hourly temperature data and using the API for advanced regression.
You can quickly test out all sorts of JSON requests with the JSON API test tool, then write code like the example above for any that you want to use. The code in step 2 of the sample above will happily send any valid JSON request to the API and give you a response back that you can process.
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. And please feel free to email us if you'd like more help.
© 2008–2024 BizEE Software – About | Contact | Privacy | Free Website | API | Integration Guide | API FAQ | API Sign-Up