10 ms·

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 u

by yold__ 6y ago

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.

mslate 6y ago
From an actuary's perspective: is Google Sheets ever entertained as an Excel alternative?
- anonymouse008 6y ago
  Not an actuary - but I think this crosses domains: the lack of comprehensive shortcuts makes Google Sheets DOA (dead on arrival) for my uses.
- deleted 6y ago
  [deleted]
- yold__ 6y ago
  It's been a couple years since I've used it, and I didn't feel it was a comparable alternative. It's decent for about 80% of spreadsheet users, but the keyboard shortcuts were lacking and it was missing some functions that I rely on. For keyboard shortcuts, most Excel power users don't use the mouse, so while it sound trivial, it's really hard to feel productive when you have to hunt around for the right button to click. From an enterprise perspective, Excel is so entrenched it would be a 5-10 year effort to port existing spreadsheets to sheets. Practically speaking, most companies wouldn't see the benefit.
  - will_pseudonym 6y ago
    > From an enterprise perspective, Excel is so entrenched it would be a 5-10 year effort to port existing spreadsheets to sheets. Practically speaking, most companies wouldn't see the benefit. And at the end of the day, it would have worse performance than Excel both in calculation speed and _much_ worse UI. One of the reasons Excel is so much better than Sheets is speed. Insurance companies spend hundreds of thousands of dollars a year on actuaries. Even if Excel cost them $500/year/user, it would be easily worth it for actuarial departments.
    - etothepii 6y ago
      The hundreds of thousands of dollars a year that insurance companies spend on actuaries is the real reason that no one will ever come off excel. The maths is not very complicated but the lack of source control and testing means each bugfix or added feature introduces another bug to fix next week or feature to add the week after.
- zinekeller 6y ago
  Used GSuite (Google Workspace?): It's fine, it is improving but lack of shortcuts even for basic tasks (I can change the font on Word with just a keyboard, try that with Docs without a mouse). Dealbreaker is the 5 million cell (not row nor column) limit, which is even lower than Microsoft's old limits (more than 15 million cells, which was increased in 2007 to you-have-a-serious-problem-if-you-somehow-fill-this-limit cells).
  - andylynch 6y ago
    Not to mention that if you're using Power Query, Excel's 1 million row limit doesn't apply in the workbook queries either.
  - HALtheWise 6y ago
    Alt+/ is the magical shortcut that makes this easy, you can simply search a font name and hit enter.
- will_pseudonym 6y ago
  No, for many reasons. Workbook calculation performance, advanced, complicated spreadsheet incompatibility, worse UI/keyboard shortcuts, slow UI, different language than VBA for custom functions (It isn't necessarily worse, but it's the same thing as trying to convince a department to switch from language X to Y including rewriting every application that's written in X. Also, every person you've ever hired was familiar with X, and has no experience with Y.) R/Python/SAS etc. are a much more compelling alternative to Excel than Google Sheets (to say nothing of the actuarial modeling software packages that are used already for more rigorous/complicated problems). If an insurance company decided to move all of their MS Office users to Google Docs/Sheets etc, my money is on the actuarial department paying for Excel out of their budget without a moment's hesitation.
- rvba 6y ago
  Uploading propertiary data to a cloud is a big no.
  - etothepii 6y ago
    Companies are more worried about this than they probably should be. The average enterprise network is nothing like as secure as people behave like it is. Where do you think your email is hosted? With few exceptions I'd expect its provided by a cloud provider these days.
SoSoRoCoCo 6y ago
It is great that you respond with succinct reasons. People who have used Python for some years seem to forget just how clunky it really is. I've been using Excel since 1990, and sure, it has its own warts, but Python is a very rudimentary tool compared to Excel. Python is a machine shop. Excel is a car. It may be a lemon, but it's a functional car. This is a great example of programmers not being able to see the forest for the trees. Reminds me of the "Once Linux gets a desktop it will take over the world" debate from circa 1997-today.
- fock 6y ago
  I wonder what the problem is with standarizing companywide around python@3.x, numpy@1.1x and pandas@1.x. At this point these can all be considered mature and why on earth would an org, which is not developing these packages, nor heavily consuming outside code (because they didn't with excel either in a sensible way?) decide to jump on the "but we need rrrrrollling release"-fad bandwagon?
  - yold__ 6y ago
    The problem isn't feasibility, it's resources. Building and rolling out a standardized environment, and maintaining it, will cost millions of dollars. It shouldn't, but it does. And for what added benefit? The end-users don't want it, you'd have to spend another couple million for a lateral move at best. More than likely, you'll end up with a pile of Python spaghetti code that runs slower than the spreadsheet (see point #4 about massively recursive calcs).
    - shnock 6y ago
      Why does building and rolling out a standardized environment cost so much? Could you break down the requisite steps and resources required to achieve this? Thank you, I appreciate it :)
      - yold__ 6y ago
        Up-front costs (mostly salaries, but all I.T. projects are "billable") 1.) Getting buy-in from solutions architect, software architecture, information security, I.T. management. This will be a 6 month process. 2.) Getting buy-in from actuarial management and audit. Another 6 month process. Recurring annual costs (over 10 years) 3.) Contractor at $150 an hour = $300K annually 4.) Contractor PM at $50 an hour = $100K annually 5.) Information security compliance hoops, getting it to play nicely with the myriad of endpoint security tools, etc 6.) Ongoing maintenance and support (failed rollouts and upgrades, user desktop support, user training)
maxerickson 6y ago
Would the people doing the implementation need to be able to choose and manage dependencies? Or could they do the work inside a prepared environment (comparable to Excel in some sense).
- fock 6y ago
  that costs millions!
  - yold__ 6y ago
    I think you are mocking me, but I'll bite. Insurance companies are contractor heavy. They bill at $150 an hour. That's $300K annually per head. Won't take long to get a million, when you add PM overhead, information security oversight and governance, etc. Again, it shouldn't cost that much, but it does.
