My thoughts on Python in Excel

My thoughts on Python in Excel
xlwings.org

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

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

    let x = 1
    let y = 2
    x + y

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

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!