Ditching Excel for Python in a legacy industry

I'm a research actuary working in reinsurance. Here is why I think Python creates more problems than it solves from the standpoint of most insurance business users:

1.) Environment management. There are many solutions for managing python dependencies, my favorite is Docker + pip. Good luck getting actuaries and underwriters to write Dockerfiles etc, and good luck getting I.T. to support Docker on Windows desktops. Like it or not, the best "feature" of Excel is that it is mostly the same on every corporate Windows machine.

2.) Unless you are using numpy / numba, Python isn't that much faster than VBA (if at all). Both are "compiled" to interpreter bytecode.

3.) Speed of development and traceability. Excel takes a lot of getting used to, but if you know the purpose of the spreadsheet (e.g. a reserve calculation), it's relatively easy to figure out what a mangled and convoluted formula is doing (Excel has a "debugger" that allows you to evaluate formulas by highlighting pieces).

4.) LAST BUT NOT LEAST. Many financial and actuarial (insurance) calculations are inherently recursive. Excel has built-in memoization (in the dynamic programming sense). It also has a reactive programming model. Good luck implementing that in Python without tripping up on the huge amount of function call overhead, even if you use a memoization decorator.

When I worked at Uber, one of the big goals of my team was converting spreadsheets from the finance team to Python and Java. The second two problems that the author mentions (pulling in more data and software best practices) were two huge factors. In the former case, you simply cannot have an org where analysts have full read access to every data store to dump a CSV (of sensitive data collocated with lord knows what) at any time. It's a security nightmare. And in the latter case, when you've reached a point of sufficient complexity, you can no longer "roll out an update" to a team of more than a few people. Without versioning and source control, the model_v2_final_FINAL(1)(1).xlsx problem becomes extreme (even on cloud platforms). This leads to mistakes, and mistakes cost time and money.

Excel has other problems that aren't described in the article. First, it intermingles data and logic. If you're not especially careful and deliberate, running an experiment with multiple inputs means that you'll inevitably fuck up one of the inputs (or forget to change some data, or otherwise fail to do the steps necessary to reliably run the model again), leading to bad output. This is a reusability problem: you can do it right (one file per experiment, "template" spreadsheets, error handling logic), but in practice very few folks do this or even care.

Second, there's no meaningful way to test. If you've got critical logic, there's no way to write proper unit tests against the spreadsheet to ensure something hasn't broken. If I had a dollar for every improperly written linear regression in a spreadsheet... Conversely, writing spreadsheets as code means that you can rest assured that important units of logic are sound, which pays dividends when you're dealing with stuff used by a whole org.

Third, spreadsheets are really only useful as the "last step" in data processing. It's not good or easy to use a spreadsheet as input to something else. The inputs to the spreadsheet are usually manually updated (importing a CSV as a sheet), and then the output is graphical by default unless you're parsing the spreadsheet (good luck) or dumping it to CSV to import elsewhere (manual step with the risk of human error). In any business where the model you're dealing with pipes into other processes, there's almost always a manual step to get that data into "the next thing", be it another model, a dashboard, a database, etc. You can hack around this, but I've never seen a hack here that isn't incredibly brittle.

This isn't to say that Excel is bad, but when you use it "at scale" there are very rough edges that dramatically increase the ongoing costs of running a business built around it. When you're building a model, it's great. When you're running that model with different data more than a few dozen times a day and using the output in other systems, the costs quickly start to add up. That's the point where someone needs to step in and say "okay y'all, production use of this needs to run on a server". And if the production implementation is built well, you'll often find it simplifies the lives of the analysts, because they can download a blob of already- or partially-processed data to work with.

I'm pushing for our actuarial team to transition to more R + Git. After 3 years of preaching, most of the actuaries now use RStudio + git as their primary work tool. It is happening.

What we did :

1) Provide documentation on everything from install to using internal R libraries for ETL.

2) Provide mostly problem free, always updated VMs with RStudio Server/ Shiny Server.

3) Establish an hotline channel for instant help on R or git.

4) A couple members on the team developed really close working relationship with IT and we have great respect for each other work.

What we provide is way better and by being active, we built users trust in the tools.

