Was very excited by the headline but my excitement is dampened by the quote above. So you need to be online to use python since calcs are done in cloud? Not to mention I've refused to purchase 365...
With Python in Excel, you can type Python directly into a cell, the Python calculations run in the Microsoft Cloud, and your results are returned to the worksheet
Was very excited by the headline but my excitement is dampened by the quote above. So you need to be online to use python since calcs are done in cloud? Not to mention I've refused to purchase 365 subscription (and opted for non-sub professional plus). Seems like yet another carrot feature to get people over to O365
I disagree. First: This is designed and aimed at enterprise users. In fact, from what I've read this will only be available to enterprise users. Large companies with licensing agreements aren't...
I disagree.
First: This is designed and aimed at enterprise users. In fact, from what I've read this will only be available to enterprise users. Large companies with licensing agreements aren't nearly as concerned about this kind of privacy. I mean, if you have the enterprise license you are already storing your files in OneDrive/Sharepoint anyway.
Second: SECURITY SECURITY SECURITY. I work in CSEC, and one of the most common points of infection are malicious office documents! Seriously, VBA in office has been a problem since... well, basically forever. The famous ILoveYou virus used office macros. But we also can't live without them. Yeah, there's other options (Jupyter notebooks, local python libs, etc), but I can't force our analysts to use those. This lowers the barrier to entry for people to migrate away from VBA and towards a language that's easier to maintain and has a much larger support community.
Since this runs in the cloud, in an isolated container, with no access to the users machine, and without network access many of the security problems are addressed (or at least improved). If you really want to use python locally with excel there's nothing to stop you from doing that. This doesn't take away those options. There are libraries you can use, or (what I usually do) you can transform the data with python before you bring it into excel.
They could have isolated it just like JavaScript is isolated on the web. I'm pretty sure Python is even designed to be that way. They want to push cloud reliance. If they want security they can...
They could have isolated it just like JavaScript is isolated on the web. I'm pretty sure Python is even designed to be that way.
They want to push cloud reliance. If they want security they can make it a toggle to disable the local sandbox.
Yeah this was my take too - I don't think Grendel's wrong about security concerns being Microsoft's rationale... but specifically, CPython and all of SciPy have already been compiled to WASM (see...
Yeah this was my take too - I don't think Grendel's wrong about security concerns being Microsoft's rationale... but specifically, CPython and all of SciPy have already been compiled to WASM (see Pyodide), so the groundwork for rock-solid sandboxing is readily available. There's no real technical barrier at play.
M$FT are just being lazy/greedy/user-hostile as usual by refusing to offer an opt-in local version. While it's "secure" in terms of compliance and checklists, I'd go so far as to argue that having the data in Microsoft's cloud is a major threat to ground-truth security and data privacy, given Azure's history of ridiculous vulns like ChaosDB.
It's even worse. Microtransactions are the next logical step to milk your customers.
While in Preview, Python in Excel will be included with your Microsoft 365 subscription. After the Preview, some functionality will be restricted without a paid license.
It's even worse. Microtransactions are the next logical step to milk your customers.
There are plenty proprietary addons that are Excel exclusive or have features exclusive to Excel. For example, back in the day at a corporate job I maintained some Excel based tools that pulled...
There are plenty proprietary addons that are Excel exclusive or have features exclusive to Excel.
For example, back in the day at a corporate job I maintained some Excel based tools that pulled financial data from Bloomberg and Reuters Refinitiv. I had some very convoluted flows where Python script pulled data from FTP, opened Excel specifically to refresh proprietary formulas, save results, then went on it's way to do next steps in a 3270 mainframe via Python again. It was ridiculous and if I could do it entirely from Excel environment it'd be much more pleasant experience.
It'd be the best if IT was developing any end user requested features so that wouldn't have to be done in the first place but you work with what you're dealt with.
IIRC Bloomberg has an API now but it's still lacking very specific features found in Excel addon.
I guess my glib point was sort of the opposite of yours. I would just cut out Excel entirely and use Python and Pandas. It’s far more powerful. When I’m working with a dataframe in Pandas, I feel...
I guess my glib point was sort of the opposite of yours. I would just cut out Excel entirely and use Python and Pandas. It’s far more powerful. When I’m working with a dataframe in Pandas, I feel like there’s nothing I can’t do. Excel, like Windows in general, makes me feel like there are constant unnecessary roadblocks in front of me to accomplish my goals, and I’m a former sysadmin. The GUI in Excel is a mess. In Pandas, often enough, complex functions are an easy one-liner. I do understand Excel’s place in the business environment, but any forward-thinking company who leans heavily on Excel should hire some developers and data people and really embrace Python and Pandas. It might initially be painful, but in the long run it’ll give them way more control.
In my example Bloomberg has de facto monopoly in that particular niche. No amount of forward thinking will replace it, unless you're Citibank in the early 90s, about to sell what later became...
In my example Bloomberg has de facto monopoly in that particular niche. No amount of forward thinking will replace it, unless you're Citibank in the early 90s, about to sell what later became Refinitiv.
If you found a data source that matches Bloomberg (you won't because institutions submit data to BB directly), there are years of development to catch up with on silly things like thousands of different regional bond type accrual calculations. And then you'd have to reproduce all bugs because everyone is expecting BB to be correct and it costs less to assume it's right rather than argue with a counter party.
You'll find stories like this in different industries. They're all married to Excel for this and plenty different reasons.
You can do a lot of the basics in Power Query that you can in Pandas. It can be very fast to put stuff together quickly. It doesn't have as good of performance, but it's easily transferable to...
You can do a lot of the basics in Power Query that you can in Pandas. It can be very fast to put stuff together quickly. It doesn't have as good of performance, but it's easily transferable to other people. I use both and will default to power query unless it becomes too complex or too slow. Although I'm sure there are ways to speed it up.
I actually love PQ. What I wish Microsoft would do is enable M as a .Net language you could call from anything else, including Ipython, C#, and F#, for data sanitation and coalition. It works for...
I actually love PQ. What I wish Microsoft would do is enable M as a .Net language you could call from anything else, including Ipython, C#, and F#, for data sanitation and coalition. It works for rapidly prototyping, but once once put together a solution it would be terrific if I could export that part of a solution beyond Excel and PowerBI. Also expand the size limitations. I could process hundreds of thousands of records, but then I couldn't export them outside PowerBI, and Excel was limited in other ways. Python/Pandas with M as the front line would be a powerful combination.
I use pandas on a daily basis bc data scientist, and even I don't think you could just replace Excel with pandas. Ignoring the fact that pandas has a learning curve even among users who already...
I use pandas on a daily basis bc data scientist, and even I don't think you could just replace Excel with pandas. Ignoring the fact that pandas has a learning curve even among users who already know python (which is already gonna be a minority of Excel users imo), there are absolutely things that are eaiser to do and more user-friendly in Excel than in pandas. I would never recommend someone keep track of their budget in pandas, for instance. And when it comes to visually presenting your data, there's a huge variety of things you can do in Excel that aren't possible in pandas.
Even as a relative novice when it comes to Excel and an experienced pandas user, I think they're designed for fundamentally different use cases, and the idea that Excel could be replaced with pandas is really underestimating the use cases Excel excels at (hehehe) and the versatility of the program within those domains.
This isn’t really what I meant. I absolutely never meant that Excel should go away and home users and bookkeepers should switch to Pandas. I don’t think that assumption gives me any benefit of the...
I would never recommend someone keep track of their budget in pandas
This isn’t really what I meant. I absolutely never meant that Excel should go away and home users and bookkeepers should switch to Pandas. I don’t think that assumption gives me any benefit of the doubt. I mean, c’mon.
Your reply elsewhere in the thread said and Your comments in this thread, particularly these bits I quoted gave me the sense that you were overlooking many of the reasons people use Excel instead...
Your reply elsewhere in the thread said
I would just cut out Excel entirely and use Python and Pandas. It’s far more powerful.
and
I do understand Excel’s place in the business environment, but any forward-thinking company who leans heavily on Excel should hire some developers and data people and really embrace Python and Pandas. It might initially be painful, but in the long run it’ll give them way more control.
Your comments in this thread, particularly these bits I quoted gave me the sense that you were overlooking many of the reasons people use Excel instead of pandas, which is why I left a comment describing how there are some fundamentally different use cases between excel and pandas that mean this switch wouldn't make sense for a large number of users. You quote a single glib example I gave but that's the general tenor of the comment I left, or at least what I intended to leave.
I understand that it can be frustrating if someone interprets what you say differently than you meant it but it's really not fair to accuse me of not giving you the benefit of the doubt for disagreeing with the words you wrote. There was nothing in your comments in this thread that indicated to me that you don't think that, say, bookkeepers should switch to pandas. I appreciate you clarifying that you did not mean that, but I can't really divine your thoughts from anything but the words you actually wrote.
Thank you for this comment. It reminds me why I stopped commenting on Reddit years ago and it makes me viscerally feel my addiction to forums in that way when an addict experiences clarity and...
Thank you for this comment. It reminds me why I stopped commenting on Reddit years ago and it makes me viscerally feel my addiction to forums in that way when an addict experiences clarity and shame for what they’ve done to themselves. I’m going to channel this feeling into something productive and kick this bad habit for good.
I can imagine how they decided to do it this way. Anaconda is a huge distribution of open source code that's unvetted by Microsoft. Also, Excel macro viruses are a thing. Consider the implications...
I can imagine how they decided to do it this way. Anaconda is a huge distribution of open source code that's unvetted by Microsoft. Also, Excel macro viruses are a thing. Consider the implications from the point of view of a risk-adverse security reviewer at Microsoft.
Even if Python is sandboxed, that's a big attack surface to run on a customer's PC, exposing it to a macro virus distributed in an Excel spreadsheet. Maybe it's safer to run it in a VM in a datacenter? And naturally, they can charge for that.
(Meanwhile, developers download and run various Python packages on their laptops all the time. But Microsoft isn't responsible for that.)
This is such bad news. Excel sheets with VBA or the upcoming Python stuff are hard to maintain, test and document. I have seen giant monoliths of spreadsheets within organizations that no one had...
This is such bad news.
Excel sheets with VBA or the upcoming Python stuff are hard to maintain, test and document. I have seen giant monoliths of spreadsheets within organizations that no one had knowledge on how it worked, but everyone relied on the results. Such thinks are just setting up giant f-ups within the company.
There are also major risks involved with scripting. Excel isn't containerized, which means scripts can access your file system.
My general rule of thumb: if you need scripting (VBA or python) for a task in Excel, then Excel is the wrong tool for the task.
Microsoft should therefore discourage, not encourage, organizations to use scripting in Excel.
I'm not sure you're really engaging with the comment you replied to. It's not saying "running python scripts on a user's PC is very very bad", it's saying "Excel spreadsheets that contain scripts...
I'm not sure you're really engaging with the comment you replied to. It's not saying "running python scripts on a user's PC is very very bad", it's saying "Excel spreadsheets that contain scripts are a nightmare to maintain and often lead to massive errors, and thus including scripting in Excel spreadsheets is bad." I see no implication that this problem is even affected by where the code is run.
Fair enough, it just wasn't super clear from reading your first comment that you were responding only to that section of the comment rather than its entire point. My mistake.
Fair enough, it just wasn't super clear from reading your first comment that you were responding only to that section of the comment rather than its entire point. My mistake.
I see this as harm reduction. People aren't going to magically move away from Excel scripts, and they certainly won't do it overnight. That's an unrealistic expectation from an enterprise...
I see this as harm reduction.
People aren't going to magically move away from Excel scripts, and they certainly won't do it overnight. That's an unrealistic expectation from an enterprise perspective.
VBA offers no path out of Excel. But, if people start to learn and use python they begin to open up more options for themselves. Sure, at first you just move the maintainability problem from one language to another, but eventually analysts will discover things like Jupyter notebooks and have more options going forward with the skills they've built.
And From a security standpoint, let me tell you, my whole CSEC department is celebrating this as a win. Containerized python that has no access to a users machine, and no network access? Yes Please! There's no way to even begin to compare that to the security nightmare that is VBA.
At worst this provides a more secure option than VBA and gives people an opportunity to build skills (Python programming) that they can use to further their career.
While i basically agree, i also respect there's a couple of use cases, mainly for employee's who work mostly in excel. There's lots of business users who ultimately need data in excel, and will do...
While i basically agree, i also respect there's a couple of use cases, mainly for employee's who work mostly in excel. There's lots of business users who ultimately need data in excel, and will do their work there, so at some point you have to have some scripting to either pull the data in or help organize it.
Still, I've done everything I can to nuke all this where possible, and simplify the hell out of it where not.
There is a very good reason for this. The nightmare that someone can put code in an excel document, send it to one of your users, then that code can execute on their machine is horrible. It's one...
There is a very good reason for this.
The nightmare that someone can put code in an excel document, send it to one of your users, then that code can execute on their machine is horrible. It's one of (if not the) most common infection vectors for malware.
I'm not saying this is a perfect solution, but it's a huge step in the right direction.
VBA running in Excel has already been used for attacks and exploits, whereas they're hosting the Python in the cloud and it thus cannot run on your machine and be thus used as an attack vector.
VBA running in Excel has already been used for attacks and exploits, whereas they're hosting the Python in the cloud and it thus cannot run on your machine and be thus used as an attack vector.
Python in Excel makes it possible to natively combine Python and Excel analytics within the same workbook - with no setup required. With Python in Excel, you can type Python directly into a cell, the Python calculations run in the Microsoft Cloud, and your results are returned to the worksheet, including plots and visualizations.
Excel users now have access to powerful analytics via Python for visualizations, cleaning data, machine learning, predictive analytics, and more. Users can now create end to end solutions that seamlessly combine Excel and Python – all within Excel.
"I am thrilled to announce the integration of Anaconda Distribution for Python into Microsoft Excel – a major breakthrough that will transform the workflow of millions of Excel users around the world.” said Anaconda CEO and co-founder Peter Wang.
They had me right up until So unless I'm mistaken here, to use Python in Excel you've got to let Microsoft harvest your use of it for their analytics and AI training by sending everything you're...
They had me right up until
the Python calculations run in the Microsoft Cloud
So unless I'm mistaken here, to use Python in Excel you've got to let Microsoft harvest your use of it for their analytics and AI training by sending everything you're doing on Excel to Microsoft even though you've already licensed Excel, even though Python and its major libraries are utterly free, and even though many uses cases can be computationally crunched in seconds by tech no more sophisticated than an old iPad. Unless I'm mistaken.
This is for enterprise users, who have a license agreement with Microsoft and are already putting their data in Microsoft's cloud any way. For enterprises this makes sense. Normal users, not so...
This is for enterprise users, who have a license agreement with Microsoft and are already putting their data in Microsoft's cloud any way. For enterprises this makes sense. Normal users, not so much.
But very few consumers are wanting to write scripts in Excel anyway.
I was going to bring up micropython and microcontrollers but stopped. I have smart lightbulbs that I know have esp32's in them that I know can run micropython. So my lightbulbs can run Python
I was going to bring up micropython and microcontrollers but stopped. I have smart lightbulbs that I know have esp32's in them that I know can run micropython. So my lightbulbs can run Python
Seems like a net positive on the whole, and I'm happy to see Anaconda get their due, since they're a seriously undervalued gem. This whole announcement post is has such a bizarre self-parody vibe...
Seems like a net positive on the whole, and I'm happy to see Anaconda get their due, since they're a seriously undervalued gem.
This whole announcement post is has such a bizarre self-parody vibe to it though... I want to laugh, but there's not a punchline. PR-wagie GVR, arbitrary cloud-only rollout, screenshots with dense expressions and comments cut-off by the tiny-ass formula bar, a McKinney exec who's been out here dumping data from Jupyter to plot in Excel. What an acid trip.
This seems like kinda big news. There is an unfortunate large number of Businesses and NFP's that still do complex things in Excel. This may make it more industry standard with integrating such a...
This seems like kinda big news. There is an unfortunate large number of Businesses and NFP's that still do complex things in Excel. This may make it more industry standard with integrating such a common and popular language to play with data.
Honestly, this isn't as great as it seems for some organisations. There's a plethora of well intentioned people floating around businesses outside of central data functions where this could spring...
Honestly, this isn't as great as it seems for some organisations.
There's a plethora of well intentioned people floating around businesses outside of central data functions where this could spring up the old VBA cottage industries again.
Unless there's a central governance function, this might cause more problems than it's worth.
That said most of the technical staff supporting the staff who use excel sheets dislike that they are using excel sheets to do these complex things instead of something that can be properly...
That said most of the technical staff supporting the staff who use excel sheets dislike that they are using excel sheets to do these complex things instead of something that can be properly versioned and audited.
There's nothing an excel sheet can do that a generic programming language + a database can't do better.
Excel should be seen roughly the same capacity as a desk calculator. I use spreadsheets myself, but if you ever feel the need to share an excel spreadsheet in a business capacity, you have outgrown your spreadsheet and it needs to be a proper application.
Most database engines are pretty powerful these days. I believe you can actually run python from within postgresql, so you don't even need to have an external program running, just the database....
Most database engines are pretty powerful these days. I believe you can actually run python from within postgresql, so you don't even need to have an external program running, just the database. Even without python, SQL itself is a more powerful language than most people give credit, and you could theoretically do a lot of this kind of work with it.
I agree with this mostly, though outside of using triggers to signal data changes and functions for sanitization, I prefer to keep code out of the database. Keeping data and code seperate prevents...
I agree with this mostly, though outside of using triggers to signal data changes and functions for sanitization, I prefer to keep code out of the database.
Keeping data and code seperate prevents vendor lockin and weird compatibility issues.
I would bet real money that this functionality doesn't see the light of day in a plethora of companies like yours. Having to send data to "the cloud" aka, Microsoft's data harvesting servers,...
I would bet real money that this functionality doesn't see the light of day in a plethora of companies like yours. Having to send data to "the cloud" aka, Microsoft's data harvesting servers, means your security team is probably going to kill this in its crib.
We are Microsoft's biggest cloud customer and we have dedicated clouds that they provide so that explanation seems unlikely, although I would say there's a decent chance we don't get Python anyway.
We are Microsoft's biggest cloud customer and we have dedicated clouds that they provide so that explanation seems unlikely, although I would say there's a decent chance we don't get Python anyway.
If you are using MS on your company you are 99% sure on a o365 subscription and your data is already on outlook / teams / sharepoint / onecloud / whatever.
If you are using MS on your company you are 99% sure on a o365 subscription and your data is already on outlook / teams / sharepoint / onecloud / whatever.
I give it a week before someone figures out how to do exploits to leak data. SEO a malicious script for some basic useful thing that also dumps spreadsheet data somewhere online.
I give it a week before someone figures out how to do exploits to leak data.
SEO a malicious script for some basic useful thing that also dumps spreadsheet data somewhere online.
I'm not saying it can't be done. But security isn't a boolian. It's a range. If I said I was going to replace a crappy 10 dollar locker padlock with a 300 dollar hardened steel lock with 8...
I'm not saying it can't be done.
But security isn't a boolian. It's a range. If I said I was going to replace a crappy 10 dollar locker padlock with a 300 dollar hardened steel lock with 8 security pins you could argue all day that it could still be picked or cut off.
But that doesn't negate the fact that it's an improvement.
I don't understand why most of the people in this thread are upset about additional functionality. The old way still works. You aren't losing any functionality here.
From a security standpoint this is objectively an improvement over VBA.
I got a new job in financial reporting recently and I need to learn macros for it, among a lot of other things I was only otherwise book-smart in. What would be the best way to learn what I need...
I got a new job in financial reporting recently and I need to learn macros for it, among a lot of other things I was only otherwise book-smart in.
What would be the best way to learn what I need to do in the right way? I don't really want to use the macro builder, nor do I really want to learn VBA.
I'm generalizing here... I've not worked with mainframes which I know are still big in finance. Using a database (sql lite works well as local files, but your tech support and manager could point...
I'm generalizing here... I've not worked with mainframes which I know are still big in finance.
Using a database (sql lite works well as local files, but your tech support and manager could point in right direction for working with the database), and then using a programming language of some sort to report on or manipulate the data.
Some of our finance people make the excel spreadsheets from database exports and then feed CSV files from those spreadsheets back to the database. This is the antipattern.
Ideally you are querying the data directly from a database, and if manipulation is required, updating the database directly (via an app usually). Part of the reason this is desireable is durability: Any place sane should be able to restore their main database from any point in time in the last 30 days, down to the millisecond. If the database itself goes down for some reason, odds are than only transactions that were in progress are lost. In a properly configured database, if you accidentally delete a whole bunch of critical data it can be restored in minutes if you call the right person. Sometimes audits require checking backups, if someone was doing something sus.
There are many types of database, but ones that use SQL are the most common. SQL is really good at doing analytics. But sometimes you'll need more power and that's where building in a full programming language like Python comes in handy. Not limited to python but it's huge in reporting.
Ideally all these things are stored as code with proper versioning and backups.
Check out Apache Airflow for an example of some cool stuff that can be done.
And if you're new to programming, Python is a great place to start. It might be a bit dated now, but Automate the Boring Stuff is always a solid entry.
Outsider perspective: I'm not analysist buy I work with them from time to time. Look into Jupyter Notebooks. Even if everyone else is using excel, you can import and export as needed. Eventually...
Outsider perspective: I'm not analysist buy I work with them from time to time.
Look into Jupyter Notebooks. Even if everyone else is using excel, you can import and export as needed.
Eventually you can show it off to your coworkers and I'm sure they will be interested. The are quite popular with the data science community
Was very excited by the headline but my excitement is dampened by the quote above. So you need to be online to use python since calcs are done in cloud? Not to mention I've refused to purchase 365 subscription (and opted for non-sub professional plus). Seems like yet another carrot feature to get people over to O365
Yeah, this is a big "fuck you give us your data" middle finger to everyone using excel.
I disagree.
First: This is designed and aimed at enterprise users. In fact, from what I've read this will only be available to enterprise users. Large companies with licensing agreements aren't nearly as concerned about this kind of privacy. I mean, if you have the enterprise license you are already storing your files in OneDrive/Sharepoint anyway.
Second: SECURITY SECURITY SECURITY. I work in CSEC, and one of the most common points of infection are malicious office documents! Seriously, VBA in office has been a problem since... well, basically forever. The famous ILoveYou virus used office macros. But we also can't live without them. Yeah, there's other options (Jupyter notebooks, local python libs, etc), but I can't force our analysts to use those. This lowers the barrier to entry for people to migrate away from VBA and towards a language that's easier to maintain and has a much larger support community.
Since this runs in the cloud, in an isolated container, with no access to the users machine, and without network access many of the security problems are addressed (or at least improved). If you really want to use python locally with excel there's nothing to stop you from doing that. This doesn't take away those options. There are libraries you can use, or (what I usually do) you can transform the data with python before you bring it into excel.
They could have isolated it just like JavaScript is isolated on the web. I'm pretty sure Python is even designed to be that way.
They want to push cloud reliance. If they want security they can make it a toggle to disable the local sandbox.
Yeah this was my take too - I don't think Grendel's wrong about security concerns being Microsoft's rationale... but specifically, CPython and all of SciPy have already been compiled to WASM (see Pyodide), so the groundwork for rock-solid sandboxing is readily available. There's no real technical barrier at play.
M$FT are just being lazy/greedy/user-hostile as usual by refusing to offer an opt-in local version. While it's "secure" in terms of compliance and checklists, I'd go so far as to argue that having the data in Microsoft's cloud is a major threat to ground-truth security and data privacy, given Azure's history of ridiculous vulns like ChaosDB.
It's even worse. Microtransactions are the next logical step to milk your customers.
Or… Pandas.
There are plenty proprietary addons that are Excel exclusive or have features exclusive to Excel.
For example, back in the day at a corporate job I maintained some Excel based tools that pulled financial data from Bloomberg and
ReutersRefinitiv. I had some very convoluted flows where Python script pulled data from FTP, opened Excel specifically to refresh proprietary formulas, save results, then went on it's way to do next steps in a 3270 mainframe via Python again. It was ridiculous and if I could do it entirely from Excel environment it'd be much more pleasant experience.It'd be the best if IT was developing any end user requested features so that wouldn't have to be done in the first place but you work with what you're dealt with.
IIRC Bloomberg has an API now but it's still lacking very specific features found in Excel addon.
I guess my glib point was sort of the opposite of yours. I would just cut out Excel entirely and use Python and Pandas. It’s far more powerful. When I’m working with a dataframe in Pandas, I feel like there’s nothing I can’t do. Excel, like Windows in general, makes me feel like there are constant unnecessary roadblocks in front of me to accomplish my goals, and I’m a former sysadmin. The GUI in Excel is a mess. In Pandas, often enough, complex functions are an easy one-liner. I do understand Excel’s place in the business environment, but any forward-thinking company who leans heavily on Excel should hire some developers and data people and really embrace Python and Pandas. It might initially be painful, but in the long run it’ll give them way more control.
In my example Bloomberg has de facto monopoly in that particular niche. No amount of forward thinking will replace it, unless you're Citibank in the early 90s, about to sell what later became Refinitiv.
If you found a data source that matches Bloomberg (you won't because institutions submit data to BB directly), there are years of development to catch up with on silly things like thousands of different regional bond type accrual calculations. And then you'd have to reproduce all bugs because everyone is expecting BB to be correct and it costs less to assume it's right rather than argue with a counter party.
You'll find stories like this in different industries. They're all married to Excel for this and plenty different reasons.
You can do a lot of the basics in Power Query that you can in Pandas. It can be very fast to put stuff together quickly. It doesn't have as good of performance, but it's easily transferable to other people. I use both and will default to power query unless it becomes too complex or too slow. Although I'm sure there are ways to speed it up.
I actually love PQ. What I wish Microsoft would do is enable M as a .Net language you could call from anything else, including Ipython, C#, and F#, for data sanitation and coalition. It works for rapidly prototyping, but once once put together a solution it would be terrific if I could export that part of a solution beyond Excel and PowerBI. Also expand the size limitations. I could process hundreds of thousands of records, but then I couldn't export them outside PowerBI, and Excel was limited in other ways. Python/Pandas with M as the front line would be a powerful combination.
I use pandas on a daily basis bc data scientist, and even I don't think you could just replace Excel with pandas. Ignoring the fact that pandas has a learning curve even among users who already know python (which is already gonna be a minority of Excel users imo), there are absolutely things that are eaiser to do and more user-friendly in Excel than in pandas. I would never recommend someone keep track of their budget in pandas, for instance. And when it comes to visually presenting your data, there's a huge variety of things you can do in Excel that aren't possible in pandas.
Even as a relative novice when it comes to Excel and an experienced pandas user, I think they're designed for fundamentally different use cases, and the idea that Excel could be replaced with pandas is really underestimating the use cases Excel excels at (hehehe) and the versatility of the program within those domains.
This isn’t really what I meant. I absolutely never meant that Excel should go away and home users and bookkeepers should switch to Pandas. I don’t think that assumption gives me any benefit of the doubt. I mean, c’mon.
Your reply elsewhere in the thread said
and
Your comments in this thread, particularly these bits I quoted gave me the sense that you were overlooking many of the reasons people use Excel instead of pandas, which is why I left a comment describing how there are some fundamentally different use cases between excel and pandas that mean this switch wouldn't make sense for a large number of users. You quote a single glib example I gave but that's the general tenor of the comment I left, or at least what I intended to leave.
I understand that it can be frustrating if someone interprets what you say differently than you meant it but it's really not fair to accuse me of not giving you the benefit of the doubt for disagreeing with the words you wrote. There was nothing in your comments in this thread that indicated to me that you don't think that, say, bookkeepers should switch to pandas. I appreciate you clarifying that you did not mean that, but I can't really divine your thoughts from anything but the words you actually wrote.
Thank you for this comment. It reminds me why I stopped commenting on Reddit years ago and it makes me viscerally feel my addiction to forums in that way when an addict experiences clarity and shame for what they’ve done to themselves. I’m going to channel this feeling into something productive and kick this bad habit for good.
I can imagine how they decided to do it this way. Anaconda is a huge distribution of open source code that's unvetted by Microsoft. Also, Excel macro viruses are a thing. Consider the implications from the point of view of a risk-adverse security reviewer at Microsoft.
Even if Python is sandboxed, that's a big attack surface to run on a customer's PC, exposing it to a macro virus distributed in an Excel spreadsheet. Maybe it's safer to run it in a VM in a datacenter? And naturally, they can charge for that.
(Meanwhile, developers download and run various Python packages on their laptops all the time. But Microsoft isn't responsible for that.)
I am rather confident that skybrian knows that and is pointing out that this is sensible for Microsoft from a risk mitigation perspective.
This is such bad news.
Excel sheets with VBA or the upcoming Python stuff are hard to maintain, test and document. I have seen giant monoliths of spreadsheets within organizations that no one had knowledge on how it worked, but everyone relied on the results. Such thinks are just setting up giant f-ups within the company.
There are also major risks involved with scripting. Excel isn't containerized, which means scripts can access your file system.
My general rule of thumb: if you need scripting (VBA or python) for a task in Excel, then Excel is the wrong tool for the task.
Microsoft should therefore discourage, not encourage, organizations to use scripting in Excel.
I'm not sure you're really engaging with the comment you replied to. It's not saying "running python scripts on a user's PC is very very bad", it's saying "Excel spreadsheets that contain scripts are a nightmare to maintain and often lead to massive errors, and thus including scripting in Excel spreadsheets is bad." I see no implication that this problem is even affected by where the code is run.
Fair enough, it just wasn't super clear from reading your first comment that you were responding only to that section of the comment rather than its entire point. My mistake.
I see this as harm reduction.
People aren't going to magically move away from Excel scripts, and they certainly won't do it overnight. That's an unrealistic expectation from an enterprise perspective.
VBA offers no path out of Excel. But, if people start to learn and use python they begin to open up more options for themselves. Sure, at first you just move the maintainability problem from one language to another, but eventually analysts will discover things like Jupyter notebooks and have more options going forward with the skills they've built.
And From a security standpoint, let me tell you, my whole CSEC department is celebrating this as a win. Containerized python that has no access to a users machine, and no network access? Yes Please! There's no way to even begin to compare that to the security nightmare that is VBA.
At worst this provides a more secure option than VBA and gives people an opportunity to build skills (Python programming) that they can use to further their career.
While i basically agree, i also respect there's a couple of use cases, mainly for employee's who work mostly in excel. There's lots of business users who ultimately need data in excel, and will do their work there, so at some point you have to have some scripting to either pull the data in or help organize it.
Still, I've done everything I can to nuke all this where possible, and simplify the hell out of it where not.
Cloud-dependent for absolutely NO reason other than controlling your data.
Obviously for security and liability reasons.
There is a very good reason for this.
The nightmare that someone can put code in an excel document, send it to one of your users, then that code can execute on their machine is horrible. It's one of (if not the) most common infection vectors for malware.
I'm not saying this is a perfect solution, but it's a huge step in the right direction.
What makes Python running in an Excel document any different from VBA running in an Excel document?
VBA running in Excel has already been used for attacks and exploits, whereas they're hosting the Python in the cloud and it thus cannot run on your machine and be thus used as an attack vector.
They had me right up until
So unless I'm mistaken here, to use Python in Excel you've got to let Microsoft harvest your use of it for their analytics and AI training by sending everything you're doing on Excel to Microsoft even though you've already licensed Excel, even though Python and its major libraries are utterly free, and even though many uses cases can be computationally crunched in seconds by tech no more sophisticated than an old iPad. Unless I'm mistaken.
This is for enterprise users, who have a license agreement with Microsoft and are already putting their data in Microsoft's cloud any way. For enterprises this makes sense. Normal users, not so much.
But very few consumers are wanting to write scripts in Excel anyway.
Depends what you're doing but ya, an old iPad can easily run some python code.
I own a graphing calculator that can run python.
(Technically micropython, but still….)
I was going to bring up micropython and microcontrollers but stopped. I have smart lightbulbs that I know have esp32's in them that I know can run micropython. So my lightbulbs can run Python
Seems like a net positive on the whole, and I'm happy to see Anaconda get their due, since they're a seriously undervalued gem.
This whole announcement post is has such a bizarre self-parody vibe to it though... I want to laugh, but there's not a punchline. PR-wagie GVR, arbitrary cloud-only rollout, screenshots with dense expressions and comments cut-off by the tiny-ass formula bar, a McKinney exec who's been out here dumping data from Jupyter to plot in Excel. What an acid trip.
This seems like kinda big news. There is an unfortunate large number of Businesses and NFP's that still do complex things in Excel. This may make it more industry standard with integrating such a common and popular language to play with data.
Honestly, this isn't as great as it seems for some organisations.
There's a plethora of well intentioned people floating around businesses outside of central data functions where this could spring up the old VBA cottage industries again.
Unless there's a central governance function, this might cause more problems than it's worth.
That said most of the technical staff supporting the staff who use excel sheets dislike that they are using excel sheets to do these complex things instead of something that can be properly versioned and audited.
There's nothing an excel sheet can do that a generic programming language + a database can't do better.
Excel should be seen roughly the same capacity as a desk calculator. I use spreadsheets myself, but if you ever feel the need to share an excel spreadsheet in a business capacity, you have outgrown your spreadsheet and it needs to be a proper application.
Most database engines are pretty powerful these days. I believe you can actually run python from within postgresql, so you don't even need to have an external program running, just the database. Even without python, SQL itself is a more powerful language than most people give credit, and you could theoretically do a lot of this kind of work with it.
I agree with this mostly, though outside of using triggers to signal data changes and functions for sanitization, I prefer to keep code out of the database.
Keeping data and code seperate prevents vendor lockin and weird compatibility issues.
While that is a completely fair concern, I don't think anything we're talking about is a real concern to the people who are trying to use Excel. :P
I'm just happy that I'll finally be able to use Python on my locked down work computer.
I would bet real money that this functionality doesn't see the light of day in a plethora of companies like yours. Having to send data to "the cloud" aka, Microsoft's data harvesting servers, means your security team is probably going to kill this in its crib.
We are Microsoft's biggest cloud customer and we have dedicated clouds that they provide so that explanation seems unlikely, although I would say there's a decent chance we don't get Python anyway.
So... The US government. Yeah, with compliance/security I'm not surprised at how locked down your machines are.
If you are using MS on your company you are 99% sure on a o365 subscription and your data is already on outlook / teams / sharepoint / onecloud / whatever.
You can install Python as a user without admin rights but yeah, maybe not so you dummy get in trouble lol
I cannot. It is not allowed on our network without waivers.
Oh man, this could revolutionize all of the databses at my work. /s
Please God no one tell the accountants
Please tell the accountants so they'll stop using VBA and things can be at least a little more secure!
I give it a week before someone figures out how to do exploits to leak data.
SEO a malicious script for some basic useful thing that also dumps spreadsheet data somewhere online.
I'm not saying it can't be done.
But security isn't a boolian. It's a range. If I said I was going to replace a crappy 10 dollar locker padlock with a 300 dollar hardened steel lock with 8 security pins you could argue all day that it could still be picked or cut off.
But that doesn't negate the fact that it's an improvement.
I don't understand why most of the people in this thread are upset about additional functionality. The old way still works. You aren't losing any functionality here.
From a security standpoint this is objectively an improvement over VBA.
I got a new job in financial reporting recently and I need to learn macros for it, among a lot of other things I was only otherwise book-smart in.
What would be the best way to learn what I need to do in the right way? I don't really want to use the macro builder, nor do I really want to learn VBA.
I'm generalizing here... I've not worked with mainframes which I know are still big in finance.
Using a database (sql lite works well as local files, but your tech support and manager could point in right direction for working with the database), and then using a programming language of some sort to report on or manipulate the data.
Some of our finance people make the excel spreadsheets from database exports and then feed CSV files from those spreadsheets back to the database. This is the antipattern.
Ideally you are querying the data directly from a database, and if manipulation is required, updating the database directly (via an app usually). Part of the reason this is desireable is durability: Any place sane should be able to restore their main database from any point in time in the last 30 days, down to the millisecond. If the database itself goes down for some reason, odds are than only transactions that were in progress are lost. In a properly configured database, if you accidentally delete a whole bunch of critical data it can be restored in minutes if you call the right person. Sometimes audits require checking backups, if someone was doing something sus.
There are many types of database, but ones that use SQL are the most common. SQL is really good at doing analytics. But sometimes you'll need more power and that's where building in a full programming language like Python comes in handy. Not limited to python but it's huge in reporting.
Ideally all these things are stored as code with proper versioning and backups.
Check out Apache Airflow for an example of some cool stuff that can be done.
And if you're new to programming, Python is a great place to start. It might be a bit dated now, but Automate the Boring Stuff is always a solid entry.
Outsider perspective: I'm not analysist buy I work with them from time to time.
Look into Jupyter Notebooks. Even if everyone else is using excel, you can import and export as needed.
Eventually you can show it off to your coworkers and I'm sure they will be interested. The are quite popular with the data science community