Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Microsoft is bringing Python to Excel

theverge.com

Post reply on HN
Print view View original

121–130 of 455 posts

Re: Microsoft is bringing Python to Excel

#121

Post by mrits »

baz00 wrote: post #81

I have to see some red flags here. The very nature and power of Excel comes from the fact that workbooks can stand alone. This means that the process is eternally tied to the cloud now. So when you, 3 years down the line, have to open a workbook from your deceased colleague, you are at the whim of the vendor supporting the existence of this integration. Step one of any workload will always be "work out how to make th…

I haven't used a non-cloud spreadsheet in probably 7 years. I also wouldn't use excel for anything that has a combination of high importance and long lifetime though. It's mostly for some brainstorming or thrown in a presentation as a screenshot.

Re: Microsoft is bringing Python to Excel

#122

Post by mulmen »

john-radio wrote: post #79
sixothree wrote: post #69

Earlier quoted context omitted.

Can you just please fix the issue with date strings (such as "20230822090811") being converted into scientific notation? I've received literally hundreds of these documents over the past decade. And as long as this problem exists, I will keep receiving them. Can you please just add a button - "unmess this document"? The number of man-hours I've wasted re-creating documents is countless. My job is to do other things n…

The scientific notation ("2.02308E+13") is just how Excel displays a large enough numeric value like your example; it's not doing any conversion, as you can see if you paste that number into Excel, confirm that it's showing the scientific notation, and then click into the cell to confirm that the formula bar still shows the original number (20230822090811). If you paste your date string in a more standard date format…

Excel only maintains 15 digits of precision for numbers. Everything else gets truncated. This truncation is done at import so it is unrecoverable.

This burned me frequently when I was generating tax audit reports for accounting. The billing system IDs were all BIGINTs but had over 20 digits.

Re: Microsoft is bringing Python to Excel

#123

Post by fullstop »

pid-1 wrote: post #65
RcouF1uZ4gsC wrote: post #34

Earlier quoted context omitted.

Because Python, with the required numerical analysis libraries is a pain to setup, and there are a ton of ways for it to get messed up. Doing everything in the cloud massively simplifies deployment and support.

Microsoft could ship its own Python distro within Excel. The biggest problem is that historically Guido / the Steering Council have avoided being involved in how py libs are distribuited, so there are many ways to do everything. But if you do have an opinion and means to enforce it, mantaining a Python environment can be a quite smooth experience.

Doesn't Guido work for Microsoft now?

Re: Microsoft is bringing Python to Excel

#125

Post by _8j50 »

cableshaft wrote: post #13

I wish it wasn't solely powered only by Microsoft Cloud and could support python running locally, but regardless I think this will still be huge and single-handedly modernizes Excel by a large margin. This alone could eliminate the need for websites that just want this sort of data. I can think of a past project at a previous job I did building an analytics website used only by a handful of people internally that cou…

I too would love this, but I can tel you, even in a sandbox python would be very easy to abuse from a security perspective. Consider how much havoc on humanity (no exaggeration!) VBA Macros have caused.

Re: Microsoft is bringing Python to Excel

#126

Post by ooterness »

john-radio wrote: post #79
sixothree wrote: post #69

Earlier quoted context omitted.

Can you just please fix the issue with date strings (such as "20230822090811") being converted into scientific notation? I've received literally hundreds of these documents over the past decade. And as long as this problem exists, I will keep receiving them. Can you please just add a button - "unmess this document"? The number of man-hours I've wasted re-creating documents is countless. My job is to do other things n…

The scientific notation ("2.02308E+13") is just how Excel displays a large enough numeric value like your example; it's not doing any conversion, as you can see if you paste that number into Excel, confirm that it's showing the scientific notation, and then click into the cell to confirm that the formula bar still shows the original number (20230822090811). If you paste your date string in a more standard date format…

Except Excel doesn't recognize ISO timestamps either.

Re: Microsoft is bringing Python to Excel

#127

Post by fzumstein »

xlwings creator and author of the O'Reilly book "Python for Excel" here! First of all, big congrats to the team! I've been in contact with the Excel team on and off over the years and I remember when an Excel project manager once described the task of adding Python to Excel as "turning a fully loaded ship". Well, I am happy that the ship has now turned and is ready to ship into more exciting waters! There are a few question marks I have given my decade long experience with the topic (although everything is still beta, so it will certainly change/improve on its way to GA):

