Live data from Hacker News

Ditching Excel for Python in a legacy industry

amypeniston.com

11–20 of 289 posts

Re: Ditching Excel for Python in a legacy industry

#11

> 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 is exactly the niche that Resolver One used to fill. It was basically an Excel-like spreadsheet but under the hood everything's Python.

Unfortunately it looks like they ceased the product in 2012 due to lack of sales. Perhaps they were too early.

https://youtu.be/u6EV2jiKRfc

Re: Ditching Excel for Python in a legacy industry

#12
When I worked at Uber, one of the big goals of my team was converting spreadsheets from the finance team to Python and Java. The second two problems that the author mentions (pulling in more data and software best practices) were two huge factors. In the former case, you simply cannot have an org where analysts have full read access to every data store to dump a CSV (of sensitive data collocated with lord knows what) at any time. It's a security nightmare. And in the latter case, when you've reached a point of sufficient complexity, you can no longer "roll out an update" to a team of more than a few people. Without versioning and source control, the model_v2_final_FINAL(1)(1).xlsx problem becomes extreme (even on cloud platforms). This leads to mistakes, and mistakes cost time and money.

Excel has other problems that aren't described in the article. First, it intermingles data and logic. If you're not especially careful and deliberate, running an experiment with multiple inputs means that you'll inevitably fuck up one of the inputs (or forget to change some data, or otherwise fail to do the steps necessary to reliably run the model again), leading to bad output. This is a reusability problem: you can do it right (one file per experiment, "template" spreadsheets, error handling logic), but in practice very few folks do this or even care.

Second, there's no meaningful way to test. If you've got critical logic, there's no way to write proper unit tests against the spreadsheet to ensure something hasn't broken. If I had a dollar for every improperly written linear regression in a spreadsheet... Conversely, writing spreadsheets as code means that you can rest assured that important units of logic are sound, which pays dividends when you're dealing with stuff used by a whole org.

Third, spreadsheets are really only useful as the "last step" in data processing. It's not good or easy to use a spreadsheet as input to something else. The inputs to the spreadsheet are usually manually updated (importing a CSV as a sheet), and then the output is graphical by default unless you're parsing the spreadsheet (good luck) or dumping it to CSV to import elsewhere (manual step with the risk of human error). In any business where the model you're dealing with pipes into other processes, there's almost always a manual step to get that data into "the next thing", be it another model, a dashboard, a database, etc. You can hack around this, but I've never seen a hack here that isn't incredibly brittle.

This isn't to say that Excel is bad, but when you use it "at scale" there are very rough edges that dramatically increase the ongoing costs of running a business built around it. When you're building a model, it's great. When you're running that model with different data more than a few dozen times a day and using the output in other systems, the costs quickly start to add up. That's the point where someone needs to step in and say "okay y'all, production use of this needs to run on a server". And if the production implementation is built well, you'll often find it simplifies the lives of the analysts, because they can download a blob of already- or partially-processed data to work with.

Re: Ditching Excel for Python in a legacy industry

#13

> 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 is exactly the niche that Resolver One used to fill. It was basically an Excel-like spreadsheet but under the hood everything's Python. Unfortunately it looks like they ceased the product in 2012 due to lack of sales. Perhaps they were too early. https://youtu.be/u6EV2jiKRfc

Here is sort of a modern variation - upload your Excel workbook to the cloud. I don't have hands on experience or any connection but I think it translates to C# under the hood.

https://www.milliman.com/en/products/milliman-mind

Re: Ditching Excel for Python in a legacy industry

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

> The most common use case is building out a web interface to replicate the Excel formula engine.

This is one of the projects that I'd worked on. We implemented a pretty thorough version of the Excel engine in JS. Load data and expressions as 2d arrays and get a nice api for the output.

https://github.com/websheets

Re: Ditching Excel for Python in a legacy industry

#15
The title is a bit misleading: It's not that typical Excel users are encouraged to, or experimenting with, using Python instead.

Rather, these are people who need to "price complex deals with increasingly large datasets". They write pricing models and need to run them.

Re: Ditching Excel for Python in a legacy industry

#16
post #5

