Running Python with Excel… or In Excel

We have talked about extending Excel with Python. there are a number of reasons you might want to extend Excel with Python. Most of these have to do with getting functions and capability that you just can’t access within a spreadsheet environment.

Examples we have given before include simulation, optimization and forecasting. For 25 years or more, Production-Scheduling.com has been about planning and scheduling in Excel. We do not add another programming environment lightly. Just because you can do something in Python that you already do in Excel doesn’t mean you should.

There are inevitably going to be additional complications and skills that you’ll need to run Python independently or alongside Excel. We are going to start sharing a number of ideas that can be effectively extended or replaced by Python. In order to support this, I’d like to provide you with a guide for getting started with Python and Excel.

The example we’ll use is a project called PyPs-Algorithms, which is our repository for tools that you may use in a number of different projects that can demonstrate the integration of Python and Excel.

Downloads

The Planning Algorithms in Python and Excel require a new way to give you a download. Previously, the downloads we provided were a zip file that comes directly from our website server. Whilst this is quick and convenient, it has a number of drawbacks. The main one is that the download is static. For projects that are being constantly updated, it is preferable to get a link to the current version rather than the version that we uploaded. Any Python code is going to come to you from GitHub rather than our own website. When we update the Python code, it gets automatically synchronised to GitHub, which is our version control system. This means that you know that you’re getting the most up-to-date version. We might look at sharing Excel files in this way later on, but primarily GitHub will be used for sharing Python code and associated workbooks that need to work with it.

https://github.com/kinetica-partners/pyps-algorithms is the repository that has the examples that we’ll use for this tutorial. It includes simple algorithms that cover a variety of different uses within planning and scheduling for supply chain. There is currently a function for calculating the total run time through a calendar. There is a bomb explosion tool, and there is a forecasting function. This repository is going to be updated from time to time, so you may find that it covers more than this.

Go to this link: https://github.com/kinetica-partners/pyps-algorithms and then “<> CODE ” and download the zip. More instructions below for setting it up. There is also an option to synchronise with this repository by installing GitHub on your machine. For the purpose of this tutorial, I’m going to assume that we have not used Python before and we will use the easier option to get started which is downloading a zip file. In this context, it is exactly the same as downloading a zip file from our website. The main difference being that you’re getting the latest version.

Running Python with Excel

You need 3 things to run Python:

1. Python code files (.py modules)
2. Python runtime environment
3. Dependencies (third-party packages)

We have solved the Python code files requirement by giving you the GitHub link. That’s number one ticked off.

We can now talk about the Python runtime environment. There are three main options for running Python with Excel.

1. Install Python (and dependencies) on your local machine.
2. Use a cloud-based python service, like Microsoft’s Python in Excel on Azure.
3. Use an Excel add-in that supports Web Assembly (WASM), like xlwings Lite.

Your computer probably does not run Python without first installing the Python runtime environment. Unless you are on Linux or have an older Mac OS version that came with Python installed, then you will need to install or otherwise access a Python runtime. It is free to download and instructions are provided below.

Python Runtime Environment and Dependencies on Your Local Machine

Python projects usually have dependencies. These are open source packages that provide functionality that is not included in the standard library that comes when you install Python.

If you’re going to have Python running locally on your machine, you’re also going to have to install the dependencies that are required for the project to work.

There is a bit of a learning curve when it comes to running python projects and a lot of that involves a basic understanding of dependencies and the environment you are running your Python code. You will probably have to start using command-line (CMD, PowerShell or Bash) which is perhaps less familiar than point-and-click. But a bit of learning goes a long way.

Install Python

This step you only need to do once. https://www.python.org/downloads/ and choose Windows or Mac OS or other. Unlike most software, it is not recommended that you download the latest version, unless you specifically want those new features. The open source ecosystem for Python packages is huge and diverse, not every package will be compatible with the latest. Currently, the latest version is 3.13 and I recommend that you download 3.12.x with x as the highest version you can see for that. If the latest version is way higher than that at time of reading, then go back one minor version (3.13.x goes back to 3.12.x).

Make sure that you choose the “Add Python to PATH” option when you install Python.

Download and save the Project

Go to https://www.python.org/downloads/windows/ and from the “<> CODE ” button you can download a zip. There will be a folder “pyps-algorithms-main” and rename this, avoiding the “-” if you are on Windows, and save to your preferred location. The Excel files are reading from this location: C:\dev\pyps_algorithms\data\current so use C:\dev\pyps_algorithms if you want to stay fully aligned with those.

