Python is one of the most popular programming languages today, loved by businesses and students alike and Excel is an essential tool to organize, manipulate and analyze all kinds of data. But, until now, there hasn’t been an easy way to make those two worlds work together.
Today, we are excited to introduce the Public Preview of Python in Excel – making it possible to integrate Python and Excel analytics within the same Excel grid for uninterrupted workflow.
Python in Excel combines Python’s powerful data analysis and visualization libraries with Excel’s features you know and love. You can manipulate and explore data in Excel using Python plots and libraries, and then use Excel’s formulas, charts and PivotTables to further refine your insights.
The preview is available now.
With Python in Excel, you can type Python directly into a cell, the Python calculations run in the Microsoft Cloud, and your results are returned to the worksheet, including plots and visualizations.
https://techcommunity.microsoft.com/t5/excel-blog/announcing-python-in-excel-combining-the-power-of-python-and-the/ba-p/3893439
FYI it runs Python in the cloud. :-/
yeah.. my thoughts exactly. Its needed and nice, but I can’t really make use of it because of that. Same with Google docs api integration. As a stand alone office worker with basic programing language ( heck python is basically default cs 101 in the US), I should be able to script quickly on a spreadsheet without getting the IT department involved. Its such a downgrade from vbscripting locally. Open office kind of works now with python but it was a bit clucky to get it set up last time I tried it.
Ah I misread it, it doesn’t require a in cloud set up like google does, so thats better. But would still like it to be local.
drcouzelis,
It’s common for applications now days include python, but a remote cloud dependency? That’s horrible! For excel to have to phone home to microsoft to run python functions is moronic. It introduces serious new security threats, completely unnecessary points of failure, potentially requires data quotas. If internet isn’t available, like traveling, too bad, you’ll need the internet even for local work.
The feature itself is cool, but the way it’s being implemented is regressive handing more control over our files to microsoft. Just like all cloud services, users don’t control the terms, including EOL and future subscription fees. This is where they are headed.: “That’s a nice spreadsheet you’ve got there. The microsoft cloud features you’ve been using for free will be discontinued but here are subscriptions available to you that will let your spreadsheet continue working.”
Yes it is tricky.
Python is nice, but also comes with its own dependency hell.
Ever tried to do data analysis on Jupyter locally? Need to maintain separate “environments” using tools like mamba. And this is not only per tool, but very possibly per notebook / project as well.
(This package needs tensorflow==2.0.1, whereas this other package needs tensorflow<2.0. And so on. What mamba (and conda) does it solving the set of equations to find a subset of versions that are compatible with each other).
Anyway, they have two choices:
1. Bundle Python in Excel with very specific package versions: "This is Python for Excel v104, including these 72 packages", and push weekly updates for the inevitable security fixes in one or more of these packages.
2. Do it remotely
I would prefer (1) of course. But I can also see them taking the easy way out.
sukru,
I don’t think microsoft needs to reinvent the wheel at all. They just need basic excel integration and if the user wants additional dependencies they can install them themselves as they would for any other project. Regular python devs would be perfectly happy with this. If anything I think they’ll be upset that it’s not installed locally and the microsoft service will be restricted.
If you want python to make a soap or rest call to a private API, do you now have to provide microsoft’s servers access to your private API? That would be ludicrous. I think it could even fall fowl of the law (think HIPAA or GDPR). Python scripts could end up unwittingly sending the data to microsoft. It would be one thing for microsoft to provide web services that users could call on, but forcing users to run their excel scripts at microsoft is awful and leaves users extremely vulnerable.
Alfman,
Turns out they are using conda: https://www.anaconda.com/excel
Yes, I agree there are GDPR issues, and other potential for data leaks. If I were to guess, I could design this to use ephemeral instances where nothing is saved (except for access logs for security).
That being said, I really wish they had a local option. Conda already runs on Windows:
https://docs.conda.io/projects/conda/en/latest/user-guide/install/windows.html
One reason I could think is actually security (in addition to following the lazy path): Python is too powerful to run locally, especially coupled with an eval() method. Unless they also build a very solid sandbox, using a virtual machine seems like a good idea. (again that virtual machine could have easily run locally instead of the cloud. ok not easily, but with some effort).
There’s no need to bundle anything, apart from an interpreter. Just create a venv per workbook and add a good interface for installing libraries.
On that note, I was going to try to use Python on LibreOffice Calc to compare, but I couldn’t figure out for the life of me how to write a simple script. A wasted opportunity to beat Microsoft on features.
Parodper,
Their integration seems to be deeper than just calling Python. It looks like there is direct integration with data analysis packages like pandas and matplotlib. Which means they cannot “risk” having the install fail on the client side.
(This is true even if they have a conda mirror. People can still mess the venv up locally).
That is of course assuming their target audience is Excel user who is a Python novice.
sukru,
They could easily have a clean stateless environment though, so that’s not a reason to not support local execution. To the extent that the environment can be customized, well logically the exact same “risk” your talking about for local environments applies to remote environments too, so again that’s not a good reason to deny users local execution.
Almost everything they can offer through python as a service could be done locally with less effort and less cost to marshal everything to their servers. This is not being done because it makes technical sense, it’s being done because microsoft wants to get users hooked into cloud services.
Running python in the cloud has very few upsides. Reducing CPU cycles is really the only argument I can think, but…
1) For simple scripts like those given in examples, the overhead of performing RPC to microsoft servers will actually use more CPU cycles/bandwidth/etc than just executing locally. It will be slower, use more electricity & bandwidth, and kill the battery faster.
2) For python scripts that are sufficiently complex to justify offloading, microsoft are not going to subsidize free compute indefinitely. It just isn’t viable to provide remote python long term without a business model. MS are either planning to convert heavy users to recurring subscriptions, or else the python service is guarantied to be shut down in the future.
So there are tons of downsides and the one upside has caveats. MS obviously knows that not supporting local execution is a real con, but they’re doing it for business reasons.
sukru,
That’s probably best.
It’s already been done though and it’s even open source:
https://chromium.googlesource.com/chromium/src/+/HEAD/docs/design/sandbox_faq.md
A similar solution would work for python scripts. For efficiency reasons, it’s likely they’re spawning isolated python processes and not full VMs for every user. I bet the same process isolation they’re using on their servers would work locally too. I think it’s much more likely that microsoft did this intentionally to gain future leverage over users than because they couldn’t implement security on their own operating system.
Aflman,
Yes, they could have taken their time, and implemented that… on Windows, macOS, iOS, and Android.
Thinking back, this could be the reason, they now support all these platforms. In addition to these they also have a web version, which should be compatible with the new feature. Meaning they already have to implement this in the cloud, why not use the cloud version instead of native on the other 4 supported platforms?
sukru,
There’s lots of applications embedding python, if they can figure it out, so can microsoft. Running remotely in the cloud could be an option, but it shouldn’t be mandatory. I feel you are too quick to give corporations the benefit of doubt, but Microsoft in particular has a history of power grabbing behavior so I for one won’t give them the benefit of doubt. Shoving unwanted cloud features down users throats is what corporations are doing now days and I know I’m not the only one complaining about it, the comments in the article are filled with these complaints. I’m pretty sure many of microsoft’s own engineers internally would have disliked this as well.
Alfman,
It is quite the opposite. Having seen so many middle management forcing projects to take wrong turns, I can see the pattern here (and the chances increase as you have 8+ long management chains).
Of course, Microsoft has many talented engineers, and strong teams with good managers.
However how many of them are willing to go over, let’s say their Director, who chose the quick and easy solution, to the VP, and ask the project to increase in cost, require more manpower, and will be finished two quarters later?
sukru,
Except that running user python scripts on microsoft’s servers is clearly much more expensive and complicated than allowing users to run it locally. At the end of the day,, when the costs are distributed across millions of users, running python locally is not only better for most customers,, but it will be *far* cheaper for microsoft in the long run too. So even a cost based argument favors users running python themselves. For better or worse, a remote python service has high operating costs and will not ultimately be sustainable medium & long term unless microsoft’s plan is to charge for it to make back the costs microsoft incures by engineering the python feature to run remotely.
So if anything, the director had to be sold on significantly higher costs of running a remote python service. The most obvious explanation is that microsoft’s plan is getting customers hooked into onto their cloud services and then to corner them into subscription services down the line. After all, the industry has been trying to figure out how to push customers into monthly subscriptions rather than owning perpetual licenses.
Alfman,
You are giving them too much credit. Do you think middle management will worry further than one or two quarters out?
They would have received their bonus, and possibly promoted to a new role by then.
sukru,
Yes, absolutely they do. It doesn’t make sense to spend money needlessly without a business model. Even at the individual level they don’t want to diminish their stock options. Microsoft are investing in cloud, even if it means not giving customers exactly what they want like local python scripting (which costs microsoft much less). I think it’s logical to deduce microsoft’s long term business plan is to phase customers into subscription services rather than to subsidize compute costs indefinitely. They’re willing to subsidize the service for a limited time to get users to get hooked before turning around and demanding payment. Time will prove whether this pans out, but if remote compute time doesn’t generate revenue, it will ultimately be canceled. So consumers would be wise to consider that these as negatives of remote compute dependencies and server compute costs. If they fall for the trap that microsoft is setting. they’re eventually going to pay for it one way or the other.
Alfman,
I think we won’t be able to agree on whether laziness/opportunism or corporate greed is driving these decisions… 🙂