This post shows a R counterpart of Excel VBA’s MSXML2.ServerXMLHTTP related commands with which server APIs are called easily. In case of R, it is done by using httr R package. As an illustration, SQL query for retrieving swap date schedule is executed by calling this server API.

Calling Server API in a simple manner

This post is simple and provides a Excel VBA code and the corresponding R code for calling SQL query indirectly by using web server API. Sample SQL returns some swap schedule as an example. In the two codes below, the server URL address is not a valid address since it is just for illustration purpose. You can just copy and paste the necessary part and modify it for your purpose.

Excel VBA : MSXML2.ServerXMLHTTP

The following Excel VBA code uses MSXML2.ServerXMLHTTP object to do the above job. Of course, specific parts depend on in-house system.

Sub Run_SQL_Btn_Click()
 
    Dim response As String
    Dim result As Variant
    Dim X As Variant
    Dim url As String
    Dim strQry As String
 
    ' SQL query
    strQry = " SELECT ID," & _
             "    to_char(RESET_DATE,  'yyyy-mm-dd')," & _
             "    to_char(START_DATE,  'yyyy-mm-dd')," & _
             "    to_char(END_DATE,    'yyyy-mm-dd')," & _
             "    to_char(PAYMENT_DATE,'yyyy-mm-dd') " & _
             " FROM  CASH_FLOW_TABLE" & _
             " WHERE PRODUCT_ID = '3911737' and ID = '2'" & _
             " ORDER BY PAYMENT_DATE, RESET_DATE"
    
 
    ' create object which is connected to server
    Dim objHttp
    Set objHttp = CreateObject("MSXML2.ServerXMLHTTP")
    
    ' This is for the illustration purpose and not valid
    url = "http://123.123.123.123:9080/importserver/InquiryOracle"
    
    objHttp.Open "POST", url, False
    objHttp.setRequestHeader "User-Agent", _
      "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0)"
    objHttp.setRequestHeader "Content-Type", _
      "application/x-www-form-urlencoded;charset=UTF-8"
    objHttp.send ("q=" & strQry)
    objHttp.waitForResponse 180000000
    response = objHttp.responseText
    
    ' Convert Result
    X = Split(response, "|")
    ReDim result(UBound(X))
    
    For i = 0 To UBound(X)
        result(i) = Split(X(i), ",")
    Next i
    
    ' Show Result
    i = 0
    For Each Data In result
        Cells(3 + i, 2) = i + 1
        For j = LBound(Data) To UBound(Data)
            Cells(3 + i, 3 + j) = Data(j)
        Next j
        i = i + 1
    Next Data
 
End Sub

Running the above Excel macro returns the following output.

R code with httr package

The following R code uses httr R package to do the above same job.

#========================================================#
# Quantitative ALM, Financial Econometrics & Derivatives 
# ML/DL using R, Python, Tensorflow by Sang-Heon Lee 
#
# https://shleeai.blogspot.com
#--------------------------------------------------------#
# Excel VBA's MSXML2.ServerXMLHTTP in R
#========================================================#
 
library(httr)
 
# SQL query to be executed
sql <- paste(
    "SELECT ID,",
    "      to_char(RESET_DATE,  'yyyy-mm-dd'),",
    "      to_char(START_DATE,  'yyyy-mm-dd'),",
    "      to_char(END_DATE,    'yyyy-mm-dd'),",
    "      to_char(PAYMENT_DATE,'yyyy-mm-dd')",
    "FROM  CASH_FLOW_TABLE",
    "WHERE PRODUCT_ID = '3911737' and ID = '2'",
    "ORDER BY PAYMENT_DATE, RESET_DATE");
 
# web server address which provides service APIs 
# But This url is for the illustration purpose and not valid
url <- "http://123.123.123.123:9080/importserver/InquiryOracle"
 
# create object which is connected to server and send it
httpResponse = POST(
    url, body = list(q=sql),
    user_agent(
      "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0)"),
    add_headers(
      'Content-Type'=
      "application/x-www-form-urlencoded;charset=UTF-8",
      'User-Agent'=
      "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0)"),
    encode = 'form')
 
# convert output as text
str.out <- content(httpResponse, as ="text")
 
# reshape output as data.frame
read.table(text=gsub("[|]", "\n", str.out), sep=",",
           col.names = c('ID','RESET_DATE', 'START_DATE', 
                         'END_DATE', 'PAYMENT_DATE'))

In the above R code, it is worth noting that a SQL query string is inserted as a member of list. Running this R code returns the following same output.

>   ID  RESET_DATE   START_DATE    END_DATE   PAYMENT_DATE
1   2   2021-10-15   2021-10-20   2021-11-10   2021-11-10
2   2   2021-10-15   2021-11-10   2021-12-10   2021-12-10
3   2   2021-10-15   2021-12-10   2022-01-10   2022-01-10
4   2   2021-10-15   2022-01-10   2022-02-10   2022-02-10
5   2   2021-10-15   2022-02-10   2022-03-10   2022-03-10
6   2   2021-10-15   2022-03-10   2022-04-10   2022-04-11
7   2   2021-10-15   2022-04-10   2022-04-16   2022-05-10
8   2   2022-04-15   2022-04-16   2022-05-10   2022-05-10
9   2   2022-04-15   2022-05-10   2022-06-10   2022-06-10
10  2   2022-04-15   2022-06-10   2022-07-10   2022-07-11
11  2   2022-04-15   2022-07-10   2022-08-10   2022-08-10
12  2   2022-04-15   2022-08-10   2022-09-10   2022-09-13
13  2   2022-04-15   2022-09-10   2022-10-10   2022-10-11
14  2   2022-04-15   2022-10-10   2022-10-16   2022-11-10
15  2   2022-09-15   2022-10-16   2022-10-20   2022-11-10

Concluding Remarks

As we call server API function using MSXML2.ServerXMLHTTP in Excel VBA, we can also do the same job by using httr package

Originally posted on SHLee AI Financial Model blog.