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

Is Microsoft Excel an Adequate Statistics Package?

practicalstats.com

Post reply on HN
Print view View original

51–60 of 108 posts

Re: Is Microsoft Excel an Adequate Statistics Package?

#51

Post by twunde »

dandermotj wrote: post #22
twunde wrote: post #10

I disagree. Where I work, we have one person that does all the hedging, price calculations and other financial modeling and he only uses Excel. The only practical downsides to his use of Excel it's that he doesn't have direct database access so we generally need to create the initial reports to give him the data and secondly Excel can only hold a little over 2M rows.

So just to give you an opposing perspective of what you just said: "we have __one__ person that does all...". So apart from the fact he can't access a database appropriately, he's the _only_ person who does this work and none of it is documented or reviewable because that's the nature of Excel. There's some serious operational risk in that. This guy disappears tomorrow and what do you do?

It's a misconception that work in Excel can't be documented or reviewed. We have the seed data he uses and the resulting Excel files (with the formulas he used still in them). This is the equivalent to version control for programming. The results are also reviewed against 1-2 appropriate sources (invoices, historical data, multiple other reports built by IT) for accuracy. To your very legitimate point that he's the only one doing this work, if he left, long-term we would probably need 3-4 people to take over his work. Short-term, the company would probably need to put together a triage team of 5-6 people to take over his existing processes. We actually moved some of the hedging to a different team last year and we had to hire two business analysts and implement a industry-specific system to do so.

Re: Is Microsoft Excel an Adequate Statistics Package?

#52

Post by blahi »

ramblenode wrote: post #50
blahi wrote: post #44

Earlier quoted context omitted.

I have 2.4 billion data points loaded in Excel at the moment. Not that it matters. "Data science" is not something that magically kicks in after you go beyond some "big data" threshold. Excel is heavily used in managerial science type of positions and I can assure you those are rather heavy on the "data science" workflows.

The maximum number of rows in the most recent version is 1,048,576 [0]. I'm deducing then that your data is in wide format. What would happen if you wanted it in long format? It seems you would just be out of luck with Excel, which is not a problem in any other major statistics software. [0] https://support.office.com/en-us/article/Excel-specification...

Except that there is Power Pivot which is xVelocity - the same columnar store database use in SQL Server.

Re: Is Microsoft Excel an Adequate Statistics Package?

#53

Post by twunde »

dandermotj wrote: post #27
nightski wrote: post #23

Earlier quoted context omitted.

Your conclusion is that it was excel causing these errors and you are implying similar errors would not be made in Python. I think it is more because of your experience as a developer why you were able to spot and correct errors.

It is absolutely the interface to excel that causes errors. I can take your excel spreadsheet, format cells, add things, change a reference and generally fuck it up and hand it back to you and you would never know. Whereas, if I change a text file then you can see what's changed. Similar errors are much less common in a programming language.d

When you're working with people who are good with Excel, they will notice. If you're only ok with excel and just use vlookups, then you're probably going to have problems, especially since those people don't tend to keep a versioned history of their files. Really the main difference is that version control has become an ingrained habit in software development whereas with Excel it's rare

Re: Is Microsoft Excel an Adequate Statistics Package?

#54

Post by twunde »

throw_away_777 wrote: post #14
twunde wrote: post #10

I disagree. Where I work, we have one person that does all the hedging, price calculations and other financial modeling and he only uses Excel. The only practical downsides to his use of Excel it's that he doesn't have direct database access so we generally need to create the initial reports to give him the data and secondly Excel can only hold a little over 2M rows.

How do you verify that his calculations are correct?

His calculations are usually compared to invoices (ie do the numbers match what we were charged for), other reports and historical data. Similarly when one of our DBAs or BAs creates an important report, we check the results against other data sources.

Re: Is Microsoft Excel an Adequate Statistics Package?

#55

Post by twunde »

lordnacho wrote: post #32
twunde wrote: post #10

I disagree. Where I work, we have one person that does all the hedging, price calculations and other financial modeling and he only uses Excel. The only practical downsides to his use of Excel it's that he doesn't have direct database access so we generally need to create the initial reports to give him the data and secondly Excel can only hold a little over 2M rows.

Why does he not simply read a tutorial on how to pull in data from a database?

I think it comes down to the fact that he hasn't needed to and that he prefers the tools he already knows. He's also not part of the IT department, so it would be similar to having your CFO have direct database access. Possible but probably not the best use of time.

Re: Is Microsoft Excel an Adequate Statistics Package?

#56

Post by jnbiche »

kgwgk wrote: post #48
lordnacho wrote: post #26

Never, ever use Excel for anything other than prototyping, spot-checking data, or as a makeshift GUI while you develop a proper one. I've seen several financial shops where people were moving millions of dollars around using Excel. IMHO, it's always an indicator of deficient processes and lack of coding skill. Yes, I do know that some clever people use it. They are productive in spite of excel, not because of it. You…

> Yes, I do know that some clever people use it. They are productive in spite of excel, not because of it. Would they be more productive if you took Excel from them? > I remember a billion dollar merger where the analyst in charge of the "modelling" showed me how they reached the line limit. It's as if making things complicated justified their salaries, so maybe that's why excel is so popular. How should the analyst…

> How should the analyst write his model in a simple way? As a Java (or Haskell, or whatever firs your ideal of simplicity) program?

