<i>> We wanted an alternative to VBA, but got an alternative to the Excel formula language.</i> indeed<p><i>> Integrating the Jupyter notebook cells inside the Excel grid was a mistake.</i> 100% agreed. this seems like the typical Microsoft behavior where the team A was pushing their product and won over team B, even though it's not really what users needed<p><i>> Python in Excel isn’t suitable for Python beginners nor for interactive data analysis.</i> exactly. it is my humble opinion that Microsoft doesn't really understand how Excel is used IRL<p>also this bit is gold:<p><i>> What I find interesting though is the multiline editing experience of PY cells. Why not give the native Excel formula language an upgrade, so I don’t have to write LET expressions such as the following one (that I still find very hard to read):</i><p><pre><code> =LET(x, 1, y, 2, x + y)
</code></pre>
<i>> Instead, allow me to write it like so?</i><p><pre><code> let x = 1
let y = 2
x + y
</code></pre>
and we haven't even talked about =LAMBDA()!<p><i>> Also, why not turn Excel tables into a native Excel DataFrame? Give them attributes instead of sticking to the functional approach and you’d be looking at something like this:</i><p><i>> =MyTable[#All].GROUPBY(...)</i><p><i>> Lot’s of possibilities to integrate the pandas functionality in a way that feels more native to Excel!</i><p>Jackpot. I'm literally building this formula language in a new spreadsheet app (it's early days but I'm eager to share it on HN when the MVP is ready!) and am writing a paper on dataframes vs. spreadsheets, so reading this has warmed my heart on this rainy afternoon<p>also as a former Django lover (I still love it, i just don't use it as much), `=MyTable[#All].GROUPBY(...)` reminds me of its ORM...<p>"hmm emoji" indeed.....
I was quite excited when I heard Python was coming to Excel but the execution pretty much guarantees that adoption is going to be horrible. If I as MSFT wanted to get people to write Python scripts that can take advantage of Excel's great abilities this is probably the worst way I could have gone about it. A ham-fisted, cloud-first feature implementation that is basically jammed into the product without appropriate contextual abstractions is a pretty poor way to get things done. Given that Excel itself has a strong tabular object model built in, why would a DataFrame be required as a separate construct?<p>> We wanted an alternative to VBA, but got an alternative to the Excel formula language<p>Optimistically, I would guess that the powers that be inside MSFT wanted to show they "integrated" Python into Excel but didn't really want to mess too much with a product that has stabilized over the past 30 years. Cynically, I would say that they've messed up the implementation on purpose so they can put a bullet through the "Python in Excel" idea without actually doing anything useful.<p>A much better way to add Python (or any modern scripting language really) to Excel is to
1. Make a special library that can be called from the language and which only works with Excel.
2. Bundle a minimal interpreter so that the scripts can be run locally without pushing everything to some godforsaken Azure datacenter. Make this a downloadable plugin for all currently supported versions to ensure backwards-compatibility.<p>This is a much better way to ensure that Python becomes <i>the</i> glue language for Excel. There are so many applications where reporting, analysis and visualization can be better achieved through automation using scripting languages. VBA is old, limited and is yet another thing for someone to learn if they want to script any Office product. All of these are hurdles that can be easily removed. I guess though that the part that really sucks is that these are all things that the MSFT of the 80s/early 90s would have done in a heart beat if it meant more people would use Excel (they built in bugs from Lotus 1-2-3 for God's sake) but I guess it's a different story today when everybody <i>does</i> use Excel.
As a counterpoint to a lot of the speculation on this thread, if you're interested in learning more about how and why we designed Python in Excel, I wrote up a doc (that is quite old but captures the core design quite well) here [1]. Disclosure: I was a founding member of the design team for the feature.<p>[1] <a href="https://notes.iunknown.com/python-in-excel/Book+of+Python+in+Excel" rel="nofollow">https://notes.iunknown.com/python-in-excel/Book+of+Python+in...</a>
Hi all, I am Felix, the author of the post. I just wanted to reiterate that this post was mainly meant as a summary of my GitHub issues I opened with them. So it should really be seen as a feedback to help them improve the product. Python in Excel currently covers a completely different use-case than xlwings, but Python in Excel inspired me to finally look into WASM and PyScript, which is an awesome product.
Maybe its time to start fresh with a clean sheet? (Pun).<p>The spreadsheet paradigm is immensely intuitive and arguably the only alternative to the standard procedural programming currently in use in number crumching.<p>But therein lies also a major weakness when used for important tasks: hard to validate.<p>Once you further combine it with API calls and whatnot, the situation gets totally out of hand: how do you reproduce anything?<p>The landscape around user interfaces, computational capability and (most importantly) the ever deeper embedding of such tools in decision making suggests to start taking the humble spreadsheet seriously and maybe that requires going back to the drawing board.
> PY cells are evaluated from left to right and top to bottom. This includes the sheets, so the first sheet gets calculated before the second sheet, etc.<p>Oh, no. This is MS Excel 4.0 Macro sheets (Ctrl+F11) all over again, except somehow with a worse execution order.<p>> It prevents you from referencing a cell with a Python object directly, as you’re always running the risk that someday, someone switches that cell to Values mode, which would break any formula that references this cell in object mode.<p>That's not a major concern. Excel already has a dozen footguns painted exactly this colour.<p>> So which output is my df1, and which one is my df2? I have no idea unless I look up the code that is sitting in cells L2 and K2<p>Excel still lets you name cells, does it not?<p>> In fact, I agree that the cloud is the best way to bring Python to Excel.<p>Heretic!
Some alternate ideas:<p>- make a python library/driver for Excel sheets that imitates Pandas Dataframe API but (a) has reactive cells (b) imports equations from Excel<p>- make a notebook IDE that integrates excel spreadsheets. Make everything reactive. Have a split notebook/spreadsheet view.<p>A little off-topic but it would be rad if Excel had more Airtable/Baserow/Grist features.
The big problem is that this is exactly backwards.<p>I rarely want to access Python from my Excel. I quite often want to access Excel from my Python.<p>A spreadsheet is a great GUI for a lot of things. A <i>lot</i> of people are employed creating "shitty version of Excel but can be driven from any of <web, desktop, application, websockets, WASM, etc.>"<p>Being able to easily drive Excel from an external Python program would make for a nice cross-platform application substrate.
"I don't want to learn the M language"<p>i am baffled. power query / M is taking over the corporate world and its extremely, extremely useful. its one of the most useful things that nobody in open source world has tried to copy (that im aware of).
This feels like a variation on Zawinski's Law: Every program attempts to expand until it can <i>run python</i> [read mail]. Those programs which cannot so expand are replaced by ones which can.<p>Or more broadly, the inner-platform effect. I'm left wondering "why would you use python within the lousy coding interface of excel, when Pandas already exists?"
I suppose it's time to share my dream excel functionality: Turbo F2.<p>F2 should enable the user to trace calculations back to the original data, not only the current calculation step.<p>In basis spreadsheet, this would yield a jumble of steps that are undocumented and hard to parse. But that's where the python, lambdas and custome functions come in: They enable legibility of turboF2.
Without interacting with it myself, none of this is surprising.<p>I have used excel in the past, and I am a long term python user. But if you asked me today what I really wanted to make my life easier and ultimately a product or business better using only excel?
I would ask for lua or scheme. I don’t need a batteries included environment embedded into a spreadsheet. I just want sane syntax for common functionality which does not require arcane knowledge and long forgotten wisdom.
One should note that this comes from xlwings, a Python-in-Excel plugin with a $1,490 professional lifetime license.<p>Excel's changes will end up eating their reason to exist.
Felix's upcoming work is intriguing and would enable a much nicer integration of Python into the Excel ecosystem. The use of PyOdide and WASM enables execution of the Python logic on-device. The same tactic is used by PySheets and taken a bit more to the extreme by implementing the cell functions, but also the entire sheet UI and logic in Python. Check it out if you are approaching the space from the perspective of a Python programmer, rather than an Excel user. See <a href="https://pysheets.app" rel="nofollow">https://pysheets.app</a>
We built our spreadsheet (<a href="https://rowzero.io" rel="nofollow">https://rowzero.io</a>) from the ground up to integrate natively with Python. Bolting it on like Microsoft did, or as an add in like xlwings, just feels second class. To make it first class, we had to solve three hard problems:<p>1. Sandboxing and dependencies. Python is extremely unsafe to share, so you need to sandbox execution. There's also the environment/package management problem (does the user you're sharing your workbook with have the same version of pandas as you?). We run workbooks in the cloud to solve both of these.<p>2. The type system. You need a way to natively interop between Excel's type system and Python's much richer type system. The problem with Excel is there are only two types - numbers and strings. Even dates are just numbers in Excel. Python has rich types like pandas Dataframes, lists, and dictionaries, which Excel can't represent natively. We solved this in a similar way to how Typescript evolved Javascript. We support the Excel formula language and all of its types and also added support for lists, dictionaries, structs, and dataframes.<p>3. Performance. Our goal was to build a spreadsheet 1000x faster than Excel. Early on we used Python as our formula language but were constantly fighting the GIL and slow interpreter performance. Instead we implemented the spreadsheet engine in Rust as a columnar engine and seamlessly marshal Python types to the spreadsheet type system and back.<p>It's the hardest systems problem our team's ever worked on. Previously we wrote the S3 file system, so it's not like this was our first rodeo. There's just a ton of details you need to get right to make it feel seamless.<p>You can try it free here: <a href="https://rowzero.io/new?feature=code" rel="nofollow">https://rowzero.io/new?feature=code</a>
Related:<p>Show HN: I've built a C# IDE, Runtime, and AppStore inside Excel<p><a href="https://news.ycombinator.com/item?id=34516366">https://news.ycombinator.com/item?id=34516366</a>
They need to rewrite Excel with native Python and C# (.net core) integration. However, this might dry up the Excel moat. 1985 wants it's data types back.
So basically... formulas running in the cloud? Seems the best way to be stuck at every possible technical issue (ISP going down, Azure going down, whatever other reason to lose connectivity...). Let alone any considerations about privacy and data protection, for which I'm pretty sure those in charge of GDPR compliance at companies start having nightmares of cloudpythonized Excel.
I looked at the clients package and had a bit trouble understanding where it sits in the Python-excel landscape. Is it a competitor to openpyxl?<p>The pro version has quite a price tag, so I’m assuming they have a big value add.
Microsoft seems to be purposely doing a poor job of integrating Python into Excel. It is a "give the monkey a banana" approach. With a rotten banana.<p>They seem to be pouring money into LAMBDA.
The mistake is "the grid". The spreadsheet as a way to allow IT-illiterate to do stuff on a desktop is a mistake.<p>Yes, we do need some kind of tabular data UIs but SMALL UIs elements, not the base of the UI. Beside that the obscene "wrapping" of Python to make it "user-safe" makes also it next to useless.<p>A less ugly approach to tabular UIs is the one from R-Studio, while it's limited, another is org-mode tables while again they are limited in UI terms. Both are a bit better than Jupyter REPL model witch is itself far better than a spreadsheet.
not sure if the average excel business user would benefit much from this. as a tech guy working with excel power users, sure i would enjoy not needing a special language.
I find that lots of features in MSFT Office exist for the sole purpose of generating lock in. This is one of them. Just a feature no one needs but enough people use that becomes a sore thumb when trying to evaluate a competing solution.
The cloud thing bugs me because a lot of times buying things, managing licenses, etc, is a hassle. I disagree that bundling Python wouldn't work given blender does it, I think inkscape does it, and done other common programs. Would it be nice if they didn't? Sure. But they do and I trade a fraction of a percent of my disk space for that modularity.<p>Also, there are a few excel handling packages for Python. I've even made graphs and everything. Because sometimes other people like to use Excel and that's fine. I'll just stick with those instead.
I agree with xlwings about this but for different reasons. MS are a company wot makes money. Python was added to Excel cos R and Python and so on - market share.<p>I suggest you do what my brother does and unanswer the question! He works for quite a large firm and is surrounded by quite a lot of data. He is a Windows user by routine and asked me for some help. I got Python + MS Visual Code installed and integrated for him and off he went.<p>He is an expert with the data and its "knowledge" and was willing to roll up his sleeves and get to grips with a grubby data processing facility. He decided on Python and he is my "customer" so I did the best I could too hence anaconda and VS code.<p>He grabs data out of SAP (he's senior enough to get IT to do his bidding) and then passes that through Python scripts and then passes that on to Excel for reporting.<p>What many seem to forget is that most apps are chainable. It's often referred to as the "unix philosophy" - each component should do one job and do it well. That's nice but also bollocks when abused as I have just done.<p>If Python is not integrated properly within Excel then do it yourself via whatever interfaces are available. Pass in and out with .csv or whatever. You just need some imagination.<p>For me: I use Libre Office and despite owning my company, I don't require everyone else to do so. I believe in freedom and expression of choice.