We are phasing out SAS and proprietary modeling tools. Python never took hold even if we bought Anaconda entreprise. Excel is there to stay for sure but since actuarial student learn R in school, it is easier to onboard new hire.

If you want to go down this path and have a chat, hit me up. I'm in P&C. We use R both in development and production environments. We use it for pricing, spatial contractual obligation, claims assignment and a couple more models.

As the author states - this is an issue for some really complex models - where the complexity, reusability and iteration challenges approach code.

Most models do NOT take that many tabs, you can build a toy model near instantly - the production line from finished model and output to publishable material is a few shortcuts away.

Having an analyst, write that same thing using Jupyter? From an accounts perspective? Man, I’d want to see it in a spread sheet. It’s just simpler, or more familiar, to debug accounting information in a spread sheet.

The idea that we are going to see all those analysts pick up code - over excel - is possible, but I’d say less likely.

I’d suspect that the idea of python inside of excel, is a winner. But given that excel is working with its own data model and data tools with power BI, or with their new Lambda function, I’d say they are also working to keep people happy within the excel ecosystem.

Interestingly, this is a version of the Bloomberg terminal debate - the terminal does everything, any upstart can only do a small part of the BB offering, allowing BB to always be relevant if not dominant.

I work in an excel heavy function (Corporate Finance). And while this sounds very exciting and fresh I am just not seeing it take hold in Fortune 500s I have/am working for. A few reasons:

1) biggest gripe: I don’t have time to maintain and fix models after I move to a new role. If it’s a Python based model I build, no one can seem to fix it when some tiny thing breaks 6 months after due to a change in the data. I’ve had to work weekends to help colleagues fix models that I don’t use anymore. I can hand Excel to a young or old worker and they can always seem to figure it out and take it over.

2) The tools seem limited when directly doing Python in Excel like the one mentioned nothing the article. VBA kind of sucks in 2020 but until Excel natively accepts Python as part of its base, I don’t love being dependent on these 3rd party tools. VBA always works.

3). I’ve recently complete an MS in Data Sci so I am very familiar with Python and R. My company doesn’t need that level of model for most things. We are a best in class in our industry and we get by using lots of Excel models. I mentioned in my first point that I have built a few things with Python. When I had to fix I just rebuilt in Excel and that was all I needed. When I kept fixing the Python code I always felt like I let folks down if I couldn’t fix their stuff right away. Yet our business makes money and we continue to do well without much Python.

I love Python. But until others start to see its value and a critical mass of individuals knows/supports/can implement Python, I will put emphasis on learning Excel tools or SQL first because those will always be supported.

The big problem I've always had with "programming" in a spreadsheet is by nature everything is obfuscated and difficult to trace. Yes, you can inspect a cell and see what the source for that cell is, but that might be 10 other cells and you can only really review one cell at a time. It's like a programming language where you only see one line of code at a time. Worse, those references usually aren't named. What does "A1 + SomeOtherTab:B2" mean?

All of this really starts to fall apart when you have 10s of tabs with hundreds of rows of data which are often copy/ pasted. You won't even notice that some intern hard-coded one value into cell F75 until you actually drill down to that cell.

Spreadsheets are great until you hit a certain complexity, then they are unmanageable messes.

That was a neat detour into reinsurance. I wonder how the main thrust of the article holds up generally. My experience in trading/finance is that Excel remains extremely preferred for last-mile use (i.e. for writing and reading reports). Whereas the data science ecosystem has been adopted to develop research platforms and manage the data into (what ultimately becomes, for most users in the organization) an Excel sheet.

I don't see this changing any time soon. I think Excel was always strongest for last-mile use. Excel is extremely powerful when you know how to use it correctly, and I routinely see people match or exceed the productivity of programmers using it for specialized use cases.

The author pretty accurately describes a business model for a startup in an enterprise company: offering a service that was once hidden in some excel sheets.

As a developer at heart turned Senior Manager, I find this article especially interesting. I stumble a lot over complaints like these in the enterprise company I am working for and truth to be told, I voiced many of these before myself.

Problems I see:

- What is the business problem the author is trying to solve? How does a tool - Python - can help do specifically do what better?

