Spreadsheets R Toast

Ivan Schneider, Writer, specializing in financial technology | 1/7/2013 | 21 comments

Ivan Schneider
Spreadsheets were fine in the 1980s -- and at the time, they represented an innovative solution to a wide range of do-it-yourself computing challenges. You could see what you were doing on one sheet, perform calculations, write scripts, and so on.

But the problem with spreadsheets is that the visual presentation constrains our mental picture of the data within a two-dimensional space. Sure, you can add worksheets and "Save as" with last year's data. But once you start adding dimensions to your rows and columns outside what you can comfortably fit on a single screen, you start to lose track of the plot.

You start cutting-and-pasting, there's data everywhere, your formulas become more and more opaque, and before you know it, you're the only one in the world who can understand what you're doing. Rather than audit your work, your supervisors instead begin to trust you, and that's when you might start taking chances with the firm's money. From there, it's not too long before regulators are trying to figure out how your spreadsheet managed to destroy billions of dollars in a bank's market capitalization due to an unsupervised gamble.

A useful concept in programming involves maintaining separate layers. Keep the data away from the business logic, and keep the business logic away from the presentation. Each layer should stand on its own. If you make a change to the data, you shouldn't have to change the business logic, and if you change the business logic, you shouldn't have to change the presentation logic. (Here's a chart from Microsoft that shows how the separation concept works.)

Excel makes it far too easy to keep the data, business logic, and presentation layers in one place. On a single worksheet, you can have some cells containing data, other calculated cells with your business logic, and an adjacent chart acting as your presentation layer. Although there are many ways to do things in Excel, the blank ledger of a new spreadsheet invites people to use Excel in a very specific way, which is also the easiest and most intuitive way, and that is to pile everything into one big scratchpad.

What's more, when it comes to rigorous statistical analysis, there are known issues involved with using Excel. As the ability to perform analytics on large data sets becomes more important, the short-term benefit of ease-of-use gives way to the long-term benefit of learning an environment purpose-built for analytics such as R, a powerful environment for statistical computing.

An analogy:

Modeling data in Excel is like cooking a meal using whatever happens to be in your refrigerator, and then serving up the completed dish to your friends. They may like your cooking, but if they have any questions about whether you used dairy, gluten, shellfish, or peanuts, they're on their own because you don't even know what's in there.

By contrast, using R is like sending someone not only a cooked meal that's ready to be popped into the microwave, but also a full recipe with step-by-step instructions detailing exactly how you made it, plus a grocery bag containing all of the required ingredients should they wish to modify the recipe to their own tastes. Even if you hacked around for hours looking at a dataset from every which way, the precise steps that you took along the way are visible, traceable and auditable. That's good for risk managers as well as people with food allergies.

Certainly, writing a repeatable recipe is more difficult than hacking around in the kitchen until it looks right. However, it's precisely that discipline that underscores the potential benefit of using R in business settings.

Don't let the complexity scare you off. If you work with data, you owe it to yourself and your career longevity to learn this new set of tools. If you're interested in making the switch from the spreadsheet to an interactive development environment for statistical computing, visit the R-Project site, take a course, try this Excel-to-R toolkit, and let me know how it goes.

View Comments: Newest First | Oldest First | Threaded View
Page 1 / 3   >   >>
DBK   Spreadsheets R Toast   1/23/2013 10:53:16 PM
Re: jeepers!
stotheco - I agree there is still plenty of room for spreadsheets.  The are easily created and cusstomizeed for buinsess applications.  Especially small to mid sized and project based.  Being able to load calculators to generate proposals is key to many existing companies.  It might change at some time but not right away.
singlemud   Spreadsheets R Toast   1/21/2013 10:03:33 PM
Re: about time
I ever used R and Excel. They server on difference purpose. R is good to make math model and analysis. Excel is good for different team communication. Since almost everyone has excel installed on his computer. A graphics excel will be perfect.
Pubudu   Spreadsheets R Toast   1/11/2013 2:11:26 AM
Re: Tinym
Yes, World and Publisher is not god for high resolution professional printing. What I met is compared to PowerPoint World and Publisher is much easier to use.
Susan Nunziata   Spreadsheets R Toast   1/10/2013 10:53:36 PM
I Hope U R Right
Thanks for a great post Ivan. Love this: "You start cutting-and-pasting, there's data everywhere, your formulas become more and more opaque, and before you know it, you're the only one in the world who can understand what you're doing."

In working on research projects, I've been on the receiving end of these. I call it "when good spreadsheets go bad." Not only is it cumbersome, it also interferes with the ability to have historical contiunity of information in your organization. If the person who created that complex spreadsheet is the only one who really understands it, and that person departs, the resulting material ends up being pretty useless.
kstaron   Spreadsheets R Toast   1/10/2013 12:09:15 PM
about time
While I love a good spreadsheet,  Excel has always had some issues, espeically with their mathmatical formulas. And don't even get me started on Works. Being albe to have something that provides tyou with a bit more transparency is almost always going to be helpful. For anyone that has used R over a standard spreadsheet, what are the big differences you feel will make this the new tech?
tinym   Spreadsheets R Toast   1/9/2013 11:05:45 PM
Re: Tinym
I assume people chose these programs because they were familiar. However, Word and Publisher are only slightly better but even those two aren't suitable for high-quality commercial printing.
Pubudu   Spreadsheets R Toast   1/9/2013 1:20:34 AM
Re: Tinym
Tiny; Do you have any idea why they use power point to design latter head and others while they have Microsoft word or Microsoft publisher in new package. What I feel is if someone can work with power point, he will be able to work with other packages also. Microsoft office package is very user friendly office package which use lot of GUI.
tinym   Spreadsheets R Toast   1/9/2013 12:32:28 AM
Re: jeepers!
I would join the movement! Did you know some people use PowerPoint to design their company letterhead and business cards? They expect this stuff to print well on an offset press but it doesn't. I've seen other design work done in Excel as well. Microsoft Office may have easy-to-use tools but they certainly aren't right for every job.
Sara Peters   Spreadsheets R Toast   1/8/2013 10:50:23 AM
Re: jeepers!
@Technocrat  Well said:  "I think Ivan takes it to another level and considers the demands of big data, which I am certain Excel is poorly suited for."  If we're going to keep up with these new demands we need to stop trying to force old tools to handle them. Otherwise we're creating a recipe for large errors at best and large frauds at worst.
Sara Peters   Spreadsheets R Toast   1/8/2013 10:42:12 AM
Re: jeepers!
@Ivan  Fascinating. Maybe we should get those people in the Anti PowerPoint Party to start a political movement against Excel too.
Page 1 / 3   >   >>


The blogs and comments posted on EnterpriseEfficiency.com do not reflect the views of TechWeb, EnterpriseEfficiency.com, or its sponsors. EnterpriseEfficiency.com, TechWeb, and its sponsors do not assume responsibility for any comments, claims, or opinions made by authors and bloggers. They are no substitute for your own research and should not be relied upon for trading or any other purpose.

More Blogs from Ivan Schneider
Ivan Schneider   10/30/2013   29 comments
Sears and Whole Foods will take a go-slow or no-go approach to microlocation services, executives from the companies said during a panel discussion Monday at the Seattle Interactive ...
Ivan Schneider   10/15/2013   8 comments
Alex S. Jones, lecturer on the press and public policy at the Harvard Kennedy School of Government, recently spoke to Harvard alumni in Seattle, and he pointed out that the invention of ...
Ivan Schneider   9/19/2013   23 comments
In response to the expanded range, frequency, and ferocity of cyberattacks against financial institutions, the financial services industry has upped spending considerably on ...
Ivan Schneider   9/11/2013   9 comments
Central clearing and other Dodd-Frank Act regulations have made it more expensive for the banks and brokerages to manage the tangled processes involved in post-trade activity: matching, ...
Ivan Schneider   8/13/2013   7 comments
With virtualization driving IT servers and storage toward a cloud-powered market for IT services, CIOs have to prepare themselves for what it means to be a buyer of commodities in a global ...
Latest Archived Broadcast
In this episode, you'll learn how to stretch the limits of your private cloud -- and how to recognize the limits that can't be exceeded.
On-demand Video with Chat
IT has to deploy Server 2012 in a way that fits the architecture of its application delivery system.
E2 IT Migration Zones
IT Migration Zone - UK
Why PowerShell Is Important
Reduce the Windows 8 Footprint for VDI
Rethinking Storage Management
IT Migration Zone - FR
SQL Server : 240 To de mémoire flash pour votre data warehouse
Quand Office vient booster les revenus Cloud et Android de Microsoft
Windows Phone : Nokia veut davantage d'applications (et les utilisateurs aussi)
IT Migration Zone - DE
Cloud Computing: Warum Unternehmen trotz NSA auf die „private“ Wolke setzen sollten
Cloud Computing bleibt Wachstumsmarkt – Windows Azure ist Vorreiter
Like Us on Facebook
Twitter Feed
Enterprise Efficiency Twitter Feed
Site Moderators Wanted
Enterprise Efficiency is looking for engaged readers to moderate the message boards on this site. Engage in high-IQ conversations with IT industry leaders; earn kudos and perks. Interested? E-mail:
moderators@enterpriseefficiency.com
Informed CIO: Dollars & Sense: Virtual Desktop Infrastructure
Cut through the VDI hype and get the full picture -- including ROI and the impact on your Data Center -- to make an informed decision about your virtual desktop infrastructure deployments.

Read the full report
Virtualization Management: Time To Get Serious
Welcome to the backside of the virtualization wave. Discover the state of virtualization management and where analysts are predicting it is heading

Read the full report
PUBLIC SECTOR RESOURCES
WHITE PAPERS
A Video Case Study – Translational Genomics Research Institute
e2 Storage Video


On the Case
TGen IT: Where We're Going Next

7|11|12   |   08:12   |   10 comments


Now that TGen has broken new ground in genomic research by using Dell's storage, cloud, and high-performance computing solutions, the company discusses what will come next for it and for personalized medicine.
On the Case
Better Care Through Better Communications

6|6|12   |   02:24   |   11 comments


The achievements of the TGen/Dell project could improve how all people receive healthcare, because they are creating ways to improve end-to-end communication of medical data.
On the Case
TGen IT: Where We Are Now

5|15|12   |   06:58   |   6 comments


TGen is breaking new ground in genomic research by using Dell's storage, cloud, and high-performance computing solutions.
On the Case
TGen IT: Where We Were

4|27|12   |   06:45   |   10 comments


The Translational Genomics Research Institute wanted to save lives, but its efforts were hobbled by immense computing challenges related to collecting, processing, sharing, and storing enormous amounts of data.
On the Case
1,200% Faster

4|18|12   |   02:27   |   12 comments


Through their partnership, Dell and TGen have increased the speed of TGen’s medical research by 1,200 percent.
On the Case
IT May Improve Children's Chances of Survival

4|17|12   |   02:12   |   8 comments


IT is helping medical researchers reach breakthroughs in a way and pace never seen before.
On the Case
Medical Advances in the Cloud

4|10|12   |   1:25   |   5 comments


TGen and Dell are pushing the boundaries of computing, and harnessing the power of the cloud to improve healthcare.
On the Case
TGen: Living the Mission

4|9|12   |   2:25   |   3 comments


TGen's CIO puts the organizational mission at the heart of everything the IT staff does.
On the Case
TGen Speeding Up Biomedical Research to Save More Lives

4|5|12   |   1:59   |   6 comments


The Translational Genomics Research Institute is revamping its computing to improve speed, storage, and collaboration – and, most importantly, to save lives.
On the Case
Computing Power Helping to Save Children's Lives

3|28|12   |   2:13   |   3 comments


The Translational Genomics Institute’s partnership with Dell is enabling them to treat kids with neuroblastoma more quickly and save more lives.
Tom Nolle
How Deep Is My Storage Hierarchy?

7|3|12   |   2:13   |   5 comments


At the GigaOM Structure conference, a startup announced a cloud and virtualization storage optimizing approach that shows there's still a lot of thinking to be done on the way storage joins the virtual world.
E2 Interview
What Other Industries Can Learn From Financial Services

6|13|12   |   02:08   |   3 comments


We asked CIO Steve Rubinow what CIOs in other industries can learn from the financial services industry about datacenter efficiency, security, and green computing.
E2 Interview
Removing Big-Data Flow Bottlenecks

6|12|12   |   02:55   |   No comments


We ask CIO Steve Rubinow what pieces of financial services infrastructure need to perform better to get traders info faster.
E2 Interview
Getting Traders the Data They Need

6|11|12   |   02:04   |   1 comment


We ask CIO Steve Rubinow: What do stock market traders need to know, how fast do they need it, and how can CIOs get it to them?
E2 Interview
Can IT Help Fix the Global Economy?

6|8|12   |   02:32   |   2 comments


We ask CIO Steve Rubinow whether today's IT can help repair the global economy (and if IT played any role in the economy's collapse).
E2 Interview
More Competitive Business via Datacenter Strategy

