Microsoft teases Python scripting in Excel Microsoft has added support for running Python code from within Excel, bringing the world's most popular programming language, by some measures, to the world's most popular spreadsheet. Sorry, Visual Basic. "Today, we are excited to introduce the public preview of Python in Excel – making it possible to integrate Python and …
I love the idea of being able to use python instead of their terrible BASIC (VBA), but now /every single/ python calculation in the spreadsheet gets sent to TEH CLAUDE and back?
So now even simple spreadsheets are network chatterboxes and don't work at all if you don't have network access, like on a plane or way the eff out in the field, or maybe your ISP is just having yet another outage.
The obvious reasoning for this is so they can have a peek at your data and even more so they can charge you fat subscription fees for it. They're trying to make Office entirely cloud hosted anyhow (so you can't even run it locally) so this would be one more step towards that.
I can see why they'd not want to let Python loose inside Excel, on customers' PCs, able to cause havoc. But yes, shoving it up in t'Cloud is going to be annoying.
Or, could be useful: "Wiggins!" bellows the boss, "Where's your end of year sales figures? They'd better be good mind you, or your for it <insert rambling stream of typical managerial threats involving paperclips, the stationery cupboard, the worst taxi company in town and an ants nest>". Wiggins: "Well, thanks to our new version of Excel, all the numbers are stuck in the Cloud, sir, but I can assure you that they were most excellent, especially once I'd manipulated the dependencies sir (books one way ticket to, well, anywhere)".
Now that you mention it, yes, Rube Goldberg -- master of wildly complex "solutions" to simple problems -- would no doubt find much to admire in the computation of spreadsheet cell using a distant computer and an elaborate communication network.
BTW, I'm pretty sure that Open Office has had Python scripting in Calc for a LONG time. My vague impression is that it works fine (and without the cloud) but isn't very useful for most people most of the time.
It is only a "nice concept" until you ponder just how horrible a complex spread sheet is to audit/debug in practice.
You know, the one Jimmy boy in accounts did years ago and then left with no documentation?
There is clearly a place for data analytics, etc, but spreadsheets are already way over-used and abused for this. Sadly for most managers/accountants this is the only tool they know about, so it gets used as a computing hammer. But such business "dependency" is EXACTLY what MS wants to encourage to get people locked in to endless subscription payments.
Why would anyone use Microsoft Excel? OpenOffice is better and it's free:
You can assign custom scripts (macros) to menu items, icons, dialog controls, and events in OpenOffice.
OpenOffice internally supports the following scripting languages:
1. OpenOffice Basic
2. JavaScript
3. BeanShell
4. Python
Hey, I love open source, and yes, VBA is bloody awful, but please tell me why Open Office is better than Excel. You've listed the scripting languages, which are definitely more varied in Open Office, but remain irrelevant in 99% of use cases. So what is so much better in OO compared to MS Excel? I've recently had to start using OO at work and on any objective measure, it is NOT better software than MS Office, it just isn't.
I hate Windows and refuse to use it - there are far better alternatives for an OS. MacOS, Ubuntu, and Mint all fit the bill. There are much better web servers than IIS, better hypervisors than Hyper-V, and better web frameworks than ASP.net. But for office apps, I don't see it fella. What exactly makes Open Office better than MS Office?
Less susceptible to malware would be my immediate thought.
If something requires something more sophisticated than OpenOffice or LibreOffice then the wrong tool is being used for the job. This is particularly true for Excel where people use it for 'production' purposes all the time. I write database applications to combat this which, if a spreadsheet needs to be surfaced, I write that xlsx out as a plain file with no embedded macro capability, or csv which is 'plain' by definition. Maybe an MD5 hash or similar mechanism would be needed if tampering were considered a problem.
'Production' in many instances I've encountered in industry is for the purpose of sharing or exchanging data with external companies. It seems that companies that specify xlsx or csv formats possibly feel that a more appropriately 'robust' sharing method is just too complex for their partners to get their head round. I appreciate EDI is overkill for many applications, but there are alternatives.
Excel recently introduced, the world’s least popular programming language, JavaScript, and that runs locally.
Maybe you could integrate Python stuff into that using Brython or Pyodide?
As for whether you should, we’ll we are talking about JavaScript, and clearly nobody ever stopped to ask that question, because lots of people use it.
Just my opinion though. Would finally fix the powershell <-> excel bridge much better. And I could stick to use "english" and programming language. Anyone who has ever seen those horrible translations of the excel language (if then sum etc formula words) translated into their native language, here German, knows what I talk about. I cannot handle the translated programming language, especially since I started with MS-basic 2.0 and 3.5 on 6502 / 7501 CPU, and no easy way to turn it to "use english function names dammit" there. Something libreoffice can.
Something else LO can do: Python macros. No fekkin cloud required :)
What LIbreOffice can't do is VBA. If OO had ever got an effective VBA implementation, there wouldn't have been any interest in python macros.
FWIW, two problems LO has with VBA, that python solves: LO can only do "declare" on Windows and Mac, and, even on those platforms, it doesn't do stack protection (required both for stability and to prevent clusters of CVA's). That means you can't do external calculations and interfaces on linux -- one of the required use cases both for VBA and python.
One thing missing from LO, that Excel will probably have: proper integration into the Excel environment. The LO implementation of python is effective on supported platforms (how much effort it takes to get it to work on linux depends on which distribution you have), but the integration into the LO programming environment is half-assed. I find myself saving the spreadsheet, unzipping it, opening and editing the python files in vi or notepad++, then saving, zipping, and re-opening in LO.
VBA may have been well-intentioned but it's probably one of the worst decisions Microsoft made with Office. Too difficult for the Office-scipt kiddies and too much of a toy for the programmers. The limitations are one of these reasons why tools like Pandas were developed. Since then Excel has become less and less of a runtime and more and more merely a reporting format.
Actually, it was developed by language experts for novices and ended up being hated by both. I wrote my first ever code in BASIC and have always liked it as a language for novices. When Microsoft went all .NET it decided that VisualBasic had to as well and suddenly forced a load of new constructs and syntax onto people that didn't help them one bit. After that VB and VBA fell quickly in popularity because, if you're going to use static typing, etc. you might as well use C#, Java or even C++. But the majority of users where overwhelmed and looked for alternatives; it's no coincidence that Pandas and Jupyter Notebooks were developed with the Excel community in mind.
Ok, I've read that they've gone and containerised it a long, long way away in the cloud. But, I honestly don't get the point. If you're going to program in Python, just use Python. Surely, the Excelness of the experience would simply be an embuggerance for the Pythonista, and the Pythonness of it won't help anyone just wanting to use a few simple formula... And the portability of the end result is...... nil.
Python and business users are like the difference between a professional builder and a home DIY hobbyist. To do something well you need to be doing it year after year, business users slap some scripted lines together they copied off Stacktrace and then go ahead and make multi-million pound decisions based on their 2 weeks of coding prowess! Coding, like building, might look eaiser to the hobbyist but what you don't see are years and years of experience to know when something is done properly, coders will unit test, QA code, double check, they don't just slap Stacktrace snippets together and pray it works ( well most don't! ).
I'm not a fan of Python, its slap-dash formatting style just makes me feel unwell, I don't wish anyone to stop using it as it's getting kids and non-coders to enjoy coding, just when you need serious coding ( or a wall built ) please hire a professional.
But, I honestly don't get the point
Well, I can answer that. Because, for some problems, the spread sheet is the preferred/appropriate user interface.
I did my "Mathlab" problems in Fortran. My Dad did his 'accounting' in BASIC. But that was because Mathlab and Lotus 123 weren't options at the time. I totally understand programmers wanting to program in /whatever language/, but programming a spreadsheet user interface in python just because you don't want a LO dependency is a sub-optimal solution
I can give an example from our developers this morning: sets of poorly curated, incomplete and non-normalised customer locations from business to be imported into our database. Developer converts to CSV, loads in Pandas, cleans up, normalises and imports. Leaving aside that the conversion to CSV wasn't necessary and is IMO never advisable (lose types and character set becomes an issue), this is a task that can largely be performed by business in the future. Yes, a heap load of provisos but we will be able to write the functions that they run directly in Excel.
"the cloud habitat for Anaconda's Python will presumably be more secure than having Excel users downloading libraries on their own. Having anticipated concerns about enabling yet another attack vector in Excel, Microsoft has preemptively declared that its snake has been tamed.'
HA HA HA HA (oh wait, they're serious; let me laugh even HARDER!)
What part of THIS is 'more secure' ?
* Data stored in the cloud where governments and evil hackers can view it
* relying on "their server" performance including the pipeline to/from it
* DDoS, downtimes, network problems, MItM and DNS cracks, etc. NEVER HAPPEN, right?
More exist but another post near the top hit on the best ones
Given the repeated references to Pandas and Anaconda, I suspect that what this is really about is using Excel as a front end to Pandas (a major Python data analysis library).
Pandas is so important and so widely used in certain fields of application (data science, machine learning) that people are learning Python just in order to be able to use it. People who had previously used Excel for certain small to medium size tasks are dropping it in favour of Pandas when Excel starts to run out of steam on really big tasks. The data and calculations will reside in Pandas, while Excel is used for data entry and reporting.
Given this, the way that Microsoft can respond is to build Pandas access into Excel. In order to do this they need to add Python scripting into Excel. They then run the calculations in the cloud because the really lucrative (for Microsoft) applications will be ones which are too big to run on a PC.
I suspect they will eventually offer tiered accounts, with prices escalating with cloud instance size.
R is nice. It is even more readable then Perl!
But seriously, if you learn Python for pandas, with some luck you might end up learning Python, a generally useful programming language. With R you end up with a lanague which is very efficient at specific things and frustrating when you need also to do some ‘normal’ programming.
While Pandas is an example of feature creep it has helped millions of people to do things they struggled to do, usually in Excel, before. R may have its fans but I know a number of people who've dropped it for Python because of the other stuff they need to do.
Pandas itself is getting a rewrite but alternatively there is Polars (written in Rust) which has the same idea but without the cruft.
Pandas uses Numpy for all its data structures but it is terrible at indexing, which is why it's usually better to create new dataframes. Polars (written in Rust) uses Apache Arrow for dataframes, which means no memcpy(). The API looks a lot cleaner than Pandas all singing and dancing one.
If you're going to learn python to do the analysis you might as well use it for the reporting too.
As for using Excel for the data entry, apart from it being hard to control inputs, if people are entering data by hand you probably don't have enough volume to really need to go beyond Excel in the first place
Yep, excel wasn't designed to work with large data sets. Don't get me wrong it's still a great tool.
I had some python experience from some sysadmin tasks. I was given a spreadsheet sheet with approx 500k records and needed to identify the duplicate records based on different data fields. Even the 64bit version of excel would lock up.
A day later a 14 line script with pandas identified the duplicate records and outputed the common records to another file so we could investigate and reconcile the records. Took it about 5 mins to spit out the output.
"In a post on data security and Python in Excel, Redmond lists the following plus points.
Excel's Python code runs on hypervisor isolated containers built on Azure Container Instances.
The container has Python and a curated set of secured libraries provided by Anaconda.
The Python code does not have access to the user's computer, devices, account, the network, any user token, or workbook properties, including formulas, charts, PivotTables, macros, and Visual Basic code."
Someone from Microsoft has already been in touch to let me know that the Python code will be serialised as text in the files. This will make it possible to share whatever is required for the report. Need to decide whether I'll expose this or just preserve it in the Excel archive.
Recently there was literally nothing else I could do get a macro I'd written on another machine working in Excel on a personal laptop. I went through various steps of trying to get Trust Centre to trust everything, trust the place I'd originally written the macro and so on, all with no joy. Absolutely useless. Add to that Microsoft now has three Macro languages for Office - VBA, Office Script* and now Python**. Given the opportunity, I'd gladly move to Libre (or even Google).
* Enterprise licence only.
** With whatever caveats.
The Microsoft of old (when Bill Gates was still CEO), the Office team took the bold decision of scandalizing on one language and runtime that would automate other applications (if you happened to think that was a good idea). We decry VBA now, but it has been frozen for two decades.
The truth is the Office team are out of idea, and churning out new demos to make people think they are getting something for new for their subscription fee. Delivering Office via a browser seems like an incredible feat, but functionally similar to running Multiplan & Word on Xenix in 1981. Providing hosted access to Anaconda Python streamed to client/browser looks incredible, but functionally similar to using MS Multiplan with SAS or SPSS in the early 1980’s.
A bold Microsoft wouldn’t just add another demo product, but integrate Python fully into Windows & Office, or better still – do something better
Oh, for god's sake. I've only just persuaded the powers that be to purge the unsupported / unmaintained / uncommented and unresilient python scripts that permeated through a bunch of business processes - only for the business to make the team that did them redundant then wonder why crap was breaking.
Another tool on the stack is not what we need to get things in order. Replacing VB with Python one supposes could be be an improvement.
But too many people are in the business of agile development and shortcuts rather than favour measured development with clear objectives and resilient design.
Blech. I'll be glad to get out of business sooner rather than have to deal with such shiat.
The Register 
Biting the hand that feeds IT
Copyright. All rights reserved © 1998–2024
