{"id":196728,"date":"2023-09-26T10:55:27","date_gmt":"2023-09-26T14:55:27","guid":{"rendered":"https:\/\/ibkrcampus.com\/?p=196728"},"modified":"2025-02-12T10:45:07","modified_gmt":"2025-02-12T15:45:07","slug":"r-httr-in-r-and-msxml2-serverxmlhttp-in-excel-vba","status":"publish","type":"post","link":"https:\/\/www.interactivebrokers.com\/campus\/ibkr-quant-news\/r-httr-in-r-and-msxml2-serverxmlhttp-in-excel-vba\/","title":{"rendered":"R: httr in R and MSXML2.ServerXMLHTTP in Excel VBA"},"content":{"rendered":"\n<p>This post shows a R counterpart of Excel VBA&#8217;s&nbsp;<strong>MSXML2.ServerXMLHTTP<\/strong>&nbsp;related commands with which server APIs are called easily. In case of R, it is done by using&nbsp;<strong>httr<\/strong>&nbsp;R package. As an illustration, SQL query for retrieving swap date schedule is executed by calling this server API.<\/p>\n\n\n\n<p><a><\/a><\/p>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"h-calling-server-api-in-a-simple-manner\">Calling Server API in a simple manner<\/h2>\n\n\n\n<p>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.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"h-excel-vba-msxml2-serverxmlhttp\">Excel VBA : MSXML2.ServerXMLHTTP<\/h3>\n\n\n\n<p>The following Excel VBA code uses&nbsp;<strong>MSXML2.ServerXMLHTTP<\/strong>&nbsp;object to do the above job. Of course, specific parts depend on in-house system.<\/p>\n\n\n\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"generic\" data-enlighter-theme=\"\" data-enlighter-highlight=\"\" data-enlighter-linenumbers=\"\" data-enlighter-lineoffset=\"\" data-enlighter-title=\"\" data-enlighter-group=\"\">Sub Run_SQL_Btn_Click()\n \n    Dim response As String\n    Dim result As Variant\n    Dim X As Variant\n    Dim url As String\n    Dim strQry As String\n \n    ' SQL query\n    strQry = \" SELECT ID,\" &amp; _\n             \"    to_char(RESET_DATE,  'yyyy-mm-dd'),\" &amp; _\n             \"    to_char(START_DATE,  'yyyy-mm-dd'),\" &amp; _\n             \"    to_char(END_DATE,    'yyyy-mm-dd'),\" &amp; _\n             \"    to_char(PAYMENT_DATE,'yyyy-mm-dd') \" &amp; _\n             \" FROM  CASH_FLOW_TABLE\" &amp; _\n             \" WHERE PRODUCT_ID = '3911737' and ID = '2'\" &amp; _\n             \" ORDER BY PAYMENT_DATE, RESET_DATE\"\n    \n \n    ' create object which is connected to server\n    Dim objHttp\n    Set objHttp = CreateObject(\"MSXML2.ServerXMLHTTP\")\n    \n    ' This is for the illustration purpose and not valid\n    url = \"https:\/\/123.123.123.123:9080\/importserver\/InquiryOracle\"\n    \n    objHttp.Open \"POST\", url, False\n    objHttp.setRequestHeader \"User-Agent\", _\n      \"Mozilla\/4.0 (compatible; MSIE 6.0; Windows NT 5.0)\"\n    objHttp.setRequestHeader \"Content-Type\", _\n      \"application\/x-www-form-urlencoded;charset=UTF-8\"\n    objHttp.send (\"q=\" &amp; strQry)\n    objHttp.waitForResponse 180000000\n    response = objHttp.responseText\n    \n    ' Convert Result\n    X = Split(response, \"|\")\n    ReDim result(UBound(X))\n    \n    For i = 0 To UBound(X)\n        result(i) = Split(X(i), \",\")\n    Next i\n    \n    ' Show Result\n    i = 0\n    For Each Data In result\n        Cells(3 + i, 2) = i + 1\n        For j = LBound(Data) To UBound(Data)\n            Cells(3 + i, 3 + j) = Data(j)\n        Next j\n        i = i + 1\n    Next Data\n \nEnd Sub<\/pre>\n\n\n\n<p>Running the above Excel macro returns the following output.<\/p>\n\n\n\n<figure class=\"wp-block-image size-full\"><img decoding=\"async\" width=\"694\" height=\"452\" data-src=\"\/campus\/wp-content\/uploads\/sites\/2\/2023\/09\/excel_sql_out-shmodel-ai.png\" alt=\"\" class=\"wp-image-196731 lazyload\" data-srcset=\"https:\/\/ibkrcampus.com\/campus\/wp-content\/uploads\/sites\/2\/2023\/09\/excel_sql_out-shmodel-ai.png 694w, https:\/\/ibkrcampus.com\/campus\/wp-content\/uploads\/sites\/2\/2023\/09\/excel_sql_out-shmodel-ai-300x195.png 300w\" data-sizes=\"(max-width: 694px) 100vw, 694px\" src=\"data:image\/svg+xml;base64,PHN2ZyB3aWR0aD0iMSIgaGVpZ2h0PSIxIiB4bWxucz0iaHR0cDovL3d3dy53My5vcmcvMjAwMC9zdmciPjwvc3ZnPg==\" style=\"--smush-placeholder-width: 694px; aspect-ratio: 694\/452;\" \/><\/figure>\n\n\n\n<h3 class=\"wp-block-heading\">R code with httr package<\/h3>\n\n\n\n<p>The following R code uses&nbsp;<strong>httr<\/strong>&nbsp;R package to do the above same job.<\/p>\n\n\n\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"generic\" data-enlighter-theme=\"\" data-enlighter-highlight=\"\" data-enlighter-linenumbers=\"\" data-enlighter-lineoffset=\"\" data-enlighter-title=\"\" data-enlighter-group=\"\">#========================================================#\n# Quantitative ALM, Financial Econometrics &amp; Derivatives \n# ML\/DL using R, Python, Tensorflow by Sang-Heon Lee \n#\n# https:\/\/shleeai.blogspot.com\n#--------------------------------------------------------#\n# Excel VBA's MSXML2.ServerXMLHTTP in R\n#========================================================#\n \nlibrary(httr)\n \n# SQL query to be executed\nsql &lt;- paste(\n    \"SELECT ID,\",\n    \"      to_char(RESET_DATE,  'yyyy-mm-dd'),\",\n    \"      to_char(START_DATE,  'yyyy-mm-dd'),\",\n    \"      to_char(END_DATE,    'yyyy-mm-dd'),\",\n    \"      to_char(PAYMENT_DATE,'yyyy-mm-dd')\",\n    \"FROM  CASH_FLOW_TABLE\",\n    \"WHERE PRODUCT_ID = '3911737' and ID = '2'\",\n    \"ORDER BY PAYMENT_DATE, RESET_DATE\");\n \n# web server address which provides service APIs \n# But This url is for the illustration purpose and not valid\nurl &lt;- \"https:\/\/123.123.123.123:9080\/importserver\/InquiryOracle\"\n \n# create object which is connected to server and send it\nhttpResponse = POST(\n    url, body = list(q=sql),\n    user_agent(\n      \"Mozilla\/4.0 (compatible; MSIE 6.0; Windows NT 5.0)\"),\n    add_headers(\n      'Content-Type'=\n      \"application\/x-www-form-urlencoded;charset=UTF-8\",\n      'User-Agent'=\n      \"Mozilla\/4.0 (compatible; MSIE 6.0; Windows NT 5.0)\"),\n    encode = 'form')\n \n# convert output as text\nstr.out &lt;- content(httpResponse, as =\"text\")\n \n# reshape output as data.frame\nread.table(text=gsub(\"[|]\", \"\\n\", str.out), sep=\",\",\n           col.names = c('ID','RESET_DATE', 'START_DATE', \n                         'END_DATE', 'PAYMENT_DATE'))<\/pre>\n\n\n\n<p>In the above R code, it is worth noting that&nbsp;<strong>a SQL query string is inserted as a member of list<\/strong>. Running this R code returns the following same output.<\/p>\n\n\n\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"generic\" data-enlighter-theme=\"\" data-enlighter-highlight=\"\" data-enlighter-linenumbers=\"\" data-enlighter-lineoffset=\"\" data-enlighter-title=\"\" data-enlighter-group=\"\">>   ID  RESET_DATE   START_DATE    END_DATE   PAYMENT_DATE\n1   2   2021-10-15   2021-10-20   2021-11-10   2021-11-10\n2   2   2021-10-15   2021-11-10   2021-12-10   2021-12-10\n3   2   2021-10-15   2021-12-10   2022-01-10   2022-01-10\n4   2   2021-10-15   2022-01-10   2022-02-10   2022-02-10\n5   2   2021-10-15   2022-02-10   2022-03-10   2022-03-10\n6   2   2021-10-15   2022-03-10   2022-04-10   2022-04-11\n7   2   2021-10-15   2022-04-10   2022-04-16   2022-05-10\n8   2   2022-04-15   2022-04-16   2022-05-10   2022-05-10\n9   2   2022-04-15   2022-05-10   2022-06-10   2022-06-10\n10  2   2022-04-15   2022-06-10   2022-07-10   2022-07-11\n11  2   2022-04-15   2022-07-10   2022-08-10   2022-08-10\n12  2   2022-04-15   2022-08-10   2022-09-10   2022-09-13\n13  2   2022-04-15   2022-09-10   2022-10-10   2022-10-11\n14  2   2022-04-15   2022-10-10   2022-10-16   2022-11-10\n15  2   2022-09-15   2022-10-16   2022-10-20   2022-11-10<\/pre>\n\n\n\n<h3 class=\"wp-block-heading\">Concluding Remarks<\/h3>\n\n\n\n<p>As we call server API function using&nbsp;<strong>MSXML2.ServerXMLHTTP<\/strong>&nbsp;in Excel VBA, we can also do the same job by using&nbsp;<strong>httr<\/strong>&nbsp;package<\/p>\n\n\n\n<p><em>Originally posted on <a href=\"https:\/\/shleeai.blogspot.com\/2021\/10\/r-httr-in-r-and-msxml2serverxmlhttp-in.html\">SHLee AI Financial Model<\/a> blog.<\/em><\/p>\n","protected":false},"excerpt":{"rendered":"<p>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. <\/p>\n","protected":false},"author":662,"featured_media":195001,"comment_status":"open","ping_status":"closed","sticky":true,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":"","jetpack_post_was_ever_published":false},"categories":[339,343,338,341,342],"tags":[806,4922,16018,16017,487,6591],"contributors-categories":[13728],"class_list":{"0":"post-196728","1":"post","2":"type-post","3":"status-publish","4":"format-standard","5":"has-post-thumbnail","7":"category-data-science","8":"category-programing-languages","9":"category-ibkr-quant-news","10":"category-quant-development","11":"category-r-development","12":"tag-data-science","13":"tag-econometrics","14":"tag-excel-vba","15":"tag-httr","16":"tag-r","17":"tag-rstats","18":"contributors-categories-sh-fintech-modeling"},"pp_statuses_selecting_workflow":false,"pp_workflow_action":"current","pp_status_selection":"publish","acf":[],"yoast_head":"<!-- This site is optimized with the Yoast SEO Premium plugin v26.9 (Yoast SEO v27.8) - https:\/\/yoast.com\/product\/yoast-seo-premium-wordpress\/ -->\n<title>R: httr in R and MSXML2.ServerXMLHTTP in Excel VBA<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.interactivebrokers.com\/campus\/wp-json\/wp\/v2\/posts\/196728\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"R: httr in R and MSXML2.ServerXMLHTTP in Excel VBA | IBKR Campus US\" \/>\n<meta property=\"og:description\" content=\"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.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.interactivebrokers.com\/campus\/ibkr-quant-news\/r-httr-in-r-and-msxml2-serverxmlhttp-in-excel-vba\/\" \/>\n<meta property=\"og:site_name\" content=\"IBKR Campus US\" \/>\n<meta property=\"article:published_time\" content=\"2023-09-26T14:55:27+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2025-02-12T15:45:07+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/www.interactivebrokers.com\/campus\/wp-content\/uploads\/sites\/2\/2023\/08\/r-splash-of-colors-background.jpg\" \/>\n\t<meta property=\"og:image:width\" content=\"1000\" \/>\n\t<meta property=\"og:image:height\" content=\"563\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/jpeg\" \/>\n<meta name=\"author\" content=\"Sang-Heon Lee\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Sang-Heon Lee\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"2 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\n\t    \"@context\": \"https:\\\/\\\/schema.org\",\n\t    \"@graph\": [\n\t        {\n\t            \"@type\": \"NewsArticle\",\n\t            \"@id\": \"https:\\\/\\\/www.interactivebrokers.com\\\/campus\\\/ibkr-quant-news\\\/r-httr-in-r-and-msxml2-serverxmlhttp-in-excel-vba\\\/#article\",\n\t            \"isPartOf\": {\n\t                \"@id\": \"https:\\\/\\\/www.interactivebrokers.com\\\/campus\\\/ibkr-quant-news\\\/r-httr-in-r-and-msxml2-serverxmlhttp-in-excel-vba\\\/\"\n\t            },\n\t            \"author\": {\n\t                \"name\": \"Sang-Heon Lee\",\n\t                \"@id\": \"https:\\\/\\\/ibkrcampus.com\\\/campus\\\/#\\\/schema\\\/person\\\/0a959ff9de7f0465a07baa1fe1ae0200\"\n\t            },\n\t            \"headline\": \"R: httr in R and MSXML2.ServerXMLHTTP in Excel VBA\",\n\t            \"datePublished\": \"2023-09-26T14:55:27+00:00\",\n\t            \"dateModified\": \"2025-02-12T15:45:07+00:00\",\n\t            \"mainEntityOfPage\": {\n\t                \"@id\": \"https:\\\/\\\/www.interactivebrokers.com\\\/campus\\\/ibkr-quant-news\\\/r-httr-in-r-and-msxml2-serverxmlhttp-in-excel-vba\\\/\"\n\t            },\n\t            \"wordCount\": 267,\n\t            \"commentCount\": 0,\n\t            \"publisher\": {\n\t                \"@id\": \"https:\\\/\\\/ibkrcampus.com\\\/campus\\\/#organization\"\n\t            },\n\t            \"image\": {\n\t                \"@id\": \"https:\\\/\\\/www.interactivebrokers.com\\\/campus\\\/ibkr-quant-news\\\/r-httr-in-r-and-msxml2-serverxmlhttp-in-excel-vba\\\/#primaryimage\"\n\t            },\n\t            \"thumbnailUrl\": \"https:\\\/\\\/www.interactivebrokers.com\\\/campus\\\/wp-content\\\/uploads\\\/sites\\\/2\\\/2023\\\/08\\\/r-splash-of-colors-background.jpg\",\n\t            \"keywords\": [\n\t                \"Data Science\",\n\t                \"Econometrics\",\n\t                \"Excel VBA\",\n\t                \"httr\",\n\t                \"R\",\n\t                \"rstats\"\n\t            ],\n\t            \"articleSection\": [\n\t                \"Data Science\",\n\t                \"Programming Languages\",\n\t                \"Quant\",\n\t                \"Quant Development\",\n\t                \"R Development\"\n\t            ],\n\t            \"inLanguage\": \"en-US\",\n\t            \"potentialAction\": [\n\t                {\n\t                    \"@type\": \"CommentAction\",\n\t                    \"name\": \"Comment\",\n\t                    \"target\": [\n\t                        \"https:\\\/\\\/www.interactivebrokers.com\\\/campus\\\/ibkr-quant-news\\\/r-httr-in-r-and-msxml2-serverxmlhttp-in-excel-vba\\\/#respond\"\n\t                    ]\n\t                }\n\t            ]\n\t        },\n\t        {\n\t            \"@type\": \"WebPage\",\n\t            \"@id\": \"https:\\\/\\\/www.interactivebrokers.com\\\/campus\\\/ibkr-quant-news\\\/r-httr-in-r-and-msxml2-serverxmlhttp-in-excel-vba\\\/\",\n\t            \"url\": \"https:\\\/\\\/www.interactivebrokers.com\\\/campus\\\/ibkr-quant-news\\\/r-httr-in-r-and-msxml2-serverxmlhttp-in-excel-vba\\\/\",\n\t            \"name\": \"R: httr in R and MSXML2.ServerXMLHTTP in Excel VBA | IBKR Campus US\",\n\t            \"isPartOf\": {\n\t                \"@id\": \"https:\\\/\\\/ibkrcampus.com\\\/campus\\\/#website\"\n\t            },\n\t            \"primaryImageOfPage\": {\n\t                \"@id\": \"https:\\\/\\\/www.interactivebrokers.com\\\/campus\\\/ibkr-quant-news\\\/r-httr-in-r-and-msxml2-serverxmlhttp-in-excel-vba\\\/#primaryimage\"\n\t            },\n\t            \"image\": {\n\t                \"@id\": \"https:\\\/\\\/www.interactivebrokers.com\\\/campus\\\/ibkr-quant-news\\\/r-httr-in-r-and-msxml2-serverxmlhttp-in-excel-vba\\\/#primaryimage\"\n\t            },\n\t            \"thumbnailUrl\": \"https:\\\/\\\/www.interactivebrokers.com\\\/campus\\\/wp-content\\\/uploads\\\/sites\\\/2\\\/2023\\\/08\\\/r-splash-of-colors-background.jpg\",\n\t            \"datePublished\": \"2023-09-26T14:55:27+00:00\",\n\t            \"dateModified\": \"2025-02-12T15:45:07+00:00\",\n\t            \"inLanguage\": \"en-US\",\n\t            \"potentialAction\": [\n\t                {\n\t                    \"@type\": \"ReadAction\",\n\t                    \"target\": [\n\t                        \"https:\\\/\\\/www.interactivebrokers.com\\\/campus\\\/ibkr-quant-news\\\/r-httr-in-r-and-msxml2-serverxmlhttp-in-excel-vba\\\/\"\n\t                    ]\n\t                }\n\t            ]\n\t        },\n\t        {\n\t            \"@type\": \"ImageObject\",\n\t            \"inLanguage\": \"en-US\",\n\t            \"@id\": \"https:\\\/\\\/www.interactivebrokers.com\\\/campus\\\/ibkr-quant-news\\\/r-httr-in-r-and-msxml2-serverxmlhttp-in-excel-vba\\\/#primaryimage\",\n\t            \"url\": \"https:\\\/\\\/www.interactivebrokers.com\\\/campus\\\/wp-content\\\/uploads\\\/sites\\\/2\\\/2023\\\/08\\\/r-splash-of-colors-background.jpg\",\n\t            \"contentUrl\": \"https:\\\/\\\/www.interactivebrokers.com\\\/campus\\\/wp-content\\\/uploads\\\/sites\\\/2\\\/2023\\\/08\\\/r-splash-of-colors-background.jpg\",\n\t            \"width\": 1000,\n\t            \"height\": 563,\n\t            \"caption\": \"Quant\"\n\t        },\n\t        {\n\t            \"@type\": \"WebSite\",\n\t            \"@id\": \"https:\\\/\\\/ibkrcampus.com\\\/campus\\\/#website\",\n\t            \"url\": \"https:\\\/\\\/ibkrcampus.com\\\/campus\\\/\",\n\t            \"name\": \"IBKR Campus US\",\n\t            \"description\": \"Financial Education from Interactive Brokers\",\n\t            \"publisher\": {\n\t                \"@id\": \"https:\\\/\\\/ibkrcampus.com\\\/campus\\\/#organization\"\n\t            },\n\t            \"potentialAction\": [\n\t                {\n\t                    \"@type\": \"SearchAction\",\n\t                    \"target\": {\n\t                        \"@type\": \"EntryPoint\",\n\t                        \"urlTemplate\": \"https:\\\/\\\/ibkrcampus.com\\\/campus\\\/?s={search_term_string}\"\n\t                    },\n\t                    \"query-input\": {\n\t                        \"@type\": \"PropertyValueSpecification\",\n\t                        \"valueRequired\": true,\n\t                        \"valueName\": \"search_term_string\"\n\t                    }\n\t                }\n\t            ],\n\t            \"inLanguage\": \"en-US\"\n\t        },\n\t        {\n\t            \"@type\": \"Organization\",\n\t            \"@id\": \"https:\\\/\\\/ibkrcampus.com\\\/campus\\\/#organization\",\n\t            \"name\": \"Interactive Brokers\",\n\t            \"alternateName\": \"IBKR\",\n\t            \"url\": \"https:\\\/\\\/ibkrcampus.com\\\/campus\\\/\",\n\t            \"logo\": {\n\t                \"@type\": \"ImageObject\",\n\t                \"inLanguage\": \"en-US\",\n\t                \"@id\": \"https:\\\/\\\/ibkrcampus.com\\\/campus\\\/#\\\/schema\\\/logo\\\/image\\\/\",\n\t                \"url\": \"https:\\\/\\\/www.interactivebrokers.com\\\/campus\\\/wp-content\\\/uploads\\\/sites\\\/2\\\/2024\\\/05\\\/ibkr-campus-logo.jpg\",\n\t                \"contentUrl\": \"https:\\\/\\\/www.interactivebrokers.com\\\/campus\\\/wp-content\\\/uploads\\\/sites\\\/2\\\/2024\\\/05\\\/ibkr-campus-logo.jpg\",\n\t                \"width\": 669,\n\t                \"height\": 669,\n\t                \"caption\": \"Interactive Brokers\"\n\t            },\n\t            \"image\": {\n\t                \"@id\": \"https:\\\/\\\/ibkrcampus.com\\\/campus\\\/#\\\/schema\\\/logo\\\/image\\\/\"\n\t            },\n\t            \"publishingPrinciples\": \"https:\\\/\\\/www.interactivebrokers.com\\\/campus\\\/about-ibkr-campus\\\/\",\n\t            \"ethicsPolicy\": \"https:\\\/\\\/www.interactivebrokers.com\\\/campus\\\/cyber-security-notice\\\/\"\n\t        },\n\t        {\n\t            \"@type\": \"Person\",\n\t            \"@id\": \"https:\\\/\\\/ibkrcampus.com\\\/campus\\\/#\\\/schema\\\/person\\\/0a959ff9de7f0465a07baa1fe1ae0200\",\n\t            \"name\": \"Sang-Heon Lee\",\n\t            \"url\": \"https:\\\/\\\/www.interactivebrokers.com\\\/campus\\\/author\\\/sang-heonlee\\\/\"\n\t        }\n\t    ]\n\t}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"R: httr in R and MSXML2.ServerXMLHTTP in Excel VBA","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.interactivebrokers.com\/campus\/wp-json\/wp\/v2\/posts\/196728\/","og_locale":"en_US","og_type":"article","og_title":"R: httr in R and MSXML2.ServerXMLHTTP in Excel VBA | IBKR Campus US","og_description":"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.","og_url":"https:\/\/www.interactivebrokers.com\/campus\/ibkr-quant-news\/r-httr-in-r-and-msxml2-serverxmlhttp-in-excel-vba\/","og_site_name":"IBKR Campus US","article_published_time":"2023-09-26T14:55:27+00:00","article_modified_time":"2025-02-12T15:45:07+00:00","og_image":[{"width":1000,"height":563,"url":"https:\/\/www.interactivebrokers.com\/campus\/wp-content\/uploads\/sites\/2\/2023\/08\/r-splash-of-colors-background.jpg","type":"image\/jpeg"}],"author":"Sang-Heon Lee","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Sang-Heon Lee","Est. reading time":"2 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"NewsArticle","@id":"https:\/\/www.interactivebrokers.com\/campus\/ibkr-quant-news\/r-httr-in-r-and-msxml2-serverxmlhttp-in-excel-vba\/#article","isPartOf":{"@id":"https:\/\/www.interactivebrokers.com\/campus\/ibkr-quant-news\/r-httr-in-r-and-msxml2-serverxmlhttp-in-excel-vba\/"},"author":{"name":"Sang-Heon Lee","@id":"https:\/\/ibkrcampus.com\/campus\/#\/schema\/person\/0a959ff9de7f0465a07baa1fe1ae0200"},"headline":"R: httr in R and MSXML2.ServerXMLHTTP in Excel VBA","datePublished":"2023-09-26T14:55:27+00:00","dateModified":"2025-02-12T15:45:07+00:00","mainEntityOfPage":{"@id":"https:\/\/www.interactivebrokers.com\/campus\/ibkr-quant-news\/r-httr-in-r-and-msxml2-serverxmlhttp-in-excel-vba\/"},"wordCount":267,"commentCount":0,"publisher":{"@id":"https:\/\/ibkrcampus.com\/campus\/#organization"},"image":{"@id":"https:\/\/www.interactivebrokers.com\/campus\/ibkr-quant-news\/r-httr-in-r-and-msxml2-serverxmlhttp-in-excel-vba\/#primaryimage"},"thumbnailUrl":"https:\/\/www.interactivebrokers.com\/campus\/wp-content\/uploads\/sites\/2\/2023\/08\/r-splash-of-colors-background.jpg","keywords":["Data Science","Econometrics","Excel VBA","httr","R","rstats"],"articleSection":["Data Science","Programming Languages","Quant","Quant Development","R Development"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.interactivebrokers.com\/campus\/ibkr-quant-news\/r-httr-in-r-and-msxml2-serverxmlhttp-in-excel-vba\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.interactivebrokers.com\/campus\/ibkr-quant-news\/r-httr-in-r-and-msxml2-serverxmlhttp-in-excel-vba\/","url":"https:\/\/www.interactivebrokers.com\/campus\/ibkr-quant-news\/r-httr-in-r-and-msxml2-serverxmlhttp-in-excel-vba\/","name":"R: httr in R and MSXML2.ServerXMLHTTP in Excel VBA | IBKR Campus US","isPartOf":{"@id":"https:\/\/ibkrcampus.com\/campus\/#website"},"primaryImageOfPage":{"@id":"https:\/\/www.interactivebrokers.com\/campus\/ibkr-quant-news\/r-httr-in-r-and-msxml2-serverxmlhttp-in-excel-vba\/#primaryimage"},"image":{"@id":"https:\/\/www.interactivebrokers.com\/campus\/ibkr-quant-news\/r-httr-in-r-and-msxml2-serverxmlhttp-in-excel-vba\/#primaryimage"},"thumbnailUrl":"https:\/\/www.interactivebrokers.com\/campus\/wp-content\/uploads\/sites\/2\/2023\/08\/r-splash-of-colors-background.jpg","datePublished":"2023-09-26T14:55:27+00:00","dateModified":"2025-02-12T15:45:07+00:00","inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.interactivebrokers.com\/campus\/ibkr-quant-news\/r-httr-in-r-and-msxml2-serverxmlhttp-in-excel-vba\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.interactivebrokers.com\/campus\/ibkr-quant-news\/r-httr-in-r-and-msxml2-serverxmlhttp-in-excel-vba\/#primaryimage","url":"https:\/\/www.interactivebrokers.com\/campus\/wp-content\/uploads\/sites\/2\/2023\/08\/r-splash-of-colors-background.jpg","contentUrl":"https:\/\/www.interactivebrokers.com\/campus\/wp-content\/uploads\/sites\/2\/2023\/08\/r-splash-of-colors-background.jpg","width":1000,"height":563,"caption":"Quant"},{"@type":"WebSite","@id":"https:\/\/ibkrcampus.com\/campus\/#website","url":"https:\/\/ibkrcampus.com\/campus\/","name":"IBKR Campus US","description":"Financial Education from Interactive Brokers","publisher":{"@id":"https:\/\/ibkrcampus.com\/campus\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/ibkrcampus.com\/campus\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Organization","@id":"https:\/\/ibkrcampus.com\/campus\/#organization","name":"Interactive Brokers","alternateName":"IBKR","url":"https:\/\/ibkrcampus.com\/campus\/","logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/ibkrcampus.com\/campus\/#\/schema\/logo\/image\/","url":"https:\/\/www.interactivebrokers.com\/campus\/wp-content\/uploads\/sites\/2\/2024\/05\/ibkr-campus-logo.jpg","contentUrl":"https:\/\/www.interactivebrokers.com\/campus\/wp-content\/uploads\/sites\/2\/2024\/05\/ibkr-campus-logo.jpg","width":669,"height":669,"caption":"Interactive Brokers"},"image":{"@id":"https:\/\/ibkrcampus.com\/campus\/#\/schema\/logo\/image\/"},"publishingPrinciples":"https:\/\/www.interactivebrokers.com\/campus\/about-ibkr-campus\/","ethicsPolicy":"https:\/\/www.interactivebrokers.com\/campus\/cyber-security-notice\/"},{"@type":"Person","@id":"https:\/\/ibkrcampus.com\/campus\/#\/schema\/person\/0a959ff9de7f0465a07baa1fe1ae0200","name":"Sang-Heon Lee","url":"https:\/\/www.interactivebrokers.com\/campus\/author\/sang-heonlee\/"}]}},"jetpack_featured_media_url":"https:\/\/www.interactivebrokers.com\/campus\/wp-content\/uploads\/sites\/2\/2023\/08\/r-splash-of-colors-background.jpg","_links":{"self":[{"href":"https:\/\/ibkrcampus.com\/campus\/wp-json\/wp\/v2\/posts\/196728","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/ibkrcampus.com\/campus\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/ibkrcampus.com\/campus\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/ibkrcampus.com\/campus\/wp-json\/wp\/v2\/users\/662"}],"replies":[{"embeddable":true,"href":"https:\/\/ibkrcampus.com\/campus\/wp-json\/wp\/v2\/comments?post=196728"}],"version-history":[{"count":0,"href":"https:\/\/ibkrcampus.com\/campus\/wp-json\/wp\/v2\/posts\/196728\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/ibkrcampus.com\/campus\/wp-json\/wp\/v2\/media\/195001"}],"wp:attachment":[{"href":"https:\/\/ibkrcampus.com\/campus\/wp-json\/wp\/v2\/media?parent=196728"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/ibkrcampus.com\/campus\/wp-json\/wp\/v2\/categories?post=196728"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/ibkrcampus.com\/campus\/wp-json\/wp\/v2\/tags?post=196728"},{"taxonomy":"contributors-categories","embeddable":true,"href":"https:\/\/ibkrcampus.com\/campus\/wp-json\/wp\/v2\/contributors-categories?post=196728"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}