Anaconda Code - Anaconda

This feature is currently in beta.

Anaconda Code empowers you to write Python or R code and run it locally, directly within Excel. This gives you flexibility and control over the environment in your workbook, allowing you to add and remove packages as needed, all while keeping code and data securely within your workbook. Anaconda Code operates independently of Microsoft’s Python in Excel feature.

Initializing Anaconda Code

Anaconda Code is included in the Anaconda Toolbox installation.

When you first launch Anaconda Code, you’ll be asked to sign in to your Anaconda account.

If you haven’t created an Anaconda Code cell yet, you’ll be asked to create one.

To get started, choose the language for your new Anaconda Code cell, set the default link mode, select the default output mode, and then click Create Code Cell.

Once you’ve selected a location for your new Anaconda Code cell, use the editor to start writing and running code.

Understanding Anaconda Code

Let’s take a look at the different elements within Anaconda Code using the Home tab for reference:

Home

Create and run Python or R code

Imports and Definitions

Customize the code that affects all code in your workbook and view script logs

Environment

Manage the packages and Pyodide or WebR version for your coding environment

Settings

Modify the default settings for running code

Account

View profile, subscriptions, sign out, and app details

Help

Access bug reporting, documentation, community forums, and privacy policy links

Active Code Cell Reference

The active code cell where your code will run

Linking

Toggle cell linking between isolated and linked modes

Cell Output

Choose whether to output cell values as an Excel value or an Anaconda Code object

Language

Select Python or R for the code cell

Delete

Delete the code cell

Copy

Copy the contents of the code editor

REF

Create a reference to data in the worksheet.

Run

Run the code in the active code cell

Running code

Create an Anaconda Code cell that can run Python or R code using the following steps:

From Home, click , then select a cell where you want to insert your code.

If you’re already in the code editor, select more next to the active code cell reference, then Add New to create a new code cell.

Subsequent code cells will be in the same language as the previously created one. To change the language, first create a new code cell, then change the code cell’s language selection in the code editor.
Set the cell linking and output options.
Select Python or R as the code language for the cell.

If you change the language for the cell to a language you haven’t yet used, you might need to click Load Environment to load the new language’s environment for the first time.
Enter your code in the code editor.

(Optional) If you want to reference a range of data from your spreadsheet or an Anaconda Code object in your code, click REF, then select the range of cells or Anaconda Code cell.

Using the REF function

When you use REF to select data cells or Anaconda Code cells, Anaconda Code creates a REF function in your code that returns a list of lists. The Imports and Definitions tab includes the following pre-defined functions to help convert the returned list of lists to different data structures.

Function	Use case	Notes
`to_df(REF(<CELL_RANGE>))`	Create a DataFrame	`to_df` assumes your data has headers
`to_array(REF(<CELL_RANGE>))`	Create a NumPy array	`to_array` assumes all data is of the same type
`to_list(REF(<CELL_RANGE>))`	Create a 1D list	`to_list` handles wide (1 x n) or tall (n x 1) data

You can change the behavior of to_df(), to_array(), and to_list() from the Imports and Definitions tab.

Function	Use case	Notes
`to_df(REF(<CELL_RANGE>))`	Create a DataFrame	`to_df` assumes your data has headers
`to_array(REF(<CELL_RANGE>))`	Create a NumPy array	`to_array` assumes all data is of the same type
`to_list(REF(<CELL_RANGE>))`	Create a 1D list	`to_list` handles wide (1 x n) or tall (n x 1) data

You can change the behavior of to_df(), to_array(), and to_list() from the Imports and Definitions tab.

Function	Use case	Notes
`to_dataframe()`	Convert to data.frame or tidyverse tibble	Converts a list of lists to tabular format, using first row as column names, if available
`to_matrix()`	Convert to matrix	Converts a list of lists to a matrix structure
`to_colwise_list()`	Convert to column-wise list of vectors	Transforms row-wise data (list of lists) into column-wise format
`is_list_of_lists()`	Check data structure	Helper function that verifies if input is properly structured as a list of lists

You can change the behavior of to_dataframe, to_matrix(), to_colwise_list(), and is_list_of_lists() from the Imports and Definitions tab.

Click Run. The cell will display the return value of the last evaluated expression. Your changes are automatically saved whenever you re-run the code.

If you write code that doesn’t have a return value (for example, you define a function but don’t call the function) and click Run, the cell will display </>NoneType.

Editing code

Do not edit your code in the cell itself; instead, modify and re-run your code directly in Anaconda Code.

An Anaconda.com account is required for users to edit shared code.

From the Home page, click more on the code you want to edit.
Click Edit in full view to open the edit view.
Adjust your code, then click Run.

Managing the environment

Anaconda Code hosts a single, self-contained environment, which manages the back-end software packages that enable you to run Python or R code within your Excel workbook. You can manage software packages within this environment to extend your code’s processing, visualization, and analytical capabilities, and even select the version of Pyodide (the WASM engine used by PyScript) or WebR (the WASM engine used by WebR) that you want to run.

Choosing a Pyodide or WebR version

The latest version of Pyodide or WebR is used by default for all new spreadsheets. For existing spreadsheets, the Pyodide or WebR versions and packages necessary for your code are pinned to the environment.

You can switch versions using the following steps:

From the Environment tab, click Edit.
Select the Pyodide or WebR version.
Click Save Changes.

A warning will appear if changing the version might result in conflicts with the installed packages. Click Confirm Update to proceed or Cancel to revert to the previously selected version.

