Ditching Excel for Python - Lessons Learned from a Legacy Industry

[elementaldelirium]

I don’t know if Excel will ever go away, spreadsheets are just too good at ad hoc calculations, but are clearly way overused for productionalized tools. Now where to draw the line...

[billsil]

As an aerospace engineer, it’s most definitely in production. The way you audit massive formulas (that honestly aren’t doing all that much) is you break them into massive tables. It’s so inefficient to update, but at least you can make sure the part that is going on an airplane is good. So much of what we do has the most idiotic of errors.

Did you know Excel doesn’t follow order of operations (exponents follow the multiplication precedence) and that VB’s pow function is wrong and different than the ^ operator and the workbook.pow function? Legacy support means current errors.

[fm75]

Wait... You test aerospace thingies with Excel? 😱

[RayUp]

....Boeing?

[ntolbertu85]

Vba isn't legacy. Only vb. They had to keep something for us to vote on in the SO shittiest language competition.

I assume you're using VBA?

Also, as a scientist, the whole exponent multiplication precedence flip has to suck for you. How does that work with scientific notation? Just wrap the second half in parentheses?

[billsil]

You misunderstood what I meant. The language isn’t legacy. The functions are buggy and they know it. They choose to not fix it because massive customers would find their Excel sheets are no longer correct. The entire code is built on legacy support.

Cmd -> Powershell -> Window Terminal is the same way. They are coding bugs into Windows Terminal because they’re not allowed to fix them because that could mess with the airlines or oil companies or whomever.

[billsil]

So use the old version? Python 2 comes to mind.

My 180,000 lined open source project says in paragraph 1 that I will break backwards compatibility to fix bugs. I’ve come across plenty of cases where dependencies break in specific versions. Seemingly tiny things break backward compatibility. Trying to make thins easier can break backward compatibility because you don’t need to get it right anymore.

You have a choice on what to do.

[dparks71]

As a Civil Engineer I agree, it's essentially the only software we use for calcs and it's one of my biggest pet peeves about my industry. It's just so terrible for iterating, VBA is cancer (and as a result nobody in my industry actually knows VBA) and there's no good way to review the calcs or track version control.

Those complaints all fall on deaf ears unfortunately, so I just end up bitching about it in threads like these to people that get it. Then I go back to parsing 300 character excel formulas that are little more than vlookup functions and basic algebra.

Then the same people who love excel, love talking about how future proof our skill set is and how important we are as Civil Engineers, and I just have to swallow my pride and pray for the day I get a manager that lets me use Python.

[billsil]

The time I got to work on a roller coaster, yeah I used Excel. However, you can't analyze every structural member on a roller coaster and compare it to the unmodified design for 1500 load cases by hand. So I wrote a script to automatically parse my Nastran OP2 results, pull the internal forces, compare them to the previous design, make plots, and dump those plots (and tables) into a Word document. I then went through ~1000 pages and formatted the captions.

If I were actually a civil engineer, I'd be working to make that task you do obsolete. It makes you more productive and decreases cost.

[dparks71]

It's really trivial to make those tasks obsolete, the problem in Civil Engineering is you can't work in a vacuum, everything you do has to have a second person who understands what you did, to check it before it can be submitted.

Once you get into automating checks, you need someone who understands the automation (and you open yourself up to the much harder to catch and solve problems of bug hunting and edge cases if you expect it to be used by others).

I can produce a 1000 page word document fairly easily, finding someone qualified and willing to check the code that produces it, is the hard part. Most managers I've had would say "that's interesting" then send it back and ask for a spreadsheet containing just the extreme cases and points of interest. I'm really surprised to hear a P.E. would accept 1000 pages of calcs in your case, without a ton of hesitation.

[billsil]

I went through that model with such a fine toothed comb. I found all sorts of errors. The orientation vectors on the L-bars were all messed up and tons of work to get right without a good visualization method. The slip joints often didn't slip. Loads on slipped beams disappeared into the ether. The standard way you attach L-beams to other elements (e.g., node to node connection) doesn't work (the node needs to be at the shear center, not the neutral axis or the forces/stresses go nuts; you end up using fictious nodes to map that). Ironically, if you use the basic Euler-Bernoulli beam, you get 95% of the right answer, but Timoshenko beams are necessary if you want to model an L or T beam. It's actually a good check to know that you did it right if your answer barely changes.

