Ditching Excel for Python in a legacy industry • Hacker News Board

Re: Ditching Excel for Python in a legacy industry

#101

Post by [deleted] » Thu, Dec 31, 2020, 2:24 AM UTC

klelatti wrote: post #97

yold__ wrote: post #91

Earlier quoted context omitted.

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)…

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 d…

[deleted]

Re: Ditching Excel for Python in a legacy industry

#102

Post by nl » Thu, Dec 31, 2020, 2:25 AM UTC

SoSoRoCoCo wrote: post #85

trimbo wrote: post #63

Earlier quoted context omitted.

> 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…

Absolutely. I worked at Intel and our distributed computing pools went from a combination of SunOS and AIX machines to Linux in about 3 months, essentially overnight, (back in the 90's). It was an astonishingly fast deployment. 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 nig…

I think the 2.x vs 3.x issues have mostly been resolved by now. I don't think I've hit one for a long time, and even StackOverflow answers are more likely to be Python 3 now.

Re: Ditching Excel for Python in a legacy industry

#103

Post by DavidPeiffer » Thu, Dec 31, 2020, 2:30 AM UTC

ogre_codes wrote: post #35

quietbritishjim wrote: post #25

Earlier quoted context omitted.

In Excel there's a toolbar button to toggle showing formulae rather than their results. I realise this doesn't counter your overall objection, but it does mean chasing down logic isn't quite as bad as having to select individual cells one at a time.

I mean... sure? There are a fair number of ways you can mitigate these issues, but the way spreadsheets are structured does not lend itself to structured/ well managed code.

I fully agree, things can get out of hand in spreadsheets. I've built my fair share of such spreadsheets, and as penance, I'm learning R.

The thing with Excel is there's a low barrier to entry, but there are a lot of differences between a great spreadsheet and a bad one. Somewhat like a junior vs senior developer, the quality of code/spreadsheet depends on what they know, how well they can troubleshoot, and how good of a system they can imagine (to then replicate as much as possible).

For example, most people are entirely unaware that Tables exist in Excel. When you want the sum of a column, rather than writing =SUM(F7:F39) and cursing when you realize you added 10 more rows and that's why the sum is not updating, you can do =SUM(tbl_Sales[SalePrice]), and when you add 10 rows, the table will automatically expand. Suddenly your formulas are somewhat self-documenting, regardless of which sheet holds tbl_Sales. Crtl+T when you've selected your data, or Insert -> Tables -> Table.

You can also make named ranges, which I would say is an analog between using {a, b, c, tempVar} versus well named variables in normal programming.

You can also trace dependents/precedents, showing arrows for how the data flows throughout the spreadsheet. Formula -> Trace Precedents/Dependents.

Re: Ditching Excel for Python in a legacy industry

#104

Post by listenallyall » Thu, Dec 31, 2020, 2:33 AM UTC

ogre_codes wrote: post #20

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…

> What does "A1 + SomeOtherTab:B2" mean?

Excel offers the ability to name any cell or range of cells. Don't even have to search through menus or the ribbon, it's right there to the left of the formula bar.

I'm well aware that the vast majority of Excel spreadsheets don't use named cells/ranges, but you can't really blame Excel for that. It couldn't be too much easier. Lots of Python programmers don't use comments or descriptive variable names either.

Re: Ditching Excel for Python in a legacy industry

#105

Post by mint2 » Thu, Dec 31, 2020, 2:37 AM UTC

yold__ wrote: post #26

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. Li…

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.

Re: Ditching Excel for Python in a legacy industry

#106

Post by stochastastic » Thu, Dec 31, 2020, 2:46 AM UTC

meztez wrote: post #88

stochastastic wrote: post #80

Earlier quoted context omitted.

I’ll second klelatti’s question about R vs Python. From my perspective Python is just as practical for actuarial calcs and better for building general purpose tools. Is there a reason Anaconda didn’t click?

GPU integration was broken for a long time. Managing VMs / Environments. The absolutely horrible integration with git/Github. Having to rebuild your environment from scratch when your workspace crashed. Imagine starting a notebook with a 45 minutes compile time. No go. One click deploy, let's just forget about it.

Thanks! That totally makes sense. If I had to pick a pain point for getting people started with Python tools it would be environments. Comments here make me think my team is working with a lot less data too.

Re: Ditching Excel for Python in a legacy industry

#107

Post by jimmyjimjimmy » Thu, Dec 31, 2020, 2:48 AM UTC

ogre_codes wrote: post #20

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…

Yep, lack of reproducibility with complex spreadsheets is a nightmare.

Re: Ditching Excel for Python in a legacy industry

#108

Post by hnracer » Thu, Dec 31, 2020, 2:48 AM UTC

klelatti wrote: post #57

meztez wrote: post #50

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 he…

I'm an actuary with a strong interest in this area - would be very interested to hear more especially on your R vs Python experience.

I've used R (3 years) and Python (8+ years) in data science and much prefer Python, because it can do things that aren't just pure data analysis, and because pandas is so amazingly good compared to R's data matrix solutions, in my opinion. I believe that the algorithmic trading industry has gone fully into Python and away from R for these reasons.

Re: Ditching Excel for Python in a legacy industry

#109

Post by stochastastic » Thu, Dec 31, 2020, 2:51 AM UTC

yold__ wrote: post #87

stochastastic wrote: post #70

Earlier quoted context omitted.

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 versio…

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 co…

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. ;)

Re: Ditching Excel for Python in a legacy industry

#110

Post by listenallyall » Thu, Dec 31, 2020, 2:56 AM UTC

bash-j wrote: post #19

RcouF1uZ4gsC wrote: post #2

> 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.

This has frustrated me as a python user for the last 7 years, working as the only python user in business environments dominated by Excel. People will say things like, "if you leave, who can support this report you made in python?" Well I say, who can support the bloated 40mb spreadsheet that would take forever to unpick and figure out how to update with new data? No one can, because I've seen people would rather reb…

> You can't even count on the data being in the same place over the 52 weeks, since they would have added and removed data points over time.

> I can just save it as a .py file and run it as a scheduled task on a virtual computer forever.

This is rather naive and short-sighted. Do you think the spreadsheet guy is moving data points around because s/he's bored at work and is screwing around with no purpose? No, the business requirements change, so he needs to update the spreadsheet to incorporate the new rules and/or data.

Which is exactly what you'll need to do with your python program, otherwise it also will break and/or produce incorrect results.

Simple example: calculate available vacation days. Last year company policy was simple, use it or lose it. Just subtract days allotted minus days used in the calendar year. This year company policy allows for up to 5 to be rolled over. Now we also need to know how many were available last year, how many were used, how many could be rolled over. Your Python program importing from SQL query, CSV, data warehouse report... totally breaks now that the data source has 5 columns instead of 2.

Claiming you can build a program in Python or any other language and run it "forever," in the context of a business, makes the whole comment lose any credibility.