[HN Gopher] My thoughts on Python in Excel
___________________________________________________________________
My thoughts on Python in Excel
Author : fzumstein
Score : 270 points
Date : 2024-06-12 09:19 UTC (2 days ago)
(HTM) web link (www.xlwings.org)
(TXT) w3m dump (www.xlwings.org)
| mathnode wrote:
| 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.
| smcin wrote:
| 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_dev wrote:
| 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.
| smcin wrote:
| That's exactly what I said above: most Excel users are non-
| programmers. Hence Python in Excel would only be used by a
| subset of Excel power users.
|
| Moreover, having to pay $$ recurring subscriptions for that
| stack to run open-source software (Python) they could run
| for free elsewhere mean it'll only be used by
| commercial/enterprise Windows-stack users who are already
| locked into some legacy workflow/data built around/glued to
| Excel. For example, financial users, or users who have some
| expensive license seat of some enterprise product(s). Means
| an even smaller subset of users.
|
| We're saying the same thing.
| superb_dev wrote:
| Are we? I'm arguing that lua would have been a better
| choice than python.
|
| Any traditional programming language that you put in
| excel is going to be a feature mostly for power users,
| and I think they could pick up lua just as easy as python
| jimbokun wrote:
| They could, but a lot more people already know Python than
| Lua.
| smcin wrote:
| Most Excel users (not the power users, just the 1.1
| billion everyday ones, including many of the enterprise
| ones) don't know how to program in any language. You're
| coming at this with a HN mindset.
|
| "Python vs Lua" is not even on their radar. And even if
| it was, their criteria would be dominated by platform
| lockin and compatibility with other licenses (e.g.
| commercial SQL, Tableau, MSFT, etc.). Not by "which open-
| source language?"
| extr wrote:
| IMO you're the one coming in with an HN mindset. Python
| has massive mindshare even among people who have never
| programmed. It is the numeric computing language du jour.
| In any given financial company there are definitely
| already python users. Lua, a language primarily known for
| plugin scripting, with no numeric computing libraries,
| that has zero mindshare among non career programmers, is
| not even in the conversation.
| smcin wrote:
| Nobody here has made a case for Lua in Excel. I wrote
| "Python vs Lua" is not even on the radar of most Excel
| users, not even the subset that are programmers.
|
| (Why are people here aggressively misreading everything I
| type, today?)
|
| > Python... is the numeric computing language du jour. In
| any given financial company there are definitely already
| python users.
|
| The original post didn't say "financial Excel users". Not
| all Excel users are financial; most aren't. I've worked
| with legal informatics users, e-commerce users,
| bioinformatic users, among others. Those sectors never
| use Excel for numeric computing, IME (drawing the
| occasional chart isn't numeric computing). They are more
| familiar with SQL, SQL macros, SQL query generators,
| importing/exporting to/from SaaS, etc. Like I said.
| bitwize wrote:
| > 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...
| TZubiri wrote:
| >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 wrote:
| +1. I've had to fire guys like the OP. Smart guys often, but
| nearly impossible to work with productively.
| Spivak wrote:
| 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 wrote:
| The likely target users for this are analyst/ quant types. Very
| likely they are also using Python on the same tasks already.
| mhh__ wrote:
| All of that is much easier in Python where you have access to a
| lot of other people data wrangling utilities
| a_bonobo wrote:
| 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.
| airstrike wrote:
| 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 wrote:
| 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 wrote:
| If you take a look at Excel's implementation, Python in Excel
| is going to be part of this person's sales funnel.
| ttyprintk wrote:
| I'd expect this once users see what can and cannot be
| version-controlled.
| isoprophlex wrote:
| Jetbrains still makes delightful products, even though MS had
| been pushing their turd of an editor onto everyone since
| forever...
| pjmlp wrote:
| Yet they feel threated enough, to have started Fleet and
| SpaceCode.
| airstrike wrote:
| _> 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 wrote:
| 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 wrote:
| 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...
| snthpy wrote:
| Hi,
|
| I'd love to read your paper when it's ready.
| wizzwizz4 wrote:
| > 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!
| airstrike wrote:
| > 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 wrote:
| 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.
| nhinck3 wrote:
| I've always wondered why they've never done a basic pass of
| fixing stuff like this.
| keithalewis wrote:
| Worked with the Excel team on the 2007 beta. They want to
| do this too, but program managers give them a hard "No."
| If you touch even one thing there is a cascade of side
| effects that will drag you off the local maximum.
|
| The PMs are right. It would be expensive to do this and
| it would piss off existing customers.
| gerdesj wrote:
| 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 wrote:
| 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 wrote:
| 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
| nhinck3 wrote:
| 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.
| ttyprintk wrote:
| With this, you get:
|
| - Reproducibility (the official, bundled Excel numerical
| routines have/had errors greater than floating-point
| precision) which avoids the unprofessional look of, say,
| least squares numbers that differ from a check by hand.
|
| - Version and environment control. This is the fastest way
| I can answer the question, "what would these new routines
| produce if run against last October's pool of databases?"
|
| - A presentation format where client customizations for
| style, dimensional units and currencies, human language,
| etc. can all be owned outside of your project.
|
| I try to sell this approach when I can. Is there a
| particular BI that strikes a better balance?
| bsder wrote:
| 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.
| macintux wrote:
| 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.
| OxfordOutlander wrote:
| Would you mind explaining in more detail what your custom
| library enabled?
| macintux wrote:
| It's been long enough that my memories are fuzzy.
|
| I do remember defining a specific format for a cover page:
| merging a large block of cells and customizing the font
| information within. I suspect I had other formatting
| convenience functions to make the reports more consistent.
|
| I created a single data structure to wrap the workbook and
| worksheet objects and include a "current" row/cell tracker,
| so that I could invoke an "add row" function to write the
| next row in the sheet without indicating _where_ in the
| worksheet the data should be added.
|
| Each write function would then take as arguments the data
| structure and a string label to indicate which worksheet I
| wanted (numeric indexing was also an option, but I found
| strings to be the best way to make the code obvious about
| what worksheet we were actively modifying).
|
| I also did some work with tagged data (via tuples) so my
| library could choose which write function to invoke in
| xlsxwriter. I don't remember the motivation for that.
| jimbokun wrote:
| 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.
| doubloon wrote:
| 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.
| qsi wrote:
| 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 wrote:
| 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 wrote:
| 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 wrote:
| That's one of the most important goals in my particular "shitty
| version of Excel", https://github.com/ironcalc/IronCalc
|
| :)
| dudus wrote:
| 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.
| doubloon wrote:
| "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 wrote:
| 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 wrote:
| Yes it's great for doing business apps. Not everyone uses excel
| to run business analysis though.
| dav43 wrote:
| 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 wrote:
| 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.
| ttyprintk wrote:
| ArcGIS, SPSS; a lot of unwieldy packages with legacy format
| considerations allow you to specify a virtualenv.
| marcodiego wrote:
| Didn't read it but, couldn't this be a "battlefield" where
| LibreOffice could have unbeatable advantage?
| billfruit wrote:
| 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 wrote:
| yes python supppoted in libraoffice
| fock wrote:
| 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 wrote:
| Yes.
| varunnrao wrote:
| 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.
| buovjaga wrote:
| 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...
| varunnrao wrote:
| I hadn't come across the ScriptForge library before so I
| just looked it up. It looks pretty cool and definitely
| looks like how MSFT should have worked on including Python
| in Excel. From my quick look, it looks like ScriptForge is
| primarily a BASIC which supports Python(?). So I can only
| imagine how much more powerful a Python specific library
| could be given the differences in Python and BASIC
| themselves.
| buovjaga wrote:
| No need to use BASIC, you can use ScriptForge in
| LibreOffice Python macros directly. ScriptForge is also
| available for use with LibreOffice BASIC macros.
| 2Gkashmiri wrote:
| I dont understand why doesn't excel have local Python. This feels
| wrong on so many levels.
| breckognize wrote:
| 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 wrote:
| looks cool!
|
| do you have a desktop app in the works?
| breckognize wrote:
| We have some development desktop builds working. Is it
| something you'd pay for?
| yvely wrote:
| Looks very cool. Will be keeping an eye on this for local
| network hosted and/or desktop application version. Thanks for
| sharing!
| breckognize wrote:
| We have private hosting available (in your VPC) for
| enterprise customers.
| IshKebab wrote:
| 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.
| fzumstein wrote:
| 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.
| breckognize wrote:
| 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.
| fzumstein wrote:
| No offense taken, and happy that xlwings was an inspiration
| for creating Row Zero! I don't really buy the security
| issues though for being the reason for switching from Excel
| to Row Zero. Yes, Excel has security issues, but so does
| the cloud, but at least the issues with Excel can be dealt
| with: disable VBA macros on a company level, run Excel on
| airgapped computers, etc. Promising that your cloud won't
| be hacked or is unintentionally leaking information is
| impossible, no matter how much auditing and certification
| you're going through. The relatively recent addition of
| xlwings Server fixes pretty much all of the issues you
| encountered in your previous company: user don't need a
| local installation of Python, but the Office admin just
| pushes an Office.js add-in to them and their done. No
| sensitive credentials etc. are required to be stored on the
| end-users computer or spreadsheet either as you can take
| advantage of SSO and can manage user roles on Microsoft
| Entra ID (that companies are using already anyways).
| rldjbpin wrote:
| 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 wrote:
| 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.
| laffra wrote:
| Your second proposal looks like https://pysheets.app
| olvy0 wrote:
| Related:
|
| Show HN: I've built a C# IDE, Runtime, and AppStore inside Excel
|
| https://news.ycombinator.com/item?id=34516366
| openrisk wrote:
| 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.
| laffra wrote:
| 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 wrote:
| https://news.ycombinator.com/item?id=40179566 - PySheets -
| Spreadsheet UI for Python (pysheets.app) - 287 points - 47
| days ago - 74 comments
| CPLX wrote:
| Seems to me that Airtable is pretty much what you're talking
| about, and is spectacularly useful.
| Lukas1994 wrote:
| 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
| varunnrao wrote:
| 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 wrote:
| > 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 wrote:
| > 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.
| varunnrao wrote:
| > 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.
| qsi wrote:
| You can do this through COM. I forgot which Python library
| I used but once you have an Excel application object you
| can do
|
| Data = xl.range('a1:a3')
|
| Sum = xl.worksheetfunction.sum(data)
|
| Xl.range('b3').value = sum
|
| Any library enabling the COM link will do.
|
| (sorry, typing this on my phone so formatting and
| capitalization are screwy)
| whywhywhywhy wrote:
| >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 wrote:
| > and even adding a button is an ordeal for them.
|
| It probably is, though..
| greentxt wrote:
| "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 wrote:
| 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 wrote:
| 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.
| fzumstein wrote:
| 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.
| snthpy wrote:
| 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 wrote:
| 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.
| fzumstein wrote:
| 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.
| laffra wrote:
| 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
| kkfx wrote:
| 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_man wrote:
| _> 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?
| kkfx wrote:
| 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 wrote:
| 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.
| keithalewis wrote:
| 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.
| localhost wrote:
| 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 wrote:
| 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.
| localhost wrote:
| 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.
| ttyprintk wrote:
| 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 wrote:
| This was interesting but seems focused on how, not why. Like
| why not python as alternative to VBA, and why cloud only.
| cbsmith wrote:
| Madness. I tell you it is madness.
| petsfed wrote:
| 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 wrote:
| 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 wrote:
| 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 wrote:
| 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.
| petsfed wrote:
| This is where I land too. Python is very handy when I need
| to do something quickly, I expect to modify exactly what
| I'm doing often, and security and efficiency are not core
| goals.
|
| But every time I start to write a class in python, it feels
| like bikeshedding to me.
___________________________________________________________________
(page generated 2024-06-14 23:03 UTC)