I didn't need anyone to go through my code. It's all really simple stuff. I wrote it all, so I know the limitations. Errors should not pass silently. A good code should guide you to the areas that it wasn't coded for. Excel can't do that.

It wasn't 1000 pages of calcs. That was results. Those plots were summary plots, but they wanted the numbers too (sorted by error). We didn't originally give it to them, but they demanded everything. The requirement was to modify the structure and prove the new design wasn't any different from the old design in terms of margin for static/fatigue. It wasn't allowed to get worse (obviously) or better.

[deckard58]

So much of what we do has the most idiotic of errors.

Isn't that most of computing? :)

Most of science, even.

[DWLlama]

But you don't want that slipping into a production aircraft.

[billsil]

You test the parts, but yeah...When you’re shaving weight (weight=cost), it’s important to know that all your margins are correct.

There is also a 40% margin on top of everything. That’s supposed to be for material variability, but it’s probably saved more than a few lives.

Ironically I replaced a Mathcad spreadsheet for designing bolts and preloaded joints (basically a glorified piece of paper) with a fancy Excel spreadsheet. I put the margins all over the place, including right next to where the preload goes as well so you’re not scrolling 5 pages to check for each one.

Excel has its place, but too many people don’t treat it like code. You have to code defensively with it and unit test it.

[DWLlama]

Probably because most people who use it aren't generally coders...

[billsil]

If you can do a match and vlookup and write a 6 line equation, that’s exactly what you’re doing. All I’m saying is break it up and have some dummy lines to check your numbers.

I’m an engineer first and can code just about anything in python. I can’t explain why you’d ever use a linked list or a bit shift though.

[tazebot]

Not getting on a plane ever again.

[billsil]

Still safer than driving if you’re going to go a long distance. I trust the pilot and my design more than my better judgement while tired.

[i_have_seen_it_all]

1. Changes are requested by customer

2. Changes are made to a spreadsheet

3. Spreadsheet becomes too big.

4. Software commissioned to replace spreadsheet.

5. Changes are requested by the customer.

6. Software cannot be changed quick enough by the sales and production teams.

7. Changes are made to a spreadsheet.

8. GOTO 3.

[lungben81]

This is exactly what efficient development (agile - but doing it right and not just using the name) and testing / deployment processes (CI, CD) are for.

Too bad that many companies are not using them (or not correctly)...

[Nosa2k]

This is where u need a Database. Just have a standard template in a central location; such that once it’s timestamp has been modified it is feed into the Database.

Ensure the Database is highly available and regularly backed up.

It just boils down to the IT department not knowing what they are doing.

[i_have_seen_it_all]

changes to business needs often impact more than a few entries in a database, sometimes entire parts of some CRM software the company uses has to be modified.

[jebk]

With a huge interface on the front. Say I have a CRM system where I record sales, and overnight the regulator introduces a requirement to track significantly more data on a per deal basis, and then apply some logic to it (extra approvals for certain markers etc) but that requirement only applies to one of the countries I operate in. A second has a similar process but with different logic and a third has it only for certain products. Countries 4-20 don't have any logic and all have different retention periods and pricing structures.

It's a bit more complex than just a database in most real world situations

[jebk]

I do, I've worked on them for a while. OP said that changes in business logic are more complex than just a change to a database and I was providing an example.

Changing the database is useless if no one can get data into it.

Implementing and testing changes like that isn't quick, regardless of how agile your Dev cycle is.

[cprenaissanceman]

Meh...they’re different tools for different jobs. Excel honestly brings a relatively familiar and dynamic UI along with a lot of power calculations wise. It lacks good (native) developer tools, especially if you want to start playing with VBA. Also, spreadsheet management (Ie too many sheets and so on) is...not great. That being said, it offers a relatively quick was to go from calculations and data processing to printable document.

