My thoughts on Python in Excel

https://www.xlwings.org/blog/my-thoughts-on-python-in-excel

by fzumstein • 2 years ago

288 118 2 years ago

mathnode• 2 years ago

Without interacting with it myself, none of this is surprising.

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.

smcin4• 2 years ago

Your personal use-case might prefer Lua or Scheme, but most casual Excel (or SQL) users are non-programmers so they won't. They'll want the equivalent of decently-documented macros or boilerplate they can easily and quickly use without modification. (One common Excel use-case will clearly be "import/munge lots of data from various sources, then pass it into some AI model, then process the output". Can't see people writing that in Lua.) The real target customers for this one are commercial/enterprise non-programmer Windows-stack users whose legacy workflow/data is built around/glued to Excel and are already locked into paying $$ monthly/annual subscription. From looking at Reddit, I don't see much other takeup of Python in Excel.

I don't get your "shouldn't need batteries-included environment" objection; MSFT is bundling Anaconda distribution libraries with Excel. I'd expect it works seamlessly online and offline, as far as everything supported by Python stdlibs. (Can you actually point to any real problem with the batteries?) Really the only part I see you can quibble is things that are currently only implemented in uncommon third-party libraries, i.e. not stdlibs/numpy/scipy/scikit-learn/pandas/polars and the main plotting, data-science, ML, DB and web libraries.

I just want sane syntax for common functionality which does not require arcane knowledge and long forgotten wisdom.

Show us some Python syntax for common functionality in Excel which does require arcane knowledge and long forgotten wisdom. Otherwise, this is purely your conjecture.

(If anything, bundling Python with Excel will stimulate healthy discussion towards which Python stdlibs need to be added/enhanced/changed, and which third-party libraries should be upgraded to stdlibs.)

superb_dev2• 2 years ago

Python in excel is a feature I would only expect to be used by power users. Someone who spends a lot of time in excel. Calling these people “non-programmers” isn’t true, excel itself is a pretty esoteric programming language.

I personally don’t think python is the problem here, but if their users can learn python they can certainly learn lua.

bitwize• 2 years ago

I would ask for lua or scheme.

Scheme? Did somebody say... Scheme?

https://apexdatasolutions.com/home2/acce%CE%BBerate/

It's a paid addon, but still...

TZubiri2• 2 years ago

excel is too bleh python is too blah myLanguageOfChoice is just right.

We can't all be special snowflakes, python and excel are lingua francas.

BenFranklin100• 2 years ago

+1. I’ve had to fire guys like the OP. Smart guys often, but nearly impossible to work with productively.

Spivak• 2 years ago

Lua is pretty uncontroversial as the embedded language of choice and is actually made specifically to be embedded and play nice with the surrounding application.

I get why they chose Python for this and it's not all that hard to embed, well the interpreter anyway, compiled modules are another story.

andylynch• 2 years ago

The likely target users for this are analyst/ quant types. Very likely they are also using Python on the same tasks already.

mhh__• 2 years ago

All of that is much easier in Python where you have access to a lot of other people data wrangling utilities

a_bonobo• 2 years ago

One should note that this comes from xlwings, a Python-in-Excel plugin with a $1,490 professional lifetime license.

Excel's changes will end up eating their reason to exist.

airstrike3• 2 years ago

While that's fair, the author makes that disclaimer at the start of the article and he specifically addresses where to go with xlwings. But above all, his arguments are right on the money...

doctorpangloss• 2 years ago

Maybe. As with many things Microsoft, one guy can do a better job.

And anyway, Microsoft did this in reaction to increasing demand. The pie will grow faster than Microsoft's pie slice will take from this guy.

adolph• 2 years ago

If you take a look at Excel’s implementation, Python in Excel is going to be part of this person’s sales funnel.

ttyprintk4• 2 years ago

I'd expect this once users see what can and cannot be version-controlled.

isoprophlex• 2 years ago

Jetbrains still makes delightful products, even though MS had been pushing their turd of an editor onto everyone since forever...

pjmlp• 2 years ago

Yet they feel threated enough, to have started Fleet and SpaceCode.

airstrike3• 2 years ago

We wanted an alternative to VBA, but got an alternative to the Excel formula language. indeed
Integrating the Jupyter notebook cells inside the Excel grid was a mistake. 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
Python in Excel isn’t suitable for Python beginners nor for interactive data analysis. exactly. it is my humble opinion that Microsoft doesn't really understand how Excel is used IRL

also this bit is gold:

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):

    =LET(x, 1, y, 2, x + y)