klelatti 6y ago
Agree completely on 1. but not sure on 2. and 4. - I think if you're using Python and need performance then you will be using Numpy etc - would be interested to hear if there are instances where this doesn't work.
- yold__ 6y ago
  numpy is great for vectorizable calculations, but many calcs (particularly for long-term life contingent risks, i.e. reserves), are not vectorizable except in the most simplistic cases.
  - klelatti 6y ago
    Thanks - sorry I'm struggling a bit - wouldn't they be vectorizable across the portfolio or across scenario for stochastic calculations. Maybe it's because of different backgrounds (mine in UK) but I'm can't recall seeing the deeply nested function calls that you're alluding to.
    - yold__ 6y ago
      Think about the calculation of an insurance product with a Fund Value. Everything is forward recursive with respect to time. Been a while, so I might butcher some of this. It is likely that you'll want a 30 year projection, so you'll call fundValue(30 * 12) fundValue(t+1) = if t > 0 fundValue(t) - charges(t) + intCred(t) else initialPrem charges(t) = netAmtAtRisk(t) * costOfInsurance(t) + riderCosts(t) + policyFee(t) netAmtAtRisk = (FaceAmt - fundValue(t)) Now think layering on decrements surrenderMargin(t) = lapseDecrement(t) * (surrenderCharge(t) * fundValue(t)) mortalityMargin(t) = mortalityDecrement(t) * netAmtAtRisk(t) investmentMargin(t) = (earnedRate(t) - intCred(t)) * assetBase(t) Now think layering on calcs necessary to calculate the assetBase (e.g. reserves + required capital)...
      - klelatti 6y ago
        That code looks very familiar! I see what you mean now. I don't think I've ever seen this implemented recursively though - can certainly see how this would end up being problematic if you tried to do this in Python! ps Thanks so much for taking the time to set this out. pps I've been working on something that implements a highly optimised version of this style of calculation - with a DSL to describe the calcs - can do 30 year cashflow projection for 1m contracts in about 1 min on quad core laptop. UK focus initially but might have wider application?
davidu 6y ago
This is the argument made against all software stack advancements. Nothing to do with industry. But when the benefits outweigh the the hurdles, change happens. And if I was starting a new insurance company (which I've considered) I'd be doing our work in code not xls, and probably python. Having RCS, Numpy, unlimited compute, unlimited storage, all gives me an advantage over my competition. :-) As to the memoization, that is not hard to manage in Python.
- yold__ 6y ago
  "As to the memoization, that is not hard to manage in Python." Yes it is. Recursive calls for financial calculations easily go hundreds of thousands of calls deep. This is why high-end actuarial modeling software either decomposes it into a dependency graph and unrolls function calls where possible, or just "brute-forces" it by being a thin wrapper over c++, i.e. using operator overloading on ::operator(). I've seen ill-fated efforts of capable software developers attempting to unroll the recursive function calls, and ending up with 2000 line functions that are impossible to maintain.
  - solresol 6y ago
    Why doesn't annotating these functions with @functools.lru_cache(10000000) work?
    - pmart123 6y ago
      My guess is the numeric inputs would be changing significantly each call?
    - yold__ 6y ago
      First of all, let me say that I've tried it :) Your recursion needs to "bottom-out" in order for that to work. If you don't get a stack overflow / out of memory error, you're good. But bear in mind that there will be thousands of stack frames. Before you get to time=0 (the recursive base case) in a long-term liability actuarial calc. The recursion isn't simple like the Fibonacci sequence . It's more like: f(t+1) = if t > 0 (f(t) + g(t)) * h(t) else initial_constant g(t) = f(t) + q(t) - d(t) q(t) = .... d(t) = ....
mch82 6y ago
> problem #1: Environment management Great observation. Python environment management is getting simpler, but is off putting for people without a software background. Unclear even CS majors get enough classroom exposure to package & dependency management to utilize Python efficiently. I’m more optimistic about an on-prem deployment of Jupyter Notebooks or Sage Math Cloud as a way to hide a lot of the setup complexity. More like a wiki for math. Curious if anyone has stories/tips to share (good or bad)?
- techphys_91 6y ago
  Is this really an issue for the use case being described? Environment management is obviously a significant consideration for software developers who need to keep track of versions etc, but it sounds as if these users primarily want to use the fundamental numpy functions. They could install one of the scientific python stacks (e.g. anaconda) or just install packages globally with pip.
  - mch82 6y ago
    Anaconda switched its software license recently to require large companies to purchase a license, so now people must wade through the purchasing department before installation & use.
  - macintux 6y ago
    Things always break over time. I have a very technical co-worker, a systems admin, with a broken Python stack on Windows. No idea what's wrong.
    - fock 6y ago
      so he did a chmod -w on their folder and it broke. Well, that's a feat I still have to work out!
- everling 6y ago
  Quant here, at my firm we've deployed a JupyterHub server which provides users with a production docker image, so that analysts and portfolio managers can perform analyses without installing python, dependencies and sql drivers locally. It is working well and spurs interest in Python across the wider org - so we let everyone use it. Similar to in OP's case, I think the real selling point of Python over Excel is advancing the capabilities and the scale of the business. Talks of different programming languages falls on flat ears in finance - show what can be done instead. With Python, Zipline and notebooks I can manage a global equity portfolio, continuously adding active strategies and adapting to real-world changes and constraints. And backtest! Excel is great, but there is an upper bound to what can be reasonably done without a thriving open source community.