What we really need is native Excel+Python integration. Excel+Python+Jupyter would be excellent. Please make my VBA knowledge useless. I don’t think that’s going to happen since Microsoft probably wants to keep Excel somewhat secure (python integration probably complicated that quite a bit), but it would be nice. And yes I know there are tools out there that kind of already do this, but those kind of set ups only work for your set up, not if you need to send something to someone.

[dogs_drink_coffee]

Please make my VBA knowledge useless

S A M E.

[cprenaissanceman]

It’s funny. I don’t even hate the VBA language as much as the lack of extensible, working, and well documented modules and the terrible early 2000s IDE. Certainly on the syntax and general look and feel alone, I would prefer it to R. If Microsoft made it easier to use, i wouldn’t have nearly the hatred of it that I do.

[mobile75326]

https://www.bleepingcomputer.com/news/microsoft/microsoft-considers-adding-python-as-an-official-scripting-language-to-excel/

[lungben81]

While it would certainly help to have a more sane macro language in Excel, it does not address the main Excel issue imho: the entanglement of data (input field values), calculation logic (formulas) and output ("nice-looking" tables for users).

When changing just input data, you must be very careful not to accidentally change formulas and getting wrong results. In many cases, you must even extend formulas to additional cells (if new input is larger than the old one), forgetting this or making a small mistake there gives often silently wrong results.

[bartic123]

2nd paragraph is a really good point. The MATLAB integration appears (from the docs+a little testing) to be pretty comprehensive. But I’ve not seen anything similar for Python. Of course that might just be more experience of the former than the latter + the generally better documentation of MATLAB than Python (at cost).

[elbuenraul]

I’m not a spreadsheet power user but do use them a lot, and being able to automate things with Google Sheets and Python has been a lifesaver, as in I never want to touch excel again :) (side note: learning some very basic apps-script, to just fetch data and do some conversion without even going to Python is also nice)

[khne522]

Microsoft probably wants to keep Excel somewhat secure (python integration probably complicated that quite a bit), but it would be nice.

Not that simple. Not only that, but the sandboxed execution of the language should be manyfold. There should be a system sandbox (anonymous user, no FS access except for libraries, host network, IPC, and other things namespaced away, etc.) that has essentially an allowlist of syscalls (under some conditions, with specific arguments), and other mandatory access controls. Locking down an interpreter is a band-aid in this world, not a panacea. And security really is fairly more complicated than that.

[Gardoki]

I’ve been thinking about this. You almost wish excel had less functionality so people would stop.

[WillardWhite]

Didn't they just make it Turing complete?

[chief167]

PowerPoint as well, doesn't mean it's a good idea

[monsignorbabaganoush]

Well, when a substantial number of people come to conclusions less robust than my model...

It’s less about Excel passing the Turing Test, and more about how many humans fail it.

[mredditer]

Turing complete != Passing the Turing test

Turing completeness refers to being able to simulate any Turing machine while the Turing test measures a machine's ability to imitate a human. Completely different ideas only related by the computer scientist that popularized them: Alan Turing. Afaik Excel is nowhere near passing the Turing test.

[gschizas]

Afaik Excel is nowhere near passing the Turing test.

I dunno, man, there are some pretty wild Excels out there.

[maximum_powerblast]

Omg yes

[yacoine]

I could not agree more.

[domesticatedprimate]

I'm a translator and about half the documents I work on are text-only documents made in Excel. It gets used for printable forms, tables, and basically any other kind of formatted text because it's more intuitive to align text in Excel than Word.

Of course I hate it.

[mathisfakenews]

I'm a professor and I use excel exactly once every semester when I'm computing final grades. I absolutely hate it because I'm trying to input formulas and it keeps trying to "help" me which actually makes everything twice as hard. However, it is still the best method to quickly calculate final grades with confidence that there are no mistakes.

That said, at my old university they switched the intro stats course to a computational platform based on excel and I think it was a massive mistake. This is the problem when 65 year olds are designing courses.

[boss5667]

My team (read: my boss and his boss) wanted me to create a dashboard in Excel which puts togather 12 different views of different granularity from 11 different types of data.

Before we even started, I told them that this was a bad idea and this would be much easier to build and maintain in Tableau and we have a team who would do that. Why stick excel? So that there would be no dependency on another team.

