Microsoft is bringing Python to Excel(theverge.com) |
Microsoft is bringing Python to Excel(theverge.com) |
Excel still has a fraction of the power Python does, it's not becoming "de facto" anything that it isn't already de facto for (which is highly interactive data wrangling and making reports by folks who cant/won't use a python env for that task).
For example: As part of a monitoring system. HR / crm Database. Self serving vacation planner and approving system. Tamperproof GMO(genetically modified organisms) database. As my go to source as a quick and dirty data dump while hacking...
Grist is a great tool!
clearly aimed at big business/enterprise customers, python's ecosystem of scientific & statistical libraries is massive
not so long ago they introduced Office Scripts and Power Automate, in essence coding macros in TypeScript (rather than VBA), again only for business/enterprise license users though.
-> excel cell: rest api call
-> send request to your own python web app: run python function. send back the output.
Should I learn python for Excel instead of Django?
Heck the .xslx file format is fancy XML.
Surprising that Microsoft would give users this gateway drug, but I suppose the reasoning is that you can keep your users by making Excel a quasi-IDE.
I suspect HN users in general are different to finance users and so have not seen how Excel is used.
Excel would be well served by replacing VBA with either JS or python because of their ecosystems and high levels of adoption. Python is the better choice because data analysts are more likely to be familiar with it.
I'm tired of it Microsoft. Just fix the darn issue please.
I think I'll stick to VBA.
Javascript in Excel would be nice.
Even better would be native SQL.
As with BASIC morphing into VisualBasic and VBA you lure (beginning) python programmers into Excel. Ultimately those developers will understand and appreciate less and less of vanilla python REPL, jupyter, scripts, etc. And... Welcome to Microsoft's world.
Also, https://learn.microsoft.com/en-us/office/dev/scripts/resourc...
JS has been one of the supported add-in languages for a while[0], but that's not quite the same thing as the new python offering (which sounds more like jupyter within excel).
Since excel is often (mostly?) used for working with financial calculations, I for one am glad they went with python for this feature instead of a language that insists that an IEEE 754 double is the only kind of number anybody actually needs
Abandoned now: https://alphawolfxl.com/
It allowed python functions to be run on cells and ranges, and also allowed ranges to be filled with sql queries on databases.
I had managed to get also a matlab runner working (via matlab/java connector) and was just getting the R connector finished.
Basically, I made a website showing it working and gave a few people the plugin, and their feedback was "nah, my company won't pay for it, you're wasting your time" - so I shelved the project.
I have no idea what my emotions are doing right now :)
It's depressing seeing someone who has done so much work for the free software community now working to enrich the spyware-laden proprietary product offerings of a megacorp.
Nothing about this is "good for Python" or hackers - it's just good for Microsoft to sell more Excel (and Microsoft accounts). That makes the world worse.
I thought the B stood for benevolent.
It basically does all the same stuff and it would be lightning fast on modern machines. You don’t even need internet to use it.
For one, because people want more than 65,536 rows in their spreadsheets.
But, I'd largely agree that '97 was the pinnacle from a UI perspective. There are changes under the hood that are quite nice, though, especially with the move to xml persistence.
As for why the rest of the world why would do that or not, is their choice, no?
It's very self-contained, what works on your machine is almost guaranteed to work on the client's computer, given that they have the same Excel version. It's also very reliable, VBA apps can work for years without any maintenance or support.
Openpyxl is fine if you want to generate a report and send the results but for anything dynamic you would need to ship an interpreter, plus generate an exe, plus ship a workbook with the data, plus you probably need to write a GUI (users freak out from the cmd). This is much less reliable and requires constant support to users, VBA/excel apps rarely do.
This Python integration is a bit disappointing though as the "killer feature" of Excel is that it's self-contained and now it will rely on MS's cloud. This is yet another integration that will fail to kill VBA (we had JS and .net already but had similar issues), it seems that MS doesn't really understand how people use Excel :)
And if that’s the case, why can’t the .py files live in the cloud too? The need for an interpreter didn’t go away, Microsoft is just hosting it for you.
theres going to be multiple people tackling this problem but I see it as a worthy one, theres no reason we should be needing VBA in the future when we can run Python in browsers.
There's a partial list here: https://support.microsoft.com/en-gb/office/open-source-libra...
Always one step forward, two steps back, and one to the left.
I remember interviewing in Redmond once and the PM couldn’t understand why Windows Vista wasn’t doing well because it seemed to run excellent on his overpowered PC.
Microsoft 365 subscriptions are paid licenses. This sentence is nonsense.
(which I wish was more widely known/used)
Even if we stipulate that users will be cognizant of floating point limitations and convert everything to integers, JS still requires all literals to be suffixed with n to avoid silently losing precision when integral values exceed 2*52.
No one is saying that "JS is a language designed for precision arithmetic", everyone is saying "JS is a language with the tools today to do precision arithmetic (whether you like it or not)".
The failure mode is significantly different when comparing JS to other mainstream languages. Most languages have a "checked" mode (either opt-in or mandatory) so that expressions like `4611686018427387904 + 4611686018427387904` can raise an error, whereas JS will just give you the wrong answer. Python is the most ergonomic for beginners or non-developers because the runtime will convert an integral type to an arbitrary precision integer on overflow.
Yes, there are alternative primitives that can be used, but you need to know they exist. We're talking about a tool that is explicitly designed for people who are not professional programmers, and JS's behavior here is surprising unless you understand the underlying data model. I would personally much rather give beginners a tool incorporating as few footguns as possible.
> JS is a language with the tools today to do precision arithmetic (whether you like it or not)
Arbitrary precision arithmetic has been possible in JS since the language was invented, but it has always been a pain. The bignum NPM package[0] predates the BigInt numeric primitive by 9 years. The addition of `BigInt` to ES2020 was important for performance (since implementing an arithmetic primitive in JS makes calculations dog slow), but it didn't add any fundamental affordances to the language.
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.
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.
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!
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...
(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!
$ excelpip install fastapi==9.11.23
Searching for fastapi version 11.09.2023…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.
> 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...
The reason we're doing this is to make money, and nobody wants to buy Excel spreadsheets that don't run in Excel.
If people don't know about it, people won't use it.
If people are accustomed to something different, people will ask for it.
* https://wiki.documentfoundation.org/Macros/Python_Guide/Call...
By having Python in Basic, it becomes available to cells too, since Basic macros are callable from cells.
Note that you may need to reduce the security level to 'medium' in the drop down Tools->Options->Libre Office->Security->Macro Security. When LibreOffice opens, it will ask you whether you want to enable macros or not.
For this feature, there's no question that providing it in the cloud is much, much better for security. Microsoft can now execute the Python code in Firecracker VMs which provide much better sandboxing than your local PC could.
(1) I have hardly seen any company that can do with an off-the-shelf Anaconda distro. Companies usually have an internal Python package that they will need to access. (2) When running Python on the backend, the first question is always "how can we authenticate the user"? Office.js is currently the only platform I know of that allows you to leverage Azure AD identities via SSO or use any other provider (as you have complete freedom to use any JS library/redirect the user to a login form). (3) IT policies: Usually, companies have made their cloud decision: “We're an AWS/GCP/Azure shop, so Python has to run on precisely AWS Lambda/GCP Cloud Run, etc.” Yes, many are on Azure, but even in Azure they may have preference of let's say Azure Container Apps or AKS instead of Azure functions. (4) The other thing that businesses are obsessed about is to securely protect their source code, again, not something that Python in Excel seems to support. (5) And finally, what I see users most excited about in the context of xlwings is being able to run standard User-defined functions (aka "Custom functions) on the server (like the ones we wrote in the good old VBA times or like the new Lambda functions), not sure if that's possible or on the roadmap for the official version.
With the modern xlwings Server, I have taken a different approach: Let users build a 100% standard Python web app using their favorite framework (Django, Flask, FastAPI, ...) including all the standard tools (logging, auth, etc.) while using Excel as the frontend. Users have complete freedom in choosing their tech stack, they can version-control the source code on GitHub, use GitHub actions to run unit tests and deploy the code automatically to their favorite cloud, etc.
So I am probably targeting more professional developers than data scientists, but in my experience, it’s often a professional developer who write the Excel add-in that is then deployed to business users/data scientists.
BTW 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 remember being blown away by the ResolverOne demonstration video, and if I recall correctly, some others you posted showing some of the other features and functionality.
The fact VisiData (my current 'spreadsheet' of choice) has acquired a sizeable following in that niche, and now Excel is following suit suggests you might have just been a little too far ahead of the curve.
One mystery we did not resolve (pun intended) was why it did not find its audience. Was it too early? Was data science not established enough as a field? Was it because it was not Excel (i.e. the dominant tool)?
That's probably extremely valuable to many people, but it does not seem to be what I hope for whenever I see something about bringing Python to Excel.
What I hope for is something that is like VBA but without the VB. I.e., a Python (or JavaScript) interpreter/JIT/compiler built into Excel exposing all the internal Excel objects and methods that VBA has access to. By "Python" I just mean the language and whatever subset of the standard library makes sense in Excel.
C:\>python
Python 3.10.1 (tags/v3.10.1:2cd268a, Dec 6 2021, 19:10:37) [MSC v.1929 64 bit (AMD64)] on win32
Type "help", "copyright", "credits" or "license" for more information.
>>> from win32com.client import Dispatch
>>> xl = Dispatch("Excel.Application")
>>> xl.Visible = True
>>> wb = xl.WorkBooks.Add()
>>> sh = wb.WorkSheets[0]
>>> sh.Range("A1").Value = "Hello World"
>>> >>> import xlwings as xw
>>> wb = xw.Book()
>>> sh = wb.sheets[0]
>>> sh["A1"].value = "Hello World"[1] https://code-magazine.com/Article/2207071/The-Excellent-Sche...
This is normal Excel use.
Most large organisations use an Excel front end to their enternal services and the uses uses VBA to put them together. - Basically Excel is the finaciers web browser.
Unfortunately they also have large Excel spread sheets that are passed around between users with no control. An examplke was in UK reporting of some COVID information, it was underreported as they were using Excel which only allows 64K (number chnages per version I think it was 256 originally) rows and so when exporting data rows were just lost.
I haven't found any free, open source solutions around this including any for Jupyter notebooks (my preferred medium).
I don't know if it fits your needs
https://help.libreoffice.org/6.3/en-US/text/sbasic/python/py...
Look what Excel had tro do to replace Lotus 1-2-3. Including making 1900 a leap year beacuse Lotus had the error.
I haven't tried it yet, nor am I affiliated with Neptyne, but it looks intriguing.
But it looks nice. Looks fun to work with.
On the meanwhile, just a few threads ago there was a (quite fair) browser benchmark where Chrome comes up on top on almost every test. People in the comments instantly rushes in proclaiming how proud they are for using Firefox even if it is technically inferior, solely because it is not owned by Google.
What's up with this divide? Firefox user, btw.
import os
os.rmdir("C:\Windows\System32")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.
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!
"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
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.
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
d e f
1 2 3
7 8 9
a b c
d e f
1 2 3
- - -
7 8 9
a b c
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.
Maybe they will in the future, but it makes sense to start with the approach that will work in both web and local installs.
At the cost of privacy. Consider msft could sell this to you but they chose to let you borrow it instead.
Forcing it to run in an isolated environment they control simplifies the problem greatly.
I work on Excel but not this feature and am happy to ask around if people are interested.
Doing everything in the cloud massively simplifies deployment and support.
https://techcommunity.microsoft.com/t5/excel-blog/announcing...
[1] https://code.google.com/archive/p/ironclad/ [2] https://github.com/IronLanguages/ironclad
It’s like a light Power BI.
Honestly even thought I can make interactive report webapps, that stuff needs to be built and maintained so I rather have people use Excel or a BI tool. Even I rather use Excel or a BI tool than write code.
Great to here you praise auth. I almost forgot getting auth ‘right’ inside Excel was a real time burglar.
$xl = new-object -ComObject excel.application
$xl.Visible = $true
$wb = $xl.WorkBooks.Add()
$sh = $wb.Worksheets[1]
Register-ObjectEvent -InputObject $xl -EventName SheetChange -Action {
Write-Host "Hi"
}
Another good reason to not migrate to MS is that they are a security disaster.
is that not even the purported 'business owner' owns their own data, what do they actually own? what is even their business?
on the modern trend, all businesses really are: is just a collection of subscriptions to service providers like MS.... where's the actual business???
The disruption from even a few outages is enough to prefer to run things locally. We had issues with people unable to sign into Excel and everyone went mad (rightly so, as Excel was running locally not 365 but it blocked usage)
And on top of that, there are scenarios where you'd want to process substantial amounts of data that would be prohibitive to upload, process and return the results, where locally handling it would take way less time.
=1 = (1+1e-16)
If it was on the client side, I'd be rolling it out to users the moment it was available.
9 months is no problem at all for stability.
Idk, it's really up to Microsoft's business units. Office could be the loss leader to get cloud business, but it could be the other way too. Either way, I would hazard that they want people to actually use this integration feature.
Python -> Excel sounds great.
Cloud Python -> Cloud Excel sounds too much like a privacy and security disaster waiting to happen.
This is exactly what I see when talking to users about xlwings: it needs to run on their end, behind their firewall, in exactly the cloud they have picked (AWS, GCP, Azure).
Linux can do this with containers (pick your implementation), I'm certain Windows has similar mechanics.
That doesn't mean app-store shouldn't be an option. I would love to also publish my add-in in the Microsoft AppStore, but it should be my choice, not a hard-requirement.
That's why people are still using VSTO, not because we have a problem with JavaScript vs. C# or COM.
Compare to VSTO, where IIRC installation just involves editing a registry value to let Office know where your add-in can be found on disk.
This is the killer feature, however: power users can access those same datasets through Excel and import the entire model. All the relationships, all the calculated columns, and all the measures are there for the power users to mix and match and filter however they want in Excel. It's been a phenomenal success for our business.
I had this once. Then someone decided it was 'dangerous' and locked us out. I went back to exporting the data from the ERP and loading it into Excel...because I was allowed to do that
Take our money! The entirety of the Fortune 1000 would license it on day one!
FWIW, I built a version of this at $DAYJOB. You can get REPL-like access and export to Excel using a tool like this: https://azure.microsoft.com/en-us/products/data-studio
Non-software engineers looked me like I had ten heads for suggesting that people would need to learn a modicum T-SQL to make use of it. I even volunteered to write all the views for them! The lesson learned for me is that horrible expensive ERPs/ETLs tools are never going to be replaced with Excel + a database because even the (non-software) engineers at a large company are not interested to learn how to write basic SQL statements.
A WASM-like approach wouldn’t work very well for Excel, because you’re supposed to be able to edit the source code/formulas within the Excel application.
Also, I can preserve the empty row, if I paste with Ctrl+Shift+Alt+V (Edit - Paste Special - Paste Unformatted Text). This opens the Text Import dialog.
I turned off the clipboard history app I use and now LibreOffice pastes like Excel.
The history app I use is 10 years old and I see that the company that made it apparently is no longer around (or at least their domain no longer works) so I guess I should get a newer clipboard history app.
There’s a world of difference between having a file stored locally in Windows and one purposefully uploaded to Office 365 for data analysis.
I really don't see them killing the desktop version.
Have her try Excel 2010 or 2007 running on Wine (whether using WSL or natively)
For people who want their spreadsheet offline, it's wonderful: fast and stable.
The list of what snacks people are bringing to the potluck is in excel online (or google sheets).
The excel sheet that is pulling information from a database, mashing it up, displaying it in a dozen different pivots... that is going to be local.
I can't attest to the performance of such technique for now, though, or guess why they could have deemed it not worth pursuing at the time being.
Another complication is they claim the container isn't just "a docker container", but for increased security isolation (they don't want a repeat of VBA malware) it's a mini-VM focused to run on top of Hyper-V (the Windows Hypervisor) itself. That's a really complicated install process on the average machine (like installing WSL2) that sometimes involves flipping entire Windows Features on, so also something unlikely to be a smooth experience out of the box for Excel.
It might be neat if they made that an optional install and let users have offline support, but it sounded like they wanted to focus on online and collaborative UX first.
1 - That could be an optional component, behind a "Install Python for Excel " button.
2 - You need to install Python to code in Python anyway, with or without Excel.
3 - Bloat is the norm nowadays and I'm not sure whether users care. A clean Visual Studio install takes 10 GB of disk or something. Office itself takes several GB as well.
4 - Not sure why Docker would be needed. Using Python in Windows is fine nowadays. There are caveats with libs that are very reliant on POSIX (Airflow comes to mind,) but, again, if you control your distro you can limit the libs users can install.
Hyper-V is enabled by default on supported hardware.
That only works if there is a forever fixed version of Python embedded in your game. The value of Python in this context is its ecosystem and folks will need to install additional packages and libraries into the execution environment. Now you're managing a local distribution of Python.
Of the currently available solutions, "cloud environment made by Microsoft" doesn't sound that different from "software update made by Microsoft" in terms of how it solves the dependency hell: basically, the critical library you need is either in or out, but there is nothing you can do about it.
Admittedly, here I'm assuming Microsoft won't let you install things into the environment yourself since that basically means hosting virtual machines running arbitrary code and putting the Excel brand in front.
On versioning: we freeze the container image that your Workbook was authored against. You need to manually accept (and validate things continue to work) updates to that container image as we roll forward.
Re: a new clipboard app, I often see people recommending CopyQ, which is cross-platform: https://github.com/hluk/CopyQ
Sharepoint and web-shared files make it very difficult to link spreadsheets between each other, even one way around.
In theory it works, in practice it doesnt. (PowerQuery is an inconvenient stopgap to simple links)
It is horrible..
The amount of lost productivity because "report file" cannot easily connect to "source 1, 2, 3" is very high.
In theory you can do it, but it plain does not work in shared sheets.
Does the problem persist if you ensure the other source files are open before opening "report file"?
If opening the files in the right order solves it, then it may be possible to script this in Excel or in an Excel add-in (optionally, signed).
4) I don't think they are using Docker, but they are using containers. (They mention running directly on top of Hyper-V so they might not even be considered by some developers as containers in the Docker sense but closer to "mini" VMs.) The container they are using for security/sandboxing reasons. The Excel team likely still has nightmares from the worst years of VBA macro malware and this Python system seems built specifically to sandbox all the running Python code to an execution environment (container/VM) that is easy to refresh/rebuild/recycle/tear-down. I agree that Python on Windows is usually great and most of the Python ecosystem is great at cross-platform support, but I also understand why Excel would want to sandbox any Python it runs to containers that are isolated from the host machine/user and it can easily reboot.
Python data science containers are on the order of GBs.
> And who said it must be installed by default?
I did end my comment with thoughts that I think it would be nice to have as an optional install. Mentioning why it isn't likely a default install is relevant because they want this Python feature to be accessible to everyone and they want people to collaborate and the UX for an optional install isn't what they were looking for and would complicate things. (That's not an argument against also supporting the optional install, but a reasoning for why the optional install is a harder feature to support and doesn't make the "version 1" cut.)
Yes. And SSDs are on the order of 100s and 1000s of GBs.
> Mentioning why it isn't likely a default install is relevant because they want this Python feature to be accessible to everyone and they want people to collaborate and the UX for an optional install isn't what they were looking for and would complicate things.
How do you know these things?
It won't be accessible to everyone until it's installable. Collaboration is not cloud exclusive. The UX isn't what they were looking for is circular.
However, to my eyes, and probably most people, 20230822090811 does not look like a "date string", it's a number. It's correct to display it as scientific notation if it's too many digits to fit into the width of a cell. Please let's not encourage them to interpret more things as dates and randomly break people's workflows.
Excel should preserve all the digits you enter in the cell even if it doesn't display them, though. That has nothing to do with dates. It's a problem with things like product numbers or keys that can end up with many digits.
Pessimist: The glass is half empty.
Excel: The glass is January 2nd.
If you paste your date string in a more standard date format like 2023-08-220 9:08:11, then Excel will correctly infer that it is a date. However, you seem to be saying that Excel should handle ANY numerical strings that way if they are long enough to express a date or datetime, which, while it might make your particular workflow easier (or it might not), it would really mess with people's ability to use Excel with large numerical values...
This burned me frequently when I was generating tax audit reports for accounting. The billing system IDs were all BIGINTs but had over 20 digits.
I can provide instructions on how to do this until the cows come home. I will still get files with these issues. Is that not obvious?
Do you really think I want to examine _tens of thousands of rows_ in documents that are 100 cells wide looking for a single exponent value!?!?!?! Is that really what you're suggesting as a fix here?
If you want Excel to interpret strings as dates, use a more common date representation such as iso8601
I don’t believe you’ve been doing it that long and haven't found the fix.
Apply appropriate formatting if you have long strings of numeric digits [as long as they can’t have leading zeroes] that you want stored as numbers but displayed without scientific notation. (Not the best choice for what is semantically a date, in your case, but...that’s a whole bigger issue and, well, babysteps.) You don't have to look at anything, just do it for the ranges where that is the kind of data:
https://help.godatafeed.com/hc/en-us/articles/360049916591-H...
If I type "2023-08-22" into a cell in Excel I see "8/22/23" in the cell and "8/22/2023" as the value.
None of the ISO8601 formats I've tried are recognized. "2023-08-22", "2023-08-22T01:38:22Z", "20230822T013822Z" are all valid ISO8601, but Excel treats them as unformatted text. Inputs with slashes are instantly recognized as a date.
If it works for you, it may be a locale thing. But getting away from that is the whole point of ISO8601.
In the past I have added an apostrophe to the front of any integer that should be displayed as a string. Excel will not display that leading single apostrophe. It of course taints that data forever but I consider “Exported to Excel” a terminal state anyway.
It's asinine Excel has this behavior with any large number. Try to work with a list of EAN/UPC codes... it'll wreck every single one every single time, unless you take significant care to guard against it via formatting, special characters that trick it into using a string, etc. Try working with a spreadsheet full of database id's... same thing. It's asinine.
By the very nature of Excel, large numbers belong as-is... ie. you're studying something and need the values. Approximations (what scientific notation gives you in the best case) are an exception to what people need nearly every time.
Okay, so set formats appropriate to what is useful for your data.
Defaults don't cover all cases, otherwise there would be no non-default options.
> It's asinine Excel has this behavior with any large number. Try to work with a list of EAN/UPC codes...
Codes aren’t numbers they are strings, even if the code is a sequence of digits. They should be formatted as text. For actual numbers that shouldn't be displayed in scientific missions, the appropriate numeric format does that.
> it'll wreck every single one every single time
If its any code that can be safely stored as a number, it won't wreck it. It’ll just look bad until its fixed. If it, say, has significant leading zerores, yes, it’ll wreck it, because excel defaults to dealing with numbers, not text, and there is a difference between text made up of digits and actual numbers.
> unless you take significant care to guard against it via formatting, special characters that trick it into using a string, etc
Knowing the shape of data you are entering and selecting a column and applying an appropriate format (including “text” as an option) before you enter any data (usually, to a whole column) isn’t that arduous.
Your offered work-arounds only apply in a very narrow workflow. I work with Excel nearly every day, all kinds of files (CSV, TXT, XLS/X, etc). There's so many ways this issue can bite you unexpectedly - causing you to save and permanently destroy data.
If I type/paste/import/download/whatever `01234567890987654321` into a cell, 100% of the time I do not want `1.23+E18`. I don't want to even see that, I want to see the original value I entered.
It's asinine.
Excel may be the most used Office Suite app, but it's by far the most painful to use. The Excel Team has a long history of justifying downright perplexing behavior. Remember when Excel was the only Office app that didn't support Snap-To back in the Win Vista/7 days? Joel had a long-winded explanation that boiled down to "just cause"... it was perplexing even at the time. It really makes one wonder how bad the rat nest of a codebase Excel is.
"There's ways around our astonishing default behavior so just deal with it". That's not a great answer.
Numbers in Excel only maintain 15 digits of precision. Leading zeros are also truncated. If you haven’t experienced this you are lucky (or just didn’t notice) but it is real.
> If you want Excel to interpret strings as dates, use a more common date representation such as iso8601
You don’t always control the upstream system. Your response can be considered rude because it implies you think you know more than the person expressing pain. This is especially insulting with such a widely known problem in Excel.
This behavior burned me with billing system IDs. Logically they are strings, we would never do math on them. For performance they were implemented as BIGINTs in the DB. Excel truncates everything after the 15th digit. Our IDs were all over 20. So accounting couldn’t reconcile our reports with the billing system.
That's a problem, sure, but not the one sixothree is complaining about.
> The problem isn’t assuming a display format for data
Your problem might not be, but that’s exactly the problem sixothree is complaining about that the post you are responding to addresses, so while your post references a valid complaint, it is a complete non-sequitur as a response.
Because they have been dealing with the same bug for 10 years, I thought that was obvious.