As the author states - this is an issue for some really complex models - where the complexity, reusability and iteration challenges approach code. Most models do NOT take that many tabs, you can build a toy model near instantly - the production line from finished model and output to publishable material is a few shortcuts away. Having an analyst, write that same thing using Jupyter? From an accounts perspective? Man,…

"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 a fair amount of stats so a fair grounding there. He grabs huge data dumps from payroll etc and performs analyses that are complex but just work.

I think one of the benefits of using Python is that you instantly divorce input data, calcs and reporting. Fire up Excel and the first thing you often do is write a title. Using Excel properly requires a lot of discipline - I wrote a Finite Capacity Planner, with forecast and labour planner for a pie factory in Excel with quite a lot of VBA. It ran my P60 hard but did the job iteratively in about 2 to 5 minutes. Easter and Chrimbo needed a fair bit of tweaking by a Planner but most of the time my model told several supermarkets what they would be ordering back in the mid 1990s and they mostly faxed or EDId our forecast back as an order.

My brother (cough) is absolutely not an analyst in the normal sense. That a non programmer can bolt together enough Python to perform analyses useful to his job is testament to the power of the libraries and examples and documentation available. I've seen his code: suck in data, process it, spit out results, report results. That's all he needs and not a OO abstraction in sight.

My two examples (me and my FC Planner with Excel and an HR bod thrashing some data to a report with Python) are different things and each uses the opposite "tool for the job" discussed in the OP. However, it is how you use a tool that is important.

Re: Ditching Excel for Python in a legacy industry

#17
post #7

I do a lot of both. Excel really is great for data where there are less than say 100k rows. Its just so easy to see exactly what you're doing and what the data looks like. If you have millions of records Python really does better but I still find it frustrating to find a way to keep peeking behind the curtain. Ideally I'd have a type safe language which can embed data the way excel does. If Excel had dotnet languages…

Try ExcelDna it lets you hook up .net to Excel

Re: Ditching Excel for Python in a legacy industry

#18
post #16
post #5

As the author states - this is an issue for some really complex models - where the complexity, reusability and iteration challenges approach code. Most models do NOT take that many tabs, you can build a toy model near instantly - the production line from finished model and output to publishable material is a few shortcuts away. Having an analyst, write that same thing using Jupyter? From an accounts perspective? Man,…

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

Thanks for some great anecdotes! A couple of thoughts:

- It's often not is Python a good fit for the task but are there Python libraries that are a good fit? If so the actual Python code may be pretty trivial and the equivalent Excel a lot more complex.

- Writing good Excel is definitely possible but needs real discipline as you say - and bad Excel can be really bad!

Re: Ditching Excel for Python in a legacy industry

#19

> 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 rebuild their own spreadsheet from scratch, than use the files they inherited from the last person.

If these tools are necessary to conduct business and they are so worried about being able to support it, why don't they use proper software for that process?

A lot of people who make these bloated spreadsheets are people with no education in computing, and don't think about the basics of how to store data that is easy to analyse later. If they are building a weekly report, they build the report and enter the data directly into the report structure, which then makes it almost impossible to analyse later. Next week they just copy the file, rename it and update the data. If you want to analyse that same data over a year, good luck! 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.

Once I got the process down in a jupyter notebook, handling all the oddities with the data coming from whichever website, CSV file, data warehouse report I need, I can just save it as a .py file and run it as a scheduled task on a virtual computer forever. The data is kept in a format that can be appended to with each update, and can be easily analysed later.

The most amazing thing with replacing excel with python is you don't need to manually perform the update process yourself. Which means it doesn't cost anything to run the process more often. Weekly reports can become daily, or even hourly email updates that are only sent when something interesting happens. People can start reacting to things shortly after they happen, rather than having to remember what happened a week or a month ago. The iteration on improving becomes so much faster. People spend more of their time discussing how to fix problems, rather than spending time building problem finders. You can even start to automate the fixing of the problem in python and people don't even have to spend time on that thing at all, ever again.

Re: Ditching Excel for Python in a legacy industry

#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 "A1 + SomeOtherTab:B2" mean?

All of this really starts to fall apart when you have 10s of tabs with hundreds of rows of data which are often copy/ pasted. You won't even notice that some intern hard-coded one value into cell F75 until you actually drill down to that cell.

Spreadsheets are great until you hit a certain complexity, then they are unmanageable messes.

Post reply on HN