Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Ditching Excel for Python in a legacy industry

amypeniston.com

Post reply on HN
Print view View original

91–100 of 289 posts

Re: Ditching Excel for Python in a legacy industry

#91

Post by yold__ »

klelatti wrote: post #84
yold__ wrote: post #75

Earlier quoted context omitted.

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.

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.

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

Re: Ditching Excel for Python in a legacy industry

#92

Post by hcrad »

analog31 wrote: post #61

I work in a small R&D team within a larger engineering organization. I use Python, and it has spread to the rest of my team. However, I've tried to share tools that I've written in Python with the engineers. The problem is that I have to hand-hold them through the process of getting Python working on their computer at the level of detail of: Here is how you find the Python editor. Double click on it. Click on "open."…

Pyinstaller + Gooey has been my go-to combo for sharing executable python with simple/intuitive UIs.

https://github.com/chriskiehl/Gooey

Re: Ditching Excel for Python in a legacy industry

#93

Post by selimthegrim »

bernardv wrote: post #72
vb6sp6 wrote: post #38

> The development environment is not user friendly, the syntax confusing, there’s no support for unit testing – I could go on. The vba ide is pretty good imo. Lack of unit test frameworks is valid but doesn't stop you from rolling your own.

Loved VB and VBA but it is too limiting when needing advanced numerical capabilities. Back in the day had to create add-ins making use of compiled Matlab code to get access to decent numerical routines. Eventually moved to Python and never looked back, although I still use Excel for certain tasks. However I do miss the VBA GUI editor built into Excel. It allowed for relatively polished interfaces in record time.

What sort of capabilities are you looking for? Newton-Raphson (which Excel has with GOALSEEK)? Sensitivity analysis?

Re: Ditching Excel for Python in a legacy industry

#95

Post by pmart123 »

solresol wrote: post #78
yold__ wrote: post #51

Earlier quoted context omitted.

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

Why doesn't annotating these functions with @functools.lru_cache(10000000) work?

My guess is the numeric inputs would be changing significantly each call?

Re: Ditching Excel for Python in a legacy industry

#96

Post by ineedasername »

I work somewhere that was stuck in even more simple excel usage, like sorting a list and counting rows for each type of category instead of using a pivot table.

I've done some more advanced work with python and xgboost for some modeling, but the biggest improvements in terms of both time saving and regular use of data for informed decision making has been implementing basic reports and dashboards. So much so that sometimes I feel like I'm creating kindergarten doodles that get praised as amazing masterpieces, which is a weird sort of embarrassment. I jokingly describe my job as "I count stuff" because a big part of what I do is still working with departments on what they want counted and the most useful way of displaying it to them. Percentages and year-on-year comparisons are magic.

I'm not quite sure what qualifies as a "legacy industry", but just about any organization that's been around for 40+ years could have the potential for massive improvements from taking advantage of improvement made during <= the past 20 years.

Re: Ditching Excel for Python in a legacy industry

#97

Post by klelatti »

yold__ wrote: post #91
klelatti wrote: post #84

Earlier quoted context omitted.

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.

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 do 30 year cashflow projection for 1m contracts in about 1 min on quad core laptop. UK focus initially but might have wider application?

Re: Ditching Excel for Python in a legacy industry

#98

Post by yold__ »

solresol wrote: post #78
yold__ wrote: post #51

Earlier quoted context omitted.

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

Why doesn't annotating these functions with @functools.lru_cache(10000000) work?

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

Re: Ditching Excel for Python in a legacy industry

#99

Post by Jwilcoxdata »

I’m surprised that there aren’t more comments about utilizing R AND Python for analysis work. These two languages actually commingle fairly well, you can build in RStudio if you like that flavor and still import Python packages to use in R code.

We do a significant amount of modeling and analysis on large data sets from a variety of disparate sources and utilizing several different packages have extended this out to standing up a fully free (save for AWS hosting) environments that perform modeling, allow reporting and Dashboarding automation, restful APIs for other services to call into.

I’d encourage anyone looking at making the jump from Excel to ‘X’ to checkout out some of the power of flex dashboards, R Shiny, Plumber and some of the different authentication mechanisms available.

Some elbow grease can create a wonderful environment.

Post reply on HN