Technical Blogs

Use Your Excel Macro Skills to Launch Risk Modeler Analyses Directly from Excel

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. 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} Copy to The GET /workflows/{workflowId} resource allows you to retrieve workflow details, including the current status of the analysis job. 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}, ... ... } 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. 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: 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. 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 '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 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.