Excel team considering Python as scripting language: asking for feedback
Folks, Microsoft is officially considering providing Python support in Excel (finally). If you are interested in this, please visit their uservoice page and let them know what you think. Thank you!
https://excel.uservoice.com/forums/304921-excel-for-windows-desktop-application/suggestions/10549005-python-as-an-excel-scripting-language
279 comments
[ 4.2 ms ] story [ 242 ms ] threadhttps://excel.uservoice.com/forums/304921-excel-for-windows-...
[disclaimer+bias: on Python team @ msft & would love to see this happen!]
https://www.python.org/dev/peps/pep-0551/
There's a YT talk as well.
Well, I guess that explains why you care about Office in 2017.
I'd argue large companies are still running Windows because of Office. The cost of retraining people, redesigning all of these user processes and converting all those documents would be massive. Whereas most new corporate applications in the last 5 years have been mostly web based.
So I am surprised Microsoft under-invest in what is they main strategic lock-in in the juicy enterprise market.
The first release to feature the Ribbon, arguably one of the all-time major changes in Office, was 2007. That took quite a bit for most users to get used to.
An Office suite is not where you add experimental features for the hell of it. People use it to get the job done in so many different scenarios, any change will significantly impact entire industries.
Office programs are the "lawyers" and "accountants" of the software world: their work has been more or less the same since they existed, and any major change to them is a basically societal upheaval, so their approach will always be naturally conservative.
There are many things lawyers, bankers, consultants and accountants would need that Office doesn't do. Linking a spreadsheet to a powerpoint document is a nightmare right now. Linking spreadsheets between them too. There should be a way to express a UDF as a spreadsheet so that people who can't code could create their own UDF. I love Apple's Number canvas approach, where a sheet is not a grid but a canvas on which you can add grids or charts, and they overflow with a scrollbar. Etc.
There are lots of new functionalities they could add that would make people's life better. Instead these products barely evolved in 20 years. Click the "fx" button in excel 2016 and you will get the same non resizable dialog box with a tiny listbox and a search box that doesn't search anything than in Office XP.
* Python 3
* Numpy, SciPy, Pandas, Matplotlib, Altair, ...
* Pythonic bindings for Excel APIs (sheets, etc etc)
* VSCode's Python Editor + Debugger built in
* Some sort of conda based env/pkg mgmt
* Right-click, "Open in Jupyter" (data/code)
* <your wish here>
I mean, MS implemented these really nice Tables in excel, but then left us no way to easily query them with something like SQL.
https://www.querystorm.com
Are there plans to get this working with the Excel web-app, or just the desktop version? I'm just having a daydream about Excel, Juypter notebooks, and OneNote Class notebooks...
Our team provides a free service for anyone to run Jupyter Notebooks on Azure. It's currently a big hit with the Edu crowd. We're seeing lots & lots of universities upload & teach courses on it. eg:
http://notebooks.azure.com/richie
There's also an Intro to Python notebook on the front page.
Hopefully the Excel team will consider Jupyter integration, esp for the web-app version! You're right that it's a killer combo. Check out xlwing's video on using Excel+Jupyter - it's brilliant.
Take a look at the top three results for "excel python" on bing.com for some great ideas on how to incorporate python into excel.
---
1.) https://www.python-excel.org
2.) https://www.pyxll.com/
3.) https://www.xlwings.org/
https://www.youtube.com/watch?v=Gu31s7kXPOg
Huh, on the one hand sounds like a such a simple way to deal with it that I can have trouble imagining a more elegant approach.
On the other hand, does it still respect the ways that Excel updates cells? Sicne that kind of requires immutability I think.
The https link doesn't seem to work, try this:
http://www.python-excel.org/
Aside from that it sounds like a cool idea.
Also extremely popular with people who are programmers and need to do things.
Sorry, this is a frustration of mine lately. Python is a fantastic introductory language. It's also a fantastic general purpose language.
Newbies get turned off of it because it feels too easy, and they know that "real" programming is supposed to be hard.
Python is great for scripting and for small projects, but I believe strong, static typing is an essential feature for large scale projects. I wouldn't want to use Python for anything that's predicted to end up with more than a couple thousand lines of code.
Edit: I suppose my point got a little lost. What I mean is that I highly doubt much Excel code is a "big project." It may feel that way when you crawl through some of the hideous VBA coding I've done but much of this is due to the inexperience of the coder and the realities of VBA. Give me python + 4 or 5 libraries and I could recode anything I've ever made in VBA in 1000 lines or less.
I understand, though: you hate VBA. And by extension, Access. But Excel, with or without Python, doesn't mean it can suddenly be used as a relational database. But you knew that.
So, what should replace Access?
Most used: everyone pays taxes. Paying taxes is popular!
A more practical example is javascript. I write some javascript, not because I like it (I hate it), but because that's the only way to make things happen in a browser. Javascript is popular. Does it mean it is liked / a good language?
No it does not, neither in theory nor in practice. Also, "less code" depends primarily on the language structure and not on whether the language is dynamically typed or not. (Haskell, for example, is more terse than Python.)
I've yet to come across something that was fewer loc in C++/java than in python.
That said, this is true even if I use pytype.
Every sufficiently large test suite will contain an ad-hoc, informally-specified, bug-ridden, slow implementation of half of a proper type system.
(I too enjoy a good pseudo-Greenspun)
More seriously something I've been pondering a lot recently watching the old pendulum swing back towards an enthusiasm for explicit typing is this:
* The advantages of static type systems are obvious and easy to articulate. * The disadvantages of static type systems are subtle and difficult to argue.
I started my career as a professional programmer when the pendulum was moving in the other direction. Essays by Paul Graham on Lisp and Python. The marvellous PJ Eby piece quoted above and Peter Norvig's "Design Patterns are artifacts of language flaws".
I just feel dynamic languages fit my brain better but maybe that's my own form of Stockholm Syndrome. Maybe I need to try a decent type system rather than the brain-damaged descendents of Java...
After some time doing that a Java project came up, so I grabbed Lombok, Vavr and started writing Java as if it was just another ML (immutability first, paying attention to side effects and so on) and the whole thing made sense. More sense than all those years of OOP teachings. The code was easy to debug, easy to reason about, easy to change. And it was Java. And that just stunned me for life.
Then of course, I started using TypeScript for React development and giggled like a little girl every time I had to refactor something, for I KNEW that it was very unlikely I'd have to stare at the debugger for long periods of time in a wild goose chase like I often had to with plain JS.
But the trick was to learn the way of doing things in the languages that really guide you towards that path.
I can definitely recommend that you try Elm if you're into frontend development, or something like F# if you want native. As far as docs go, the Elm guide and fsharpforfunandprofit.com are both great; the latter I can recommend regardless of your language choice for making typed functional programming make sense. I can also recommend the book Type-Driven Development with Idris, which has also been an invaluable resource to really understand that way of doing things.
Anyway, I doubt that a VBA replacement would need types. The use case is small scripts.
There's room for both paradigms - it's kinda silly to argue strict superiority of either because there's just no empirical evidence that having static typing or not drastically changes bug count.
If you take a look at some of the studies out there that do exist (which there are, admittedly, few, and it's a fundamentally tough thing to measure), e.g. [0], it usually tends to be the case the both typed and untyped languages show up in the realm of "least likely to produce bugs"
[0] http://web.cs.ucdavis.edu/~filkov/papers/lang_github.pdf
Put me with Op. I'll use Python for prototypes and small tools but get past that and I want a statically typed language. Not just for validation but also refactoring.
You're testing things that need testing either way, and incidentally also testing the types.
However, it tests values for correctness. Values have types. So if you are testing whether something has the correct value, you are also testing implicitly that it has the correct type, because for the values to match, the types must also match.
That's what the vast majority of testing is when it isn't simply testing mock code implementations.
My only complaint about Pytype is that there's no `Char` type at compile time (ie `for x in "a string"` -> Iterable[Char] instead of Iterable[str] during typechecking). But alas.
[1] https://github.com/python/typeshed
On the flip side, i use interfaces and dependency injection constantly in java to work around static typing. In python, i write probably ~20% the amount of code because i never use interfaces, wiring logic or convert types.
Write a Python program. Compile it as a Cython program. (Already faster, with no changes.) Add C types to the speed-critical parts. (Up to many 100s of times faster than Python)
Python programming is an aesthetic that needs learning. Many of the worst written, and least maintainable python codebases I've seen are by programmers/teams coming from "proper" languages and don't think they have to learn how to write idiomatic python.
"So, the sad thing is that these poor folks worked much, much harder than they needed to, in order to produce much more code than they needed to write, that then performs much more slowly than the equivalent idiomatic Python would."
I included a link to this blog-post in the "letter of intent" (don't know the exact English term) that I sent to my potential employer just before my first interview for a professional (Python) programmer job, back in 2005. I got the job. Good times.
Best example I've seen was from a couple of Java devs transitioning to python, they re-wrote a system 3 times: 1st time: 35k loc 2nd time: 10k 3rd: 2k
The third rewrite was also significantly faster than the first two :)
This is empirically false.
There are (of course!) good reasons to consider static typing. But, in my experience, use of static typing has never been a first-order predictor of business or technology success. It's quite possible to build considerable value with, for example, a large Python 2.x code base.
A fun, and tangentially related, talk: https://www.destroyallsoftware.com/talks/ideology
For example, nullability annotations reduce nil pointer exceptions.
Static typing removes the need to make certain typing unit tests, makes refactors easier to do in large code bases, makes it easier for compilers to generate faster code and so on. Think of them as compiler level linters.
Anyway, python has a static gradual typing mechanism. You should try it out :D
http://www.esug.org/data/ESUG2004/ValueOfSmalltalk.pdf
In my view, classes are types. (Well, maybe I'm just spoiled by C++.)
I understand the word "type" to mean a property of a variable which restricts the range of values it can hold, and the set of methods which can be invoked on it. Smalltalk doesn't have any way to do either of those things, so it has no types.
Interestingly, it seems that this was planned for Smalltalk, but never implemented; a 1981 article about the design of Smalltalk [1] says:
"Also, message protocols have not been formalized. The organization provides for protocols, but it is currently only a matter of style for protocols to be consistent from one class to another. This can be remedied easily by providing proper protocol objects that can be consistently shared. This will then allow formal typing of variables by protocol without losing the advantages of polymorphism."
[1] https://www.cs.virginia.edu/~evans/cs655/readings/smalltalk....
> big boon
But they argued ‘essential’. You’ve watered that down to nice-to-have.
Not everyone likes IDEs (Emacs and VIM are still by far superior to many) and not everyone wants to deal with all the extra code and boilerplate and ad-hoc data classes that comes with static typing, to name a few.
Dynamic languages have faster iteration times and from experience that can yield higher quality software. They're easier to fit in the functional paradigm, better to model data transformations, and a bunch of other goodies.
You can't judge something without taking into account the context in which its used. And for scripting something like Excel dynamic is clearly superior.
You may like plain text editor but for someone who doesn't know how to program, typing a variable then dot, and having a drop down of what is available from there, with an embedded documentation and direct IDE feedback on what is correct or incorrect syntax immediately after typing every character is super useful. RTFM isn't novice friendly.
Its still possible for dynamic languages to have auto-completion. There's way more information available at runtime than at compile-time.
Besides, IDEs tend to have the entire world in most autocompletions, which is not useful either.
There would be no IDE here, you'll probably still write code from within Excel and advanced users will use separate source files to leverage their editor of choice.
The novices you mention will not want to leave Excel. A dynamic runtime with reflection is all you need to give a friendly experience. That doesn't prevent type hints, inference or autocompletion.
I would hate a typed language in there. Besides most static typing systems are ridiculously weak and introduce more headaches than they actually solve.
E.g. Go (and I still love Go, but it's pretty weak...)
With some of the spreadsheets I've seen, the next Netflix might just come out of it.
Just kidding, but only a bit. I've been at factories where if VBA stopped working, I literally don't know if we could have produced product that day.
You mean like Dropbox, Evernote, Ansible, OpenStack etc.
Python is strongly typed and has an available static typechecker, so, even if this is true, it doesn't rule out Python for this use case.
because it has no long term potential
It also allows a developer to manipulate individual bits, which is pretty amazing for security research or other cases when you want to get low level but don't want to get in the weeds with C.
You'd be hardpressed to reinvent excel in a browser. Sheets is a big, advanced web app but it doesn't even come close.
No true scottsman etc.
Why do you find it so fantastic? What stands out for you?
Python was my first real programming language, but I don't see anything very attractive about it now - save the libraries.
I would even say it would fit better, not because the language is better, but because indentation wouldn't go in the way for quick & dirty one-offs.
How do you get that kind of "watch" behavior with `xlrd` and `xlwt`? You don't, you have to re-run a script. Sucks.
But the point I'm making is that: It's still a good thing for Excel to be integrating more tightly with python. It'll help all of those non-developer normies get the magic of coding.
But the scars from that era are so vivid in my mind, that I feel the need of a confirmation.
That MS is now a gentle, caring beast? Large organizations are not like that, they are money making machines that adopt strategies to maximise profit. EEE is no longer such a strategy, though it worked very well for a long time.
Will MS ever go EEE again? In a heartbeat if they felt it would maximise profits.
Does that seem likely in the short to medium term? No, the industry is different now, and they have to play nice.
They still do the EEE strategy. Fool me once...
When working with software like office, you are going to have a lot of users that don't exactly spend their days programming, or studying syntax. Excel is a little different in their own regard. The functions system has a lot of depth to it, and makes sense to a cell system, but for some it can be a but overwhelming in terms of how its written, since everything has to be written exactly as a function. I guess you also have generic office macros, haven't used them since high school.
In saying that, could this somehow be a reason why the office team have decided on a python interpreter, instead of say JavaScript or Microsoft's work on Typescript. Because python is easy to write, fast to learn, therefore users or even really employees in large data management positions don't have to worry about their time input?
Henry Ford: "If I asked customers what they wanted, they would have said faster horses"
Steve Jobs: "A lot of times, people don't know what they want until you show it to them"
Seriously. Just build it and and make it great. When you can show that you can do amazing things with it, release it. The downvoters will also then adopt it, if you made it great.