- There are no specific measurements mentioned. How big is the data the author mentioned, how long does an analysis cycle take, how large are the teams, the affected people? What about maintaining the software stack? How many requests are there per year?

-What about cost savings? How could they help us compete with other companies? Lead cycles of even weeks may bother a developer but not the business.

It is not that I don't believe his suggestions. It is just that I don't get to the point other than "my favorite tool could do it, too." We could easily substitute Python with R, for example.

"The spreadsheet took 30+ seconds to open" I know this is an annoyance, but how often do you open it? One time a day? 20 times an hour?

"The new model logic is testable and can be upgraded independently" this is one of the most valuable points here, as long as you work in a larger environment. So context is needed here as well.

I know a colleague of mine who is extremely well versed in Excel who has put a decent amount of magic into her sheets. However, even losing her and starting all over again is from a business perspective way cheaper than trying to put her solution behind a cloud service.

It would be fun, to have a conversation with the author.

XLL lets you also write .NET code, basically anything that can compile to a DLL, to make custom functions you can expose in Excel - and it is a pretty old supported integration method by Microsoft with Excel. COM enabled DLLs were another way to do this, but they ran slower.

Not that I have any issue with getting Python in my Excel, but people seem to forget that .NET is also an option.

Getting these capabilities enabled in a locked down corporate IT environment traditionally was difficult but I suspect that is changing.

I have also lived the whole, turning a model in Excel into an app exercise. At the time, we rewrote a fairly complex demand planning app from Excel/VBA to C# since the other dev team members were C# devs and could support the app.

However, during the project, I did a demo of how one could build a Winforms app in VB.NET also, to the developer who was the Excel/VBA guru. He'd had no idea that coding in VB.NET and Winforms was close enough that he nearly could have been doing that instead.

The compiled C# version of the model we built, went from running a single instance of the model in 1 hour, to under 1 minute. We could re-run their model for tens of thousands of instances daily, without breaking a sweat.

Ironically, the rewritten version in C# never saw the light of day as the project was canceled (corporate politics and wisdom). However, the simple optimizations we identified in the rewrite were given to the Excel guru who actually made improvements to his tool that let it run in more like 10 minutes.. and it was even object oriented and modular! He learned he could do a lot more in Excel/VBA that he didn't even know about.

Coding is coding.. just some tools make the jobs easier or harder.

This reminds me of Bill Gates' comment about secretaries writing VBA. Thankfully, that didn't happen. My friend rides herd on all the python code written at BigBank. He said they now have over 1MM python scripts, "Most of them written by people who had no business writing code in the first place." Python certainly lowers the barrier to writing code, but I seriously doubt many Excel power users will jump on the Python bandwagon. If you are not up to speed with the latest BI tools for Excel, you are missing out. Power Pivot is brilliant and dynamic ranges are the cat's meow. With LAMBDA there is no need to try cramming Python into Excel. Don't underestimate how much large corporations prefer to use products written by professionals instead of relying on a menagerie of ad hoc packages cooked up by well-meaning amateurs.

This is a really interesting article on some of the challenges facing enthusiasts for technologies such as Python in a "traditional" corporate environment.

I'm a really strong advocate for these technologies - and have been developing a notebook based product for use in the insurance sector - but there is a lot of resistance.

- There is very little awareness of the power of open source tools to do traditional data manipulation tasks;

- In addition to Excel there are both legacy and newer proprietary systems backed by consulting firms that have a strong hold over parts of the market.

On the other hand there are some areas where there is increasing adoption of Python and (especially I think) R to do statistical analyses that are difficult / impossible in Excel. Also DataScience tools and techniques are now being taught as part of standard actuarial courses.

Finally, firms are increasingly acutely aware of the risks of relying on Excel and are looking for tools with better control / testing environments.