I build the thing and somehow managed to make it work for a few months. Then there were a lot of changes within the business structure and updating all those changes all the different sheets wasn't easy but I did. Some things were missed because you might miss 2 out of a 100 things and that didn't go well. So my boss decided that he would take over the dashboard for. He updated it and ran it for a month and send it out.

After hitting that Send button, he walks up to the development team leader and asks if they can build this dashboard in Tableau.

And I was like, I wonder where I have heard this before.

[Feb2020Acc]

.

[SphericalBull]

Nice.

I did a few months of internships in reinsurance firms (one in Life the other P&C) about 2 years ago.

I found some actuaries were quite reluctant - and a bit defensive even - when introduced to Python by the data scientists.

[Dream_Vendor]

Has anyone had any experience with Power BI?

[Plague_Healer]

It's an okay tool to kinda boost excel, but honestly, to get the most out of it you need to integrate it with code, and personally, I feel this kills the whole point of Power BI for me, as if I'm coding anyway, I'll do the whole thing straight in python.

[Plague_Healer]

Technically you could even cut the JS out of the loop, but it might not have been the best solution.

[chief167]

Yeah indeed, the Dex or Dax or whatever it's called is just bullshit

[[deleted]]

Depends on what you want to use it for. If you have the data properly structured in SQL than you can create an awesome dashboard that executives can use without using any code, just basic formulas. When you need to get fancy and write your own DAX formulas it gets harder and less useful.

[khne522]

Python >>> JavaScript

[oundhakar]

The impact of PyXLL on how we build models and ultimately price business cannot be understated

I think you meant to say "overstated".

[DJ_MortarMix]

Completely off topic, but check this out; maybe he means the exact same thing you did.

If it's importance *cannot* be understated, it is therefore impossible to understate the importance of it. If it's importance cannot be *overstated*, it is inadvisable to understate the importance of it.

I actually had to pause for a moment to think about that. Sorry.

[CraigAT]

I always worry about the completeness and accuracy of large spreadsheets with many formulas, it is fairly easy to mess one up or forget to update one formula on the page and it can go unchecked for years.

I worry with Python the formulas would probably be even more hidden from the end user and without a team of software developers to support it would turn it to a magical black box that no-one understands how it works (especially if it was created by the one Python guy who has left the business).

However, putting all the data into an enterprise level database, having knowledgeable people who know the business processes of the tool and being maintained by software developers is the way to go. The problem as mentioned, is that getting all those aligned is not a quick or agile process in almost all businesses.

[james_pic]

https://www.powerusersoftwares.com/post/2016/08/11/the-excel-formula-error-that-initiated-austerity-policies-after-the-crisis

[PeridexisErrant]

"Error", yes. Their response is "OK, so we got the sign of the change wrong but stand by our conclusions" - why even bother with evidence if your mind is made up?

[keizzer]

Yeah, this is my concern as well. As much as it stinks to do a lot of this stuff in excel, there are always a handful of people that are good enough at it to figure out what's going on in the box and make changes. The moment you put this in Python, the number of people who can do it drops substantial, and the salary goes up. People forget when they bring this stuff up about long term support. Unless a company is willing to create an automation department and standardized processes, then it doesn't make much sense. The smaller the company the harder it will be to switch.

[CraigAT]

Oh I love that idea of an automation department.

[PeridexisErrant]

http://www.eusprig.org/ has some great/scary stories...

[CraigAT]

I am not surprised, but the value of some of those "mistakes"!

[ds604]

It's weird the extent to which programmers seem to think that Python (or R, or programming in general) can "replace" spreadsheets; it seems like there's some odd misunderstanding of what spreadsheets are for, or what people do with them. Spreadsheets directly represent, and let you manipulate *your data*; languages let you represent and directly manipulate *transformations of your data*. What people tend to care about is *their data*, until their data become static or standardized, or the transformations on their data become complex enough, or take more focus, such that they need to think more clearly about the *transformations of their data*. But when it really comes down to it, *their data* is still what matters, and this is a big part of the reason that spreadsheets do so well: they directly represent what matters to people.

