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.
Linux did take over the world, just not on the desktop. It was on servers and mobile, which now have more users than desktops or laptops (edit: servers via the web).
Technology gets its warts fixed when it grows along an explosive new market, especially if the market ends up being larger than the last.
Python is currently riding the data science wave, and that wave is growing. If that market expands to the point where large scale data-science type work wags the dog of VBA/excel, the clunkiness[1] will work itself out.
[1] - I don't actually understand what's clunky about Python in the context of the article. Seems like a reasonable direction in a complex market (reinsurance) driven by actuaries. I'd be surprised if newgrad actuaries/stats people aren't using Python?
Linux dominates the server world AND the entertainment device world (hello busybox & gstreamer!)
[1] Regarding clunkiness of Python: mostly it is the packages, installation, and 2.x vs 3.x nightmare that persists. Everyone seems to forget the initial pain getting the Python env to work, esp. when it comes to cython native compilation issues / arch wheels, unsupported packages, etc. The only issue I have with python is it is extremely challenging to make cross-platform deployments for single-executables. I've tried three different approaches and they were all trainwrecks. Once that is ironed out, I'll be switching from Electron to whatever Python offers.
but that discounts the tens of thousands of projects that are already out there that are in use and need conversion.
it'll take probably 3-5 years for it to really go away.
But I class this as a packaging issue more than a 2.7 vs 3.x issue: you see the same problems with (as a random example...) different versions of OpenCV - people not using virtual environments have problems even if they are all on 3.7.
When I think of the "2.x vs 3.x problems" I was thinking more of the language and core libray level incompatibilities.
The Catalina release notes[1] said they would remove it and I was sure they mentioned it again in this year’s WWDC but apparently it’s still included after all.
[1] “Scripting language runtimes such as Python, Ruby, and Perl are included in macOS for compatibility with legacy software. Future versions of macOS won’t include scripting language runtimes by default, and might require you to install additional packages. If your software depends on scripting languages, it’s recommended that you bundle the runtime within the app.”
I'm honestly not sad that it never happened.
It took over my desktop around ~1998 and I wonder if massive adoption of Linux on the desktop would have benefited me or been worse (from my perspective).
As it stands literally every single tool I want/need to do my job is already available for Linux and indeed many of those tools are simply better on Linux (docker on a mac is horrible, I have a work issued current gen macbook pro, I use it purely for testing docker set-ups and then it goes back in its case).
Its going to sound elitist but not dumbing down the platform for the average user is a benefit to me.
For things where data exploration and formulas should be in the foreground or where data and formulas should be strictly separated python (or a python/jupyter notebook) has tangible benefits (e.g. really good list syntax).
Pretty much all servers run on linux.
Linux also dominates smartphones in the form of Android phones.
Chrome OS is also linux, and it's market share is currently at around 6%.
I mean, other than desktops Linux pretty much is everywhere.
Not really.
See any Linux specific APIs on the NDK official APIs?
https://developer.android.com/ndk/guides/stable_apis
Android is a mix of Java and Kotlin based frameworks, ISO C and C++, POSIX subset and a couple of additional libraries.
Whatever kernel gets used is an implementation detail for Google and Android device makers.
Can be completely replaced in Android 12, and the eco-system would continue to work.
> Chrome OS is also linux, and it's market share is currently at around 6%.
Basically Android (already mentioned above) and Web stacks.
https://chromeos.dev/en/android-environment
https://chromeos.dev/en/web-environment
Ah, but it does expose Linux you say, https://chromeos.dev/en/linux
Indeed, except of the small detail that as shown on the Google IO talk, it is actually a design similar to WSL 2, running a second kernel on a hypervisor based environment.
The real kernel powering ChromeOS doesn't get exposed to userspace and can also be replaced at any time, if Google so desires.
In fact, in a near future Android and ChromeOS can be running on top of Fuchsia and most consumers wouldn't even notice.
Thank you, I appreciate it :)
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)
You just don't jump from "humans in Excel" to "CI/CD perfected pipeline" overnight, nor do you need it.
Excel shops still have costly expenses rewriting entire workflows/re-doing Excel files constantly as people come and go, it's not like there isn't already maintenance cost with the current method.
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.
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.
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.
This cannot be stressed enough. I've outlived generations of finance teams at many startups, and I've seen firsthand the masterpieces/abominations left behind in Excel. Imagine a dozen sheets with ad-hoc queried data copy/pasted from System A/B/C/D into Excel, with formulas that feed formulas that feed formulas. Sometimes columns are inputs (seasonality adjustments for monthly forecasts), sometimes their outputs (modeled growth * last year * seasonality adjustment) and more often than not their right next to each other and maybe they have different cell background colors or a black separator line. Maybe.
And this is just finance. For many e-commerce businesses, planning is done in Excel with equal zeal.
It would take about 12 hours to calculate, and would error out before finishing about 30% of the time. It needed to be run once a day for something reasonably important.
I don't use Excel much these days, but I do point people to a video if they do plan on doing anything:
* [You suck at excel - Joel Spolsky](https://m.youtube.com/watch?v=0nbkaYsR94c)
I worked for a company that used an opaque excel spread sheet as a part of its accounting system - turns out there where bugs and we found a massive short fall one of the contributing factors in the collapse of the company.
Do you have any pointers to learning materials on how to do this? Would be interested in reading more on it.
There also Joel Spolsky video I linked in another comment: https://youtu.be/0nbkaYsR94c
I don't actually use Excel much so others might have better resources.
I’m curious about docker + pip, why do you like that better than poetry or pipenv?
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 :)
Perhaps some blas implementations offer more features, but that would defeat the purpose of a standard interface.
With Excel there is no such separation, and when there is it would make a great punchline to an XKCD or Dilbert cartoon.
"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-pa...
https://www.washingtonpost.com/news/wonk/wp/2016/08/26/an-al...
https://www.sciencealert.com/excel-is-responsible-for-20-per...
4. Maybe. Not sure it is really an issue.
- 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_functio...
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.
I agree that to interact with Excel programmatically VBA is a better choice (and no doubt C#/VB.NET as well, but I have no direct experience). For what it's worth, for interacting with Excel and Office more generally, I've always though VBA is extremely well designed.
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.
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.
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)?
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.
If a recurring task can be reasonably parameterized then a Streamlit app might be a better choice in some instances. I've developed a monitoring application for our portfolios where I can track daily asset weights, underlying data points, computations etc. Not displaying code ensures that the output can be consumed by a wider audience.
We've tested JH with K8 in GCP which was straightforward also. With a small team though tending towards a single VM deploy (based on "The Littleist JupyterHub") which looks a lot easier to maintain.
They could install one of the scientific python stacks (e.g. anaconda) or just install packages globally with pip.
I mean, in an ideal world you'd use Julia or a Cython extension, but if you already have something in Python/numpy, numba only requires you add a decorator to your function and it gets jitted.
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)...
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?
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)
https://techcommunity.microsoft.com/t5/excel-blog/announcing...
https://www.excelcampus.com/functions/dynamic-array-formulas...
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.
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?
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.
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.
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.
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.
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.
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.
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) = ....
You wind up needing to know the order of calculations since things are no longer lazily evaluated via recursion. This is a problem when you have dozens of "columns" (i.e. recursive functions or arrays as you are suggesting). Often times, the value in the array is NULL (or worse, leftover from a previous calculation). You are left to manually try and re-order the calculations, which is not trivial when there are hundreds of functions.
Excel takes care of these details for you automatically. Users program functionally and recursively (fill-down) without even thinking about it. Excel reactively updates when dependent values change (re-evaluates as necessary).
If power, speed, and scale are necessary, there are purpose-built systems (with Domain Specific Languages) which specifically solve this problem in the insurance domain (e.g. FIS Prophet, Risk Agility, AXIS, etc).
I've been working on a product that turns JupyterLab into an IDE for life insurance calculations - Python API wrapped around an optimised C / GPU computation layer underneath, all integrated with key open source libraries.
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.