R : httr in R and MSXML2.ServerXMLHTTP in Excel VBA
[This article was first published on K & L Fintech Modeling, and kindly contributed to R-bloggers]. (You can report issue about the content on this page here)
Want to share your content on R-bloggers? click here if you have a blog, or here if you don't.
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. Want to share your content on R-bloggers? click here if you have a blog, or here if you don't.
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 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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 | 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 | cs |
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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 | #========================================================# # Quantitative ALM, Financial Econometrics & Derivatives # ML/DL using R, Python, Tensorflow by Sang-Heon Lee # # https://kiandlee.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’)) | cs |
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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 | > 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 | cs |
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. \(\blacksquare\)
To leave a comment for the author, please follow the link and comment on their blog: K & L Fintech Modeling.
R-bloggers.com offers daily e-mail updates about R news and tutorials about learning R and many other topics. Click here if you're looking to post or find an R/data-science job.
Want to share your content on R-bloggers? click here if you have a blog, or here if you don't.