5|4|12   |   2:46   |   1 comment


Businesses need to be competitive, yet efficient, and both goals affect datacenter design.
E2 Interview
The Recipe for Greater Efficiency

5|3|12   |   3:14   |   2 comments


Intel supplies the best ingredients to drive greater datacenter efficiency and support new compute, storage, and networking needs.
E2 Interview
Datacenters Enabling Business Transformation

5|1|12   |   06:37   |   1 comment


Dell’s Gaurav Chand says that for the first time ever datacenter technology is truly enabling all kinds of organizations to transform their business and achieve new objectives.
Tom Nolle
Cloud Data: Big AND Persistent!

3|28|12   |   2:11   |   10 comments


We always hear about "Big" data, but a real issue in cloud storage is not just bigness but also persistence. A large data model is less complicated than a big application repository that somehow needs to be accessed. The Hadoop send-program-to-data model may be the answer.
Tom Nolle
Project Lightning Streamlines Storage

2|16|12   |   2:09   |   2 comments


EMC's Project Lightning has matured into a product set, and it's important, less because it has new features or capabilities in storage technology and management, than because it may package the state of the art in a way more businesses can deploy.
Tom Nolle
Big Data Appliance Is Big News

1|12|12   |   2:18   |   No comments


Oracle's release of a Hadoop appliance for Big Data may be a signal that we're shifting to database appliances.
Tom Nolle
Myopia Can Hurt Storage Policy

12|22|11   |   2:08   |   No comments


We're at the beginning of a cloud-driven revolution in storage, but Oracle's quarter shows that enterprises are hunkering down on old concepts because they're afraid of the costs in the near term.
Sara Peters
An Untrained User & a Mobile Medical Device

12|19|11   |   2:43   |   11 comments


Untrained end users, clueless central IT staff, and expensive mobile devices are a worrisome combination for healthcare CIOs.
Tom Nolle
Too Many Labels on 'Big Data'?

12|9|11   |   2:12   |   3 comments


However you label it, structured and unstructured information are different and will likely always require different tools.
Sara Peters
E2 Debuts New Storage Section

12|8|11   |   1:51   |   1 comment


Need strategic guidance on everything from SSDs to 100 percent virtualized datacenters? Look no further.