I'm way more proficient as a programmer, than an Excel user, so it was my assumption that all these marketing guys that constantly work with Excel can do wonders with it. I mean, they probably can, but recently I tried to use it (actually, it was LibreOffice Calc, so there might be my problem, but I don't know if the difference really is this big) instead of writing a python or bash script (as I would usually do) and was unpleasantly surprised by how complicated the stuff that I would consider standard is, like concatenating columns, grouping/counting unique values, dirty data semi-automated cleanup and such. Everything would require either multiple clicks in multiple menus to perform something that appears to be very ill-composable actions in Calc, or would require writing 50-line Basic script (on kinda ugly APIs) for something that I can do by simply converting all of that to csv and writing 5 lines of Python (or sometimes even shell text-utils, literally). My general impression was that it is not really a tool made with power-user efficiency in mind, ending up being not very efficient for anyone, since it isn't very intuitive software to use for an excel-noob anyway.

So my question is, is this really the state of art for visual working with data-sheets, semi-manual data editing and such?

I was assuming that running a Jupyter/Pluto/RStudio and doing stuff in Python/Julia/R when you don't indent to do actual data-analysis/learning, but only something that seems like basic stuff/preprocessing is more of a bad habit, because Excel was actually made to work with tabular (DataFrame-like) structures, but I ended up feeling like there's no way I would actually prefer Excel for that.

I work somewhere that was stuck in even more simple excel usage, like sorting a list and counting rows for each type of category instead of using a pivot table.

I've done some more advanced work with python and xgboost for some modeling, but the biggest improvements in terms of both time saving and regular use of data for informed decision making has been implementing basic reports and dashboards. So much so that sometimes I feel like I'm creating kindergarten doodles that get praised as amazing masterpieces, which is a weird sort of embarrassment. I jokingly describe my job as "I count stuff" because a big part of what I do is still working with departments on what they want counted and the most useful way of displaying it to them. Percentages and year-on-year comparisons are magic.

I'm not quite sure what qualifies as a "legacy industry", but just about any organization that's been around for 40+ years could have the potential for massive improvements from taking advantage of improvement made during <= the past 20 years.

I work in a small R&D team within a larger engineering organization. I use Python, and it has spread to the rest of my team. However, I've tried to share tools that I've written in Python with the engineers. The problem is that I have to hand-hold them through the process of getting Python working on their computer at the level of detail of: Here is how you find the Python editor. Double click on it. Click on "open." Find the Python file. Click on "run." Click on "Run Module." Or you can press F5. No, you have to be in the editor when you press F5. Now do you see that a window just appeared? Look at the entries and buttons in the window...

It's really quite harrowing. Whereas if I put the same thing in an Excel file, they can bring it up themselves and I can quickly walk them through using it.

And I don't think my UI's are all that bad. But going from throwing together a simple Tkinter GUI, to something that is totally user proof and self installing is actually quite a lot of work.

We have a few customers in reinsurance, and for the most part the goal is to do the opposite of what the python solutions try to do. Instead of integrating foreign stuff into existing workbooks, the goal is to retain the existing worksheets as source of truth and build modern tools around the files. The most common use case is building out a web interface to replicate the Excel formula engine.

In the python space, there are libraries like openpyxl and xlrd, but the real hurdle is introducing python into an ecosystem which otherwise has no natural knowledge. JavaScript is the language of choice for modern Excel addins as Excel provides an actual API for it https://docs.microsoft.com/en-us/office/dev/add-ins/referenc...

If the industry needs a better data analysis solution, that's fine, find one or maybe start to build one. But saying "I'll just throw some Python at it" isn't a good solution. It's like saying you're gonna replace a lawnmower with tool steel and a welding torch. Not only is it not guaranteed to end up as a better solution, but you're setting yourself up for a lot more work than you think.

I think replacing excel with python is a bad idea.

Think of it as a developer: you write a small application, and everything - code, code dependencies, data, really everything - is in one file and can be sent over email. The program behaves the same everywhere. The code can be understood by people who have no coding literacy.

Good luck doing that in python.

But I guesd I don't understand the fad with python anyway.

Related note about something I've been working on: https://JuliaActuary.org

It's basically packages to support actuarial work written in Julia, which addresses a lot of the issues of Python/R (environment management, runtime speed, rich cross-package compatibility).

> At the end of the day, making a change in an Excel sheet is easy; understanding formulas is achievable; but learning to code is hard.