Install a Package Manager like uv or pip

You will need this for easy management of the dependencies. I recommend uv as it makes the process easier. It does involve a more unusual installation process. Here is the process:

Windows

1. Open PowerShell (Search, PowerShell)
2. In PowerShell copy paste in the following code

powershell -ExecutionPolicy ByPass -c "irm https://astral.sh/uv/install.ps1 | iex"

Mac OS

1. Applications → Utilities → Terminal
2. In Terminal copy paste in the following code

curl -LsSf https://astral.sh/uv/install.sh | sh

Install the Dependencies

Now we are going to set up a virtual environment. Simply, this installs the dependencies with the correct versions and confirmed to be working with this project.

Windows

1. Open PowerShell (Search, PowerShell)
2. In PowerShell copy paste in the following code line by line

cd C:\dev\pyps_algorithms
uv venv
uv sync
.venv/scripts/activate

Mac

1. Applications → Utilities → Terminal
2. In Terminal copy paste in the following code line by line

cd /path-to-the-folder/pyps_algorithms
uv venv
uv sync
source .venv/scripts/activate

Now you are ready to run the project with a local version of Python

Using a Cloud-based Python Service, like Microsoft’s Python in Excel

When you use Microsoft’s Python in Excel, included in Excel 365 and 2024 versions, you don’t need to have Python installed. Any code that you include is sent to their cloud service, Azure, run there and the results sent back. Whilst this is easier for users, it has some limitations. These limitations affect the way that functions work- all inputs need to come from Excel cells and cannot be passed from function to function. The algorithms that we are looking at here would need to be extensively redesigned to work with this method.

There will be other projects that will demonstrate Microsoft’s Python in Excel, such as the first inventory simulation webinar. For these planning algorithms, we will have to use alternative means to run Python without an install.

There are other ways to run Python in the cloud using a browser. These often are based around interactive notebooks that allow you to run Python and see the results in the same page. These include: JupyterLab, Google Colab (uses Jupyter Notebooks) and Molab (using Marimo Notebooks). We will cover the use of notebooks in another blog post.

All this choice can sound overwhelming. The best thing to do is choose one method and then spend some time getting used to it. The other choices can then be seen to provide additional benefits to justify the time that it takes to learn them.

Excel add-ins that supports WebAssembly (WASM) – xlwings Lite

The alternative approach for running Python in Excel doesn’t use the cloud to run the Python code. Instead it uses something called WebAssembly. This provides a portable environment for the code to run. Often this is in the browser. Modern Excel Add-Ins, web Add-Ins that you get from the Microsoft store, use web browser technology to extend Excel in a web-enabled way. The Excel add-in, xlwings lite, uses this technology to run the Python code inside the Add-in and the code is stored in the workbook itself.

Again, there are a couple of options for running Python in Excel WebAssembly.

Installing xlwings lite

xlwings Lite is free to use. You need to have Excel 365 subscription version or 2021/2024 lifetime licence versions. Go here: https://appsource.microsoft.com/en-us/product/office/wa200008175 and install. That is it.

Once you have installed xlwings lite you can use these example workbooks. The xlwings lite add-in is now on the Home menu in Excel.

I believe that XL Wings Lite is the best option right now. So our examples will all use XL Wings Lite.

Alternatives to Excel Wings Lite

The prominent alternative for running Python in an Excel add-in is the Anaconda Toolbox for Excel. Anaconda is a company that provides a starter pack for using Python with data science. They also developed PyScript, which is the intermediary technology that bridges between Excel and WebAssembly. So they have good pedigree. The Anaconda toolbox for Excel is another good way of running Python in Excel without having to send all of your data over to Microsoft’s Azure service.

The reason that we choose xlwings Lite rather than Anaconda Toolbox for Excel is primarily because xlwings Lite gives you better access to the XL object model. In other words, it is more powerful in manipulating Excel files using Python.

Summary

In this rather complicated post, I have attempted to give you all of the options for running Python with Excel. And provide some recommendations for the best way to achieve each of those methods.

For a detailed description of how to use the example package PyPs-Algorithms, please see the future blog post that we will link to here.

More information and training for using Python and Excel

I am in the process of developing a training course for Excel users to get started with using Python alongside their Excel skills. If you are interested in joining this course, please sign up for the waiting list. People on the waiting list will be given a 20% discount when the course is released.