Wolfram Computation Meets Knowledge

Advanced Computation for Spreadsheets: Wolfram CloudConnector for Excel

Advanced Computation for Spreadsheets: Wolfram CloudConnector for Excel

Microsoft Excel is among the most popular tools in the world. For non-technical and advanced users aspiring to extend beyond Excel’s built-in feature set, we’re proud to announce the easiest and most productive tool for doing so: Wolfram CloudConnector for Excel, now available to anyone running Excel on a Windows system. You can access the advanced computational power of the Wolfram Language for your data directly from your spreadsheets.

Simple Function Calls

We’ve made it easy for anyone to start using CloudConnector for Excel using the Wolfram Excel function, which allows you to call native Wolfram Language code directly in an Excel cell. For example, CurrentDate in Excel will get you today’s date:

=Wolfram("CurrentDate")

=Wolfram("CurrentDate")

We can also add additional parameters to the Wolfram function. These values get slotted into the expression. RandomWord can take additional parameters, such as a number, which will generate that many words:

RandomWord
&#10005

RandomWord[5]

So in Excel, we can write:

=Wolfram("RandomWord",5)

=Wolfram("RandomWord",5)

Even though we have written this in a single Excel cell, the output fills out into a column.

Mixing Wolfram Language and Excel Code

CloudConnector automatically converts the Wolfram Language output to be displayed in Excel. If you wanted to get all the business days for the next 10 days, you would use the function DayRange:

DayRange
&#10005

DayRange[Now, DayPlus[Now, 10], "BusinessDay"]

Now let’s run this expression in a spreadsheet with the Wolfram function, applying quotes and escaped quotes as necessary. We add an “&” on the end to make the expression a pure function:

=Wolfram("DayRange[Now, DayPlus[Now, 10], ""BusinessDay""]&")

=Wolfram("DayRange[Now, DayPlus[Now, 10], ""BusinessDay""]&")

Notice how the dates are converted to an Excel format. This is an example of the automatic conversion from the Wolfram Language.

You can also pass data stored in a spreadsheet cell as an argument to this function:

=Wolfram("DayRange[Now, DayPlus[Now, #], ""BusinessDay""]&", B2)

=Wolfram("DayRange[Now, DayPlus[Now, #], ""BusinessDay""]&", B2)

Any update to the cell being used as an argument (in this case, B2) will trigger recalculation of the formula in Excel.

Centralize as an APIFunction

Often you will want to store the code outside the spreadsheet, either because you don’t want users to see or edit it, or because you want to be able to push centralized updates to many users simultaneously. Deploying the code as an API and then calling it from the spreadsheet addresses this need.

Converting the Wolfram Language code we had before into an APIFunction only requires some minor changes:

CloudDeploy
&#10005