This is the crux of the matter. I would guess that there are more than an order of magnitude Excel users than Python programmers. Python is great if you already know programming, but expecting domain experts to learn Python in large numbers is going to be a daunting barrier.

Loved the post. I spent more than a year trying to pull a prominent reinsurer on the rock, out of spreadsheet hell, and into the modern age. Baby step #1 would have been to transfer critical data to a database environment and baby step #2 would have been to extract business logic and very-poorly written VBA code into an external code library (Python) that is source-controlled and auditable... I can still hear the Chikadees laughing at me. Last I heard said firm was still deep in spreadsheet hell.

> We used an Excel spreadsheet-based model with dozens of tabs containing complex formulas, endless pivot tables and unintelligible VBA code.

> The tangled mess of VBA was re-written into independent Python modules, each of which performs a distinct function.

Hardly a valid argument. I mean, VBA supports using modules too, so it basically comes down to an increased skill of the programmer and maybe more knowledge about the actual scope as it is a rewrite, but there is no reason why it could not be done with VBA.

I do a lot of both. Excel really is great for data where there are less than say 100k rows. Its just so easy to see exactly what you're doing and what the data looks like. If you have millions of records Python really does better but I still find it frustrating to find a way to keep peeking behind the curtain.

Ideally I'd have a type safe language which can embed data the way excel does. If Excel had dotnet languages instead of VBA and could store data arrays in XLBs it'd slay.

The title is a bit misleading: It's not that typical Excel users are encouraged to, or experimenting with, using Python instead.

Rather, these are people who need to "price complex deals with increasingly large datasets". They write pricing models and need to run them.

I’m surprised that there aren’t more comments about utilizing R AND Python for analysis work. These two languages actually commingle fairly well, you can build in RStudio if you like that flavor and still import Python packages to use in R code.

We do a significant amount of modeling and analysis on large data sets from a variety of disparate sources and utilizing several different packages have extended this out to standing up a fully free (save for AWS hosting) environments that perform modeling, allow reporting and Dashboarding automation, restful APIs for other services to call into.

I’d encourage anyone looking at making the jump from Excel to ‘X’ to checkout out some of the power of flex dashboards, R Shiny, Plumber and some of the different authentication mechanisms available.

Some elbow grease can create a wonderful environment.

This is actually about using a paid, closed-source add-on PyXLL, that integrates python into Excel, but only to the Windows version.

And costs 25 USD per month (but has a free trial).

Excel is never actually ditched.

edit: oh, that's only step 4. Step 5 is actually ditching Excel.

I dont claim that Excel is the best tool or that it should be used in reinsurance, but it seems that the author does not know how to use it properly.

It sounds more as if they knew Python so they use Python. If author would know Java they would write that Java is better.

For example combining data with logic does not need to happen in Excel. Someone who does this might make the same bad practice in Python too.

Also there are no thoughts that there are many bad Excels, but there are also many bad programs (in Python and every other language). There is some sort of magic thinking that a rookie who switched from Excel to Python will somehow not produce spaghetti code. What is not true at all. Those Excels are much easier to debug by the business side. Any program is a black box.

Author makes empty claims that "excel forumals are long" or that models have many tabs. If the Python program gets as big it might also become a mess.

How many times have you heard that the new programmer looks on old code and says that it is spaghetti? Nearly every time. Nealry every time they want a rewrite too. And Excel just works.

I doubt author used Python programs made by others. There are no comments on that. Also eas authors code reviewed by a real programmer? Author is self thought so odds are that they create some really awful code and dont even know it.

I’m a diehard user of the “PyData” toolchain (pandas, numpy, seaborn, sklearn, etc). Models become too much for Excel when either (a) you want to incorporate live updating datasets, (b) you want multiple users to be able to query the model simultaneously without affecting each other, or (c) you want to incorporate probabilistic calculations i.e. your inputs and/or outputs are distributions. Python blows Excel out of the water in these cases, it’s not a question of speed or ease of use.

