Hacker News Reader

Comment by corty | Hacker News Reader

Big big reason: R doesn't alter data without being asked to, Excel does so, silently, sneakily. Anything important should not be done in Excel.

https://www.theverge.com/2020/8/6/21355674/human-genes-renam... https://stackoverflow.com/questions/165042/stop-excel-from-a...

View on HN

While that is a very real problem, it isn't totally persuasive. It is just as easy to create traps in R (I lost days of my youth to R's challenging-to-defend jungle of an almost-type-system). It is even harder to debug than in Excel too - I would expect less bugs are found in R scripts because less people are up to the challenge of trying.

People shouldn't do important work in Excel. If it is important, people should be involved who have invested the time in learning something more powerful. Indeed, we could ask they aspire all the way to good practice and store their data in a database and their code in git. But there needs to be a process to verify model correctness no matter what tool is being used and bugs will exist in R as well as in Excel.

I agree that R isn't ideal, and a proper programming language is preferable to both. Which one of course will depend on the domain, but proper control over float precision, fixed-point arithmetics, bignum and debuggability isn't good in R either. It's just that R is still far above Excel.

I mostly agree, but at least Excel is more easily inspectable by a human, which makes a certain class of errors (specifically, silly logic bugs) less likely to get through.

Not really. Logic in Excel is based on stuff like SUMIF( ...) which is totally unreadable without lots of context about the cell or range it refers to. If someone fumbled the fill command and some cell is missing the formula? You'll never know and the result will be automatically and silently zero. References to that result will just use the zero as input. Mixed fixed/nonfixed references like A$3? Unreadable and hard to understand. References per se? Unreadable because undescriptive, maybe there will be a column or field label, maybe there won't. Also, formulas are always hidden by default, you'll just get a sea of numbers.

Calling Excel easily inspectable is laughably wrong imho. Just the opposite.

Data tables and keeping one tab per table solves all of those.

Sounds more like an issue with the skill of average excel users than a feature gap.

It's not obvious to me that Excel is more easibly inspectable, can you say more?

To me, R seems more easily inspectable, as all the logic of a program is visible just by looking at text files, where in Excel it's hidden "under the surface", you have to click on cells, look at what's there, go click on other cells that relate to it, remember what you were looking at in the first one that's now invisible, etc.

It's the visualization of logic that I'm thinking of. You can press F2 on a cell and visually see what data is being pulled into the function, and you can easily manually check that that single calculation is accurate by re-creating that calculation by hand, and then infer that all the other stuff in the column is also accurate.

For anything very complicated though, I'd prefer R, as Excel eventually gets unwieldy. Although, I'm saying that from the perspective of being a reasonably experienced coder. The majority of the population should just use Excel, especially in a work context in non-technical teams. No matter how much you push for R, other people in the team aren't going to see the value and aren't going to go along with it, and your R code will be useless after you've left.

Why isn't R a proper programming language? What would you use over R for data analysis or statistical analysis?

R has a beautiful functional ability based on S-expressions, allowing some clever stuff to be done (i.e. tidyverse), incredibly fast (data.table is faster than Python, Julia, Matlab etc).

Can data.table play well with the tidyverse yet?

And as a side note, I believe it's moving down the rankings in the h2o benchmarks [1].

[1]: https://h2oai.github.io/db-benchmark/

Oh that's great. I'm really glad to see Julia getting faster there.

> Anything important should not be done in Excel.

Hold on a second while I go shut down the global economy for two years so we can teach everyone finance person how to program.

Do not shutdown the global economy. Just maybe move to tools with less issues around process governance:

"The 7 Biggest Excel Mistakes of All Time"

https://www.teampay.co/insights/biggest-excel-mistakes-of-al...

"The financial fails and business risks of spreadsheets"

https://www.webexpenses.com/2020/10/financial-fails-business...

"Nightmare on spreadsheet: take Excel use seriously"

https://www.icaew.com/insights/viewpoints-on-the-news/2020/o...

"Excel – The Dirty Secret"

https://tax.thomsonreuters.co.uk/blog/excel-the-dirty-secret...

"8 Challenges When Using Excel For Accounting"

https://www.senacea.co.uk/post/excel-for-accounting-challeng...

As though mistakes wouldn’t happen with non-programmers working in R.

In R, you can use accepted methods of software development and engineering, such as version control, automated testing, CI/CD to catch mistakes and prevent them from causing harm. In Excel? Not really.

Do you think the average finance department employee is going to do that?

Especially those departments should do so and learn to do it. Financial departments are always the ones complaining about the cost of mistakes, compliance risks (of the monetary kind) and doing due diligence. Preventing the production of wrong figures in financial excel sheets ticks all those boxes. They should bloody well be doing it, but I'm also not holding my breath ;)

