Last Updated on November 16, 2023
Table of contents
- What is Python in Excel?
- Why should I use Python in Excel?
- Why shouldn’t I use Python in Excel?
- What tools can I use to have Python in Excel?
- What is PyXLL?
- How to set up PyXLL?
- How to use PyXLL?
- What is Microsoft Python in Excel?
- How to set up Microsoft Python in Excel?
- How to use Microsoft Python in Excel?
- PyXLL vs Microsoft Python in Excel
- Which tool is better overall?
What is Python in Excel?
Python can be used in Excel to allow seamless integration of the Python programming language inside the Excel application. It provides many capabilities and improvements over what standard Excel offers.
Why should I use Python in Excel?
- Python in Excel tools offer ease of extendability
- Is easy to use
- Is very powerful
- Allows for easier explanation and usage by non-tech-savvy people
- Many businesses still hold a lot of data and Excel and use it day-to-day
Why shouldn’t I use Python in Excel?
- It constrains Python and the way you’re used to building Python features and apps
- Might not be the best combination for the solution to your problem
- If you’re good with Python and building projects, Excel is a nuisance
What tools can I use to have Python in Excel?
Python in Excel tools vary and the two most popular and maintained ones are Microsoft’s Python in Excel and PyXLL which is developed and maintained by Tony Roberts.
In this article, we will explore both of these solutions and compare them head to head over several facets such as:
- Setup
- Documentation
- Ease of use
- Performance
- Extendability
- Support
Although I will try to be as objective as possible, please ensure that you do your own research and choose the tool that fits your particular needs for your particular context.
What is PyXLL?
PyXLL is a Python Add-in for Microsoft Excel that is aimed at professionals and as a replacement for VBA. It is developed for better development experience and performance.
Website: PyXLL – The Python Excel Add-in
How to use PyXLL?
Here are some examples on how to use Python with PyXLL:
How to set up PyXLL?
To set up PyXLL, we will need to have Python installed. I will create a new environment and execute the following commands:
$ pip install pyxll
$ pyxll installIf you don’t know what the above code mean, then check this video on installing Python and Python packages: Windows version, Mac version.
This will take you through the installation steps. What is nice about PyXLL is that it offers you a free one-month trial without needing any credit card information. When you get to a prompt asking you for the license key say “n” if you want to use the trial.
If you want to get a license key, please navigate to the website pricing page and select the plan that works for you.
Now that we have PyXLL installed, you can check its status by running pyxll status. To configure your PyXLL, you can write pyxll configure.
In the folder you chose to install PyXLL into you will find some examples, alongside the PyXLL add-in and its configuration file. If the PyXLL add-in has been successfully installed, your Excel will load it automatically.
What is Microsoft Python in Excel?
Microsoft Python in Excel is a new feature in Excel that is developed by Microsoft which seamlessly introduces Python inside your Excel for the Microsoft 365 Insider program members. Its goal is to unlock all the capabilities of Python without the need to set it up yourself.
How to set up Microsoft Python in Excel?
To set up Microsoft Python in Excel, you will need to have a subscription to the Microsoft Office 365 suite. They also offer a one-month free trial if you want to test it out first.
To get the subscription, navigate to the following URL and choose one of the two plans that fit your use case the most. I personally have the Microsoft 365 Family plan on a yearly subscription but any type of subscription will be suitable.
When you decide, click the “Buy Now” button and follow through with the payment process. After that, you will be redirected and will be able to install Microsoft Office 365 which will automatically validate your license through your Microsoft account.
When done, all we need to do is to open the Microsoft Office 365 app and start Excel. Then, click on the “Account” button in the lower left section of your screen. There, you will see some information about your account settings.
Press on the Microsoft 365 Insider and join it. The Beta channel is the one I have and it gives you access to beta features as they come out. Once you join, it will trigger the update of Excel that will grab these features.
Now, open a new sheet and write =PY in a cell. It will show a tooltip that you should click which will prompt you to try out Python in Excel. To execute the code in the cell press CTRL+Enter and add a new line by pressing Enter.
If everything has been set up correctly, you will see Python in your Excel menu bar.
How to use Microsoft Python in Excel?
Here are some examples on how to use Microsoft Python in Excel:
PyXLL vs Microsoft Python in Excel
In the following sections, we will be comparing PyXLL to Microsoft Python in Excel on various points to see how they fair up to one another. We will also run a few experiments to see the differences in their performance and overall usability.
Let’s start by evaluating the setup process.
Setup
When it comes to the overall setup process. Both approaches are pretty straightforward. Saying that I’d give more points to Microsoft as Python works outside of the box and the setup steps are even fewer if you already have a subscription.
When it comes to PyXLL, it requires you to already have Python and to properly deal with environments.
Documentation
The documentation is very well-written and organized in PyXLL with various examples and custom features that Microsoft’s Python in Excel doesn’t have or offer. The Microsoft version features interactive examples and guides. They also have a short quickstart in the world of Python in Excel.
They also point to their free materials on Python, security, and more. Thus, I’d say that both products are on par with documentation. The main difference is that the PyXLL documentation is centralized and heavier due to the amount of breadth it covers with its features.
It is important to keep in mind that this is still a Beta version for Microsoft and that things might change by the time this article is published or by the time that you are reading it.
Ease of use
On the usage side of things, both versions do fairly well and give you the Python experience that you might be looking for. Although, PyXLL has more features that make your life easier which Microsoft currently doesn’t support or offer.
For example, these are the main differences I’ve noticed:
PyXLL
- Uses your local Python env and thus has access to your installed libraries.
- All code is written and managed outside of Excel itself which allows you to debug it with your preferred IDE and approach.
- You can write custom Excel functions without the need for special syntax.
Microsoft Python in Excel
- Runs on the Azure Cloud which doesn’t have internet access so it stops you from installing libraries.
- Currently, there are no tools for version control, debugging, testing, etc. You can’t use your IDE.
- Doesn’t allow for custom functions, all code is written inside the cell.
Due to the nature of PyXLL running locally, you get a performance edge as it can use your computer’s resources better and you don’t depend on the network traffic.
Saying that, if we are speaking about the pure “ease of use” Microsoft is easier to use as it requires fewer extra steps. When it comes to ease of use in terms of solving problems, debugging, and the development lifecycle, PyXLL clearly wins.
Performance
When it comes to performance, we see the main difference in where the code is being executed. This was touched on in the previous header. The best thing that we can do in this comparison is to run a test.
For the test, I’ll use 500 rows of financial data and run a calculation with both versions of Python.
I’ll repeat the measurements 5 times and take the average result. To calculate the time it takes, I’ll use the Fast Excel profiler.
Here are the specifications of the Machine that I’m using to run this experiment:
- 32GB RAM
- AMD Ryzen 7 3700X 8-Core Processor
Take note that I don’t have the best internet which is likely to be a confounding variable in this experiment.
The calculation that we’ll be running is the Black Scholes Option pricer. Below you can find the code for it using PyXLL, and Microsoft Python in Excel is exactly the same without the decorators and the PyXLL import:
import numpy as np
from pyxll import xl_func
from scipy.stats import norm
N = norm.cdf
@xl_func
def bs_call(S, K, T, r, sigma):
d1 = (np.log(S / K) + (r + 0.5 * sigma**2) * T) / (sigma * np.sqrt(T))
d2 = d1 - sigma * np.sqrt(T)
return S * N(d1) - K * np.exp(-r * T) * N(d2)
@xl_func
def bs_put(S, K, T, r, sigma):
return bs_call(S, K, T, r, sigma) - S + np.exp(-r * T) * KThe results are in and they don’t look good for Microsoft. PyXLL takes, on average, a second to calculate all the rows, while Microsoft takes 4 minutes. Now imagine having a usual finance dataset that can have thousands of rows!
Not only that, but if you change a single row value the Microsoft version will run all the calculations again instead of just that row. This requires improvements.
In terms of performance, PyXLL is clearly the winner.
Extendability
When it comes to extendability, PyXLL is clearly the winner for now. This is due to the fact that it uses Python from outside of Excel and allows us to transform any of your Python functions that use any libraries into something that you can use in Excel.
Microsoft’s version takes the Python in Excel more hardcore and everything is written inside Excel cells themselves and it currently doesn’t support the installation of libraries.
Support
In terms of support, we need to acknowledge that there is a team of people working on this at Microsoft while PyXLL is solely supported by Tony Roberts. Thus, whilst there is Tony, there is PyXLL and Microsoft will likely stay with us for tens of decades to come.
This also means that there are more resources for support at Microsoft. This doesn’t mean that they will be faster, more diligent, or friendly at giving that support. We all know that large companies tend to move slower than smaller teams and/or solo devs.
Which tool is better overall?
Overall, the tool to use will depend on your personal preferences and tech-savvyness. If you are a VBA dev that is very new to Python, or a Python beginner starting with Microsoft Excel’s version of Python will have a lower barrier to entry.
If you are comfortable with Python and development, PyXLL will definitely be the tool that you should go for if your problem requires Python running in Excel.
If Microsoft steps up the game and makes improvements, we might see the tables flip.