stochastastic 6y ago
Hey fellow reinsurance actuary! I totally agree that Excel has its place in modeling, especially one-offs, and your criticisms make sense. That said, we have been moving a lot of our calculations to Python. We have had way too many rickety tools to move files or send emails (“first you open this spreadsheet and click this button, then you open this spreadsheet and click this button, then...”), and way too many version control issues over the years. Python solves those nicely. I’m curious about docker + pip, why do you like that better than poetry or pipenv?
- yold__ 6y ago
  One reason why Python is so successful is that it places very nicely with C code. Many of Python's libraries are thin wrappers around native DLLs. For example, numpy is a wrapper around a BLAS DLL (e.g. Intel MKL). Pipenv manages the python side of things, but don't exert control over the system DLLs (like Docker does). Anaconda gets very close to what Docker does (by managing DLLs). Have not used poetry, so can't comment. Ultimately, like most dependency management issues, lacking a stable DLL environment won't be a problem until it is :)
  - stochastastic 6y ago
    Okay, I can totally see it now. We are still at the stage where people seem to think that I’m neurotic for worrying about the python side of things, so DLLs have not been on the radar. ;)
  - lldbg 6y ago
    numpy is much more than a wrapper around a BLAS dll. BLAS implements three sets of operations: Level 1: unary and binary vector vector operations, one transform. Level 2: Matrix vector operations. Level 3: Matrix matrix operations (most famously the dgemm routine). Perhaps some blas implementations offer more features, but that would defeat the purpose of a standard interface.
  - z3t4 6y ago
    In Nodejs native libraries are a PITA. The compatibility API breaks on a schedule every 6 month, dependencies get updated by OS distros sometimes breaking stuff, then you rely on the OS being able to compile the library. I hope Python has better native interop.
mint2 6y ago
Why do actuaries refer to workstation/desktop computers with more than 16 cores as “super computers” it’s embarrassing but sometimes I give in an say “the super computer” because I’m in a hurry and they’ll give me a blank stare if I call it a workstation or anything like that.
- mark-r 6y ago
  They really are supercomputers though. Do you know how much faster a modern PC is compared to say a Cray-1? Especially if it has a decent graphics card.
  - ryukafalz 6y ago
    Yeah but at this point my phone is comparable. If being faster than a Cray-1 qualifies something as a supercomputer, the definition is meaningless now.
  - mint2 6y ago
    So a typical actuary’s technological reference point is stuck in 1985? That explains a lot about excel and sas egp. But seriously, calling a fairly standard computer in the tech world a “supercomputer” is just another example of the underlying attitude in insurance that makes many actuaries recoil in horror about the thought of “programming” aka learning python or any programming best practices.
sam_bristow 6y ago
Funny story about Excel on corporate machines. A couple of years ago the company I work for got boight by an Italian company. When we finally migrated the Windows users over to the corporate Office installs a bunch of people found that Excel wouldn't work for them. Things like sum(A1:A20) were syntax errors. After a bunch of digging i worked out that the localisation from corporate meant they suddenly had Italian function names not English. Very confusing. Excel is a program that is both incredible and terrifying to me. There are ways of building spreadsheets that are reliable and auditable. Then there's how 95% of people do it. You can start out really quickly and make great progress. But it tends to grow and metastasize before you know it.
- HALtheWise 6y ago
  This brings up a good point, which is that Excel supports localization, while Python just assumed you know English.
- etothepii 6y ago
  It must be easier to build an auditable and reliable solution using a high-level language programming language and concepts like source control and automated testing. Excel is only easier if you aren't interested in building something auditable and reliable solution that might have some hope of being maintained after you have left the company.
  - sam_bristow 6y ago
    That's the thing, most Excel workbooks start out as a one-off then gradually get adapted and extended until they're load-bearing. They're often built by specialists in another dept who definitely wouldn't consider themselves programmers. Doing it 'properly' would probably mean having to spec put the problem, get a budget, maybe wait a few months for someone to look at it. And the same thing every time the requirements change. Excel is available today and they can get started solving their immediate problem straight away. After it's been in use for a couple of years and shown value someone takes a look and sees the Lovecraftian horror it's become.
etothepii 6y ago
The calculations typically performed by actuaries are individually all fairly simple but in aggregate without automated testing and version control it is reckless to use them in pricing or portfolio calculations.
eyeball 6y ago
the new functions in recent versions of excel make an even strong case for its use https://techcommunity.microsoft.com/t5/excel-blog/announcing-lambda-turn-excel-formulas-into-custom-functions/ba-p/1925546 https://techcommunity.microsoft.com/t5/excel-blog/announcing... https://www.excelcampus.com/functions/dynamic-array-formulas-spill-ranges/ https://www.excelcampus.com/functions/dynamic-array-formulas...
- etothepii 6y ago
  I have wondered about this, the lambdas and table could be of huge benefit against some of the most egregious excel mistakes but that isn't an argument for excel's use. The problem is not one of it not being possible to do automated testing or source control in Excel. VBA is Turing complete so anything is possible, it's more one of not thinking, or understanding why, those things are important. Once you do come to think of such things as important you will quickly never use Excel for anything but the most basic calculations.