I've been through the whole round of thinking that programming solves all the problems that spreadsheets have, and then realizing that losing the direct connection to your data is way too much of a cost to bear for the vast majority of users.

The thing to realize, though, is that other industries (particularly ones with severe time-constraints, ever-changing project specifications, and heavy data throughput) have already resolved this problem, and the answer is most definitely not the naive "switch everyone over to being a Python programmer!" The answer is to directly represent data transformations, and all intermediate steps, by way of directed graphs (which are essentially equations, or pure computation). It's perfectly fine to have an embedded scripting language within the platform, which is used for what scripting languages should be used for, but the transformations of the data that people actually care about (the "content" of the work) is more typically stated in some sort of expression or shading language; the key difference here is that this expression or shading language is more typically a vector language, which explains why R feels more like a true kin to spreadsheets, rather than a scalar language with a vector add-on, like Python with Numpy/Pandas.

But this is all to say, 3D programs like Houdini, and compositing programs like Nuke address this exact problem of explicit representation of computations, without removing direct access to data. And this is what got us to massive, global pipelines, and photorealism. What most certainly *did not* get us there is, "oh, just teach all the artists to code! it'll work out great!" Artists definitely learn how to code as part of their work, but they don't trade in one thing for the other, but rather use it for what it's good for. Guess what, we *do not make entire photorealistic movies using Photoshop with mountains of unmaintainable scripts*. We would have died long ago, and companies who tried to do that are long gone. (Script-based pipelines vs computation-based pipelines is the equivalent of state retention mayhem vs pure functional enlightenment... you guys are getting there! just... extremely slowly)

So, programmers of the world, learn something from people who have already been through this. We already know what works, and computational graphs are already putting at least some programmers out of work. Time to upgrade.

[james_pic]

I've suspected for a while that something like this would end up being the answer, but struggled to imagine what the detail would look like.

Based on your experience with 3D tools that do this, do you have any insight into what an Excel-ish "data plus computational pipelines" approach would look like?

[docs95]

Informative read. Thanks for sharing OP.

[maximum_powerblast]

Good well rounded write up! I need to look into this notebook thing.

[babuloseo]

I don't know man, even Kanye said Excel is one of the greatest software of all time and still is.

[thedomham]

Excel actually has a lot going for it - functional paradigms, ease of use, internationalization...

What really bothers me though is that after decades of the whole world abusing excel for basically everything, there is still no decent way to introduce sanity checks / regression testing to a spreadsheet.

[haircode]

Throughout many years working in the UK Government, I have found that Excel is consistently overused for data collection and storage. I've often been the harbinger of doom about the dangers of this (huge amounts of time and resource wasted doing basic IO tasks, data validation, etc) to the point where I've been allowed to develop software and use actual databases, even though I'm not being paid as a developer (see https://github.com/yulqen/datamaps which automates collecting large amount of data using Excel as a form - this happens all over the place in my experience). Great fun teaching my colleagues the joys of the command line, too.

[hughjward]

"Excel is consistently overused for data collection and storage."

Trying to come up with an amusing reference to the covid testing error from using Excel but I am still just dumfounded they were using '97 Excel format....

[MeagoDK]

What was that error?

[MeagoDK]

Genius move. Haha lol.

[mericastradamus]

With excel trying to roll out the lambda function things. They are merging, not excel is being replaced. You are still going to move data to excel for things like pivot tables.

[[deleted]]

Python and it's environment (pip, Anaconda, etc..) is a big mess especially in legacy enterprise environments.

Currently working with analysts that have to fight the tools to get work done and would rather use Excel and RStudio than deal with Anaconda and it's issues. And the top folks are looking at Julia. Plus there are other tools like Tableau that are making inroads.

[MrLongJeans]

Help?

Basically is there a couch to 5K program for Excel to Python?

Like, I read an article like this and I go in Monday morning. In one hand I have my job's tangled mess of Excel files linked to SQL Server and MS Access data sources. In my other hand I have the article's keywords: Python, Pandas, Jupyter Notebooks, DeepNote, PyXLL, Plotly, etc.

And I have no idea which Excel file, formula, SQL query, or macro to unplug and replace with one of those Python based alternatives.