Managing software packages

From the Environment tab, click Edit.
To add new packages, click Add.
(Optional for Python) Click the down arrow to add from either PyPI, the PyScript app, or a direct download link to a Python wheel (.whl).
Search for the package name, then click Add beside the package you want to add.
Once you’ve added all the packages you want to include, click Add Packages.

Packages that contain compiled code might not be compatible with the PyScript or R WASM engine. For more information, visit PyScript.net or r-wasm.org.

To remove a package, click Edit, then click Delete beside the package you want to remove.

Customizing code initialization

You can think of Anaconda Code’s Imports and Definitions as an initialization file for your code or like the first cell in a Jupyter Notebook. All code in this section is available to all cells, whether they are run isolated or linked.

Imports

Definitions

Creating user-defined functions

User-defined functions (UDFs) allow you to write Python or R functions and call them directly from a spreadsheet cell.

UDFs in Python

Creating and calling a UDF

From the Imports and Definitions tab, decorate a function with @UDF, as shown in the following example:
Python UDF Example
```
@UDF
def my_custom_function(x, y):
    return x ** y
```
Click Apply.
In an open cell, enter =ANACONDA. If you added the example above to your definitions list, the option to call ANACONDA.MY_CUSTOM_FUNCTION appears in the dropdown.
Arrow down to ANACONDA.MY_CUSTOM_FUNCTION, press Tab, and then complete the function.
Use Ctrl+Enter (Windows)/Ctrl+Return (macOS) to run the code.

If you’d prefer the UDF uses a name other than the function name, use the name argument to provide a unique name. Set nested to False to remove ANACONDA. from the name.

Renaming Python UDF Example

@UDF(name="MYBANK.PORTFOLIO_ANALYSIS", nested=False)
def my_custom_function(x, y):
    return x ** y

Using range arguments

Specifying a UDF.Range argument tells Excel that the input of the function is a 2D range. Without specifying this, Excel will show a #CALC! Unliftable Array error if a 2D range is passed into the UDF. Parameters specified as UDF.Range will always be passed as a 2D array to the function, even if a single cell is passed in.

Example usage of UDF.Range:

Python Range Example

@UDF
def square_me(data: UDF.Range) -> UDF.Range:
    return [[val ** 2 for val in row] for row in data]

You can also add type hints for ranges. For example,UDF.Range[str].

UDFs in R

Creating and calling a UDF

From the Imports and Definitions tab, register the UDF with UDF.register() and pass the function as an argument, as shown in the following example:

The UDF must be registered after the function is defined.
R UDF Example
```
my_custom_r_function <- function(x, y) {
    x ^ y
}
UDF.register(my_custom_r_function)
```
Click Save and Apply.
In an open cell, enter =ANACONDA. If you added the example above to your definitions list, the option to call ANACONDA.MY_CUSTOM_R_FUNCTION appears in the dropdown.
Arrow down to ANACONDA.MY_CUSTOM_R_FUNCTION, press Tab, and then complete the function call.
Use Ctrl+Enter (Windows)/Ctrl+Return (macOS) to run the code.

If you’d prefer the UDF uses a name other than the function name, register the function with the UDF, then use the name argument to provide a unique name and set nested to False to remove ANACONDA. from the name.

Renaming R UDF Example

my_custom_r_function <- function(x, y) {
    x ^ y
}
UDF.register(my_custom_r_function, name="MYBANK.PORTFOLIO_ANALYSIS", nested=FALSE)

Using range arguments

Setting the range_args parameter tells Excel that the input of the function is a 2D range. Without specifying this, Excel will show a #CALC! Unliftable Array error if a 2D range is passed into the UDF. Parameters specified as range_args will always be passed as a 2D array to the function, even if a single cell is passed in.

Example usage of range_args:

R Range Example

my_custom_r_function <- function(data) {
    sum(data)
}
UDF.register(my_custom_r_function, range_args=c("data"))

Adding function documentation

To add documentation to your function, use the doc parameter:

R Documentation Example

my_custom_r_function <- function(x, y) {
    x ^ y
}
UDF.register(my_custom_r_function, doc="Computes x raised to the power of y.")

Modifying workbook settings

While you can adjust the settings for running code in your workbook on a case-by-case basis when creating and editing code, you can also assign default settings from the Settings tab.

Cell linking

Run Isolated

Run Linked

Cell output

Output	Description
Excel Values	When outputting a DataFrame, array, list, and so on, the values will “spill” to fill the required space. If the spill were to overwrite cells containing data, the cell displays a `#SPILL` error.
Local Code Object	For certain object types, you can view the contents in a “Card View” by clicking the cell. You can reference this cell and the returned object like you would any other object.

Troubleshooting

If you encounter an issue that is not listed here, you can obtain support for Anaconda through the Anaconda community forums or by opening a support ticket.

Error installing functions

Cause

Solution

Tools

​Initializing Anaconda Code

​Understanding Anaconda Code

​Running code

​Editing code

​Managing the environment

​Choosing a Pyodide or WebR version

​Managing software packages

​Customizing code initialization

​Creating user-defined functions

​Modifying workbook settings

​Cell linking

​Cell output

​Troubleshooting

​Error installing functions

Initializing Anaconda Code

Understanding Anaconda Code

Running code

Editing code

Managing the environment

Choosing a Pyodide or WebR version

Managing software packages

Customizing code initialization

Creating user-defined functions

Modifying workbook settings

Cell linking

Cell output

Troubleshooting

Error installing functions