jandrewrogers 6y ago
I use both Excel and Python, and like both. They solve different kinds of problems, even within the same context. Excel is fantastic for what I would describe as linear modeling, building a graph of effects in single data models. I reach for Python when I need to fundamentally transform the data model at points to answer the desired question. That is difficult to the point of being impractical in Excel, especially if the data model is large or exploratory. Python is more programmable in this regard but also lacks the strong static typing that would be useful in such work. I can’t imagine not using either.
kfk 6y ago
Hi, this is a late reply but I am actually pulling this off. Your points above all make sense but are around the desktop paradigm. If you move to the cloud paradigm not only most of the points go away but you gain a lot by having data all in one place (S3) and strict collaboration (github). Specifically the Python env problem goes away if you ask analysts to work online with notebooks (ie jupyter hub).
harha 6y ago
For 1. I would say R is a good option. It works relatively well everywhere and has an ok IDE, lots of packages that make life easier (tidyverse). I also wouldn’t recommend python for exactly that reason.
RobinL 6y ago
I think these are all fair points and a good reflection of the downside of Python. But there are also some pretty huge upsides. My view is that for any model of significant complexity, the pros of Python outweigh the cons from a technical point of view. - Abstraction. It's very difficult to effectively abstract parts of a model in Excel. It's a bit like a doctor having to 'model' a human being as a collection of atoms, rather than having abstractions like organs, cells etc. This makes it very hard to build re-usable components, so analysts end up reinventing the wheel. You also quickly hit a 'complexity ceiling' in Excel, above which mistakes and errors becoming much more likely, and complexity is very difficult to manage. - Existing libraries provide a huge range of sophisticated calculations and operations for which we don't need to write any code. - Separation of concerns - particularly separating data from model. Easy in Python, hard in Excel. Another aspect of this is that using data science software promotes the use of tidy data[0] (i.e. clear thinking about how data should be structured). - Unit/integration tests. For complex models, these are essential. Users of Excel (even extremely clever/competent people) don't have have a great reputation for producing error-free spreadsheets, and I think this is an important reason why, alongside copy-paste errors. The tools for testing in Excel/VBA are rudimentary. - Version control. This is particularly important for historical reproducibility because it allows us to run past models, and also understand what has changed in the codebase since. I appreciate some of the above is also possible in VBA, but if you're writing an entire model in code and not really using Excel at all, my view is it's better to use a more sophisticated programming language. There is also an important cultural point of having to re-skill everyone, and I can see that in some context that means in the short run at least, Excel/VBA may still be better overall. I've written a bit more about all of this here: https://www.robinlinacre.com/transforming_analytical_functions/ https://www.robinlinacre.com/transforming_analytical_functio... [0] https://vita.had.co.nz/papers/tidy-data.pdf https://vita.had.co.nz/papers/tidy-data.pdf
- pjmlp 6y ago
  > I appreciate some of the above is also possible in VBA, but if you're writing an entire model in code and not really using Excel at all, my view is it's better to use a more sophisticated programming language. Which is why many VBA experts eventually adopt VB.NET instead of jumping into a complete foreign language, with the benefit that is actually compiled to native code (JIT/NGEN), if performance is ever an issue.
raverbashing 6y ago
Those are fair criticisms but I think under Windows, Docker+pip is the worse way of managing it 2 and 4 are surprising, it would be interesting to do a benchmark and maybe figure out the best way to do stuff in Python for your case About 3, I suppose that's why developers should break up complex expressions (and not only in Python)
- etothepii 6y ago
  Its absolutely true that an untested 1000 line python function is no better than a 1000 line untested VBA function.
belorn 6y ago
The article specific mention that the environment is the browser using python notebook. In the author use case there is no docker, no pip, no window desktop for which I.T. support is managing python on client machines. I also wonder, if I.T. support were to use docker, are they doing that for python, or would they still continue to use docker even if they move away from python?
etothepii 6y ago
Just because one can trace ones way through an excel spreadsheet should be of negligible comfort in the aftermath of a multi-million dollar loss.
newdude116 6y ago
From the submitted link: "The desire to price increasingly complex deals with increasingly large datasets" Bingo! Most people use Excel when they actually should use a database. I am sure you can use Excel with a database like MS Access, but then again, who does? To your arguments: 1. " and good luck getting I.T. to support Docker on Windows desktops." Yah. Great experience to work with Excel on Linux. 2. You can always link compiled code for stuff that needs to be fast. But in the end most people wont use neither python nor Excel for HFT 3. " it's relatively easy to figure out what a mangled and convoluted formula is doing" https://www.sciencemag.org/news/2016/08/one-five-genetics-papers-contains-errors-thanks-microsoft-excel https://www.sciencemag.org/news/2016/08/one-five-genetics-pa... https://www.washingtonpost.com/news/wonk/wp/2016/08/26/an-alarming-number-of-scientific-papers-contain-excel-errors/ https://www.washingtonpost.com/news/wonk/wp/2016/08/26/an-al... https://www.sciencealert.com/excel-is-responsible-for-20-percent-of-errors-in-genetic-scientific-papers https://www.sciencealert.com/excel-is-responsible-for-20-per... 4. Maybe. Not sure it is really an issue.
- cutler 6y ago
  The case for using a database with Excel is at least as strong as for using Python or C# to model Excel data. There are excellent free adapters for MySQL and PostgreSQL.
1337shadow 6y ago
I thought the point was to make a web interface in Python to query some database and process to calculations on a powerful server, in which case the database and server for sure are going to be faster than excel, including the WITH RECURSIVE SQL statement, and only a browser is necessary for users which makes the solution not only multi platform but also remote-friendly. As such, your post really makes me wonder what they are trying to do at all.
sriku 6y ago
If you have valid reasons to make the move from Excel to Python, why not consider Julia? Environment is easier to manage (Pkg.add), the language "looks like Python and walks like C", math-friendly style possible, just-ahead-of-time compilation resulting in high performance (enough to not need native code implementations), interactive development (Jupyter was named for Julia-Python-R after all), @memoize may be enough for you and Pluto gives you reactive notebooks. Bonus - tools are also emerging to make stand alone distributables. Disclaimer: I neither work for nor am I affiliated with Julialang. I just use it.
yufeng66 6y ago
I am an actuary for 20 years and people have been trying to replace Excel for at least 15 of them. But Excel is not going anywhere. I think it will be even more popular with the recent introduced lamda function. Excel formulas will Turing complete and we don’t have to use VBA anymore.
deknos 6y ago
from your POV, what would be an better replacement than Excel? provided it's opensource?

