Excel warriors who save governments and companies from spreadsheet errors • Hacker News Board

Re: Excel warriors who save governments and companies from spreadsheet errors

#171

Post by heavenlyblue » Sat, Oct 17, 2020, 8:44 PM UTC

OzCrimson wrote: post #163

heavenlyblue wrote: post #148

Earlier quoted context omitted.

I would say that you probably do not know what you did wrong having done it in Excel. Stuff gets complicated really fast in Excel thus it’s much easier to do something that you can’t possibly debug and thus live in blissful ignorance.

It's just unbelievable ... how people KNOW about situations that they weren't in and don't ask any questions. But since you brought it up I can tell you about my accuracy in Excel: - The process involved 2 people checking my work before it was sent to payroll. And we did catch and correct mistakes. We were able to trace back where and how the mistakes were make, and build in ways to prevent or flag future mistakes. -…

It has nothing to do with your particular situation. This is a reasonable assumption to make if somebody comes and wants a solution in Excel.

But ok. So what you are saying your Excel was reviewed by somebody else. How is this any different from reviewing somebody else’s code?

I work in a company where even the least technical analysts are capable of using numpy. We have libraries to use Google Sheets as input-output data. So you get the best of the two worlds.

What does Excel resolve here?

Re: Excel warriors who save governments and companies from spreadsheet errors

#172

Post by heavenlyblue » Sat, Oct 17, 2020, 8:46 PM UTC

OzCrimson wrote: post #163

heavenlyblue wrote: post #148

Earlier quoted context omitted.

I would say that you probably do not know what you did wrong having done it in Excel. Stuff gets complicated really fast in Excel thus it’s much easier to do something that you can’t possibly debug and thus live in blissful ignorance.

It's just unbelievable ... how people KNOW about situations that they weren't in and don't ask any questions. But since you brought it up I can tell you about my accuracy in Excel: - The process involved 2 people checking my work before it was sent to payroll. And we did catch and correct mistakes. We were able to trace back where and how the mistakes were make, and build in ways to prevent or flag future mistakes. -…

Also I don’t believe that you could not use Excel to cleanse the data and then put it back in the database.

And I also do not believe that a python script would not do the same cleansing of the data in a much more maintainable way.

Re: Excel warriors who save governments and companies from spreadsheet errors

#173

Post by iagovar » Sat, Oct 17, 2020, 8:52 PM UTC

ksquarekumar wrote: post #145

iagovar wrote: post #101

Earlier quoted context omitted.

Knime. Desktop app is free, is not as polished as alteryx but with some community love it will improve massively. It already is WAY better than Excel. Stuff is organized in a manner someone unfamiliar with data handling or programming can understand it. It takes a little bit to master, an some stuff (like looks and variable passing) is still unintuitive.

And how does knime/everything else compare with PowerBI, given how aggressively it is being pushed by Microsoft, and the fact that it would come bundled as part of most organizations Office 365 deployment.

When you see it, you understand it. Knime interface is designed around "data pipelines" if you wish, completely different from PowerBI, which seems more oriented to dashboards.

Re: Excel warriors who save governments and companies from spreadsheet errors

#174

Post by 867-5309 » Sat, Oct 17, 2020, 9:23 PM UTC

DanBC wrote: post #170

867-5309 wrote: post #169

Earlier quoted context omitted.

uncertain for the downvotes, care to share which date you see in those numbers..?

I don't know why you got downvotes. Here's an explanation of the way Excel stores dates: https://news.ycombinator.com/item?id=24809037

that's a lot more helpful, thank you

Re: Excel warriors who save governments and companies from spreadsheet errors

#175

Post by bpiche » Sat, Oct 17, 2020, 9:38 PM UTC

OzCrimson wrote: post #84

ssdspoimdsjvv wrote: post #83

Earlier quoted context omitted.

Excel is the proverbial hammer that makes everything look like a nail. It is very flexible, powerful and beginner-friendly. And while you can solve a lot of problems with it, some of those should be handled by other programs (or other people). The problem is that the average Excel user doesn't have the right knowledge to judge what the best solution is to their problem. And sometimes it only becomes clear after long…

Again. What's the comparable alternative? This is so frustrating because I've worked in small departments in an international company. Our problems were too small to get consistent, needed help from IT and we weren't important enough to get one of the limited licenses to the fancy reporting system. Ultimately, there's all this bad talk about evil Excel, but we get no help or other resources. And what about all the ju…

R, or Python.

Re: Excel warriors who save governments and companies from spreadsheet errors

#176

Post by OzCrimson » Sat, Oct 17, 2020, 9:42 PM UTC

heavenlyblue wrote: post #172

OzCrimson wrote: post #163

Earlier quoted context omitted.

It's just unbelievable ... how people KNOW about situations that they weren't in and don't ask any questions. But since you brought it up I can tell you about my accuracy in Excel: - The process involved 2 people checking my work before it was sent to payroll. And we did catch and correct mistakes. We were able to trace back where and how the mistakes were make, and build in ways to prevent or flag future mistakes. -…

Also I don’t believe that you could not use Excel to cleanse the data and then put it back in the database. And I also do not believe that a python script would not do the same cleansing of the data in a much more maintainable way.

