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

Re: Ditching Excel for Python in a legacy industry

#251

Post by cutler » Thu, Dec 31, 2020, 5:45 PM UTC

pjmlp wrote: post #225

RobinL wrote: post #171

Earlier quoted context omitted.

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

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

Yes, I was wondering why Python was the automatic choice considering C#.Net has typesafe native APIs for Excel on Windows.

Re: Ditching Excel for Python in a legacy industry

#252

Post by SoSoRoCoCo » Thu, Dec 31, 2020, 5:48 PM UTC

vimy wrote: post #218

SoSoRoCoCo wrote: post #114

Earlier quoted context omitted.

macOS still ships with Py2.7 and has dependencies, and npm-gyp only recently switched to 3.x. same with python SDR. it depends what you use: less popular packages are still languishing. 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.

As of macOS 11.0 there is no python anymore. You have to download it.

Really? YAY!!! I upgraded to Big Sur and it is still there... but I don't know if it is a legacy since I've been TimeMachining my machines since 2012 and things persist.

Re: Ditching Excel for Python in a legacy industry

#253

Post by cutler » Thu, Dec 31, 2020, 5:50 PM UTC

newdude116 wrote: post #196

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…

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

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.

Re: Ditching Excel for Python in a legacy industry

#254

Post by cutler » Thu, Dec 31, 2020, 6:15 PM 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…

Clojure now has libpython which would enable you to add the recursion.

Re: Ditching Excel for Python in a legacy industry

#255

Post by RobinL » Thu, Dec 31, 2020, 6:32 PM UTC

cutler wrote: post #251

pjmlp wrote: post #225

Earlier quoted context omitted.

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

Yes, I was wondering why Python was the automatic choice considering C#.Net has typesafe native APIs for Excel on Windows.

I was more thinking about migrating away from Excel fully rather than interfacing with Excel from Python.

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.

Re: Ditching Excel for Python in a legacy industry

#256

Post by plaidfuji » Thu, Dec 31, 2020, 6:37 PM UTC

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

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

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

Re: Ditching Excel for Python in a legacy industry

#257

Post by disgruntledphd2 » Thu, Dec 31, 2020, 7:31 PM UTC

wodenokoto wrote: post #194

disgruntledphd2 wrote: post #189

Earlier quoted context omitted.

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

You do have access to a repl when using jupyter notebooks. You can hook a notebook or a repl to an existing kernel. I always have a command line attached to my notebooks. When using jupyter lab I attach the build-in terminal and place it at the bottom. When using notebooks I attach it from my terminal. The experience in Rstudio is still better imho. It’s also a more mature text editor and ide than jupyter.

Ok fair enough, I only used notebooks when I can't avoid it. I'm pretty sure you don't get a repl by default though, is there an involved set up in jupyter?

Re: Ditching Excel for Python in a legacy industry

#258

Post by mint2 » Thu, Dec 31, 2020, 9:14 PM UTC

mark-r wrote: post #122

mint2 wrote: post #105

Earlier quoted context omitted.

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.

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.

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.

Re: Ditching Excel for Python in a legacy industry

#259

Post by gerdesj » Thu, Dec 31, 2020, 10:25 PM UTC

tomcam wrote: post #28

gerdesj wrote: post #16

Earlier quoted context omitted.

"analysts" - lol! I know a HR director at a multi-national. He'd had enough of Excel and liked the look of this Python thing. I showed him R as well for balance but he wanted Python. I showed him how to install a Python distro and MS Code on his Windows machine, wired them up and off he went a few months back. The board are in awe of his presentations. He is not an IT bod at all but a Uni. degree in Psycho. involves…

All good anecdotes, but I’m still kind of stuck on the whole you got to work at a pie factory thing

(Sorry, misty eyed recollection alert)

I should point out that "pies" in the UK is a rather generic term. MBOs (mince beef and onion), sausage rolls, pasties, pork pies and quiche was made in this south Devon based factory, near Plymouth.

It was a good corp citizen thing to attend the 1100 "taste panel" which was part of the quality process. Obviously Product Dev, QA and the line crews could not mark their own work so office staff were expected to taste to standard. The idea is that you taste samples from the store that is post bake. This is a perishable product and there are stores (freezers, chills etc) to provide time buffers throughout production.

There are a lot of constraints. You always make to forecast. In this case, back then, you had to deliver to depot with seven plus days of shelf life. The product needs meat and dough prep, make, bake and wrap and shoving in the back of a trunker (lorry/truck). You need to ensure you've got all your raw ingredients available and most of those have a shelf life and somewhere to store. Your machines have a nominal 100% production rate and a defined servicing period, expected breakdown rate, need cleaning and more. Some machines will do the job end to end and some will only do part of the process. You have bakeries and stores with varying characteristics. Some products have special requirements.

It is clearly a "simple" job of defining, understanding and controlling your constraints and solving a few equations. I absolutely loved it as a challenge. This was 1995ish. I inherited a System 36 that was basically a glorified accounting system with some stock control and a few other things. If it got too warm in summer I used to put bags of solid CO2 that I could scrounge from Despatch (the whole factory panics when it gets really hot) in it.

I am quite partial to pork pies and pasties.

Re: Ditching Excel for Python in a legacy industry

#260

Post by gerdesj » Thu, Dec 31, 2020, 10:47 PM UTC

wendyshu wrote: post #23

gerdesj wrote: post #16

Earlier quoted context omitted.

"analysts" - lol! I know a HR director at a multi-national. He'd had enough of Excel and liked the look of this Python thing. I showed him R as well for balance but he wanted Python. I showed him how to install a Python distro and MS Code on his Windows machine, wired them up and off he went a few months back. The board are in awe of his presentations. He is not an IT bod at all but a Uni. degree in Psycho. involves…

What's a bod?

A "bod" is a person (body.) It is a colloquialism in the UK. You generally only use it in this case to denote an anonymous/generic person: "A bod did stuff".

However, bod is also used as a formal abbreviation for body: "You have a lovely bod". In this case you should be reasonably familiar with the object or you will get slapped!

Sorry, bod means a person.