And there are massive bugs in programs that use these tools like the Linux kernel still.

Would not necessarily be a bad idea.

https://theconversation.com/economists-an-excel-error-and-th...

Not that this is Excel's fault, but researchers should definitely either seriously learn how to use computer stuff, or just don't.

A person can't drive on the highway without a license, the same rigor should be applied here, especially in academic circles.

This. Most finance people who live by Excel have ninja level skills with Excel. I have had to unpack logic from some excel sheets into webapps and they had so much sophisticated programming embedded into it. And it is so much easier intuitive to use Solver for linear equations in Excel than any web application.

Regarding the graphing ability , R may have more power but plotting the graphs in Excel is so much more WYSIWYG.

finance people who learn to program will outperform those who don't.

I believe that’s true. There still aren’t enough to go around and won’t be for a very long time.

Excel is the closest we’ve come as an industry to building a tool that enables non-programmers to program. If it disappeared tomorrow, as some arrogant posters apparently wish it would, tremendous value would be destroyed. Not just in terms of existing workflows but in terms of new workflows that would not be done in R but instead would be done by hand or not at all.

I’m a hobbyist programmer who has worked in various finance jobs and is now working in investment banking. I would love to use programming in finance but I have to say it’s come in handy very little.

Well, prolly not in IBD. But in Sales and Trading or at a lot of quant funds it is very common.

Actually when you use R functions to import CSV files, they also guess data types and make some conversions by default. The difference is that in R you can turn them off if you want to. But in Excel there are automatic changes you can't turn off.

You can set the data type to "text" when importing CSVs, that turns most things off.

If you try reading an Excel file via SSIS you are out of luck. You can't turn of the auto guessing feature.

Most, but not all. That is the entire point: Excel is never safe in any configuration.

Not sure about that. It is my understanding that Excel only tries to guess the type of an input if it has not been explicitly specified by the user.

The problem with "things that look like dates being interpreted as dates" comes from not specifying that a column has type "text".

Even if you do specify a column as a text excel WILL assume that it contains dates sometimes.

Happened to me multiple times, even when i set each cell as a text.

Seems like copypasting a tab separated values resets the cells to their default state.

If "not all", can you provide an example of what gets converted if you specify text?

Just want to know to avoid future gotchas.

I'm not sure, have to try it out but text fields can be delineated by starting with a single quote ('), that might be a special case. It might not be though, I don't have Excel open to check. Pretty much everything is left alone if you specify "text" at the import stage though. (This is using the text import wizard, or text-to-columns)

As a total R beginner I was hugely put off by the complexities of data cleansing especially columns with ?different data types.

"Floating-point arithmetic may give inaccurate results in Excel"

https://docs.microsoft.com/en-us/office/troubleshoot/excel/f...

"Align numerical precision Excel 2013 and R"

https://stackoverflow.com/questions/39531655/align-numerical...

"Numeric precision in Microsoft Excel"

https://en.wikipedia.org/wiki/Numeric_precision_in_Microsoft...

Almost all of these are issues with floating point in general, I'd say. Some are with naive implementations of Excel functions, fair enough (for example variance as (sum x_i^2/n - (sum x_i/n)^2) that are indeed better implemented in R or Julia.

IEEE 754 just has unintuitive properties.

Kahan (the "father of IEEE 754") has a rant (among many others) about Excel as well, and how it tries to hide some of the floating point complexities more or less successfully:

Floating-Point Arithmetic Besieged by “Business Decisions”

https://people.eecs.berkeley.edu/~wkahan/ARITH_17.pdf

But you also have issues like 'Catastrophic cancellation':

https://carolomeetsbarolo.wordpress.com/2012/07/20/catastrop...

Or:

"OOPS XL Did It Again"

https://carolomeetsbarolo.wordpress.com/2014/06/22/oops-xl-d...

From the Wikipedia article:

"Although Excel can display 30 decimal places, its precision for a specified number is confined to 15 significant figures, and calculations may have an accuracy that is even less due to five issues: round off,truncation, and binary storage, accumulation of the deviations of the operands in calculations, and worst: cancellation at subtractions resp. 'Catastrophic cancellation' at subtraction of values with similar magnitude."

That's an issue inherent in floating point computations with fixed precision (such as IEEE 754).

  julia> 1e20 + 1000 - 1e20
  0.0

  julia> 1e20 + 10000 - 1e20
  16384.0

  Python 3.9.6 (default, Jun 28 2021, 19:24:41) 
  >>> 1e20 + 1000 - 1e20
  0.0
  >>> 1e20 + 10000 - 1e20
  16384.0