However, I think Python’s biggest current weakness is the lack of a general purpose plotting library with good defaults or GUI-based tweaking. Matplotlib “can do anything”, provided you’re willing to google how to rotate axis labels and 20 other things to get legible styling. Seaborn is an improvement but still takes re-writing about 10-20 lines of code for each plot. As far as interactive libs, I prefer bokeh but it’s still too low level and missing fundamental capabilities like histograms. Holoviews is an interesting wrapper but still suffers the same limitations. Plotly... is popular, which is about all I can say for it. I find that I hit random walls and inflexibilities often bc it tries to be too one-size-fits-all. I understand ggplot from R is kind of the gold standard. Wish someone would do a carbon copy port to python.

Final random thought: my feeling is that white collar industries like insurance that are built around a network of Excel jockeys are in for a major disruption. If you built these companies from the ground up with a software dev team and mindset you could probably cut headcount 5x. It might not make business sense for a company deeply rooted in Excel to make that transition, but then again, that’s exactly why and how disruption happens.

I’m an actuary working in life insurance and I’m so shocked to see how inefficient processes are in many insurance companies. Some models take hours to run in excel whereas they could be easily run in a copule of minutes using python or c#. Not to mention the importance of having a clear version history that is just impossible with excel.

Lots of discussion here of Python and R as alternatives to Excel. But no mention of drag and drop data processing tools such as Alteryx, Knime or Easy Data Transform. Are these not a more natural alternative to Excel for people who don't have a programming background?

> The development environment is not user friendly, the syntax confusing, there’s no support for unit testing – I could go on.

The vba ide is pretty good imo. Lack of unit test frameworks is valid but doesn't stop you from rolling your own.

People have been integrating web services with excel for two decades. Previously you had to write a C XLL to do it properly, but COM add-ins have been supported for a long time, although have oddities, and now you can load .net assemblies into excel. You can use kerberos baked into windows for authentication, or just use http basic auth, and control access to data sets just like any other thing. So I guess, I'm confused by the article because a lot of the claims about how you have to do things in excel don't seem to be quite right.

I like the idea but it’s a bit concerning that everyone would be expected to build models in Python. You’d think there would be enough reuse and structure that it wouldn’t be needed or an application could be built to simplify the construction of the models.

If it’s so complex that it needs to be coded up in Python and everyone is doing that bespoke each time it feels like alarm bells should be going off.

The problems with excel IMO are:

- large datasets are an issue

- it doesn't have some libraries without extra cost (and in my instance long winded approvals). I'm specifically thinking about linear programming libraries here.

- VBA is less easy to code in

Otherwise excel is great.

Side note - is anyone exploring Jai? This seems to try to be solving the installation and compatibility issues that mires coding these days.

We are using notebooks in visual studio code to process csv downloaded from a corporate BI and replace Excel analysis. As a programmer I dreaded questions like "do you know how to program macros in Excel?"

Slightly off topic but any suggestions for the best forum for issues relating to use of Python / R in this sort of environment (thinking insurance / finance specifically)?

Blockpad (http://www.blockpad.net) tries to ease some of these pain points but more in the engineering space.

I love python. In investment banking I can see a usage for recurring analyses of fundraising or grabbing data from the SEC. it would be next to impossible for analyses by client.

I recently did a project where a financial model for IAS-19 built in Excel was migrated to a cloud based solution based on Python/Django/Pandas.

Actually on the life sciences domain I worked a couple of years, I saw them adopting VB.NET alongside Excel, Python was not even on the radar.

I wish I could ditch excel but I use both viewing the data and doing some ad hoc calcs is much easier for me with excel.

How do you get compliance/IT to sign off on the inherently risky pip install mysterypackage though?

Better sell some cat bonds if you’re adopting Jupyter notebooks en masse ...

F# is a better Python - especially with Units of Measure

Does your name rhyme with "tennis tin"?

Why use Python when MS built in JS?

Is this a joke? The PyXLL add on costs $25 a month. Hard pass.

I wish there was just a desktop program like Excel that actually fucking works well on Mac and PC. I like Google Sheets but it's way too integrated in the cloud for my tastes.

Excel has so many good features, but the core of it is so fucking buggy.

It sucks that I gotta bust out Jupyter and use Pandas to double check my work, especially dates, because I can't trust Excel.