(1) I have hardly seen any company that can do with an off-the-shelf Anaconda distro. Companies usually have an internal Python package that they will need to access. (2) When running Python on the backend, the first question is always "how can we authenticate the user"? Office.js is currently the only platform I know of that allows you to leverage Azure AD identities via SSO or use any other provider (as you have complete freedom to use any JS library/redirect the user to a login form). (3) IT policies: Usually, companies have made their cloud decision: “We're an AWS/GCP/Azure shop, so Python has to run on precisely AWS Lambda/GCP Cloud Run, etc.” Yes, many are on Azure, but even in Azure they may have preference of let's say Azure Container Apps or AKS instead of Azure functions. (4) The other thing that businesses are obsessed about is to securely protect their source code, again, not something that Python in Excel seems to support. (5) And finally, what I see users most excited about in the context of xlwings is being able to run standard User-defined functions (aka "Custom functions) on the server (like the ones we wrote in the good old VBA times or like the new Lambda functions), not sure if that's possible or on the roadmap for the official version.

With the modern xlwings Server, I have taken a different approach: Let users build a 100% standard Python web app using their favorite framework (Django, Flask, FastAPI, ...) including all the standard tools (logging, auth, etc.) while using Excel as the frontend. Users have complete freedom in choosing their tech stack, they can version-control the source code on GitHub, use GitHub actions to run unit tests and deploy the code automatically to their favorite cloud, etc.

So I am probably targeting more professional developers than data scientists, but in my experience, it’s often a professional developer who write the Excel add-in that is then deployed to business users/data scientists.

Re: Microsoft is bringing Python to Excel

#128

Post by mulmen »

DandyDev wrote: post #103
sixothree wrote: post #91

Earlier quoted context omitted.

Do you _really_ think I've spent the past 10 years getting files like this and I still don't understand the reason. I didn't ask for an explanation. I asked for a fix. What are you doing here?? I can provide instructions on how to do this until the cows come home. I will still get files with these issues. Is that not obvious? Do you really think I want to examine _tens of thousands of rows_ in documents that are 100…

My my, why so angry? You're expecting something that Excel doesn't support for good reason. The string you used as an example might be a date string to YOU, but to Excel and many of its users it's just a large integer. Lots of regular use cases would break if Excel would interpret that string as a datwle If you want Excel to interpret strings as dates, use a more common date representation such as iso8601

It’s one of the most infuriating and dangerous behaviors I have ever encountered. Excel silently truncates data. The anger is warranted. The problem isn’t assuming a display format for data, it is that a poor assumption actually mangles data. Excel doesn’t fail safe.

Numbers in Excel only maintain 15 digits of precision. Leading zeros are also truncated. If you haven’t experienced this you are lucky (or just didn’t notice) but it is real.

> If you want Excel to interpret strings as dates, use a more common date representation such as iso8601

You don’t always control the upstream system. Your response can be considered rude because it implies you think you know more than the person expressing pain. This is especially insulting with such a widely known problem in Excel.

This behavior burned me with billing system IDs. Logically they are strings, we would never do math on them. For performance they were implemented as BIGINTs in the DB. Excel truncates everything after the 15th digit. Our IDs were all over 20. So accounting couldn’t reconcile our reports with the billing system.

Re: Microsoft is bringing Python to Excel

#129

Post by extraduder_ire »

giaour wrote: post #48
garyclarke27 wrote: post #12

Why does Excel need to run Python calculations in the Cloud? I think I'll stick to VBA. Javascript in Excel would be nice. Even better would be native SQL.

> Javascript in Excel would be nice. JS has been one of the supported add-in languages for a while[0], but that's not quite the same thing as the new python offering (which sounds more like jupyter within excel). Since excel is often (mostly?) used for working with financial calculations, I for one am glad they went with python for this feature instead of a language that insists that an IEEE 754 double is the only ki…

JS has BigInt now, and it almost works like you'd expect. Need to put 'n' after all of your numbers though.

Re: Microsoft is bringing Python to Excel

#130

Post by ayhanfuat »

baz00 wrote: post #81

I have to see some red flags here. The very nature and power of Excel comes from the fact that workbooks can stand alone. This means that the process is eternally tied to the cloud now. So when you, 3 years down the line, have to open a workbook from your deceased colleague, you are at the whim of the vendor supporting the existence of this integration. Step one of any workload will always be "work out how to make th…

I think this is easier to manage to be honest. They are using Anaconda distributions so if you tie each workbook to a distribution with possibility to update/rollback it makes things a lot easier than trying to manage a local installation.
Post reply on HN