In Python or R, like the rest of us do. Or SAS, Stata, or even SPSS if they want a familiar interface (although using the SPSS GUI instead of its scripting interface will put them at risk of the same kinds of mistakes as Excel is).

Ensuring reproducibility and robustness isn't rocket science, and it doesn't require the obscurity of Haskell or the verbosity of Java to do properly. But it does require learning to script to program their models, instead of relying on GUI (which should be perfectly natural to analysts and their long Excel formulas).

Edit: To be clear, when I say "scripting" here, I mean text-driven programming, as opposed to using a GUI interface (which is rarely robust or reproducible, and certainly isn't in plain Excel).

Re: Is Microsoft Excel an Adequate Statistics Package?

#57

Post by lordnacho »

kgwgk wrote: post #48
lordnacho wrote: post #26

Never, ever use Excel for anything other than prototyping, spot-checking data, or as a makeshift GUI while you develop a proper one. I've seen several financial shops where people were moving millions of dollars around using Excel. IMHO, it's always an indicator of deficient processes and lack of coding skill. Yes, I do know that some clever people use it. They are productive in spite of excel, not because of it. You…

> Yes, I do know that some clever people use it. They are productive in spite of excel, not because of it. Would they be more productive if you took Excel from them? > I remember a billion dollar merger where the analyst in charge of the "modelling" showed me how they reached the line limit. It's as if making things complicated justified their salaries, so maybe that's why excel is so popular. How should the analyst…

Yes, if people didn't think that crappy tools were OK, they would have better tools, and they'd have a better idea of what they could do. For instance, I did a lot of fixed income spreadsheets at one point. Instructive for learning how the business works, but ultimately if you stay with the spreadsheets, you are not full exploiting opportunities. If I were to come back to that I'd automate the opportunity finding as well, going back through time to validate. That's something you just can't do with an excel sheet.

Models that take up entire spreadsheets are not models. There is never enough data to validate the sheer number of degrees of freedom that such models contain. There would be things like sub-models of entire divisions of firms. Who even has data that could validate all the potential things that could happen? These spreadsheets are pure ludic fallacy; advisors pretending they know how adding or removing some employees will affect some merger.

Re: Is Microsoft Excel an Adequate Statistics Package?

#58

Post by kgwgk »

jnbiche wrote: post #56
kgwgk wrote: post #48

Earlier quoted context omitted.

> Yes, I do know that some clever people use it. They are productive in spite of excel, not because of it. Would they be more productive if you took Excel from them? > I remember a billion dollar merger where the analyst in charge of the "modelling" showed me how they reached the line limit. It's as if making things complicated justified their salaries, so maybe that's why excel is so popular. How should the analyst…

> How should the analyst write his model in a simple way? As a Java (or Haskell, or whatever firs your ideal of simplicity) program? In Python or R, like the rest of us do. Or SAS, Stata, or even SPSS if they want a familiar interface (although using the SPSS GUI instead of its scripting interface will put them at risk of the same kinds of mistakes as Excel is). Ensuring reproducibility and robustness isn't rocket sc…

I don't think you know what a financial model is: https://en.m.wikipedia.org/wiki/Financial_modeling#Accountin...

Re: Is Microsoft Excel an Adequate Statistics Package?

#59

Post by kgwgk »

lordnacho wrote: post #57
kgwgk wrote: post #48

Earlier quoted context omitted.

> Yes, I do know that some clever people use it. They are productive in spite of excel, not because of it. Would they be more productive if you took Excel from them? > I remember a billion dollar merger where the analyst in charge of the "modelling" showed me how they reached the line limit. It's as if making things complicated justified their salaries, so maybe that's why excel is so popular. How should the analyst…

Yes, if people didn't think that crappy tools were OK, they would have better tools, and they'd have a better idea of what they could do. For instance, I did a lot of fixed income spreadsheets at one point. Instructive for learning how the business works, but ultimately if you stay with the spreadsheets, you are not full exploiting opportunities. If I were to come back to that I'd automate the opportunity finding as…

> These spreadsheets are pure ludic fallacy; advisors pretending they know how adding or removing some employees will affect some merger.

Yes, I agree. And I think Excel is an appropriate tool for that job.

Re: Is Microsoft Excel an Adequate Statistics Package?

#60

Post by jnbiche »

kgwgk wrote: post #58
jnbiche wrote: post #56

Earlier quoted context omitted.

> How should the analyst write his model in a simple way? As a Java (or Haskell, or whatever firs your ideal of simplicity) program? In Python or R, like the rest of us do. Or SAS, Stata, or even SPSS if they want a familiar interface (although using the SPSS GUI instead of its scripting interface will put them at risk of the same kinds of mistakes as Excel is). Ensuring reproducibility and robustness isn't rocket sc…

I don't think you know what a financial model is: https://en.m.wikipedia.org/wiki/Financial_modeling#Accountin...

Well, I've never worked in finance, but if these are like the mathematical models that I used to make predictions in grad school and for the year after grad school when I worked as a statistical analyst, I do. I presume these are predictive mathematical models, used for forecasting?

Why in the world would Python or R be inadequate for that type of work, if that's what you're implying? What do you think they lack that Excel has, other than a friendly GUI interface? Or SAS, Stata, or SPSS (all of whom have versions that they market heavily toward finance).

Post reply on HN