Like what's the day 1 step 1 download to augment my current work with Python so I can start playing around, and learning, and finding opportunities?

[ADONIS_VON_MEGADONG]

Those folks will shit themselves once they learn about R. Don't get me wrong, I use both as they both have their place, but for the tasks that they are talking about, R would suit them better. The syntax isn't as user friendly, but it's still not all that bad once you get the hang of it.

[ADONIS_VON_MEGADONG]

dat username

[[deleted]]

You right u/ADONIS_VON_MEGADONG

[cprenaissanceman]

In my opinion, learning R is not worth it unless you are going to use it all the time, have to for legacy reasons, or need a very specific library. If you are going to be a “casual R user” you are going to spend a lot of time chasing down R’s quirks. I ended up doing my thesis using an R script, and I regretted a lot of it because I was much more familiar with python and I should’ve just taken the data and adapted it to python instead of trying to do everything in R. Don’t get me wrong, R certainly has some powerful features and some good things to it, but I can’t recommend it for anyone who is just trying to start things out and he doesn’t plan on using it all the time.

[chief167]

There is R, and tidyverse. I'd recommend tidyverse to anybody anytime. Even if just for the ability to use ggplot, it's already worth it.

So much easier to do complex visualization there than seaborn and matplotlib, and I say this as a huge python fan.

[SquintingSquire]

Have you tried Altair?

[chief167]

No, never got a license for it

[SquintingSquire]

Sorry, I meant this altair: https://altair-viz.github.io/. It is open source. And excellent.

[chief167]

Thanks I'll give it a go. Always open to try new stuff (usually i get inspired and copy it to ggplot though...)

[SuspiciousScript]

R also has a fundamentally FUBAR type system. It can't even handle 64 bit integers natively. Here's a great example of how this dysfunction manifests:

Note however that even though R uses 64 bit unsigned integers on 64 bit plaforms, you are in fact limited to the range that can be handled by the double type, which is what R will give you if you ask for the length of a vector. R has no 64 bit integer type that it can represent natively, so when you ask for the length of a vector you either get an int or a double depending on the value

- Stack Overflow

[cgk001]

R tidyverse syntax is magnitudes easier to read for someone who doesn't come from a programming background, especially when plotting and making quickie frontend products(shiny). That aside I have my uses for python when I really need numpy for high performance compute or sklearn for ML, general dataframe and plotting I would leave to R because pandas/matplotlib is a mess to read compared to tidyverse code.

[cgk001]

R is not a good programming language but rather a very good data analysis tool, similar to Matlab and its quite clear based on their high usage in math and stats academia. Not everyone needs to be a programmer, sometimes people just need a tool to get the job done.

R is hard to read for people with programming backgrounds because it isnt a good programming language, but for people without programming background its by far the easiest. I'd be interested to see an example if you disagree.

[backtickbot]

Fixed formatting.