You've got all the answers about a company in Chicago in 2005-2008 and how it should have been run.

Why didn't you jump in and help us?

Re: Excel warriors who save governments and companies from spreadsheet errors

#177

Post by bpiche » Sat, Oct 17, 2020, 9:42 PM UTC

Moru wrote: post #154

AnIdiotOnTheNet wrote: post #116

Earlier quoted context omitted.

"The right solution to their problem" being a developer, at least as far as developers are concerned. Hammers and nails...

Sometimes that database feels more like a jetplane when all I need is a hammer for my nail. Excel (or libreoffice calc) is perfect for a lot of stuff, especially those small things that you won't ever do again.

Excel is a UI over a database table. With a bunch of other UI driven math/stats functions built in. It really comes down to a difference between UI driven folks and folks that are code literate.

Re: Excel warriors who save governments and companies from spreadsheet errors

#178

Post by PlugTunin » Sat, Oct 17, 2020, 10:08 PM UTC

In my former life I worked in Finance departments of Fortune 500 companies, responsible for process and report automation, and making Excel-based systems less unwieldy -- often by converting them to Access. I have an Accounting & Info Systems degree. I'm well-versed w/ relational databases, SQL, and VBA. In more recent years, to distract myself from wanting to die, I've become proficient with Python and R. Unfortunately, a gap in my resume a few years ago became the reason recruiters started treating me like a leper.

What went wrong, besides bad luck? I was good enough at what I did that the perception formed that I'm not a real Finance person. So, instead of being used in roles with longevity, where writing code was an adjunct to domain expertise, I became The Tech Guy: a'hired gun', used exclusively to build tools for the real Finance pros to use in perpetuity.

Reading these comments is upsetting because they suggest that in 2020, someone w/ my background should be able to support themselves. To those who understand the power of databases and automation, the inanity of using Excel to do almost everything except wash your dishes is painfully obvious. Trust me: there's a better way.

Many technical people -- W/OUT a business background -- are prepared to do whatever is necessary to improve their understanding of The Business Side. Why is it that asking finance types to write code is akin to asking them to perform brain surgery? Perhaps grasping math and accounting principles doesn't imply creativity, or competency w/ language and logic? I've worked with people who can do all of these things. In 20 years, those who can't may struggle to find work. Somehow, in spite of my background, I ended up in the same boat.

Sorry to sound bitter, but people with skills who can't find work are sometimes more miserable than those without any skills, who have resigned themselves to a life of earning minimum wage. I miss the good old days, when I could flush money down the toilet for sport.

Re: Excel warriors who save governments and companies from spreadsheet errors

#179

Post by conductr » Sat, Oct 17, 2020, 10:10 PM UTC

moomin wrote: post #143

conductr wrote: post #88

Earlier quoted context omitted.

QOE drove me nuts in a past life. M&A of small companies with crappy financial data. Biggest caveat is basically garbage in and garbage out. But often they already had the deal done and would just feel better if I told them the garbage didn’t stink.

One of my best behaviours for actually writing one of these things is to have a single page that only has inputs/assumptions on it and put an annotation/note on every last data item/series saying where you got it from, because someone on the warpath will want to know where you got that BS number from two months later, and it's very satisfying to show it was them.

Agree. That’s a must. You have to cover your ass when it comes to this type of work. It’s high stakes enough that I save off copies of emails (.msg) and create a Sources folder for these, spreadsheets, and I’ll even do a screen capture if I have to login to some system and pull the data myself. I am usually given the parameters on what to pull, and it’s very easy for someone to accuse me of pulling the data wrong a month or more after the fact. I did this on every analysis. I can relate to what your saying. Two months later something like this always happens;

What was you assumption of growth?

Infinity.

What?! That’s ridiculous why would you assume that?

forward back an email you sent to me when I pointed out the growth rate seemed high but you insisted it was a slam dunk (aka the only way the deal would go through and we’d get that bonus)

At times I feel like I put so many footnotes in the resulting presentation later the information should be interpreted as meaningless.

Re: Excel warriors who save governments and companies from spreadsheet errors

#180

Post by benhurmarcel » Sat, Oct 17, 2020, 10:59 PM UTC

cosmie wrote: post #16

NoPicklez wrote: post #4

I work at a large firm which has shifted itself from using Excel, to significantly promoting data analytics tools such as Alteryx in place of Excel. One of the MAJOR improvements from using Alteryx is that it is easier to audit and review the process. With Excel you can be down the rabbit hole of data manipulation or formula driven outputs and realise you have made a mistake but cannot find where or how. Or cannot fi…

Excel has similar capabilities (since Excel 2010) in the form of PowerQuery[1]. It can pull in 3rd party data sources (including custom HTTP calls if you want to reach out to an API), has data types, can both process and store substantially larger data volumes (data is cached/stored separately from the spreadsheet data in a compressed, columnar format and processed by a separate data processing engine), and every ste…

> PowerQuery is already widely distributed and available to anyone with Excel installed

Looks like you need to install it separately as a module, it's not included with base Excel. So every user needs to download it, and have admin rights to install it. That makes it a non-starter in many of the corporate use-cases.