We never got the results of our covid tests from early in the pandemic because someone imported the medical data into Excel, which predictably and destructively modified the patient IDs by stripping all leading zeroes.

I've used perl, python, and R for scientific data for a really long time but have always made a concerted effort to avoid Excel. My reasoning feels the same as when people say Java is the best language because it can be run on any device, which is like saying anal sex is the best sex because you can do it with any animal.

Maybe I'm missing out on a great experience, but the notion has always made me uncomfortable.

> My reasoning feels the same as when people say Java is the best language because it can be run on any device, which is like saying anal sex is the best sex because you can do it with any animal.

Please tell me you have said this to someone in a work meeting. This is hilarious.

Even if it wasn't used, I may end up using it at work myself.

There is nothing sneaky about it. Excel imports things it thinks are dates as dates. It's not a silent modification. It's a loading issue. Properly loading data into R is not particularly easy either.

So if I have Zip codes with leading 0’s, use R?

>So if I have Zip codes with leading 0’s, use R?

Several other software tools also mess up leading 0s including R if used in a naive way without specifying extra options. My previous comment about this: https://news.ycombinator.com/item?id=25017116

Like R, MS Excel can also preserve leading 0s -- if you specify the option on import. (Click on Excel 2019 Data tab and import via "From Text/CSV" button on the ribbon menu and a dialog pops up that provides option "Do not detect data types" (Earlier version of Excel has different verbiage to interpret numbers as text))

The problem that I've had with this issue and Excel is that Excel performs overly-clever-automagic type conversions in certain situations where you don't want them. And then especially when you're collaborating on files with non-technical normal office workers, they're not expecting this behavior. So the end result has been that this issue has bitten me many times, despite knowing how to ideally tell Excel to handle it.

If you have zip codes, don't store those in a numeric type (doesn't matter if it's Excel, R, or any other system)

Zip codes aren't numbers, they are strings that happen to contain only numeric characters

WAIT. All these words on SO and here have been written because people don't know to change the column type from "General" to "Text"?

They would like to be able to just open a csv file in excel without going through a file import wizard. Changing the column type after the fact is too late.

If you get to the point that you are using r and python, it should be because you are doing working with larger data sets, or it’s specifically sophisticated.

If you can learn a language, you could constrain type conversions easily once you have for knowledge.

Its not like there aren’t painful gotchas in other tools- it’s an issue if you aren’t aware of them and if they impact your work.

If it’s big, unusually complex, you probably want a DB before analysis.

If it’s repetitive, or advanced modeling/ml: python/r

I haven't used Excel since I was a child and learnt to program. I didn't realise just how bad it was. Someone asked for a list of ids for some inventory and they imported it into Excel. They were all 4 digit alphanumeric but the ones that looked like numbers, like "0001" it just treated it as a number, showed it as 1 no option to disable this ridiculous behaviour.

Excel doesn't alter data without being asked either. What it does do by default is try to change how the data is displayed. If the column is properly set to the correct data type the data will be shown correctly because Excel doesn't change the actual data. Column types can be set when csv files are imported.

The problem described in the article isn't an Excel issue. It is an issue of the geneticist failure to learn the basics of how their tools work.

> Excel doesn't alter data without being asked either. What it does do by default is try to change how the data is displayed.

This is easily falsifiable. In a "general" cell, when I enter 0002, it gets changed into 2, not just in display, but in actual content. When I change the cell type to text, it'll still be 2. Only if I enter 0002 after changing the type to text is the content kept.

Similar when I want to have the text 3/17 or SEPT1 in a cell, I have to format it before typing or the data does get altered. If you try setting it to "text" after you typed it, you'll get some number that's not very useful to you.

If you enter it prior to specifying the formatting you are entering it as a date. The number you see when you change to text is the numeric value reflecting the date/data you entered.

Just type ‘0002.

the only way to prevent Excel from converting date-like number values into 'real dates' is to format the number values as text. or escape each and every number value so this doesn't occur.

this is not just a workaround - it's recommended by Microsoft. because you literally cannot turn this functionality off.

how is that not an "Excel issue"?

That is how spreadsheets work. Try Libreoffice or Google Sheets. You will find they operate the same. Spreadsheets format all cells as a generic type where the value entered to it is gets assigned a more specific type by the spreadsheet application. If you want to insure that the value you entered is interpreted as a specific type you need to identify that to the spreadsheet application prior to entering the value.

I was responding to the claim that Excel "doesn't alter data without being asked either." I guess it does, and it should?

It also interprets and coerces values in formulas.

Empty cells are interpreted as zero, which can be downright catastrophic, if the data is just missing.

Reply on news.ycombinator.com