logo image

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.

Close
Expand

Copy to

      
        curl --location --request POST 'https://{env_url}/riskmodeler/v2/portfolios/{portfolioId}/process' \
--header 'Content-Type: application/json' \
--header 'Authorization: <<API Key>> \
--data-raw '{
"id": {portfolioId},
"edm": {edm},
"exposureType": "PORTFOLIO",
"currency": {
"code": "USD",
"scheme": "RMS",
"vintage": "RL18",
"asOfDate": "2020-03-01"
},
"modelProfileId": {modelProfileId},
"eventRateSchemeId": 168,
"treaties": [],
"outputProfileId": 1,
"jobName": {jobName}
}'
202 Accepted
Location: https://{env_url}/riskmodeler/v1/workflows/{workflowId}
curl --location --request POST 'https://{env_url}/riskmodeler/v2/portfolios/{portfolioId}/process' \
--header 'Content-Type: application/json' \
--header 'Authorization: <<API Key>> \
--data-raw '{
"id": {portfolioId},
"edm": {edm},
"exposureType": "PORTFOLIO",
"currency": {
"code": "USD",
"scheme": "RMS",
"vintage": "RL18",
"asOfDate": "2020-03-01"
},
"modelProfileId": {modelProfileId},
"eventRateSchemeId": 168,
"treaties": [],
"outputProfileId": 1,
"jobName": {jobName}
}'
202 Accepted
Location: https://{env_url}/riskmodeler/v1/workflows/{workflowId}
Close
Expand

Copy to

The GET /workflows/{workflowId} resource allows you to retrieve workflow details, including the current status of the analysis job.

Close
Expand

Copy to

      
        curl --location --request GET 'https://{env_url}/riskmodeler/v1/workflows/{workflowId}' \
--header 'Authorization: {API Key}'
200
{
"id": {workflowId},
"userName": "user.name@rms.com",
"status": "FAILED",
"submitTime": "2021-05-13T17:34:26.179Z",
"startTime": "2021-05-13T17:34:28Z",
"endTime": "2021-05-13T17:35:02Z",
"name": {Name},
...
...
}
curl --location --request GET 'https://{env_url}/riskmodeler/v1/workflows/{workflowId}' \
--header 'Authorization: {API Key}'
200
{
"id": {workflowId},
"userName": "user.name@rms.com",
"status": "FAILED",
"submitTime": "2021-05-13T17:34:26.179Z",
"startTime": "2021-05-13T17:34:28Z",
"endTime": "2021-05-13T17:35:02Z",
"name": {Name},
...
...
}
Close
Expand

Copy to

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.

excel

The command button itself is only used to display the Run Analysis user form.

comand-btn

 

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.

submit-btn

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.

general

Close
Expand

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
Close
Expand

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:

workflowID = RM_Run_Analysis(Me.TextBox1, Me.TextBox2, Me.TextBox3, Me.ComboBox1)

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.

module 1

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.

Close
Expand

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

'Structure API request and payload
Set objRequest = CreateObject("MSXML2.XMLHTTP")
strUrl = "https://{env_url}/riskmodeler/v2/portfolios/" & portfolioId & "/process"
Body = "{""id"":" & portfolioId & ",""edm"":""" & edmName & """,""exposureType"":""PORTFOLIO"", " & _
"""currency"":{""code"":""USD"",""scheme"":""RMS"",""vintage"":""RL18"",""asOfDate"":""2020-03-01""}," & _
"""modelProfileId"":" & modelProfileId & ",""eventRateSchemeId"":174,""treaties"":[],""outputProfileId"":1,""jobName"":""" & jobName & """}"

'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

'Structure API request and payload
Set objRequest = CreateObject("MSXML2.XMLHTTP")
strUrl = "https://{env_url}/riskmodeler/v2/portfolios/" & portfolioId & "/process"
Body = "{""id"":" & portfolioId & ",""edm"":""" & edmName & """,""exposureType"":""PORTFOLIO"", " & _
"""currency"":{""code"":""USD"",""scheme"":""RMS"",""vintage"":""RL18"",""asOfDate"":""2020-03-01""}," & _
"""modelProfileId"":" & modelProfileId & ",""eventRateSchemeId"":174,""treaties"":[],""outputProfileId"":1,""jobName"":""" & jobName & """}"

'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
Close
Expand

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.

run-analysis

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.

database-error

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.

update-status

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.

command-btn

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.

module2

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.

Close
Expand

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
Close
Expand

Copy to

Update Status from the Excel Macro

The macro configured above can now be used to update the Status column in the spreadsheet.

sample-edm

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.

sample-edm2

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.

Alexandria Julius
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.

Tech Banner

Questions or Feedback on Current Blog Posts? Suggestions for Upcoming Blog Posts?

close button
Overlay Image
Video Title

Thank You

You’ll be contacted by an Moody's RMS specialist shortly.