by yold__ 6y ago

mslate 6y ago

From an actuary's perspective: is Google Sheets ever entertained as an Excel alternative?

anonymouse008 6y ago
Not an actuary - but I think this crosses domains: the lack of comprehensive shortcuts makes Google Sheets DOA (dead on arrival) for my uses.
deleted 6y ago
[deleted]
yold__ 6y ago
It's been a couple years since I've used it, and I didn't feel it was a comparable alternative. It's decent for about 80% of spreadsheet users, but the keyboard shortcuts were lacking and it was missing some functions that I rely on. For keyboard shortcuts, most Excel power users don't use the mouse, so while it sound trivial, it's really hard to feel productive when you have to hunt around for the right button to click. From an enterprise perspective, Excel is so entrenched it would be a 5-10 year effort to port existing spreadsheets to sheets. Practically speaking, most companies wouldn't see the benefit.
- will_pseudonym 6y ago
  > From an enterprise perspective, Excel is so entrenched it would be a 5-10 year effort to port existing spreadsheets to sheets. Practically speaking, most companies wouldn't see the benefit. And at the end of the day, it would have worse performance than Excel both in calculation speed and _much_ worse UI. One of the reasons Excel is so much better than Sheets is speed. Insurance companies spend hundreds of thousands of dollars a year on actuaries. Even if Excel cost them $500/year/user, it would be easily worth it for actuarial departments.
  - etothepii 6y ago
    The hundreds of thousands of dollars a year that insurance companies spend on actuaries is the real reason that no one will ever come off excel. The maths is not very complicated but the lack of source control and testing means each bugfix or added feature introduces another bug to fix next week or feature to add the week after.
zinekeller 6y ago
Used GSuite (Google Workspace?): It's fine, it is improving but lack of shortcuts even for basic tasks (I can change the font on Word with just a keyboard, try that with Docs without a mouse). Dealbreaker is the 5 million cell (not row nor column) limit, which is even lower than Microsoft's old limits (more than 15 million cells, which was increased in 2007 to you-have-a-serious-problem-if-you-somehow-fill-this-limit cells).
- andylynch 6y ago
  Not to mention that if you're using Power Query, Excel's 1 million row limit doesn't apply in the workbook queries either.
- HALtheWise 6y ago
  Alt+/ is the magical shortcut that makes this easy, you can simply search a font name and hit enter.
will_pseudonym 6y ago
No, for many reasons. Workbook calculation performance, advanced, complicated spreadsheet incompatibility, worse UI/keyboard shortcuts, slow UI, different language than VBA for custom functions (It isn't necessarily worse, but it's the same thing as trying to convince a department to switch from language X to Y including rewriting every application that's written in X. Also, every person you've ever hired was familiar with X, and has no experience with Y.) R/Python/SAS etc. are a much more compelling alternative to Excel than Google Sheets (to say nothing of the actuarial modeling software packages that are used already for more rigorous/complicated problems). If an insurance company decided to move all of their MS Office users to Google Docs/Sheets etc, my money is on the actuarial department paying for Excel out of their budget without a moment's hesitation.
rvba 6y ago
Uploading propertiary data to a cloud is a big no.
- etothepii 6y ago
  Companies are more worried about this than they probably should be. The average enterprise network is nothing like as secure as people behave like it is. Where do you think your email is hosted? With few exceptions I'd expect its provided by a cloud provider these days.

SoSoRoCoCo 6y ago

It is great that you respond with succinct reasons. People who have used Python for some years seem to forget just how clunky it really is. I've been using Excel since 1990, and sure, it has its own warts, but Python is a very rudimentary tool compared to Excel. Python is a machine shop. Excel is a car. It may be a lemon, but it's a functional car. This is a great example of programmers not being able to see the forest for the trees. Reminds me of the "Once Linux gets a desktop it will take over the world" debate from circa 1997-today.

fock 6y ago
I wonder what the problem is with standarizing companywide around python@3.x, numpy@1.1x and pandas@1.x. At this point these can all be considered mature and why on earth would an org, which is not developing these packages, nor heavily consuming outside code (because they didn't with excel either in a sensible way?) decide to jump on the "but we need rrrrrollling release"-fad bandwagon?
- yold__ 6y ago
  The problem isn't feasibility, it's resources. Building and rolling out a standardized environment, and maintaining it, will cost millions of dollars. It shouldn't, but it does. And for what added benefit? The end-users don't want it, you'd have to spend another couple million for a lateral move at best. More than likely, you'll end up with a pile of Python spaghetti code that runs slower than the spreadsheet (see point #4 about massively recursive calcs).
  - shnock 6y ago
    Why does building and rolling out a standardized environment cost so much? Could you break down the requisite steps and resources required to achieve this? Thank you, I appreciate it :)
    - yold__ 6y ago
      Up-front costs (mostly salaries, but all I.T. projects are "billable") 1.) Getting buy-in from solutions architect, software architecture, information security, I.T. management. This will be a 6 month process. 2.) Getting buy-in from actuarial management and audit. Another 6 month process. Recurring annual costs (over 10 years) 3.) Contractor at $150 an hour = $300K annually 4.) Contractor PM at $50 an hour = $100K annually 5.) Information security compliance hoops, getting it to play nicely with the myriad of endpoint security tools, etc 6.) Ongoing maintenance and support (failed rollouts and upgrades, user desktop support, user training)

maxerickson 6y ago

Would the people doing the implementation need to be able to choose and manage dependencies? Or could they do the work inside a prepared environment (comparable to Excel in some sense).

