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.
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.
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.
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.