> Instead, allow me to write it like so?

    let x = 1
    let y = 2
    x + y

and we haven't even talked about =LAMBDA()!

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:
=MyTable[#All].GROUPBY(...)
Lot’s of possibilities to integrate the pandas functionality in a way that feels more native to Excel!

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

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

"hmm emoji" indeed.....

Closi• 2 years ago

You can do multiline formulas in the advanced formula environment, but still follows the same syntax as the formula language (with comments). i.e.:

=LET(

    x,1, // assign 1 to y

    y,2, // assign 2 to y

    x+y  // add x and y

)

Same with LAMBDAs

layer8• 2 years ago

You can use Alt+Enter to create multiline formulas in normal formula entry. In addition, the formula bar can be dragged down (or press Ctrl+Shift+U) to be multiline [0]. One drawback is that you can’t use the Tab key for indentation and have to use spaces.

[0] https://www.ablebits.com/office-addins-blog/formula-bar-exce...

snthpy2• 2 years ago

Hi,

I'd love to read your paper when it's ready.

wizzwizz4• 2 years ago

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.

Oh, no. This is MS Excel 4.0 Macro sheets (Ctrl+F11) all over again, except somehow with a worse execution order.

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.

That's not a major concern. Excel already has a dozen footguns painted exactly this colour.

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

Excel still lets you name cells, does it not?

In fact, I agree that the cloud is the best way to bring Python to Excel.

Heretic!

airstrike3• 2 years ago

Excel still lets you name cells, does it not?

It does, but that too is a half assed feature so users struggle with it IRL

tichiian• 2 years ago

Excel implementers struggle with it.

Half the features of Excel cannot deal with named cells. Try for example to use named cells in Conditional Formatting. Doesn't work at all, ranges cannot be named cells or tables, and condition formulas can only reference named cells with extremely ugly contortions.

There are lots and lots of additional examples of similar problems.

Excel is a loose agglomeration of unrelated features hidden under a pretty GUI.

gerdesj2• 2 years ago

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.

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.

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.

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.

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.

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.

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.

claaams• 2 years ago

That workflow sounds so painful. Why not get a BI tool that can integrate directly into your data warehouse and do reporting through that?

Foobar8568• 2 years ago

Because in large companies BI and data are managed by external people, and without a budget at 5, if not 6 digits, nothing happens.

If there are some self service BI, most likel self service is only by name.

Add some variants related to security, max number of licenses allowed (hilarity ensures if IT bears the cost on behalf of the business without being able to charge back), etc

nhinck32• 2 years ago

It's really not, I've used python in the past but prefer R for this.

As it stands excel is a better presentation layer than almost all BI tools once you're past the modelling and analysis stages.

gerdesj2• 2 years ago

Sometimes you have to do your thing and then enforce change (if you can). Large orgs can be just as odd as individuals but on a far grander scale.

I've worked for other firms and then my own for the last 24 years. My job title is Managing Director but I am under no illusion that my word is final. It is really final but only when I say so and I never do.

Oh a BI thingie. Yes that will fix everything. No it wont.

bsder• 2 years ago

The big problem is that this is exactly backwards.

I rarely want to access Python from my Excel. I quite often want to access Excel from my Python.

A spreadsheet is a great GUI for a lot of things. A lot of people are employed creating "shitty version of Excel but can be driven from any of <web, desktop, application, websockets, WASM, etc.>"

Being able to easily drive Excel from an external Python program would make for a nice cross-platform application substrate.

macintux2• 2 years ago

I've used Python libraries to generate reports in Excel, frequently enough that at my last job I wrote a custom library to wrap xlsxwriter to simplify for my use case. Tremendously useful.

OxfordOutlander2• 2 years ago

Would you mind explaining in more detail what your custom library enabled?

jimbokun2• 2 years ago

Fascinating to think of Excel growing into a modern replacement for Visual Basic. The spreadsheet that becomes so important to the company it’s handed over to the software developers to make it into a real application is almost a meme at this point.

Being able to steadily enhance a spreadsheet into a real application without rewriting from scratch could be a real game changer.

doubloon2• 2 years ago

visual basic has been included in excel for decades, its just called VBA not VB. if you thanos snapped VBA out of existence the world would probably stop functioning for several weeks.

qsi2• 2 years ago

You can access the VBA object model from Python once you've got a COM conduit. There are a few Python libraries to allow you to do this; you'll be able to write VBA-like code in Python accessing Excel's internals in a similar manner. Debugging can be a bit painful though. I've done this both from Matlab and from Python, and it's fine for smaller projects, writing structured/formatted Excel workbooks, etc.

I agree that Excel as a GUI can be phenomenal.

jimbobthrowawy• 2 years ago

Since it was announced, my assumption has been that microsoft is putting python in excel like this to make using python to manipulate spreadsheets less common since that'd eat a lot of their moat around their program. If it were common enough, it wouldn't matter too much what spreadsheet program you were using.

setopt• 2 years ago

XLWings – the product made by the author of this post – lets you do exactly that. You can make a Python script that connects to a live Microsoft Excel process, iterates over sheets and cells, reads/writes data to them, etc.

I’ve used it at work some years ago, and it’s a great product.

nhatcher• 2 years ago

That's one of the most important goals in my particular "shitty version of Excel", https://github.com/ironcalc/IronCalc

dudus• 2 years ago

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.

doubloon2• 2 years ago

"I don't want to learn the M language"

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

jimmcslim• 2 years ago

The M language is incredibly tied to Power Query however. There are some open-source projects that seek to enable it M outside of Power Query, but they seem to rely on private APIs and are not cross-platform.

mardifoufs• 2 years ago

Yes it's great for doing business apps. Not everyone uses excel to run business analysis though.

dav43• 2 years ago

I haven’t seen it being used anywhere in big corporates (+20k employees) at all.

I attempted it once and stop as I found error checking impossible, source control impossible and ability to see code changes impossible - compared to a simple python script that is just text and can be source controlled.

Interested in a different opinion as maybe I missed something.

Neywiny• 2 years ago

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.

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.

ttyprintk4• 2 years ago

ArcGIS, SPSS; a lot of unwieldy packages with legacy format considerations allow you to specify a virtualenv.

marcodiego• 2 years ago

Didn't read it but, couldn't this be a "battlefield" where LibreOffice could have unbeatable advantage?

billfruit• 2 years ago

What's the state of things at Libre office regards to this? Do they support python or other languages for macros and for formula/expressions?

mikeqq2024• 2 years ago

yes python supppoted in libraoffice

fock• 2 years ago

There's a Python wrapper for the Java API. Documentation is scarce. Also Libreoffice only supports any of the nice and new Excel functionality .... since last month: https://bugs.documentfoundation.org/show_bug.cgi?id=126573

tomrod• 2 years ago

Yes.

lassoiat• 2 years ago

Delusional.

While I wish this was not the case, the consumers of my excel spreadsheets barely have enough excel skills as is.

The idea there is any value in scrapping all that for LibreOffice is completely and utterly delusional.

varunnrao4• 2 years ago

Python on LibreOffice appears to be more concerned with moving GUI elements and not enough with manipulating cells. There doesn't appear to be a straightforward library/module which helps me access specific cells and get their values. This would be a greater value proposition than allowing me to control Calc as a puppet using a Python script which seems to be their main idea.

buovjaga2• 2 years ago

Did you try the ScriptForge library shipped with LibreOffice: https://help.libreoffice.org/latest/en-US/text/sbasic/shared...

See the SFDocuments.Calc service there. ScriptForge makes it more convenient to work with the API.

Using it with Python: https://help.libreoffice.org/latest/en-US/text/sbasic/shared...

2Gkashmiri• 2 years ago

I dont understand why doesn't excel have local Python. This feels wrong on so many levels.

breckognize4• 2 years ago

We built our spreadsheet (https://rowzero.io) 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:

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.

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.

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.

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.

You can try it free here: https://rowzero.io/new?feature=code

victor106• 2 years ago

looks cool!

do you have a desktop app in the works?

breckognize4• 2 years ago

We have some development desktop builds working. Is it something you'd pay for?

yvely• 2 years ago

Looks very cool. Will be keeping an eye on this for local network hosted and/or desktop application version. Thanks for sharing!

breckognize4• 2 years ago

We have private hosting available (in your VPC) for enterprise customers.

IshKebab• 2 years ago

These are exactly the issues I would have guessed you would run into when using Python in a spreadsheet. Python has really been promoted above its level of competence. It's not suitable for these things at all.

I would say Typescript is a more obvious choice, or potentially Dart. Maybe even something more obscure like Nim (though I have no experience of that).

I get that you want compatibility with Pandas, Numpy, etc. but you're going to pay for that with endless pain.

fzumstein4• 2 years ago

As the author of said second class add-in, let me just guess that your most popular feature request was adding the "Import from xlsx" functionality...which describes the whole issue: it's always Excel + something, never something instead of Excel.

breckognize4• 2 years ago

My apologies, that came off harsher than I intended. I've used xlwings in previous jobs to complete Excel automation tasks, so thank you for building it. xlwings is one of the projects that motivated me to start Row Zero. My main issue with it, and other Excel add-ins, is they break the promise of an .xlsx file as a self-contained virtual machine of code and data. I can no longer just send the .xlsx file - I need the recipient to install (e.g.) Python first. This makes collaboration a nightmare.

I wanted a spreadsheet interface, which my business partners need, but with a way for power users (me) to do more complicated stuff in Python instead of VBA.

To borrow your phrasing, our thesis is that it has to be Excel-compatible spreadsheet + something, not necessarily Excel + something. It's early days for us, but we've seen a couple publicly traded companies switch off Excel to Row Zero to eliminate the security risks that come with Excel's desktop model.

rldjbpin• 2 years ago

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.

jszymborski• 2 years ago

Some alternate ideas:

- make a python library/driver for Excel sheets that imitates Pandas Dataframe API but (a) has reactive cells (b) imports equations from Excel

- make a notebook IDE that integrates excel spreadsheets. Make everything reactive. Have a split notebook/spreadsheet view.

A little off-topic but it would be rad if Excel had more Airtable/Baserow/Grist features.

laffra3• 2 years ago

Your second proposal looks like https://pysheets.app

olvy0• 2 years ago

Show HN: I've built a C# IDE, Runtime, and AppStore inside Excel

https://news.ycombinator.com/item?id=34516366

openrisk• 2 years ago

Maybe its time to start fresh with a clean sheet? (Pun).

The spreadsheet paradigm is immensely intuitive and arguably the only alternative to the standard procedural programming currently in use in number crumching.

But therein lies also a major weakness when used for important tasks: hard to validate.

Once you further combine it with API calls and whatnot, the situation gets totally out of hand: how do you reproduce anything?

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.

laffra3• 2 years ago

I tried that exactly with PySheets by implementing the sheet in Python itself, rethinking how Jupyter Notebook would look if it treated the data science problem as a dependency graph rather than a linear storytelling document. See https://pysheets.app

diggan• 2 years ago

https://news.ycombinator.com/item?id=40179566 - PySheets – Spreadsheet UI for Python (pysheets.app) - 287 points - 47 days ago - 74 comments

CPLX• 2 years ago

Seems to me that Airtable is pretty much what you’re talking about, and is spectacularly useful.

Lukas1994• 2 years ago

IMO the better paradigm is coming from enterprise applications like Anaplan. Cells are not the right abstraction to work with numbers. Most of the time you work with multi-dimensional quantities (eg revenue by product, geography, month).

We’re working on a more approachable implementation of that paradigm at https://causal.app

varunnrao4• 2 years ago

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?

We wanted an alternative to VBA, but got an alternative to the Excel formula language

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.

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.

This is a much better way to ensure that Python becomes the 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 does use Excel.

pmontra• 2 years ago

Make a special library that can be called from the language and which only works with Excel

There are plenty of those libraries without the "only works with Excel" part, if you mean to have Excel running on the machine. As a Python example, a customer of mine is using XlsxWriter and openpyxl for .xlsx files, xlrd for .xls plus python-docx for dealing with docx files.

I don't remember why the two modules for xlsx files and not just one. My customer runs that software on Linux inside a Django app. I expect that Microsoft is interested only in Excel on Windows and in running Python inside Excel. What I expected was a VBA editor for Python and maybe a library for Windows to access Excel objects from a Python script in a cmd or powershell prompt.

probably_wrong• 2 years ago

I don't remember why the two modules for xlsx files and not just one.

Last time I checked OpenPyXl doesn't deal correctly with .xlsm files - there's a parameter for that but I believe it's still experimental. In my case this meant that, on a Mac, Excel would complain that a file generated with OpenPyXl was corrupt and then successfully "recover" every generated file.

My wild guess is: your customer reads the files with OpenPyXl, processes the data with Pandas, and then uses XlsxWriter as the custom Excel writing engine.

varunnrao4• 2 years ago

What I expected was a VBA editor for Python and maybe a library for Windows to access Excel objects from a Python script in a cmd or powershell prompt.

This is exactly it. The killer feature for including any modern scripting language in Excel. Both XlxsWriter and openpyxl can r/w from Excel files but I have to manipulate the data using another library like `pandas`. Instead if MSFT gave a library which I can import into a Python script and use like

  import msft_excel_lib as xl
  data = xl.get('A1:A3')
  sum = xl.sum(data)
  xl.write("B3", sum)

would be much better than whatever it is they have shipped today without having to make much changes to anything else. I wouldn't even grudge them if they say that this library can run under some weird virtual environment found only within Excel to maintain product retention.

whywhywhywhy• 2 years ago

but didn't really want to mess too much with a product that has stabilized over the past 30 years.

This describes the feeling of all major features added to the native app incumbents that built their foundations in the 90s: Office, Creative Cloud, etc

You really get the feeling the core dev teams of these apps have a poor understanding of their own foundations and even adding a button is an ordeal for them.

fransje26• 2 years ago

and even adding a button is an ordeal for them.

It probably is, though..

greentxt• 2 years ago

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

Sounds exactly right. Embrace/extinguish. It's The Way.

ale42• 2 years ago

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.

LiamMcCalloway• 2 years ago

I suppose it's time to share my dream excel functionality: Turbo F2.

F2 should enable the user to trace calculations back to the original data, not only the current calculation step.

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.

fzumstein4• 2 years ago

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.

snthpy2• 2 years ago

Hi,

Thanks for the post. Very insightful!

I'm looking forward to trying out your pywasm module, or should it be ESP (Excel Subsystem for Python)?

wodenokoto• 2 years ago

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?

The pro version has quite a price tag, so I’m assuming they have a big value add.

fzumstein4• 2 years ago

xlwings is a competitor to VBA rather than openpyxl: openpyxl can read and write the Excel file on disk, without involving the Excel application. xlwings is all about automating the Excel application, and write macros and custom functions in Python.

laffra3• 2 years ago

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 https://pysheets.app

kkfx3• 2 years ago

The mistake is "the grid". The spreadsheet as a way to allow IT-illiterate to do stuff on a desktop is a mistake.

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.

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.

rrr_oh_man2• 2 years ago

The mistake is "the grid". The spreadsheet as a way to allow IT-illiterate to do stuff on a desktop is a mistake.

But why?

kkfx3• 2 years ago

At a basic level because we almost never have to deal with pure tabular data without anything else. A spreadsheet was designed to deal with pure data, even without a note. Than since in the real world we need notes, titles, small bits of text etc spreadsheets devs add styling and the nightmare began, a grid to deal with data, but also formatting styles, people to manually tweak column and row width and height, add colors, ... all manually of course. Then overflowing text. At a certain point in time Microsoft decide to allow "integration", witch is not integration at all (OLE, with the ability in this case to insert a spreadsheet inside a text document) and the nightmare keeps extending.

The most basic error is WYSIWYG, it can work to a certain extent, like CAD systems, where you can "free draw" something but any line have defined parameters, we have snap concept to state a line is connected to another, defined properties like length and so on so in the end we have "a model", a free draw witch is also a set of data constrained/in relation with a set of functions but in general it's a nightmare. The second basic error is try to compensate the lack of integration, a system designed for commercial purpose in witch any software is a standalone closed product, by adding features that in theory can only grow to create "the complete thing" witch is an impossible goal and makes in practice just bloatware. The third basic error is consider users a bunch of imbeciles who can only learn a thing "click around on some pictogram and enter text like a chimp plunging fingers slow and hard". If you craft something simple and explain the principle behind people will learn and act. Oh, of course you need a bit of INITIAL training but after it they are ready, like teaching how to fish vs giving a fish to a hungry person. Oh, of course commercially hungry people are nice, they are tied to the vendor and they keep spending, being locked where they are, but locked people are not productive, like slaves do not work well compared to free employees.

That's the spreadsheet mistakes and modern IT mistake in general: the will to keep users ignorant while trying to made them productive anyway instead of teaching them, giving them powerful tools. It's not transforming anyone in an engineer, it's just the same concept of schooling for all, ensuring anyone have a minimum culture needed to be a Citizen in a society.

datavirtue• 2 years ago

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.

keithalewis2• 2 years ago

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.

They seem to be pouring money into LAMBDA.

localhost2• 2 years ago

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.

[1] https://notes.iunknown.com/python-in-excel/Book+of+Python+in...

hypercube33• 2 years ago

I'm genuinely curious why python instead of something like PowerShell for Excel specifically. Seems a little out of the farm but I also get how it's a more adopted language.

localhost2• 2 years ago

Python is the most popular language for data analysis with a rich ecosystem of existing libraries for that task.

Incidentally I've worked on many products in the past, and I've never seen anything that approaches the level of product-market-fit that this feature has.

Also, this is the work of many people at the company. To them go the real credit of shipping and getting it out the door to customers.

ttyprintk4• 2 years ago

To associate Excel with all those third-party Python analytical packages. Monte Carlo comes to mind; in the distant past, that was an expensive third-party Excel plug-in.

mrgoldenbrown• 2 years ago

This was interesting but seems focused on how, not why. Like why not python as alternative to VBA, and why cloud only.

cbsmith• 2 years ago

Madness. I tell you it is madness.

petsfed2• 2 years ago

This feels like a variation on Zawinski's Law: Every program attempts to expand until it can run python [read mail]. Those programs which cannot so expand are replaced by ones which can.

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?"

pphysch• 2 years ago

Once Python became the lingua franca for next-gen HPC applications (i.e. machine learning), we had a good indication that the (higher-level) programming language wars will soon be over.

Students graduating today are comfortable in Jupyter notebooks but not a CLI.

Also on HN front-page is a full implementation of llm.c in Python-superset Mojo.

Once Python takes off in the browser, either by compiling to WASM (via something like Mojo) or interpreted by PyScript, it's over.

Total GvR victory.

FractalHQ• 2 years ago

Python will have to pry the Typescript types from my cold dead hands before I choose it on purpose. If anything is going to replace Typescripts web dominance, it will need to offer an advantage.. not a regression. Python would be such a harsh downgrade in so many ways.

lwtarsx1• 2 years ago

Google just fired the Python team, though that of course might also have political reasons. I don't think that Google or anyone else is prepared to use PyScript.

I would not either, given the attitude of Python core towards security and correctness.

My thoughts on Python in Excel

Related Stories