fock 6y ago
that costs millions!
- yold__ 6y ago
  I think you are mocking me, but I'll bite. Insurance companies are contractor heavy. They bill at $150 an hour. That's $300K annually per head. Won't take long to get a million, when you add PM overhead, information security oversight and governance, etc. Again, it shouldn't cost that much, but it does.

klelatti 6y ago

Agree completely on 1. but not sure on 2. and 4. - I think if you're using Python and need performance then you will be using Numpy etc - would be interested to hear if there are instances where this doesn't work.

yold__ 6y ago
numpy is great for vectorizable calculations, but many calcs (particularly for long-term life contingent risks, i.e. reserves), are not vectorizable except in the most simplistic cases.
- klelatti 6y ago
  Thanks - sorry I'm struggling a bit - wouldn't they be vectorizable across the portfolio or across scenario for stochastic calculations. Maybe it's because of different backgrounds (mine in UK) but I'm can't recall seeing the deeply nested function calls that you're alluding to.
  - yold__ 6y ago
    Think about the calculation of an insurance product with a Fund Value. Everything is forward recursive with respect to time. Been a while, so I might butcher some of this. It is likely that you'll want a 30 year projection, so you'll call fundValue(30 * 12) fundValue(t+1) = if t > 0 fundValue(t) - charges(t) + intCred(t) else initialPrem charges(t) = netAmtAtRisk(t) * costOfInsurance(t) + riderCosts(t) + policyFee(t) netAmtAtRisk = (FaceAmt - fundValue(t)) Now think layering on decrements surrenderMargin(t) = lapseDecrement(t) * (surrenderCharge(t) * fundValue(t)) mortalityMargin(t) = mortalityDecrement(t) * netAmtAtRisk(t) investmentMargin(t) = (earnedRate(t) - intCred(t)) * assetBase(t) Now think layering on calcs necessary to calculate the assetBase (e.g. reserves + required capital)...
    - klelatti 6y ago
      That code looks very familiar! I see what you mean now. I don't think I've ever seen this implemented recursively though - can certainly see how this would end up being problematic if you tried to do this in Python! ps Thanks so much for taking the time to set this out. pps I've been working on something that implements a highly optimised version of this style of calculation - with a DSL to describe the calcs - can do 30 year cashflow projection for 1m contracts in about 1 min on quad core laptop. UK focus initially but might have wider application?

davidu 6y ago

This is the argument made against all software stack advancements. Nothing to do with industry. But when the benefits outweigh the the hurdles, change happens. And if I was starting a new insurance company (which I've considered) I'd be doing our work in code not xls, and probably python. Having RCS, Numpy, unlimited compute, unlimited storage, all gives me an advantage over my competition. :-) As to the memoization, that is not hard to manage in Python.

yold__ 6y ago
"As to the memoization, that is not hard to manage in Python." Yes it is. Recursive calls for financial calculations easily go hundreds of thousands of calls deep. This is why high-end actuarial modeling software either decomposes it into a dependency graph and unrolls function calls where possible, or just "brute-forces" it by being a thin wrapper over c++, i.e. using operator overloading on ::operator(). I've seen ill-fated efforts of capable software developers attempting to unroll the recursive function calls, and ending up with 2000 line functions that are impossible to maintain.
- solresol 6y ago
  Why doesn't annotating these functions with @functools.lru_cache(10000000) work?
  - pmart123 6y ago
    My guess is the numeric inputs would be changing significantly each call?
  - yold__ 6y ago
    First of all, let me say that I've tried it :) Your recursion needs to "bottom-out" in order for that to work. If you don't get a stack overflow / out of memory error, you're good. But bear in mind that there will be thousands of stack frames. Before you get to time=0 (the recursive base case) in a long-term liability actuarial calc. The recursion isn't simple like the Fibonacci sequence . It's more like: f(t+1) = if t > 0 (f(t) + g(t)) * h(t) else initial_constant g(t) = f(t) + q(t) - d(t) q(t) = .... d(t) = ....

mch82 6y ago

> problem #1: Environment management Great observation. Python environment management is getting simpler, but is off putting for people without a software background. Unclear even CS majors get enough classroom exposure to package & dependency management to utilize Python efficiently. I’m more optimistic about an on-prem deployment of Jupyter Notebooks or Sage Math Cloud as a way to hide a lot of the setup complexity. More like a wiki for math. Curious if anyone has stories/tips to share (good or bad)?

techphys_91 6y ago
Is this really an issue for the use case being described? Environment management is obviously a significant consideration for software developers who need to keep track of versions etc, but it sounds as if these users primarily want to use the fundamental numpy functions. They could install one of the scientific python stacks (e.g. anaconda) or just install packages globally with pip.
- mch82 6y ago
  Anaconda switched its software license recently to require large companies to purchase a license, so now people must wade through the purchasing department before installation & use.
- macintux 6y ago
  Things always break over time. I have a very technical co-worker, a systems admin, with a broken Python stack on Windows. No idea what's wrong.
  - fock 6y ago
    so he did a chmod -w on their folder and it broke. Well, that's a feat I still have to work out!
everling 6y ago
Quant here, at my firm we've deployed a JupyterHub server which provides users with a production docker image, so that analysts and portfolio managers can perform analyses without installing python, dependencies and sql drivers locally. It is working well and spurs interest in Python across the wider org - so we let everyone use it. Similar to in OP's case, I think the real selling point of Python over Excel is advancing the capabilities and the scale of the business. Talks of different programming languages falls on flat ears in finance - show what can be done instead. With Python, Zipline and notebooks I can manage a global equity portfolio, continuously adding active strategies and adapting to real-world changes and constraints. And backtest! Excel is great, but there is an upper bound to what can be reasonably done without a thriving open source community.

stochastastic 6y ago