CloudDeploy[
 APIFunction[{"x" -> "Integer"}, 
  DayRange[Now, DayPlus[Now, #x], "BusinessDay"] &, 
  AllowedCloudExtraParameters -> All], "MyDayRangeFunction", 
 Permissions -> "Public"]

There is one parameter, "x", that can take an integer. The APIFunction is deployed as a CloudObject with the name DayRange.

An Excel user can access this API with the WolframAPI function:

=WolframAPI("MyDayRangeFunction",Parameter("x",C2))

=WolframAPI(

This formula evaluates entirely in the cloud. The source code is never seen by the caller of the API.

Advanced Computation, Minimal Code

With additional knowledge of the Wolfram Language, you can develop powerful Wolfram APIs, which would normally require a long and tedious development process in other systems.

Here, an APIFunction calculates the shortest tour of the 20 largest cities in a country and displays the result:

CloudDeploy
&#10005

CloudDeploy[
 APIFunction[{"Location" -> "Country"}, 
  Module[{cities, tour}, cities = EntityList[
EntityClass[
      "City", {
       EntityProperty["City", "Country"] -> Slot["Location"], 
        EntityProperty["City", "Population"] -> TakeLargest[20]}]]; 
    tour = FindShortestTour[GeoPosition[cities]][[2]]; 
    GeoListPlot[cities[[tour]], GeoLabels -> True, 
     Joined -> True]] &,
AllowedCloudExtraParameters -> All], "ShortestTourFunction", 
 Permissions -> "Public"]

This is an APIFunction that takes a single parameter, "Location", which must be a "Country":

=WolframAPI("ShortestTourFunction",Parameter("Location",B3))

Notice how the Excel formula creates an image. This is specialized functionality built for CloudConnector that allows you to make changes to spreadsheet values to trigger updates to this image. With this short amount of code, you can connect your spreadsheets to the full computational power of the Wolfram Language.

Wolfram CloudConnector for Excel creates a user-focused, feature-filled link from Excel to the Wolfram Cloud, while cutting developer time for more advanced computation. CloudConnector is a free plugin that runs on Excel for Windows. It does not require any additional Wolfram technology to be installed—just an appropriate Wolfram Cloud Account to create APIs. CloudConnector for Excel is available now for use in both the Wolfram Cloud and Wolfram Enterprise Private Cloud.

Comments

Join the discussion

!Please enter your comment (at least 5 characters).

!Please enter your name.

!Please enter a valid email address.

11 comments

  1. Although you refer to “escaped quotes”, I do not see an escape sequence in the example within the Excel input — rather I see two quote characters in sequence. Is Excel not displaying the \ character? If so then perhaps that is on Excel to fix, but it is quite confusing to the user.

    Reply
    • Hi David, In Excel the easiest way to put a double quote into a string is by adding 2 double quotes sequentially. There are other ways to do this but its even more confusing. Anthony

      Reply
    • Using double quotes is indeed how you escape quotes in Excel (not using a backslash)

      Reply
    • I think the doubling of the quotes counts here as the escaping mechanism.
      “Escaping” does not necessarily refer to a backslash sequence. It has the more general meaning of making a character “escape” from the way it would normally be treated in a certain context. Prepending backslash and doubling are two frequently used mechanisms for implementing this general idea.

      Reply
  2. Mathematica is astonishing! Is there any API available also to Google Sheets?

    Reply
  3. This is great! Thank you.

    Can you give an example of how to call Wolfram from within Excel VBA? I.e. not directly from within a worksheet cell but from a module or class written in Excel VBA.

    Reply
  4. For me, your example code for ShortestTourFunction does not work. If I copy it into my notebook, I get:

    CloudDeploy[
    APIFunction[{“Location” -> “Country”},
    Module[{cities, tour},
    cities = EntityClass[“City”,
    {EntityProperty[“City”, “Country”] -> Entity[“Country”, “Germany”],
    EntityProperty[“City”, “Population”] -> TakeLargest[20]}];
    tour = FindShortestTour[GeoPosition[cities]][[2]];
    GeoListPlot[cities[[tour]], GeoLabels -> True, Joined -> True]] &,
    AllowedCloudExtraParameters -> All], “ShortestTourFunction”, Permissions -> “Public”]

    1. Germany is hardcoded. I fixed that by:
    EntityProperty[“City”, “Country”] -> #Location

    2. GeoListPlot[cities[[tour]],… gives a Part-Error. It should read:
    GeoListPlot[EntityList[cities][[tour]],…

    Reply
    • My apologies for this Werner and thank you for pointing this out. The correct code should be:

      CloudDeploy[APIFunction[{“Location” -> “Country”},
      Module[{cities, tour}, cities = EntityList[EntityClass[“City”,
      {EntityProperty[“City”, “Country”] -> #Location,

      EntityProperty[“City”, “Population”] -> TakeLargest[20]}]];
      tour = FindShortestTour[GeoPosition[cities]][[2]];

      GeoListPlot[cities[[tour]], GeoLabels -> True, ImageSize -> Large,
      Joined -> True]] & ,
      AllowedCloudExtraParameters ->
      All], “ShortestTourFunction”,
      Permissions -> “Public”]

      Reply
  5. Ok, Anthony, we agree.

    Your Excel-Connector is really great and very helpful.

    May I remind you of my question from above? “How to call Wolfram from within Excel VBA? I.e. not directly from within a worksheet cell but from a module or class written in Excel VBA.”

    I think it just needs a wrapper function or class callable by VBA analogous to your spreadsheet function “WolframAPI”.

    Reply