Calling R/Python Locally from Excel Directly or via an API

In this chapter, we are going to discuss calling R and Python from within Excel. You may ask yourself why you would want to do that when there are many functions inside of Excel that one can use or, if so desired, can write with the VBA portion of the application. One reason why you might want to call R or Python from Excel is to leverage the power and flexibility of these programming languages for data analysis and visualization. Excel is a widely used spreadsheet application that can handle large datasets and perform basic calculations and functions. However, Excel has some limitations when it comes to more advanced or customized tasks, such as statistical modeling, machine learning, web scraping, natural language processing, and so on. By calling R or Python from Excel, you can access the rich libraries and packages that these languages offer and use them to manipulate, transform, and visualize your data in more sophisticated...

Technical requirements

In this chapter, you can find all the code on GitHub at the following link: https://github.com/PacktPublishing/Extending-Excel-with-Python-and-R/tree/main/Chapter%2011.

You will need to install the following external software:

• BERT, which can be found here: http://bert-toolkit.com/download-bert
• Python and xlwings (refer to the Setting up an environment section)
• plumber for R
• FastAPI for Python

Let’s begin with the first part.

Calling R and Python from Excel locally

In this first part of the chapter, we are going to learn about using the +or Excel that can interface with R directly, along with xlwings to interact with Python from Excel. We will also quickly show how one can use a VBA script to call R from Excel. In this chapter, we’re going to cover the following main topics:

• Why you would want to call R/Python from Excel locally
• Setting up an environment
• Calling R/Python directly from Excel

Let’s begin!

Why you would want to call R/Python from Excel locally

As we discussed in the opening, it is possible to do a wide variety of analysis and programming in Excel via the use of VBA. However, this can be tedious to write and difficult to implement. By harnessing the power of BERT and xlwings, you can use an already rich landscape of functions that are ready to go or write your own in those languages and use them in Excel.

With BERT, you get the power of R in Excel: R is a powerful statistical programming language with a wide range of capabilities. BERT allows you to use these capabilities directly in Excel without having to switch to a separate R environment. This can be very convenient if you are already working in Excel and don’t want to leave the application.

BERT allows you to write R functions that can be used as custom Excel functions. This can be useful for creating functions that are not available in Excel or for improving the performance of existing Excel functions...

Setting up an environment

Since setting up an environment for BERT and xlwings is non-trivial, we will walk you through the process in detail in the coming subsections.

Steps to set up BERT for R

In this section, we will cover the installation of BERT for Windows so that we can utilize BERT to manipulate Excel from inside of R. The first thing that we will have to do is download the BERT installer, which can be obtained from here: https://bert-toolkit.com/download-bert.

Once this is downloaded, you can then install it as you would any other program. Once installed, you can then use the Add-ins ribbon from Excel to open the BERT Console, as shown here:

Figure 11.1 – BERT Console from the Add-ins ribbon in Excel

Once you see it, click the button, and the console will open, as shown here:

Figure 11.2 – The BERT Console

Now, let’s move to Python.

Steps to set up xlwings for Python

In this subsection...

Calling R/Python directly from Excel

In this section, we will dig into the ways you can call R and Python from Excel using the tools set up in the previous section. We will cover several ways of achieving this and give examples so that you can try them out as well.

Executing R with VBA and BERT

Another great way of calling R from Excel is via the VBA macro. This requires that a workbook be saved as a macro-enabled workbook. Since BERT is designed to work from Excel to R, the syntax of an R expression can be written in the VBA console and called with the following in VBA:

Application.Run "BERT.Exec", r

Let’s look at an easy example:

Sub PlotNormalDensity()
    r = "plot(density(rnorm(1000)))"
    Application.Run "BERT.Exec", r
End Sub

This will end up producing a plot of the density of a random normal distribution. Let’s see the output:

Figure 11.4 –...

Calling Python from Excel using xlwings

You have three options to call Python from Excel using xlwings:

• The Run button under the xlwings tab of the ribbon
• Macros: These call Python from Excel
• User Defined Functions (UDFs) (Windows only)

Let’s have a look at the pros and cons of all three, as well as an example!

The Run button

The Run button expects a function called main in a Python module with the same name as your workbook. This is a quote from the documentation and a hard prerequisite. The main benefit of this method is that there is no VBA and no macros; you can use a normal XLSX file, which can be very useful in security-restricted situations where XLSM files are not allowed.

To try out the Run button, follow these steps:

1. Create a Python module called sumitup.py with the following code:

   import xlwings as xw
   def main():
               wb = xw.Book.caller()
      &...

