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

61–70 of 108 posts

Re: Is Microsoft Excel an Adequate Statistics Package?

#61

Post by kgwgk »

jnbiche wrote: post #60
kgwgk wrote: post #58

Earlier quoted context omitted.

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…

No, these are not mathematical models used for forecasting. In this context, "financial statement forecasting" means making up financial statements. Something like this: https://mycourses.aalto.fi/pluginfile.php/144740/mod_resourc...

Re: Is Microsoft Excel an Adequate Statistics Package?

#63

Post by dandermotj »

lordnacho wrote: post #34
ZanyProgrammer wrote: post #28

Earlier quoted context omitted.

I could've sworn I've seen people who work in the financial and maybe data science industries who've praised Excel to the heavens on HN. Like on the same level as Python, R, etc. I don't work in finance (or data science really) so I can't comment, but it seems merely a tool to me.

No way, not data science. How are you going to load in millions, possibly billions of individual numbers? With finance there's a lot of things that seem like good fits, but only if you keep them small. Something like a personal budget is fine, where everything fits on a screen.

The "big data vs. small data" argument isn't the issue for data scientists. It's the total lack of decent statistical libraries, visualisation tools, programming capabilities and on and on. Don't think anything I've worked on would come close to hitting the excel memory ceiling, but without a doubt completing it in excel would be nigh on impossible.

Re: Is Microsoft Excel an Adequate Statistics Package?

#64

Post by gjm11 »

twunde wrote: post #51
dandermotj wrote: post #22

Earlier quoted context omitted.

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

> if he left, long-term we would probably need 3-4 people to take over his work.

I hope you're paying him really well.

Re: Is Microsoft Excel an Adequate Statistics Package?

#65

Post by avs733 »

princeb wrote: post #42

there are a few unique issues i face with excel beyond the stats package. 1. if you shift cells (ctrl c, ctrl v) around, delete or insert rows, you may mess up existing cell references in formulas without realizing it. your vlookups, hlookups will not change your column numbers just because you did. your vba code will not change your A1 cell references. things will blow up here in spectacular fashion. 2. if you have…

don't use v/hlookup...use index(match()) and you will solve that problem

Also, the folks running R should pay Matlab/Mathworks to give them a workshop on writing help files.

Re: Is Microsoft Excel an Adequate Statistics Package?

#66

Post by nerdponx »

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.

I'm pretty sure there is an ODBC plug-in for Excel

Re: Is Microsoft Excel an Adequate Statistics Package?

#67

Post by nerdponx »

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.

I'm pretty sure there is an ODBC plug-in for Excel

Re: Is Microsoft Excel an Adequate Statistics Package?

#68

Post by learningman »

ramblenode wrote: post #47

A point I have not seen mentioned is that Excel encourages bad practices for data visualization. No other statistics or data analysis software I am aware of gives you the option of 3D-ifying a 2D plot. This adds negative value to the plot for anyone who is actually interested in data over eye candy. Simple example is a pie chart. Give it depth and it becomes much more difficult to reason about, and one's reasoning co…

Agreed for the most part, although there are rare valid uses for 3d graphs. I think most contemporary data vis people would say that Excel makes it possible to make pie charts at all means it may encourage bad practices.

Re: Is Microsoft Excel an Adequate Statistics Package?

#69

Post by avs733 »

A couple notes from a heavy Excel and heavy R (and heavy several other similar products) user...

factories run on excel...terrifyingly so. I know semiconductor semiconductor fabs that basically run on queries in a many sheeted workbooks. The reason is because not everyone codes, especially not factory foreman, technicians, etc. They don't need a continuously running app (well they do but they don't know it) they just need formatted data.

That links to comment two. The issue that Excel solves over code goes back (IMHO) to two issues that come up with coders/noncoders. First, most users of excel are interested in the answer rather than the process. I get the value of being able to audit your steps visually, but the transition from Excel to R is a transition from the primary visual being data (i.e., results) to process. Depending on your perspective that can be highly meaningful.

Second, and probably more importantly, Excel lets you visualize your data as it progresses. For those with lower (or just different/less abstract) spatial and visual reasoning ability, seeing the progress of data from column to column can have a fairly profound effect. This extends to students who are trying to learn by seeing the progression as steps of a processing algorithm are applied. Doing so in code abstracts that process heavily. For some, especially those not used to treating data in the abstract/blind via code.

Re: Is Microsoft Excel an Adequate Statistics Package?

#70

Post by hexane360 »

I've spent a lot of time using Excel in ways it was never meant for.

If anyone is trapped using Excel to make a box plot, it is possible (disclamer please for the love of god never do this): Make a stacked column chart. The first series should be equal to Q1 minus the graph minimum. Format this to be invisible. Series two should be median - Q1, formatted as a black outline on a white bar. Series three should be Q3 - median, formatted like series two. Error bars can be added to give the whiskers. Here's an example: https://i.ytimg.com/vi/ucWmfmXb1kk/maxresdefault.jpg

Again, don't do this.

For a histogram, just make a min and max for each bin, and use countifs(range, ">=" & min, range, "<" & max) to get the amount in each bin.

Post reply on HN