Hey fellow reinsurance actuary! I totally agree that Excel has its place in modeling, especially one-offs, and your criticisms make sense. That said, we have been moving a lot of our calculations to Python. We have had way too many rickety tools to move files or send emails (“first you open this spreadsheet and click this button, then you open this spreadsheet and click this button, then...”), and way too many version control issues over the years. Python solves those nicely. I’m curious about docker + pip, why do you like that better than poetry or pipenv?

yold__ 6y ago
One reason why Python is so successful is that it places very nicely with C code. Many of Python's libraries are thin wrappers around native DLLs. For example, numpy is a wrapper around a BLAS DLL (e.g. Intel MKL). Pipenv manages the python side of things, but don't exert control over the system DLLs (like Docker does). Anaconda gets very close to what Docker does (by managing DLLs). Have not used poetry, so can't comment. Ultimately, like most dependency management issues, lacking a stable DLL environment won't be a problem until it is :)
- stochastastic 6y ago
  Okay, I can totally see it now. We are still at the stage where people seem to think that I’m neurotic for worrying about the python side of things, so DLLs have not been on the radar. ;)
- lldbg 6y ago
  numpy is much more than a wrapper around a BLAS dll. BLAS implements three sets of operations: Level 1: unary and binary vector vector operations, one transform. Level 2: Matrix vector operations. Level 3: Matrix matrix operations (most famously the dgemm routine). Perhaps some blas implementations offer more features, but that would defeat the purpose of a standard interface.
- z3t4 6y ago
  In Nodejs native libraries are a PITA. The compatibility API breaks on a schedule every 6 month, dependencies get updated by OS distros sometimes breaking stuff, then you rely on the OS being able to compile the library. I hope Python has better native interop.

mint2 6y ago

Why do actuaries refer to workstation/desktop computers with more than 16 cores as “super computers” it’s embarrassing but sometimes I give in an say “the super computer” because I’m in a hurry and they’ll give me a blank stare if I call it a workstation or anything like that.

mark-r 6y ago
They really are supercomputers though. Do you know how much faster a modern PC is compared to say a Cray-1? Especially if it has a decent graphics card.
- ryukafalz 6y ago
  Yeah but at this point my phone is comparable. If being faster than a Cray-1 qualifies something as a supercomputer, the definition is meaningless now.
- mint2 6y ago
  So a typical actuary’s technological reference point is stuck in 1985? That explains a lot about excel and sas egp. But seriously, calling a fairly standard computer in the tech world a “supercomputer” is just another example of the underlying attitude in insurance that makes many actuaries recoil in horror about the thought of “programming” aka learning python or any programming best practices.

sam_bristow 6y ago

Funny story about Excel on corporate machines. A couple of years ago the company I work for got boight by an Italian company. When we finally migrated the Windows users over to the corporate Office installs a bunch of people found that Excel wouldn't work for them. Things like sum(A1:A20) were syntax errors. After a bunch of digging i worked out that the localisation from corporate meant they suddenly had Italian function names not English. Very confusing. Excel is a program that is both incredible and terrifying to me. There are ways of building spreadsheets that are reliable and auditable. Then there's how 95% of people do it. You can start out really quickly and make great progress. But it tends to grow and metastasize before you know it.

HALtheWise 6y ago
This brings up a good point, which is that Excel supports localization, while Python just assumed you know English.
etothepii 6y ago
It must be easier to build an auditable and reliable solution using a high-level language programming language and concepts like source control and automated testing. Excel is only easier if you aren't interested in building something auditable and reliable solution that might have some hope of being maintained after you have left the company.
- sam_bristow 6y ago
  That's the thing, most Excel workbooks start out as a one-off then gradually get adapted and extended until they're load-bearing. They're often built by specialists in another dept who definitely wouldn't consider themselves programmers. Doing it 'properly' would probably mean having to spec put the problem, get a budget, maybe wait a few months for someone to look at it. And the same thing every time the requirements change. Excel is available today and they can get started solving their immediate problem straight away. After it's been in use for a couple of years and shown value someone takes a look and sees the Lovecraftian horror it's become.

etothepii 6y ago

The calculations typically performed by actuaries are individually all fairly simple but in aggregate without automated testing and version control it is reckless to use them in pricing or portfolio calculations.

eyeball 6y ago

the new functions in recent versions of excel make an even strong case for its use https://techcommunity.microsoft.com/t5/excel-blog/announcing-lambda-turn-excel-formulas-into-custom-functions/ba-p/1925546 https://techcommunity.microsoft.com/t5/excel-blog/announcing... https://www.excelcampus.com/functions/dynamic-array-formulas-spill-ranges/ https://www.excelcampus.com/functions/dynamic-array-formulas...

etothepii 6y ago
I have wondered about this, the lambdas and table could be of huge benefit against some of the most egregious excel mistakes but that isn't an argument for excel's use. The problem is not one of it not being possible to do automated testing or source control in Excel. VBA is Turing complete so anything is possible, it's more one of not thinking, or understanding why, those things are important. Once you do come to think of such things as important you will quickly never use Excel for anything but the most basic calculations.

jandrewrogers 6y ago

I use both Excel and Python, and like both. They solve different kinds of problems, even within the same context. Excel is fantastic for what I would describe as linear modeling, building a graph of effects in single data models. I reach for Python when I need to fundamentally transform the data model at points to answer the desired question. That is difficult to the point of being impractical in Excel, especially if the data model is large or exploratory. Python is more programmable in this regard but also lacks the strong static typing that would be useful in such work. I can’t imagine not using either.

kfk 6y ago

Hi, this is a late reply but I am actually pulling this off. Your points above all make sense but are around the desktop paradigm. If you move to the cloud paradigm not only most of the points go away but you gain a lot by having data all in one place (S3) and strict collaboration (github). Specifically the Python env problem goes away if you ask analysts to work online with notebooks (ie jupyter hub).