Calling R and Python from Excel via an API

An API, or Application Programming Interface, serves as a bridge between different software applications, allowing them to communicate and share data in a standardized way. It’s like a waiter at a restaurant who takes your order and conveys it to the kitchen, bringing back the meal once it’s ready.

In the digital world, an API specifies how software components should interact. It defines the methods and data formats that applications can use to request and exchange information. APIs can be used for various purposes, such as accessing web services, databases, or even hardware devices.

One fundamental use of APIs is enabling third-party developers to integrate their applications with existing services or platforms. In the context of your coding interests in R and Python, APIs can be utilized for data retrieval and for exposing your models to other software, including Excel.

The beauty of APIs lies in their versatility...

An introduction to APIs

You can think of APIs as a set of rules that will allow one piece of software to interact with another. A quick example of the usage of an API would be the weather app on a smartphone connecting with the weather system to get the current weather or a forecast of the weather.

An easy way to think of an API, besides a mechanism for different systems to communicate, is to think of a contract. The documentation of an API will specify how a system can connect with and talk with the system, what it is allowed to do, and how often.

Systems that maintain an API will often act as a sort of client and server type arrangement. A REST API is one of the most popular types of API today. REST stands for representational state transfer. The major pro of this type of API is that it is stateless. Statelessness means that servers do not save client data between requests. The requests that are sent to the server will remind you of a URL. A generic REST API call might look...

Open source solutions for exposing R as API endpoints

We are going to start off by first showing how to expose R as an API endpoint via the plumber package. The plumber package and its associated documentation can be found at the following URL: https://www.rplumber.io/index.html.

The first thing we will do is build out a very simple single-argument API to obtain the histogram of a standard normal distribution. Let’s take a look at the code; we will then discuss what is happening inside of it:

#* Plot out data from a random normal distribution
#* @param .mean The mean of the standard normal distribution
#* @get /plot
#* @serializer png
function(.mean) {
  mu <- as.numeric(.mean)
  hist(rnorm(n = 1000, mean = mu, sd = 1))
}

The lines starting with #* are comments. In the plumber API, these comments are special and are used for documentation. They describe what the API endpoint does and provide information about the parameters. The first comment introduces...

Open-source solutions for exposing Python as an API endpoint

FastAPI is a modern, fast (high-performance) web framework for building APIs with Python 3.7+ based on standard Python-type hints. It’s easy to use and allows you to create robust APIs quickly.

You can install FastAPI using pip:

pip install fastapi

The following is a simplified example of creating a FastAPI endpoint to expose a Python function:

from fastapi import FastAPI, Query
app = FastAPI()
@app.get("/api/add")
def add_numbers(
    num1: int = Query(..., description="First number"),
    num2: int = Query(..., description="Second number"),
):
    result = num1 + num2
    return {"result": result}

In this example, the add_numbers function available at the /api/add endpoint takes two query parameters (num1 and num2), representing the numbers to be added. The Query function from FastAPI...

Calling APIs from Excel VBA

Now, we are going to go over the code that will allow us to use a curl request to obtain the image from the API that was generated from the plumber_api.R file. In order to do this, you will have to run the code from the previous section: root |> pr_run(); this is the portion that will open up the swagger dialogue and give you the URL that is running from plumber. For me, at the time of this writing, it is as follows: http://127.0.0.1:6855.

In this section, we are going to specifically execute the GET request via a curl command in VBA. Here is the code that will run, and the explanation will follow:

Sub MakeCurlRequestAndInsertImage()
       ' Define the curl command
       Dim curlCommand As String
       curlCommand = "curl -X GET ""http://127.0.0.1:6855/plot?.mean=0"" -H ""accept: image/png""...

Summary

In this chapter, you have learned ways to call R and Python from Excel instead of the other way around to empower your end users even further. We have covered the reasons for doing so and two very different approaches: calling R and Python locally and via an API endpoint.

For locally calling R and Python, we covered BERT and xlwings in detail, from setting up and testing the environment via creating R and Python solutions up to and including how to call those solutions from Excel using the various methods provided by BERT and xlwings, such as VBA code and UDFs.

Next, you learned about API endpoints and the benefits of using an API endpoint to connect Python and R to Excel. We discussed the pros and cons of such a setup and then delved into the two flavors of API hosting: open source tools and commercial solutions. We have covered the two most used open source setups: plumber for R and FastAPI for Python. Finally, we had a look at commercial solutions for hosting R and...