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

Re: Ditching Excel for Python in a legacy industry

#181

Post by belorn » Thu, Dec 31, 2020, 10:01 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…

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?

Re: Ditching Excel for Python in a legacy industry

#182

Post by etothepii » Thu, Dec 31, 2020, 10:06 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…

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.

Re: Ditching Excel for Python in a legacy industry

#183

Post by etothepii » Thu, Dec 31, 2020, 10:08 AM UTC

Mauricebranagh wrote: post #179

sam_bristow wrote: post #137

Earlier quoted context omitted.

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

And you find a bug that costs you $$$$, 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.

This is the real problem. It's not with excel per se, but the complete lack of automated testing and source control.

Re: Ditching Excel for Python in a legacy industry

#184

Post by bbact » Thu, Dec 31, 2020, 10:11 AM UTC

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.

Re: Ditching Excel for Python in a legacy industry

#185

Post by etothepii » Thu, Dec 31, 2020, 10:14 AM UTC

eyeball wrote: post #128

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…

the new functions in recent versions of excel make an even strong case for its use https://techcommunity.microsoft.com/t5/excel-blog/announcing... https://www.excelcampus.com/functions/dynamic-array-formulas...

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.

Re: Ditching Excel for Python in a legacy industry

#186

Post by etothepii » Thu, Dec 31, 2020, 10:19 AM UTC

shp0ngle wrote: post #154

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.

The main point is to try and separate view, data and calculation engine.

PyXLL is great for helping with this as you can move the calculations into Python and thus have them automatically tested and protected by source control.

I assume this is possible for VBA but have never seen it done in practice.

Re: Ditching Excel for Python in a legacy industry

#187

Post by etothepii » Thu, Dec 31, 2020, 10:25 AM UTC

sheetjs wrote: post #9

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

In principle I agree strongly with this approach, especially when an extant "working" solution already exists. However, some of the cargo-cultery that goes on almost defies belief.

I once heard of a company that created a database table with columns "workbook","sheet name","row","col","value" that they would extract all of their spreadsheets into as a "Database backend" for their spreadsheets.

Re: Ditching Excel for Python in a legacy industry

#188

Post by tornadofart » Thu, Dec 31, 2020, 10:25 AM UTC

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.

Re: Ditching Excel for Python in a legacy industry

#189

Post by disgruntledphd2 » Thu, Dec 31, 2020, 10:26 AM UTC

wodenokoto wrote: post #175

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…

The current top comment (sibling to the one I’m replying to) argues that keeping Python environments across actuaries/users computers up to date is too difficult. This is nicely solved by using R server. I’ve worked in an R server shop, and the experience is really nice. You log on to the server in chrome or Firefox and the browser window basically becomes RStudio and all calculations are done on the server and all c…

The big problem with notebooks is that you don't have a real REPL. This prevents one from single step debugging and tracing. This is one area where RStudio is much, much better.

The trouble is that so many of the younger DS people are focused on Python, that it makes financial sense to just deal with all its problems. There's also a lot more programming tools (though less statistical modelling tools).

Re: Ditching Excel for Python in a legacy industry

#190

Post by disgruntledphd2 » Thu, Dec 31, 2020, 10:29 AM UTC

bostonfincs wrote: post #126

hnracer wrote: post #123

Earlier quoted context omitted.

Does R have robust CSV parsing? I remember using the default and it'd be extremely finicky about getting the header and index flags right and wouldn't typecast numeric columns properly (instead they'd end up as factors and not play nice)

Personally have never had a problem with R csv parsing

It happens, but mostly because other formats don't produce usable CSV's. The biggest problem is if there are any free-entry text fields (common for customer/business name), and there isn't full quoting around these fields, base R will break.

I believe both fread and readr::read_csv do the right thing here, but the base-R perspective on data manipulation before read.csv is to use Perl (the R-core team are pretty old-school, to be fair).