harha 6y ago

For 1. I would say R is a good option. It works relatively well everywhere and has an ok IDE, lots of packages that make life easier (tidyverse). I also wouldn’t recommend python for exactly that reason.

RobinL 6y ago

I think these are all fair points and a good reflection of the downside of Python. But there are also some pretty huge upsides. My view is that for any model of significant complexity, the pros of Python outweigh the cons from a technical point of view. - Abstraction. It's very difficult to effectively abstract parts of a model in Excel. It's a bit like a doctor having to 'model' a human being as a collection of atoms, rather than having abstractions like organs, cells etc. This makes it very hard to build re-usable components, so analysts end up reinventing the wheel. You also quickly hit a 'complexity ceiling' in Excel, above which mistakes and errors becoming much more likely, and complexity is very difficult to manage. - Existing libraries provide a huge range of sophisticated calculations and operations for which we don't need to write any code. - Separation of concerns - particularly separating data from model. Easy in Python, hard in Excel. Another aspect of this is that using data science software promotes the use of tidy data[0] (i.e. clear thinking about how data should be structured). - Unit/integration tests. For complex models, these are essential. Users of Excel (even extremely clever/competent people) don't have have a great reputation for producing error-free spreadsheets, and I think this is an important reason why, alongside copy-paste errors. The tools for testing in Excel/VBA are rudimentary. - Version control. This is particularly important for historical reproducibility because it allows us to run past models, and also understand what has changed in the codebase since. I appreciate some of the above is also possible in VBA, but if you're writing an entire model in code and not really using Excel at all, my view is it's better to use a more sophisticated programming language. There is also an important cultural point of having to re-skill everyone, and I can see that in some context that means in the short run at least, Excel/VBA may still be better overall. I've written a bit more about all of this here: https://www.robinlinacre.com/transforming_analytical_functions/ https://www.robinlinacre.com/transforming_analytical_functio... [0] https://vita.had.co.nz/papers/tidy-data.pdf https://vita.had.co.nz/papers/tidy-data.pdf

pjmlp 6y ago
> I appreciate some of the above is also possible in VBA, but if you're writing an entire model in code and not really using Excel at all, my view is it's better to use a more sophisticated programming language. Which is why many VBA experts eventually adopt VB.NET instead of jumping into a complete foreign language, with the benefit that is actually compiled to native code (JIT/NGEN), if performance is ever an issue.

raverbashing 6y ago

Those are fair criticisms but I think under Windows, Docker+pip is the worse way of managing it 2 and 4 are surprising, it would be interesting to do a benchmark and maybe figure out the best way to do stuff in Python for your case About 3, I suppose that's why developers should break up complex expressions (and not only in Python)

etothepii 6y ago
Its absolutely true that an untested 1000 line python function is no better than a 1000 line untested VBA function.

belorn 6y ago

The article specific mention that the environment is the browser using python notebook. In the author use case there is no docker, no pip, no window desktop for which I.T. support is managing python on client machines. I also wonder, if I.T. support were to use docker, are they doing that for python, or would they still continue to use docker even if they move away from python?

etothepii 6y ago

Just because one can trace ones way through an excel spreadsheet should be of negligible comfort in the aftermath of a multi-million dollar loss.

newdude116 6y ago

From the submitted link: "The desire to price increasingly complex deals with increasingly large datasets" Bingo! Most people use Excel when they actually should use a database. I am sure you can use Excel with a database like MS Access, but then again, who does? To your arguments: 1. " and good luck getting I.T. to support Docker on Windows desktops." Yah. Great experience to work with Excel on Linux. 2. You can always link compiled code for stuff that needs to be fast. But in the end most people wont use neither python nor Excel for HFT 3. " it's relatively easy to figure out what a mangled and convoluted formula is doing" https://www.sciencemag.org/news/2016/08/one-five-genetics-papers-contains-errors-thanks-microsoft-excel https://www.sciencemag.org/news/2016/08/one-five-genetics-pa... https://www.washingtonpost.com/news/wonk/wp/2016/08/26/an-alarming-number-of-scientific-papers-contain-excel-errors/ https://www.washingtonpost.com/news/wonk/wp/2016/08/26/an-al... https://www.sciencealert.com/excel-is-responsible-for-20-percent-of-errors-in-genetic-scientific-papers https://www.sciencealert.com/excel-is-responsible-for-20-per... 4. Maybe. Not sure it is really an issue.

cutler 6y ago
The case for using a database with Excel is at least as strong as for using Python or C# to model Excel data. There are excellent free adapters for MySQL and PostgreSQL.

1337shadow 6y ago

I thought the point was to make a web interface in Python to query some database and process to calculations on a powerful server, in which case the database and server for sure are going to be faster than excel, including the WITH RECURSIVE SQL statement, and only a browser is necessary for users which makes the solution not only multi platform but also remote-friendly. As such, your post really makes me wonder what they are trying to do at all.

sriku 6y ago

If you have valid reasons to make the move from Excel to Python, why not consider Julia? Environment is easier to manage (Pkg.add), the language "looks like Python and walks like C", math-friendly style possible, just-ahead-of-time compilation resulting in high performance (enough to not need native code implementations), interactive development (Jupyter was named for Julia-Python-R after all), @memoize may be enough for you and Pluto gives you reactive notebooks. Bonus - tools are also emerging to make stand alone distributables. Disclaimer: I neither work for nor am I affiliated with Julialang. I just use it.

yufeng66 6y ago

I am an actuary for 20 years and people have been trying to replace Excel for at least 15 of them. But Excel is not going anywhere. I think it will be even more popular with the recent introduced lamda function. Excel formulas will Turing complete and we don’t have to use VBA anymore.

deknos 6y ago

from your POV, what would be an better replacement than Excel? provided it's opensource?