Using Excel to create processes or reports is advantageous because basic Excel skills are nearly universal in the office, and almost any sequence of steps done in Excel can be automated using VBA. Excel power users have traditionally used VBA to reduce repetitive and routine tasks, eliminate errors, and save time, but the Excel end user does not necessarily need to know the intricacies of a macro to be able to use it. What if we leveraged the power of RMS Intelligent Risk Platform's REST APIs in the simplicity of an Excel application?
This tutorial shows how to build macros to set off Risk Modeler portfolio analyses and check the analysis status directly from Excel. It is meant to be an example of how to call REST APIs from VBA to get you started, and does not include exhaustive functionality, validation, or error handling.
Prerequisite
This tutorial uses ActiveX controls to create a user form and the Microsoft MSXML2.XMLHTTP library to make HTTP requests. For both to work, this tutorial requires:
Microsoft Excel 2010 or more recent on a Windows machine.
Sample Risk Modeler API Requests
This tutorial uses two Risk Modeler APIs, one to set off an analysis and one to check the status of analyses. It’s helpful to be familiar with the API requests and responses before trying to call them from VBA.
The POST /portfolios/{portfolioId}/process resource enables you to run a portfolio analysis on a specific portfolio ID and returns the workflow ID in the Location header.
Let’s now build a form in Excel that uses these two APIs to set off analyses and check the status of those analyses.
Add a Command Button to Run Analysis
The first command button you’ll want to add is Run Analysis, which should run an analysis and then populate the spreadsheet columns A:E with the analysis parameters.
The command button itself is only used to display the Run Analysis user form.
Private Sub CommandButton1_Click()
UserForm1.Show
End Sub
Configure a User Form and Submit Button
When a user clicks the Run Analysis command button, it should initialize a user form that takes in the required analysis parameters that will be needed for the API to run an analysis. The user wants to define the EDM name, portfolio ID, job name and which model to run on the portfolio. The Model combo box is configured with a list of model options. The additional payload fields are abstracted out of the user form.
Once the variables are input by a user, the user should be able to click a Submit button to submit the analysis, store the submission details in the Excel sheet, and store the workflow ID for the submission in the table.
Copy to
Private Sub CommandButton1_Click()
Dim r As Long
Dim workflowID As String
With Worksheets("Sheet1")
workflowID = RM_Run_Analysis(Me.TextBox1, Me.TextBox2, Me.TextBox3, Me.ComboBox1)
If workflowID <> "" Then
r = .Range("A" & .Rows.Count).End(xlUp).Row + 1
.Range("A" & r) = Me.TextBox1
.Range("B" & r) = Me.TextBox2
.Range("C" & r) = Me.TextBox3
.Range("D" & r) = Me.ComboBox1
.Range("E" & r) = workflowID
End If
End With
End Sub
Private Sub CommandButton1_Click()
Dim r As Long
Dim workflowID As String
With Worksheets("Sheet1")
workflowID = RM_Run_Analysis(Me.TextBox1, Me.TextBox2, Me.TextBox3, Me.ComboBox1)
If workflowID <> "" Then
r = .Range("A" & .Rows.Count).End(xlUp).Row + 1
.Range("A" & r) = Me.TextBox1
.Range("B" & r) = Me.TextBox2
.Range("C" & r) = Me.TextBox3
.Range("D" & r) = Me.ComboBox1
.Range("E" & r) = workflowID
End If
End With
End Sub
Copy to
Make a REST API Call with VBA to Run an Analysis
The call to the Risk Modeler REST API to run an analysis and return the workflowID happens after the user clicks the Submit button in the line above:
The Me. variables passed in are coming from the user entries into the user form, which are used to call the RM_Run_Analysis function.
RM_Run_Analysis handles the following steps:
Map user form input model parameter to associated model profile IDs.
Structures the API request and payload.
Makes the asynchronous Risk Modeler API call to process the portfolio analysis.
Parses the workflow ID from the Location response header and returns as the response.
Returns an error message in a message box if the API call does not successfully start a workflow.
The full VBA function to run an analysis is below, where env_url is the environment URL for your tenant and api_key is an API key with permission to make API calls against the tenant. The model profiles and model profile IDs are examples and would also need to be updated to reflect the specific Risk Modeler tenant.
Copy to
Option Explicit
Public Function RM_Run_Analysis(edmName As String, portfolioId As String, jobName As String, model As String) As String
Dim objRequest As Object
Dim strUrl As String
Dim blnAsync As Boolean
Dim strResponse As String
Dim modelProfileId As String
Dim Body As String
Dim locationHeader
Dim workflowID As String
Dim status As String
'Map user form input model parameter to associated model profile IDs
If model = "Earthquake" Then
modelProfileId = 5
End If
If model = "Flood" Then
modelProfileId = 84
End If
If model = "Wildfire" Then
modelProfileId = 219
End If
'Make asynchronous API call
blnAsync = True
With objRequest
.Open "POST", strUrl, blnAsync
.SetRequestHeader "Content-Type", "application/json"
.SetRequestHeader "Authorization", {api_key}
.Send Body
'spin wheels whilst waiting for response
While objRequest.readyState <> 4
DoEvents
Wend
strResponse = .ResponseText
locationHeader = .getResponseHeader("Location")
status = .status
End With
'Parse workflow ID from Location response header and return
If status = 202 Then
workflowID = Right(locationHeader, InStr(1, locationHeader, "/"))
RM_Run_Analysis = workflowID
'Return error message if workflow is not started successfully
Else
MsgBox objRequest.ResponseText
End If
End Function
Option Explicit
Public Function RM_Run_Analysis(edmName As String, portfolioId As String, jobName As String, model As String) As String
Dim objRequest As Object
Dim strUrl As String
Dim blnAsync As Boolean
Dim strResponse As String
Dim modelProfileId As String
Dim Body As String
Dim locationHeader
Dim workflowID As String
Dim status As String
'Map user form input model parameter to associated model profile IDs
If model = "Earthquake" Then
modelProfileId = 5
End If
If model = "Flood" Then
modelProfileId = 84
End If
If model = "Wildfire" Then
modelProfileId = 219
End If
'Make asynchronous API call
blnAsync = True
With objRequest
.Open "POST", strUrl, blnAsync
.SetRequestHeader "Content-Type", "application/json"
.SetRequestHeader "Authorization", {api_key}
.Send Body
'spin wheels whilst waiting for response
While objRequest.readyState <> 4
DoEvents
Wend
strResponse = .ResponseText
locationHeader = .getResponseHeader("Location")
status = .status
End With
'Parse workflow ID from Location response header and return
If status = 202 Then
workflowID = Right(locationHeader, InStr(1, locationHeader, "/"))
RM_Run_Analysis = workflowID
'Return error message if workflow is not started successfully
Else
MsgBox objRequest.ResponseText
End If
End Function
Copy to
Run an Analysis From the Excel Macro
The macro configured above can now be used to start an analysis. When the analysis is successfully set off, the Excel sheet is filled with the user inputs and the resulting workflow ID.
If there is an error in the API call, a message box is shown so that the user can correct their input to the form.
Add a Command Button to Update Workflow Status
Next, the user wants an Update Status command button, which will populate the Status column in the Excel sheet. When Update Status is clicked, it returns the current status for all workflows listed directly in column F.
Instead of bringing up a user form, the Update Status command button directly calls the function to loop through the workflow IDs in the Excel sheet and get the status for a workflow ID from Risk Modeler for each.
Make a REST API Call With VBA to Get Workflow Status
The call to the Risk Modeler REST API to get the workflow status happens directly in the user click of the Update Status button in the line above:
status = RM_Get_Status(Cells(i, "E").Value)
The Cells(i, "E").Value is used to pass in an individual Workflow ID into the RM_Get_Status function to iterate through the API calls for all values in the Workflow ID column.
RM_Get_Status handles the following steps:
Structures the API request.
Makes the Risk Modeler API call to get the workflow details.
Parses the workflow status from the API response JSON. For simplicity, this tutorial uses the open source VBA JSON converter available here: https://github.com/VBA-tools/VBA-JSON
The full VBA function to return the status for a workflow ID is below, where env_url is the environment URL for your tenant and api_key is an API key with permission to make API calls against the tenant.
Copy to
Option Explicit
Public Function RM_Get_Status(workflowID As String) As String
Dim objRequest As Object
Dim strUrl As String
Dim Json As Object
'Structure API request
Set objRequest = CreateObject("MSXML2.XMLHTTP")
strUrl = "https://{env_url}/riskmodeler/v1/workflows/" & workflowID
'Make API call
With objRequest
.Open "GET", strUrl, False
.SetRequestHeader "Authorization", {api_key}
.Send
End With
'Get status from JSON response
'Uses the VBA JSON converter: https://github.com/VBA-tools/VBA-JSON
Set Json = JsonConverter.ParseJson(objRequest.ResponseText)
RM_Get_Status = Json("status")
End Function
Option Explicit
Public Function RM_Get_Status(workflowID As String) As String
Dim objRequest As Object
Dim strUrl As String
Dim Json As Object
'Structure API request
Set objRequest = CreateObject("MSXML2.XMLHTTP")
strUrl = "https://{env_url}/riskmodeler/v1/workflows/" & workflowID
'Make API call
With objRequest
.Open "GET", strUrl, False
.SetRequestHeader "Authorization", {api_key}
.Send
End With
'Get status from JSON response
'Uses the VBA JSON converter: https://github.com/VBA-tools/VBA-JSON
Set Json = JsonConverter.ParseJson(objRequest.ResponseText)
RM_Get_Status = Json("status")
End Function
Copy to
Update Status from the Excel Macro
The macro configured above can now be used to update the Status column in the spreadsheet.
When the user clicks Update Status, the Status column is populated for each workflow ID in column E. When a workflow ID is populated with an existing status, rerunning the macro will get the most recent status from the Risk Modeler API back to Excel.
This tutorial shows Run Analysis and Update Status as two illustrative ways APIs can be called from VBA, but there are dozens of other powerful use cases for connecting directly to RMS Intelligent Risk Platform via REST API. APIs can be used to bring data from Excel into RMS Intelligent Risk Platform, to set off processes, or to return data or results from RMS Intelligent Risk Platform.
Share
Alexandria Julius
Technical Consultant
Alexandria Julius is a technical consultant on RMS’s Consulting team. As a technical consultant, she works on custom solution implementations to integrate clients with RMS products. She is responsible for workflow design, custom software development, testing, and reporting. She holds a BS in Engineering Science from Smith College and a MS in Geodetic Engineering and Remote Sensing from The Ohio State University. Alexandria is based in the RMS Hoboken, New Jersey office.
Questions or Feedback on Current Blog Posts? Suggestions for Upcoming Blog Posts?