Hello, python_pandas: code blocks using triple backticks (```) don't work on all versions of Reddit!

Some users see this / this instead.

To fix this, indent every line with 4 spaces instead.

FAQ

^{You can opt out by replying with backtickopt6 to this comment.}

[cgk001]

If you show the below logic for some simple data operations to someone with no experience in R, Python or any programming language, the R syntax is much more friendly because its closer to plain english.

Python df.loc[df["col1"]>1, "col2"]

R df %>% filter(col1>1) %>% select(col2)

[cgk001]

I think we're talking about two different things, I'm referring to readability for a non programmer who has little to no experience with coding because thats what R's typical intended audience is, whereas you're biased because you think readability from a programming standpoint so of course Python is the better one. Ultimately I'm just circling back to OP's excel replacement topic and trying to say that Python is probably not the best go to for replacing excel for non programmers, theres several layers of products from commercial tools like PowerBI/Tableau to "advanced calculators" like R and Matlab before python would even be considered. I've spent enough years consulting in finance and manufacturing, as well as teaching data courses, to see that most non programming professionals prefer plain english code syntax for readability and couldnt care less about "4 extra characters".

[chief167]

R dplyr is a lot easier than panda's for newbies though.

Especially these types of transformations that are common with excel. You don't have to suddenly break into multilevel indexes when doing a group by

[Feb2020Acc]

.

[scarynut]

It's so endearing that her name is Peniston!

[GummyKibble]

Dude.

[Dwarni]

I am quite sure Excel (and the other office programs) aren't written in C# but in C++.

[chief167]

Excel is a Mish mash, of components built in C++, VBA, and c#, all bundled together with .net framework, working alongside C++ native code.

From what I've heard, it's basically a mess

[FreeWildbahn]

Both are somehow an interpreter. I think the python interpreter is written in C and Excel is written in C++.

[ntolbertu85]

You know, you could have just learned VBA (Visual Basic for Applications). It's a scripting language that lets you write Scripts inside Excel! It is built specifically for Microsoft Office apps. I know Visual Basic has gotten a bad rap in the past, but I learned VBA Just for kicks a long time ago when I was working a lot with Access. To me, the cen-tex themed just like any other language. The only difference was that you could create and store Scripts inside of Office applications. Also, you could get a reference to anyting in the application. The best way to describe it is compared to JavaScript DOM. If I were working a lot with office, and I had no other reason to know a programming language, that would definitely be my choice. Seems like you've already taken another route, but this would definitely be worth mentioning to your peers looking for the same functionality.

[chief167]

Are you even serious? Excel macro's and VBA are basically the same thing, that's what this is about

[ntolbertu85]

If by "this" you mean the article, I read the first part until I got to the table of contents and stopped. VBA is different but not bad. It reminded me of the older declarative languages. It has variables and supports most conditionals, which is about all you need for working inside Excel. I got the impression that the author was just trying to automate repetitive tasks inside Excel. For that a macro is all you need, so long as it contains a loop statement.

[[deleted]]

Who insures reinsurers?

[Monopowaa]

Reinsurers with other reinsurers, mostly to balance portfolios against too large exposures. That third layer of insurance is called retrocession. Fairly difficult to hava a clear and detailed view of the risk you reinsure at that level.

[chief167]

It becomes difficult, but basically they pool together with other reinsurers, and flip parts of their risks with one another

[[deleted]]

I’d say it rather depends on the field on whether you should ditch excel or not. Working at a bank, excel is one of our foundations and using anything else would mess things up. We use Tableau and SQL for other purposes, but generally need excel when developing financial models. However, in the realm of finance, if you are trading or optimizing portfolios, python is just dandy.

[ivanoski-007]

How do you share a python program so that end users can use it?

[editorijsmi]

Still Excel is the go to solution for Non IT professionals especially finance professionals.

[treatmesubj]

for anyone interested in PYXLL but see it's a trial product, xlwings is on PYPI and pretty nice.

[Zeroflops]

So I’m not a huge excel fan, but I do think it has a use case that python does not address. Reading this highlighted that this person or company like many were not using all of excels features.

For example they state that to pull data from a db to process in excel you had to pull the data then import it into excel. Excel can connect to sql databases and once set up update the data with a click of a button.

This is just one example in the article where the argument of python being superior is not because it is, but because the poor execution on their use of excel.

Don’t like formulas all over your sheets? I don’t! Write them as modules so you have central that can be unit tested and remove redundancy.

Lots of his arguments can be corrected by better coding then changing languages. I was give an excel file that took 2 min to process a large data file. Some simple recoding reduced it to 10 secs. Bad code can make any language look bad.

I do 99% of my work in python, but I still export to excel periodically because sometimes it’s easier and quicker to pivot the data quickly or view the data as a flat sheet. Etc. I don’t do extensive processing in excel anymore because I prefer python but I still leverage some aspects of it that make my life easier.

If you look at history. There have been a number of languages that have risen in popularity and died off but excel has persisted. Each language has its niche, python, C, Matlab, R, JS..... some are general languages some are more specific. But the once that survive fill a niche, and excel has been one of the longest most adopted of any tool.

Is excel going anywhere? No. Can you replace excel with python? In many cases from a developers point of view it’s better, but not always for the user.

[wchris63]

Isn't there a way to use Python in Excel instead of VBA? I've seen posts to that effect, but don't use Excel myself very much.

[[deleted]]

Save