This was a nice surprise to see today as an Ex-Excel developer who worked on trying to bring Python to Excel (and, I guess, failing ;)).
7+ years ago I had the option of leaving the Excel team. My then boss’s boss knew I had an interest in bringing Python to Excel and offered me a chance to tackle it if I chose to stay. What was meant to be a 6 month project turned into a ~3 year project, the Python part faded away and we ended up enabling JavaScript Custom Functions in Excel instead.
For Python we were also running ‘in the cloud’ (AzureML v1), although there was some back-and-forth on if we should run locally. I think what made the Python part disappear was our partner AzureML team re-orged, re-released, re-hired, we lost a PM and our work caught the attention of another partner team who realised they could use our code to execute their JavaScript out-of-process. And so I spent a lot of time ensuring that feature was successfully shipped at, I guess, the detriment of Python.
I had a lot of help from some strong engineers and learnt a lot. The core of the work was modifying the calculation engine of Excel to allow functions to compute asynchronously, allowing the user to continue working on other parts of their spreadsheet while the remote endpoint (be it JavaScript, Python or something else) was computing. Previously the spreadsheet would lock up while calculations were running, and that wouldn’t be cool for long-running unbounded calculations. Have to wonder if any of the stuff we built made it into this new feature.
Super great to see this and look forward to trying it out.
Thanks for your work! The async JS functions are amazing and allowed me to ship rock-solid Custom functions with xlwings and Python. The problem with Office.js is that it’s just simply too hard to get started with all the npm bloat for the average Excel user (even more professional devs struggle). Well, I am pretty happy how I am saving users from having to use Node.js: https://docs.xlwings.org/en/latest/pro/server/officejs_custo...
Oh this must be Felix! I saw and played with xlwings many years ago and was impressed :)
Agreed with the friction around getting started. I would like to see it reduced. It’s cool to see you using them to wrap Python functions - I have done something similar before myself, and still need to get around to shimming Haskell into excel at some point.
Another thing I don’t like is how JavaScript Custom Function cell formulas have a horrible string after the _xldudf marker, which becomes noticeable if your workbook doesn’t/can’t load the custom functions addin (or you unload the addin)
> The problem with Office.js is that it’s just simply too hard to get started with all the npm bloat for the average Excel user (even more professional devs struggle)
IMO deployment is an even harder problem. The Office team makes it incredibly difficult to deploy web add-ins (presumably for business reasons) and accordingly most companies I'm familiar with are still using the ancient, barely-supported COM+VSTO add-in models.
I don’t unfortunately. Given the more technical nature of the feature I’m inclined to agree Microsoft should make a bountiful supply of working examples.
> the Python part faded away and we ended up enabling JavaScript Custom Functions in Excel instead.
Nobody could have anticipated that GPT4 would author Python better than anything else.
Nonetheless, I kind of just want Google Drive except Excel, without adopting anything more than the Office ecosystem, that just works with simultaneous editing.
I wish it wasn't solely powered only by Microsoft Cloud and could support python running locally, but regardless I think this will still be huge and single-handedly modernizes Excel by a large margin.
This alone could eliminate the need for websites that just want this sort of data. I can think of a past project at a previous job I did building an analytics website used only by a handful of people internally that could have been just as well served with something like this, had it existed at the time.
> This alone could eliminate the need for websites that just want this sort of data
I catch flak for this all the time! Our users just want to be able to use Excel to free-form analyze their data. IT, of which I'm a part of, insists on building web apps to do that for them. They're never happy because all they wanted was Excel.
I keep explaining all we have to do is build a data mart built using SQL Server, and use our ETL tools to keep it populated with the data they need to analyze. Don't let them anywhere near the source data. They keep looking at me like I have ten heads. Instead they want to launch multi-million dollar projects to bring Tableau to our users - and they don't want it! It's asinine!
Our company uses Power BI for this very reason. Most of our users are content with what's available in the reports we've built in Power BI. However, a few power users directly connect to the datasets we've created (that also feed the Power BI reports) and can mix and match that data in Excel.
Excel will import datasets from the Power BI client, and you can refresh the data at any time. The nice part of this arrangement is that the whole model is imported. All the relationships between tables, all the added calculation columns, all the measures are there for use by the end user in Excel.
Power BI has been a great solution for our company (~1000 employees).
To be fair, Tableau Desktop, Tableau Prep, and Tableau Cloud are great if expensive products. With Tableau Prep, you get a visual ETL tool. Being technical, I prefer SQL, but for business folks Tableau Prep is more of a REPL so they see what their actions are going to do. They can schedule these Prep flows in Tableau Cloud which will ETL and produce daily datasets which users can then interact via the dashboards on Tableau Cloud.
IMO, Excel is on the right path with out of the box connectors to Salesforce, Azure databases, etc. What it really needs is full blown SQL with REPL output so the users can see the effects of their SQL. (The way Tableau Prep does it is that it defaults to a sampling of rows to output immediate results). There used to be a Microsoft service called Excel online but it was another thing you had to buy and was super confusing. Excel needs to let users schedule their ETL/datasets in SharePoint online and then let other users subscribe to it. They can call it Excel Super+ and charge a small fortune. I am positive there are people with their wallets open.
Google’s ecosystem makes this incredibly simple.. all you need is to populate BigQuery with the datasets of interest, and Sheets can pull it in via a connector that’s configureable in a GUI. I sincerely hope my org never migrates to MS for this reason alone.
> I keep explaining all we have to do is build a data mart built using SQL Server, and use our ETL tools to keep it populated with the data they need to analyze.
I can relate and have experienced this. The result was that they would end up exporting from a series of Tableau/Looker workspaces, and importing into excel and doing their analysis there.
Then continually making enhancement requests on the Tableau/Looker workspaces to get any additional data they need for their excel work.
Then of course, the excel document and its multitude of versions would get emailed around until it hit the attachment size limit, or if there were too many version of it to figure out which one was "actionable".
We finally figured out what they were doing when the enhancement requests for the reporting workspaces got too bizarre.
How do you lock down Excel - that is the issue we have where I work and the main reason excel is strongly discouraged.
There are two main issues my org has battled with:
1.People basically turn excel into their own mini databases which causes a support nightmare.
2. This is strongly related to 1) Data provenance is a nightmare because so many people were passing around excel spreadsheets it became very difficult to track down the source of the figures people were using. User A passed their spreadsheet to user B who modified it passed it to User C who further modified it, passed it to User d from there some figures ended up in front of a manager and now we have a problem...
It's not so much a problem with Power users those are the types of people using python (or R) it's the tier of users below that level that cause the damage with excel.
You're probably asking for something impossible. It's common for the raw data to not fit in excel. You have to do some sort of aggregation or filtering, which means having that app where you aggregate and filter.
Almost all these tools support exporting to Excel or CSV, so they can do whatever they want in Excel after that.
> I keep explaining all we have to do is build a data mart built using SQL Server, and use our ETL tools to keep it populated with the data they need to analyze.
I mean, that's like >90% of building a CRUD web app. If you truly only need the R in CRUD, your approach sounds reasonable. Otherwise, just go the extra distance and build a CRUD app with csv export / direct SQL access.
We use Excel for a bunch of "critical" tasks as defined by the financial regulator, which means any of their dependencies also have to be assessed to ensure they meet the required level of stability.
What this means is we'd need to go from just requiring a laptop and a copy of the spreadsheet to requiring redundant internet connections and validating Microsoft's backend meet the regulator's requirements. I'm pretty sure that would unfortunately rule out using it.
I think it's similar to the current generative A.I. trend.
The companies I work for while consulting (mainly financial and health organizations) have been very cautious about any company or PII data going to these A.I. programs, and the cloud in general, and are looking to make their own local run generative A.I. LLMs for their employees to use.
By it's very nature these Excel documents will be filled with company data, so I can imagine these companies would also be a bit cautious sending those calculations to the cloud, especially if they can't control the flow of data from their I.T. departments via web applications.
But I'm just a software engineer at the end of the day, and don't professionally use Excel all that much right now (if anything I use it much more for my hobby of board game design). So I can't speak with too much authority on the matter. Just my observations.
It won't work offline, for one. I use Office exclusively without internet as otherwise who knows what private stuff it's sending back to Microsoft, which then becomes eligible for warrantless surveillance by the federal police.
I know it's old fashioned to want local software to only change things on my local file system, but if I didn't want that paradigm, I'd just use Google Docs.
The Cloud, and therefore the integration of as much as possible in it, is core Microsoft strategy.
It's about the recurring payments, the lock-in, and ultimately literally having the knowledge and business processes of the world economy on their own computers. If this is true, the reason why it is important to run data and scripts locally should be clear, and so is the motivation of your PMs to actively combat any such things.
But if you get a summary in your inbox and show it to your PMs let us know how that goes down.
Is there any technical reason excel couldn’t ship with Python? The license allows it [1] and it would probably solve the issue of all users being able to open any document like the cloud solution, but still run locally.
Can you just please fix the issue with date strings (such as "20230822090811") being converted into scientific notation? I've received literally hundreds of these documents over the past decade. And as long as this problem exists, I will keep receiving them.
Can you please just add a button - "unmess this document"? The number of man-hours I've wasted re-creating documents is countless. My job is to do other things not fix excel documents.
Not OP but I would never want to use this if I was running a security minded business. Why would it be okay to send out my entire dataset to the Microsoft cloud to run a few simple queries. How long is the data stored on your servers? Since it’s operating on the data directly, it’s not possible to anonymize or redact the contents of the data.
I can put this in an email if you want, but one good reason would be that my laptop is probably faster than the cloud compute that they are willing to give away to 365 subscribers. I can do much more inefficient things in a single cell if I know my laptop can pick up the slack :)
(Although, I'm guessing the reason they did it this way is so they could keep the execution model the same for Excel on the web? It's always running on the external service vs sometimes running locally and sometimes running externally.)
How about an actual API that lets you drive Excel from Python rather than the other way around?
With that API, it won't matter whether you want Python, Ruby, Lua, whatever, for your scripting language.
This is nice, but it welds a specific version of Python inside Excel and makes it a gigantic PITA to use your normal development tools on your Python code.
There are few key reasons for running in a hypervisor isolated container on Azure with no access to the Internet:
- We can guarantee a consistent experience for all users. Imagine having to maintain your own local distribution of Python and guaranteeing that it works with Excel as versions diverge over time? Yikes.
- We make it possible to share your Excel workbook with other users and have the calculation just work. That wouldn't work with random local installs of Python and users would be super frustrated by this.
- Security. Imagine opening an Excel workbook that can execute Python code running locally as you.
Also, I totally agree with your second point. Trying to write an internal app in Python that integrates with all of your existing IT infrastructure is an exercise in frustration at best. Excel is already part of the IT infrastructure virtually everywhere and is a programmable reactive canvas.
Disclosure: I work on the design team for the feature.
I agree with point 3, but points one and two don't make any sense because games have been skipping with Python scripting for decades and this is never a problem. The python interpreter is embedded into your program as a library and it doesn't have any dependency on the whatever python version the user installed.
Could you not achieve the same by shipping the required tools with Excel and running them inside a Windows Sandbox?
Isolation isn't as good, but it probably protects users against the threats you/they care about?
When you open a workbook that is using B show a warning before running anything. Then behind the scenes spin up a new venv that installs everything from requirements.txt and executes the code in workbook locally.
It running in Microsoft Cloud is precisely what I think will actually allow this to be useful functionality for my company in ways that macros aren't, because macros are seen here to present a vector for running malware on client machines, whose utility doesn't justify the risk.
Just wait for someone to run malware on your mystery cloud instance of Excel. Have you seen the cross-instance and cross-domain security holes Microsoft has been running into? The latest and greatest was on their Power Platform ("Excel-like"). Nothing like silently shipping your data off to a remote location for inspection by blackhats. Microsoft cloud security is torn apart and laying all over the floor right now...nevermind all the half baked features that let you get kneck deep in a project before you see the grande middle finger from project hellscape.
It sort of makes sense to limit it to the cloud, because making Python an integral part of the Excel file format would reduce its portability and introduce all sorts of compatibility headaches going forward.
If you've ever dealt with AWS Lambda you will know what a versioning and upgrading shit show this is going to be wherever you stick it, be that client or cloud.
I worked on a very early python integration used as a workflow DSL back in 1998 and they had to dump it and write their own DSL in the end.
Whether it runs in the cloud or locally, the included python source will be written for a specific runtime version and environment. If excel were to run python locally, it would presumably bundle an interpreter (+ any needed pip packages) rather than relying on whatever was installed locally.
The cloud execution model does still have an advantage in that it can retain old runtimes in perpetuity for backwards compatibility, though
Excel already has versioned file formats with significantly different affordances, and hasn't appeared to have all sorts of compatibility headaches as a result.
> It sort of makes sense to limit it to the cloud, because making Python an integral part of the Excel file format
Moving it from the cloud to local doesn't make it an integral part of the Excel file format. Whatever python service is running in the cloud could just run locally.
Oh the cloud part is going to be a huge deal breaker in so many industries. This will automatically be blocked by default at my work place for certain.
What's the difference between Python and VBA in that respect? Do you open Excel files now? Pretty sure that wild Office macros were once one of the main sources for computer herpes.
I too would love this, but I can tel you, even in a sandbox python would be very easy to abuse from a security perspective. Consider how much havoc on humanity (no exaggeration!) VBA Macros have caused.
I have to see some red flags here. The very nature and power of Excel comes from the fact that workbooks can stand alone. This means that the process is eternally tied to the cloud now. So when you, 3 years down the line, have to open a workbook from your deceased colleague, you are at the whim of the vendor supporting the existence of this integration.
Step one of any workload will always be "work out how to make the software work again" and that's terrible.
I know this because I have been in that situation several times in the last couple of decades with random Excel, outlook and word add-ins. And those don't even do the processing remotely!
> We can't even own the things we're wageslaving away at our desk for.
But we never owned those things we're wageslaving away at our desk for, that's why its called wageslaving. Whether our employers own them or rent them seems pretty immaterial.
I think this is easier to manage to be honest. They are using Anaconda distributions so if you tie each workbook to a distribution with possibility to update/rollback it makes things a lot easier than trying to manage a local installation.
Try explaining that to the dude who inherited the workbook in 9 months who doesn't know what Anaconda is, barely knows what Python is and was told that his job was Excel which he's really good at because he did a Udemy course on it 3 years ago.
> This means that the process is eternally tied to the cloud now.
Not only the cloud, also the subscription. If you cancel the subscription (note that the announcement mentioned it might be a paid module), don't have the subscription (because you got the Excel from a supplier), etc. you already have a problem using this Excel sheet.
I haven't used a non-cloud spreadsheet in probably 7 years. I also wouldn't use excel for anything that has a combination of high importance and long lifetime though. It's mostly for some brainstorming or thrown in a presentation as a screenshot.
> I haven't used a non-cloud spreadsheet in probably 7 years.
Last time I use a cloud spreasheet was a month ago, to download the content into Excel
> I also wouldn't use excel for anything that has a combination of high importance and long lifetime though. It's mostly for some brainstorming or thrown in a presentation as a screenshot.
It's funny but that's exactly how I see online spreadsheet: for unimportant things that may not work at any point in the future if the account is closed. However, the collaborative features make them nice for brainstorming.
This will be true whether it uses the cloud or not. Excel worksheets can already have external links (both on the Web and to other files). If those web resources or other files aren't available, you're out of luck! And the older ones may only open on software that no longer runs on modern machines.
This is why it's important to restore your backups once in a while, or in this case, validate your critical files.
It's an exacerbation of an existing problem - who has not been sent an Excel file and opened it to discover some Sharepoint (or whatever) link is missing.
The new competition to this market have gained market share by making workbooks not stand alone, it's a whole growing product segment. So they either have to evolve or die.
I was so excited about Python in Excel, until it said "run in the cloud." There just doesn't seem to be any reason for this except to tie you into their cloud service. It feels so much like protecting me from unsafe printer ink.
I'm generally a fan of MS Office products and try to give them the benefit of the doubt.
Please help me think of a plausibly good reason for requiring Python to run in the cloud, that is not just about lock-in.
These are red flags. M$ is forcing people into their cloud. Their hijacking of your local file system with onedrive is a great example that many people already ran into.
I think we are about 5 years overdue from bailing from M$. They need to be avoided. Every product from Windows to Office already has anti-consumer features.
Not asking customers to manage a local python installation is one thing, although I totally understand how excel + python power users would likely be comfortable bringing and maintaining their own python runtime.
There are a lot, I mean a lot apps that support scripting languages and don't need you to manage the language's runtime yourself. Blender's UI is entirely in Python. Civilization games have most game logic in Lua.
Probably because most users of excel don't have any experience installing and running Python which is even a pain in the ass for people that know Python lol.
You also have people using Excel on iOS, and Android devices. This allows them to use and read Python in their spreadsheets.
>Probably because most users of excel don't have any experience installing and running Python which is even a pain in the ass for people that know Python lol.
An embedded Python is about 10 MiB. I just checked. Excel could easily have its own separate copy.
there's always Grist. its not a clone of excel but its a spreadsheet/hybrid database so it does some similar things, but most importantly let's you use python for formulas and also has a self hosted option if you want to run it locally
Back in 2005, some friends and I started a company called Resolver Systems to build a Python-enabled spreadsheet, which we called Resolver One. Sadly, it never took off in the marketplace, and we had to pivot; we would up creating PythonAnywhere, an online coding and hosting environment, which worked out pretty well and was acquired by Anaconda last year. And now, the circle is closed :-)
(Just for clarity: the team working on this inside Anaconda is entirely separate from the PythonAnywhere team. It would have made a perfect Hollywood-ready story if it had been the same people...)
It was a great product - I remember your pitch and indeed we met a few times at the London python for finance meetup (remember the "Enthought Python Distribution"?). The only issue is that Resolver was IronPython-based so there were a bunch of libs that kinda didn't work.
I have to second that, it seemed to be a great tool. I've often wondered why it couldn't get traction¹. I can recall people gushing about it at what may have been the same Python meetup in London, and also people being impressed enough by it at a Haskell meetup that they were lamenting an imagined lost opportunity to shove a "real language" in to a spreadsheet ;)
I'd always kind of assumed that the target audience would have appreciated the IronPython use, as the .net ecosystem would likely have been more valuable to them. Having just looked I see that numpy wasn't available on IronPython until 2010, and I'm sure that would have been useful to have a little earlier.
¹ Pretty sure I've referenced it here a few times too.
I built my first app on PythonAnywhere! I've had stuff running on there for almost a decade now. I loved being able to make small tweaks to the app directly in the PythonAnywhere web app. Such a great product. Really missed stuff like that when I started using GCP/AWS.
A colleague was joking, "I guess even Microsoft itself isn't able to manage local Python installations on Windows…"
But imagine if Microsoft actually managed to finally solve the "setup a local Python environment" situation once and for all and Excel became the standard Python package manager across operating systems!
$ excelpip install fastapi==9.11.23
Searching for fastapi version 11.09.2023…
> A colleague was joking, "I guess even Microsoft itself isn't able to manage local Python installations on Windows…"
I've been waiting for Microsoft to solve package management on Windows since I played around with debian's apt 20 years. That's not snark, it seemed like Microsoft's product complexity and dedication to backwards compatibility of software would make them the ones to move software management forward. Really dig into the problem space and reinvent the package manager beyond anything that's come out of the open source space.
Instead we've got windows update, windows features, microsoft store, software center and winget (you know, that Microsoft-built package manager for Windows you install from the other Microsoft-built package manager for Windows).
> It seemed like Microsoft's product complexity and dedication to backwards compatibility of software would make them the ones to move software management forward.
That's precisely why they _didn't_ go with the package manager model of software distribution.
Package managers in most distros assume that everything you download via apt/dnf is a part of the operating system and integrates with whatever dependencies the OS ships with. It's a great system if you're an operating system maintainer, but terrible if you're a third-party software developer who has no interest in dealing with 5-6 different OS's repo formats and dependency versions. On Windows and macOS, you vendor most everything, provide runtime installers for the rest, and that's usually enough to be resilient to bitrot.
Package formats like Flatpak, Snap and AppImage are the only projects who seem to be taking the problem of software distribution on Linux seriously, and of those only Flatpak seems to have the mature tooling and lack of myopia necessary to pull it off. For the times that I do miss apt on Windows, chocolatey is pretty good at installing software and vcpkg works great for linkable libraries.
My mind can't help think of the following problem... would Microsoft have to freeze an LTSC set of packages? How would $GOV_AGENCY using a version of Excel one generation behind collaborate with the world?
I wish Libreoffice would have smelled the coffee long time ago. In principle scripting using Python is possible but exceedingly cumbersome/ugly and there seems to be no roadmap to improve on this.
The same warning applies to the linux desktop and all its apps more generally. Increasingly proprietary platforms will be rolling out advanced "AI" functionality extensions to classic apps that are cloud based, many of them via a Python API.
Its a pity that the various strands of the open source universe are so siloed. The potential of the sum being more than the parts is squandered.
I think in a lot of cases its not even the advanced stuff that keeps people on Excel and other proprietary things. It's the seemingly little things.
My biggest beef with LibreOffice's spreadsheet (and with Apple's Numbers) is how they handle copy/paste of discontiguous selections.
Consider this 5 row by 3 column set of data:
1 2 3
4 5 6
7 8 9
a b c
d e f
Suppose you select the 1 2 3 row, the 7 8 9 row, and the a b c row, copy them, and paste just below the d e f row. In Excel you get this (first 4 rows omitted for brevity):
d e f
1 2 3
7 8 9
a b c
In LibreOffice and Numbers you get this (empty cells denoted by "-"):
d e f
1 2 3
- - -
7 8 9
a b c
I haven't found a setting to change this. Excel doesn't have a setting for this either as far as I know, so people who do want discontiguous copy/paste to preserve spacing would probably be as irked by Excel as I am by LibreOffice and Numbers. But I don't think I've ever actually wanted the spacing preserved so it is LibreOffice and Numbers that irk me.
To me Excel's behaviour is way more problematic here. I carefully select the data I want, and it silently includes irrelevant data as well. I don't want the spacing preserved, but I really don't want other random data included.
To be honest, I don't even fully understand what are the use-cases for integrating python with spreadsheets right now. I use both of them separately for different things but cannot see what it can be good at doing in an integrated environment.
python is so easy to script that imo excel is nailing its own coffin. with an aging and retiring demographic of people that have never used a programming language, anyone who learns to incorporate python into excel will end up preferring python because its both more flexible and scalable.
i have no reason to use excel except for basic drawing board math, if someone demands a spreadsheet out me i automate its generation in python so i never have to deal with it again
I see spreedsheets more as an UI. An extremely intuitive (and reactive!) way of spatially decomposing computation. Yes, this creates lots of problems (you can not easily validate it etc.) but it is also very enabling. For sure more people are now exposed to programming, but how many are actually comfortable doing even simple stuff directly using code is a question mark.
A jupyter notebook is maybe somewhere in between. But even if other interfaces manage to bridge the gap further, I doubt the spreadsheet metaphor is going to go away in the visible future. Pivot tables and the like are like COBOL and mainframes :-) The corporate world would die a sudden death if you were to eliminate it (another reason to always make sure you can run things locally).
> with an aging and retiring demographic of people that have never used a programming language
This simply isn't true in any way and is completely divorced from modern businesses. Everybody I know who works in an office setting works with Excel. None of them know Python and none of them have touched a programming language. Almost all of them are younger than 30. And if anybody who's 20 starts at one of these companies, they'd better know Excel because that's what the business is built on. Not like that 20 year old is gonna know anything about programming either. 99% of people just aren't programmers and aren't going to learn programming when they just want to futz around with some numbers and the easiest (yet also the most powerful) tool they know for it is a spreadsheet.
If somebody in any business, anywhere, at any point wants to do something with numbers, they're going to use Excel.
Not a chance. There's nothing scary about VBA (except its inflexibility and lack of a lot of modern features), and people aren't learning that either. Of course, the last decade of vaguely threatening to remove it from Office completely (in favor of js and now python?) hasn't helped people think VBA would be a good investment of their time to learn. A required internet connection and having Python only work on Windows doesn't make it look like much better of an investment.
Also, imo, Python and whatever random environments and libraries that you'll have to use with it to get your work done, will be in almost every way worse than just using VBA. And if you use VBA, your work will be effortlessly portable. The only problem I have with it is that I have to come up with ways to get the code out of the embedded IDE into source control, but there are scripts that help.
> i have no reason to use excel except for basic drawing board math, if someone demands a spreadsheet out me i automate its generation in python so i never have to deal with it again
I, on the other hand, do it in VBA, and expose every important calculation within clearly labeled spreadsheets so programming-illiterate people can audit them and feel confident about the output.
This comment seems out of touch with why people use Excel. The fact is, Excel is popular because it manages to be pretty easy to use, while also being extraordinarily powerful and flexible. For most users, they'll ask "why would I program something in Python when Excel has a built in function to handle that?"
> i have no reason to use excel except for basic drawing board math
I think you underestimate the amount of work, globally, done in Excel. If Excel vanished it would be a business/economic extinction level event, It's not going anywhere.
> Python in Excel is currently available to users running Beta Channel on Windows. This feature will roll out to Excel for Windows first, starting with build 16.0.16818.2000, and then to the other platforms at a later date. [1]
> Python in Excel is available in Excel for Windows. The feature is not available in Excel for Mac, Excel on the web, Excel for iPad, Excel for iPhone, or Excel for Android. On unsupported platforms, workbooks containing Python can be viewed but Python cells display an error when recalculated. [2]
Readit News
7+ years ago I had the option of leaving the Excel team. My then boss’s boss knew I had an interest in bringing Python to Excel and offered me a chance to tackle it if I chose to stay. What was meant to be a 6 month project turned into a ~3 year project, the Python part faded away and we ended up enabling JavaScript Custom Functions in Excel instead.
For Python we were also running ‘in the cloud’ (AzureML v1), although there was some back-and-forth on if we should run locally. I think what made the Python part disappear was our partner AzureML team re-orged, re-released, re-hired, we lost a PM and our work caught the attention of another partner team who realised they could use our code to execute their JavaScript out-of-process. And so I spent a lot of time ensuring that feature was successfully shipped at, I guess, the detriment of Python.
I had a lot of help from some strong engineers and learnt a lot. The core of the work was modifying the calculation engine of Excel to allow functions to compute asynchronously, allowing the user to continue working on other parts of their spreadsheet while the remote endpoint (be it JavaScript, Python or something else) was computing. Previously the spreadsheet would lock up while calculations were running, and that wouldn’t be cool for long-running unbounded calculations. Have to wonder if any of the stuff we built made it into this new feature.
Super great to see this and look forward to trying it out.
Recalc or die
Agreed with the friction around getting started. I would like to see it reduced. It’s cool to see you using them to wrap Python functions - I have done something similar before myself, and still need to get around to shimming Haskell into excel at some point.
Another thing I don’t like is how JavaScript Custom Function cell formulas have a horrible string after the _xldudf marker, which becomes noticeable if your workbook doesn’t/can’t load the custom functions addin (or you unload the addin)
Thanks for your comment
IMO deployment is an even harder problem. The Office team makes it incredibly difficult to deploy web add-ins (presumably for business reasons) and accordingly most companies I'm familiar with are still using the ancient, barely-supported COM+VSTO add-in models.
Deleted Comment
Abandoned now: https://alphawolfxl.com/
I abandoned this when some people I asked to beta test it said "their company wouldn't pay for it" and gave me the impression I'd wasted my time....
....Not sure how to feel about this.....
In general there is a scarcity of working code that you can inspect to see how javascript in Excel works.
Now it is mostly "how to draw an owl"
I have a private github repo called NVBC, the "NecroVisualBasiCon", with all of my favorite VBA-isms.
When I code it, I indent all of the `End *` lines to column 80, to make the VBA look more like python.
Nobody could have anticipated that GPT4 would author Python better than anything else.
Nonetheless, I kind of just want Google Drive except Excel, without adopting anything more than the Office ecosystem, that just works with simultaneous editing.
This alone could eliminate the need for websites that just want this sort of data. I can think of a past project at a previous job I did building an analytics website used only by a handful of people internally that could have been just as well served with something like this, had it existed at the time.
I catch flak for this all the time! Our users just want to be able to use Excel to free-form analyze their data. IT, of which I'm a part of, insists on building web apps to do that for them. They're never happy because all they wanted was Excel.
I keep explaining all we have to do is build a data mart built using SQL Server, and use our ETL tools to keep it populated with the data they need to analyze. Don't let them anywhere near the source data. They keep looking at me like I have ten heads. Instead they want to launch multi-million dollar projects to bring Tableau to our users - and they don't want it! It's asinine!
Excel will import datasets from the Power BI client, and you can refresh the data at any time. The nice part of this arrangement is that the whole model is imported. All the relationships between tables, all the added calculation columns, all the measures are there for use by the end user in Excel.
Power BI has been a great solution for our company (~1000 employees).
IMO, Excel is on the right path with out of the box connectors to Salesforce, Azure databases, etc. What it really needs is full blown SQL with REPL output so the users can see the effects of their SQL. (The way Tableau Prep does it is that it defaults to a sampling of rows to output immediate results). There used to be a Microsoft service called Excel online but it was another thing you had to buy and was super confusing. Excel needs to let users schedule their ETL/datasets in SharePoint online and then let other users subscribe to it. They can call it Excel Super+ and charge a small fortune. I am positive there are people with their wallets open.
I can relate and have experienced this. The result was that they would end up exporting from a series of Tableau/Looker workspaces, and importing into excel and doing their analysis there.
Then continually making enhancement requests on the Tableau/Looker workspaces to get any additional data they need for their excel work.
Then of course, the excel document and its multitude of versions would get emailed around until it hit the attachment size limit, or if there were too many version of it to figure out which one was "actionable".
We finally figured out what they were doing when the enhancement requests for the reporting workspaces got too bizarre.
Insanity.
There are two main issues my org has battled with:
1.People basically turn excel into their own mini databases which causes a support nightmare.
2. This is strongly related to 1) Data provenance is a nightmare because so many people were passing around excel spreadsheets it became very difficult to track down the source of the figures people were using. User A passed their spreadsheet to user B who modified it passed it to User C who further modified it, passed it to User d from there some figures ended up in front of a manager and now we have a problem...
It's not so much a problem with Power users those are the types of people using python (or R) it's the tier of users below that level that cause the damage with excel.
Almost all these tools support exporting to Excel or CSV, so they can do whatever they want in Excel after that.
I mean, that's like >90% of building a CRUD web app. If you truly only need the R in CRUD, your approach sounds reasonable. Otherwise, just go the extra distance and build a CRUD app with csv export / direct SQL access.
Alternatively PMs read all the feedback sent via the feedback UI on Excel web and desktop so you can use that instead if you prefer.
What this means is we'd need to go from just requiring a laptop and a copy of the spreadsheet to requiring redundant internet connections and validating Microsoft's backend meet the regulator's requirements. I'm pretty sure that would unfortunately rule out using it.
The companies I work for while consulting (mainly financial and health organizations) have been very cautious about any company or PII data going to these A.I. programs, and the cloud in general, and are looking to make their own local run generative A.I. LLMs for their employees to use.
By it's very nature these Excel documents will be filled with company data, so I can imagine these companies would also be a bit cautious sending those calculations to the cloud, especially if they can't control the flow of data from their I.T. departments via web applications.
But I'm just a software engineer at the end of the day, and don't professionally use Excel all that much right now (if anything I use it much more for my hobby of board game design). So I can't speak with too much authority on the matter. Just my observations.
I know it's old fashioned to want local software to only change things on my local file system, but if I didn't want that paradigm, I'd just use Google Docs.
It's about the recurring payments, the lock-in, and ultimately literally having the knowledge and business processes of the world economy on their own computers. If this is true, the reason why it is important to run data and scripts locally should be clear, and so is the motivation of your PMs to actively combat any such things.
But if you get a summary in your inbox and show it to your PMs let us know how that goes down.
[1] https://wiki.python.org/moin/PythonSoftwareFoundationLicense...
Can you please just add a button - "unmess this document"? The number of man-hours I've wasted re-creating documents is countless. My job is to do other things not fix excel documents.
(Although, I'm guessing the reason they did it this way is so they could keep the execution model the same for Excel on the web? It's always running on the external service vs sometimes running locally and sometimes running externally.)
With that API, it won't matter whether you want Python, Ruby, Lua, whatever, for your scripting language.
This is nice, but it welds a specific version of Python inside Excel and makes it a gigantic PITA to use your normal development tools on your Python code.
Deleted Comment
How out of touch with the real world is the Excel team?
- We can guarantee a consistent experience for all users. Imagine having to maintain your own local distribution of Python and guaranteeing that it works with Excel as versions diverge over time? Yikes.
- We make it possible to share your Excel workbook with other users and have the calculation just work. That wouldn't work with random local installs of Python and users would be super frustrated by this.
- Security. Imagine opening an Excel workbook that can execute Python code running locally as you.
Also, I totally agree with your second point. Trying to write an internal app in Python that integrates with all of your existing IT infrastructure is an exercise in frustration at best. Excel is already part of the IT infrastructure virtually everywhere and is a programmable reactive canvas.
Disclosure: I work on the design team for the feature.
A. run python in the cloud (default)
B. specify path to your local python interpreter
When you open a workbook that is using B show a warning before running anything. Then behind the scenes spin up a new venv that installs everything from requirements.txt and executes the code in workbook locally.
I worked on a very early python integration used as a workflow DSL back in 1998 and they had to dump it and write their own DSL in the end.
The cloud execution model does still have an advantage in that it can retain old runtimes in perpetuity for backwards compatibility, though
Moving it from the cloud to local doesn't make it an integral part of the Excel file format. Whatever python service is running in the cloud could just run locally.
There are existing solutions for that, but it would be more complicated for MS to support and secure.
Wasm maybe, full-blown runtime without any sandboxing? Nope.
Have a look at xlwings, which runs on any cloud and also locally.
But Microsoft Cloud is a website!
Step one of any workload will always be "work out how to make the software work again" and that's terrible.
I know this because I have been in that situation several times in the last couple of decades with random Excel, outlook and word add-ins. And those don't even do the processing remotely!
"You will own nothing, and you will be happy" has followed us to... spreadsheets? We can't even own the things we're wageslaving away at our desk for.
But we never owned those things we're wageslaving away at our desk for, that's why its called wageslaving. Whether our employers own them or rent them seems pretty immaterial.
That's the problem.
Not only the cloud, also the subscription. If you cancel the subscription (note that the announcement mentioned it might be a paid module), don't have the subscription (because you got the Excel from a supplier), etc. you already have a problem using this Excel sheet.
Last time I use a cloud spreasheet was a month ago, to download the content into Excel
> I also wouldn't use excel for anything that has a combination of high importance and long lifetime though. It's mostly for some brainstorming or thrown in a presentation as a screenshot.
It's funny but that's exactly how I see online spreadsheet: for unimportant things that may not work at any point in the future if the account is closed. However, the collaborative features make them nice for brainstorming.
This is why it's important to restore your backups once in a while, or in this case, validate your critical files.
Half the things don’t work, customer is sure it worked in X ( now broken and invisible way ) and they really don’t remember that correctly.
It's not a red flag, it's evolution
I'm generally a fan of MS Office products and try to give them the benefit of the doubt.
Please help me think of a plausibly good reason for requiring Python to run in the cloud, that is not just about lock-in.
I think we are about 5 years overdue from bailing from M$. They need to be avoided. Every product from Windows to Office already has anti-consumer features.
http://python-history.blogspot.com/2009/01/microsoft-ships-p...
There are a lot, I mean a lot apps that support scripting languages and don't need you to manage the language's runtime yourself. Blender's UI is entirely in Python. Civilization games have most game logic in Lua.
You also have people using Excel on iOS, and Android devices. This allows them to use and read Python in their spreadsheets.
An embedded Python is about 10 MiB. I just checked. Excel could easily have its own separate copy.
(Just for clarity: the team working on this inside Anaconda is entirely separate from the PythonAnywhere team. It would have made a perfect Hollywood-ready story if it had been the same people...)
I'd always kind of assumed that the target audience would have appreciated the IronPython use, as the .net ecosystem would likely have been more valuable to them. Having just looked I see that numpy wasn't available on IronPython until 2010, and I'm sure that would have been useful to have a little earlier.
¹ Pretty sure I've referenced it here a few times too.
A colleague was joking, "I guess even Microsoft itself isn't able to manage local Python installations on Windows…"
But imagine if Microsoft actually managed to finally solve the "setup a local Python environment" situation once and for all and Excel became the standard Python package manager across operating systems!
I've been waiting for Microsoft to solve package management on Windows since I played around with debian's apt 20 years. That's not snark, it seemed like Microsoft's product complexity and dedication to backwards compatibility of software would make them the ones to move software management forward. Really dig into the problem space and reinvent the package manager beyond anything that's come out of the open source space.
Instead we've got windows update, windows features, microsoft store, software center and winget (you know, that Microsoft-built package manager for Windows you install from the other Microsoft-built package manager for Windows).
That's precisely why they _didn't_ go with the package manager model of software distribution.
Package managers in most distros assume that everything you download via apt/dnf is a part of the operating system and integrates with whatever dependencies the OS ships with. It's a great system if you're an operating system maintainer, but terrible if you're a third-party software developer who has no interest in dealing with 5-6 different OS's repo formats and dependency versions. On Windows and macOS, you vendor most everything, provide runtime installers for the rest, and that's usually enough to be resilient to bitrot.
Package formats like Flatpak, Snap and AppImage are the only projects who seem to be taking the problem of software distribution on Linux seriously, and of those only Flatpak seems to have the mature tooling and lack of myopia necessary to pull it off. For the times that I do miss apt on Windows, chocolatey is pretty good at installing software and vcpkg works great for linkable libraries.
The same warning applies to the linux desktop and all its apps more generally. Increasingly proprietary platforms will be rolling out advanced "AI" functionality extensions to classic apps that are cloud based, many of them via a Python API.
Its a pity that the various strands of the open source universe are so siloed. The potential of the sum being more than the parts is squandered.
My biggest beef with LibreOffice's spreadsheet (and with Apple's Numbers) is how they handle copy/paste of discontiguous selections.
Consider this 5 row by 3 column set of data:
Suppose you select the 1 2 3 row, the 7 8 9 row, and the a b c row, copy them, and paste just below the d e f row. In Excel you get this (first 4 rows omitted for brevity): In LibreOffice and Numbers you get this (empty cells denoted by "-"): I haven't found a setting to change this. Excel doesn't have a setting for this either as far as I know, so people who do want discontiguous copy/paste to preserve spacing would probably be as irked by Excel as I am by LibreOffice and Numbers. But I don't think I've ever actually wanted the spacing preserved so it is LibreOffice and Numbers that irk me.Not many people care because the most popular use case of any office suite is to interoperate with Microsoft file formats.
APSO script organiser is recommended for running scripts from inside LibreOffice: https://extensions.libreoffice.org/en/extensions/show/apso-a...
python is so easy to script that imo excel is nailing its own coffin. with an aging and retiring demographic of people that have never used a programming language, anyone who learns to incorporate python into excel will end up preferring python because its both more flexible and scalable.
i have no reason to use excel except for basic drawing board math, if someone demands a spreadsheet out me i automate its generation in python so i never have to deal with it again
A jupyter notebook is maybe somewhere in between. But even if other interfaces manage to bridge the gap further, I doubt the spreadsheet metaphor is going to go away in the visible future. Pivot tables and the like are like COBOL and mainframes :-) The corporate world would die a sudden death if you were to eliminate it (another reason to always make sure you can run things locally).
This simply isn't true in any way and is completely divorced from modern businesses. Everybody I know who works in an office setting works with Excel. None of them know Python and none of them have touched a programming language. Almost all of them are younger than 30. And if anybody who's 20 starts at one of these companies, they'd better know Excel because that's what the business is built on. Not like that 20 year old is gonna know anything about programming either. 99% of people just aren't programmers and aren't going to learn programming when they just want to futz around with some numbers and the easiest (yet also the most powerful) tool they know for it is a spreadsheet.
If somebody in any business, anywhere, at any point wants to do something with numbers, they're going to use Excel.
Also, imo, Python and whatever random environments and libraries that you'll have to use with it to get your work done, will be in almost every way worse than just using VBA. And if you use VBA, your work will be effortlessly portable. The only problem I have with it is that I have to come up with ways to get the code out of the embedded IDE into source control, but there are scripts that help.
> i have no reason to use excel except for basic drawing board math, if someone demands a spreadsheet out me i automate its generation in python so i never have to deal with it again
I, on the other hand, do it in VBA, and expose every important calculation within clearly labeled spreadsheets so programming-illiterate people can audit them and feel confident about the output.
I think you underestimate the amount of work, globally, done in Excel. If Excel vanished it would be a business/economic extinction level event, It's not going anywhere.
Its so slow.
> Python in Excel is currently available to users running Beta Channel on Windows. This feature will roll out to Excel for Windows first, starting with build 16.0.16818.2000, and then to the other platforms at a later date. [1]
> Python in Excel is available in Excel for Windows. The feature is not available in Excel for Mac, Excel on the web, Excel for iPad, Excel for iPhone, or Excel for Android. On unsupported platforms, workbooks containing Python can be viewed but Python cells display an error when recalculated. [2]
[1] https://techcommunity.microsoft.com/t5/excel-blog/announcing...
[2] https://support.microsoft.com/en-us/office/introduction-to-p...