ideas-computer-jasper-jasperSpreadsheet

how can Jasper reinvent the spreadsheet?

http://baselinescenario.com/2013/02/09/the-importance-of-excel/#

http://news.ycombinator.com/item?id=5198187

spreadsheets:

pros:

• easy to get started
  • "can be "Programmable" to the extent that it needs to be. You don't have to start with code. Formulas and conditionals are great for most things. People usually ease into Macros gently." "You want data entry with some calculations and maybe a few if-then rules here and there? What's better than Excel?"
• easy to share
• see your dataflow (the actual data values, not just a map of the flow)
• can change data at any point and it propagates for you

cons:

• hard to automate testing
• often leads to manual data entry, manual copy-and-paste
• "There is no way to trace where your data come from, there’s no audit trail (so you can overtype numbers and not know it), and there’s no easy way to test spreadsheets, for starters"

misc cool features:

• Formulas/Trace Prededents and Trace Dependents

" I don't see any reason why you can't have the agility of Excel with a few of the nice tools that modern IDEs have to make it easier to debug or secure a spreadsheet "app". Excel actually has a lot of features for preventing issues such as formula debugging, input validation and named ranges that make formulas more readable, but the UI is terrible and it's not very discoverable. The problem with Excel seems to be primarily that the MS desktop monopoly made it very hard for people to market better UIs for the "quick financial model" use case. The only way around that was either in B2B sales (where the economics forced you into building big ERP systems where management locked down the processes) or over the web, where we've had to wait for browser technology to reach a sufficient level of power before such a system could be built. I think since IE9, we're now at that point. It wouldn't surprise me if we now start to see a multitude of apps chip away at Excel's dominance in each of its use cases (like trello is doing for the 'lists of stuff' use case).

reply

georgewfraser 1 day ago

We (Fivetran) are doing exactly this, bridging the gap between spreadsheets and coding and making real algorithms accessible to non-programmers. The primary thing that makes spreadsheets more approachable is the live-updating, and this feature can be separated from the grid-of-cells model with a cleverly designed language. We'll be starting a private beta in the next few weeks, stay tuned.

"

"

An opportunity presents itself to plug a product I helped write version 0.1 of: http://www.glbsoft.com

If your users spend all their time in Excel, why not connect it to the enterprise back end? "

"

Excel and similar tools are never going to go away. It's just got too much utility for that. The best thing we can do is provide spreadsheet users with the same quality of tools as we ourselves are used to.

I know a startup in the Netherlands working on just that, and it's a golden business opportunity with a large untapped market!

http://app.infotron.nl/

http://app.infotron.nl/Home/Blog

http://www.felienne.com/?cat=15 "

"

My company created this: http://www.synapseinformation.com - and is currently used by a leading UK High Street Bank (case study at the site) - it solves the multi-user data sharing problem for Excel - as well as providing automated data integrity in Excel Spreadsheets - we are looking for more users for this - comments/feedback/questions about it would be welcome ... "

" Imagine Excel with a complete audit trail of all keystrokes and spreadsheet interactions. Quite a few problems would be solved.

reply

intended 1 day ago

Many firms have absolutely gargantuan spreadsheets and workbooks.

I suspect that individual files would become humongous with those features added.

reply

michaelfeathers 1 day ago

If you store the commands from the beginning, you can recreate the state at any point in time.

Space gets cheaper every day.

reply

intended 1 day ago

The idea does have merit. On the average case it should be able to work.

A few things may be an issue with implementation, one of which would be excel macros. As it stands excel undo cannot regress past the last use of a macro. I suspect a state list may encounter a similar roadblock.

As it stands excel can be very very slow on large sets of data. On a sufficiently complex and clean model, I've seen load times and computer slow downs which begin to make Tokyo traffic seem mild, if it doesn't just hang. "

" danso 2 days ago

One of the amusing/annoying things I've learned when working with business/finance people is how the spreadsheet seems for them to be a freeform tableau with which to conveniently display an assortment of calculations, as opposed to a structured data format.

For example, I'm inclined to list financial data in this somewhat-normalized format in an Excel spreadsheet:

        Apples	1/10/2013	40	$50
	Oranges	1/12/2013	12	$200
	Apples	1/15/2013	30	$80
	Oranges	2/1/2013	10	$40
	Pears	2/2/2013	50	$100
	Pears	2/9/2013	20	$40

However, people I've partnered with, and who most definitely (I think) have a better grounding in financial math than I do, might structure their spreadsheet like this:

    Apples  1/10/2013   40  $50     Oranges 2/1/2013    10  $40
    Apples  1/15/2013   30  $80     Oranges 1/12/2013   12  $200
    Total Apples:       70  $130    Total Oranges:      22  $240

                                Pears   2/2/2013    50  $100
                                Pears   2/9/2013    20  $40
                                Total Pears:        70  $140

(you can imagine the bespoke text-formatting/cell-coloring that also ends up as part of the spreadsheet)

While I understand that their priority is to not care about data processing...not only is this format extremely annoying to machine parse, but it seems unwieldy for any professional use case. When you want to add more rows to each category, you're going to run into layout problems, for one thing. And at some point, your wrists are going to get a nice dose of carpal tunnel syndrome from all the clicking-to-navigate that you end up doing.

reply

po 2 days ago

Interestingly, Apple's Numbers tries to break people of this (perhaps unsuccessfully) by decoupling the 'sheet' with the column/row/cells grids. You can have multiple cell tables on a single page.

When I first saw this functionality I was really excited by it as a way to keep data consistent and independent of the layout. In reality I think the tooling around creating/moving/linking the cell groups is a bit awkward to use. Perhaps some day it will get there, or someone else will pick up the idea and run with it some more.

reply

justincormack 1 day ago

Lotus Improv did this first. Excel has pivot table support that supports this use case too if people know how to use it.

reply

lloeki 1 day ago

You miss the point, both Lotus Improv and Excel are an (pseudo-)endless wall of cells.

Numbers puts bounded tables on a page[0][1] instead of fitting the pages (and everything, really) into an endless table dating from Multiplan, thus solving the "hacking the cells to implement layout" problem. In this example[2] a table is actually selected, and allows for south, east and south-east extension.

[0]: http://www.file-extensions.org/imgs/app-picture/3615/iwork-n...

[1]: http://maymay.net/blog/wp-content/uploads/2008/09/example-bu...

[2]: http://farm2.static.flickr.com/1400/1050927588_8765bb65a6.jp...

" "

I faced this before. I have a solution: Open up PHPMyAdmin?, show them how it looks like in a proper database.

Then show them how to lock panes in Excel. Problem pretty much solved.

reply

crazcarl 2 days ago

I think danso's problem would also be solved by showing them pivot tables in Excel.

reply

cgio 2 days ago

sumifs

reply

NamTaf? 2 days ago

sumproduct rules my world when it comes to this.

reply"

" robomartin 1 day ago

This is a very interesting article, but I do find it very one-sided. The problem isn't Excel, it's those who use it from a foundation of ignorance or carelessness. No doubt, human error does creep into every human activity. Even so-called experts fumble. It happens to all of us. Blaming the tool or the tool maker is pure nonsense.

I have used the tools mentioned in this article, Excel and Powerpoint, extensively. And I have used them for engineering and business purposes. And, yes, I have made mistakes that have cost me money. Funny enough, never when used for financial applications but rather for engineering.

Over time we developed a rather complex (probably a nearly a hundred columns and two-hundred rows with data and formulas) Excel workbook used to calculate timing parameters and counter reset values for a complex timing and synchronization circuit inside an FPGA. After one crank of the wheel this spreadsheet would output a table of hex values that would get plugged into Verilog code to setup the circuit. Everything worked as expected, or so we thought.

Then, a few weeks later, as the code was being run through test-benches (unit tests) we discovered that a corner case was broken. Being that it was hardware we immediately thought that the problem was the circuit. The code was checked and re-checked, simulated and re-simulated. After about six months of hard debugging work we discovered that the Excel spreadsheet was the culprit. We had used the ROUND() function in a calculation, when, in reality, we were supposed to use ROUNDUP(). Only one of hundreds of formulas contained this mistake. This cost us six months of engineering time and, therefore, lots of money.

Having suffered through something like that I still could not bring myself to blame Excel for the problem. We made a mistake. No, in this case, I made a mistake. And that was that. Blaming Excel or Microsoft would have been nothing less than moronic.

I have used Excel to design polyphase FIR filters, maintain state machine code, produce complex lookup table initialization values for a variety of languages, prepare data for EDA tools, process sensor data and even generate G-code to machine bolt patterns on a Haas CNC mill.

Oh, yes, also invoices, purchase orders and financial analysis.

It's a great tool. And, if you use VBA you can really make it do some amazing stuff.

Powerpoint is another interesting tool. Of course, the most visible application is to create slides for presentations. However, you can also access its automation layer via VBA. And, yes, Visual Basic isn't the most elegant of programming languages, but, who cares, this is about getting a job done, not about authoring monuments to computer science. Anyhow, the most interesting application I found for this was the creation of a full simulation of the physical control panel for a device. The simulation included buttons and even a pretty good simulation of a dot matrix display. It was used during a training presentation in lieu of having the actual physical device there. The menu code was pulled out of the state machine that ran the menu on the actual device (written in C but easily translated to VB). It was really cool.

What surprises me is that financial folks, as related by the article, don't seem to have a system in place to create "checks and balances" on spreadsheets that might actually be used for very serious financial decisions. That's odd, but I can see it happening. Startup opportunity? Maybe.

reply

gruseom 1 day ago

A compelling if painful story. You must have some thoughts about what you could have done to track down the spreadsheet problem sooner – what are they? Also, if you could change Excel in any way you chose, what might be ways for it to prevent or detect such problems?

I'd like to ask about a technicality as well, if you don't mind. You said that ROUNDUP was used in hundreds of formulas and ROUND in only one. How many of these formulas were identical except for relative cell references, as opposed to doing logically distinct things? By "identical except for relative cell references" I mean the kind of formulas you get by selecting a cell and telling Excel to copy its formula across a range -- Excel sees these as distinct formulas, but really they're computing the same thing on different inputs; in APL it would be a single expression. I'm wondering if the bad ROUND guy was identical to some other formula in the sheet, or whether it was a fundamentally new formula that someone typed in and forgot to use ROUNDUP. Really, I guess I'm just wondering how the bad formula happened to get entered.

The reason for that last question is that I'm interested in what a spreadsheet would be like that recognized common formulas across a range and treated them as a single data-parallel computation, rather than a bunch of separate cells that happen to have relatively identical formulas. Clearly this would have performance advantages (parallelism and data locality). But it would also seem to have a better chance at preventing errors, since it would treat "computation X across an array of N cells" as a single thing, rather than N relative copies of X that might get altered separately. Curious to hear your thoughts on that general idea too.

reply

robomartin 1 day ago

Not many people on HN mention APL. I programmed in APL professionally for almost ten years and know (knew?) the language well.

It's too late for me to go dig-up this old spreadsheet. Maybe I'll do it tomorrow. Excel does warn you when there are discrepancies between adjoining cells. If I remember correctly this was a cell that was copied down and used on a row. Thinking back I think the formula was entered once but replicated Dow a range of rows.

The problem with the APL comparison is that the language can become less elegant if the operations to be applied across the rows of a matrix are somewhat varied. I'm typing on an iPad right now so I can't even try to give you an APL example. Maybe tomorrow from my PC.

robomartin 1 day ago

Excel does have array formulas. Every time I used them I have to look up usage. From memory they work well for matrix transforms and such things. Again, my memory fails me because It's probably been five or more years since I've touched them.

I have a busy day today so I have to stay off HN. I'll look into some of the items requiring deeper answers later tonight or tomorrow.

reply

roel_v 1 day ago

"Excel sees these as distinct formulas, but really they're computing the same thing on different inputs; in APL it would be a single expression."

Excel notices when there is one formula that breaks a pattern, and puts a warning sign on that call (my Office 2003 version of Excel does). Now it presumably misses some cases, but the standard 'drag a formula, change one of them' case, Excel saves you from.

reply "

"

Excel is an incredible product, especially if you look at its UI.

• Performance is astounding. Sorting thousands of rows by color? No problem.
• The formula Editor. I edit the cell which holds the formula and it highlights, in color, the data ranges that provide the input for the formula. I can copy the cell, which moves the formula. I can, through drag and drop, expand the data area for input.
• All the little things. The Sum, etc helpers in the status bar. Autoexpand of number ranges.

If you sat down now and tried to replicate it, you would tear your hair out. Best piece of software ever done by MS.

" talaketu 2 days ago

Yes, so excellent that it lets me add a date to USD and format it as a percentage, as I copy and paste that one formula into every row of data.

reply

intended 1 day ago

There is also the occasional - ctrl c, alt es v. And then realize you copied date values along with your data.

Excel is great, until that moment that you do something in haste over a large set of heterogenous data and cells.

And if you are doing finance, a world of pain awaits you.

edit: One other gripe about it, is if you use a macro, you can never use the easy ctrl-z out of whatever mistake you've just made.

Backups for a new analyst are few and far between. Backups for me a short while into my job could happen hourly.

reply

anigbrowl 1 day ago

Well, I didn't say it was strongly typed :-) But you make a good point.

reply "

" fiatmoney 2 days ago

Excel is fantastic if you picture it as the World's Best Visual REPL. There's no doubt that it's overused, but it does occupy a very particular sweet spot. In fact, I think one of the best use cases for something like Light Table is to act as a spreadsheet killer. "

"

DanBC? 2 days ago

Raymond Panko has some interesting writing about spreadsheet errors.

(http://panko.shidler.hawaii.edu/ssr/mypapers/whatknow.htm)

The European Spreadsheet Risks Group has some 'horror stories' (http://www.eusprig.org/stories.htm)

It's an interesting field of research, and you could probably make a bit of money if you can audit unintentional human error; deliberate human deceit; or software errors.

reply

incongruity 2 days ago

While you're at it, I think the piece: A critical review of the literature on spreadsheet errors by Powell, Baker, and Lawson is a valuable read as well. Here's a free copy of it: http://mba.tuck.dartmouth.edu/spreadsheet/product_pubs_files... http://mba.tuck.dartmouth.edu/spreadsheet/product_pubs_files/Literature.pdf

reply

viraptor 2 days ago

I'm really surprised something like EuSpRiG? exists. Then again I'm really happy it does.

reply "

" Regular person here who learned to code using VBA with Excel and Access to build spreadsheets and "dashboards" (oh the suits love "dashboards"!!) to track sales for what grew to be a half-billion dollar book of insurance business. The problem with all the "professionals" is that they take forfuckingever to get anything useful and usable to you when you're trying to run an actual business. Plus, you have to sit on endless conference calls trying to explain to some "professional" why you want to see the data the way you want to see it, not the way that's easiest for them to program. And building out the specs. And following their painful changelog process. And waiting some more. And know they're getting paid more than you even though a lot of the time they leave at noon on Friday.

If you want people to use your "professional" code, learn something about how to get it done now. The people who are making it happen right now don't have time for all this. Not everything needs to be engineered - not even the small chunks of the global financial system I have dealt with. "

"

smtddr 2 days ago

Say what you will about Microsoft, but Excel is amazing.

I'm no genius, but during my corp years in $LARGE_PHONE_COMPANY the excel macros I was able to write, interfacing with SAP-HR/BW and HP's Quality Center, was a great time-saver and made a lot of things very stream line and less error-prone. All the COM objects that are available to VBscript create crazy possibilities that, really, you could start a whole company just writing macros to stream-line work for other corps.

I really should look into that kind of thing in Open/LibreOffice? now that I'm more at home in Linux.

reply

toyg 2 days ago

you could start a whole company just writing macros to stream-line work for other corps

Oh, quite a lot of companies started that way. Many of them still do it exactly like that.

Worse: for a while the trend was clearly "all these Excel sheets are terrible, let's replace 'em with <SHINY_NEW_CENTRALISED_WEB_SYSTEM>!", but now established web-based software products are busy touting their wonderful Excel add-ins that seamlessly push data back-and-forth from spreadsheets.

reply

ConstantineXVI? 2 days ago

Developer tools have always been Microsoft's sweet spot. Excel just happens to be targeted at non-developers.

reply

roel_v 1 day ago

"I really should look into that kind of thing in Open/LibreOffice? now that I'm more at home in Linux."

You can start with that now, and be done with it by tea time...

reply

mdda 1 day ago

{not sure if sarcasm detected}

Since LibreOffice? embeds macro writing in Python, the environment is pretty rich - even compared to the mighty Excel VBA...

reply "

" rossjudson 2 days ago

Spreadsheet verification and type systems have been studied extensively by the rather terrifyingly productive Martin Erwig. See http://web.engr.oregonstate.edu/~erwig/papers/abstracts.html, and scroll down the page.

"Software Engineering for Spreadsheets" "Automatic Detection of Dimension Errors in Spreadsheets" "Type Inference for Spreadsheets"

etc.

reply

ig1 2 days ago

Excel has far worse problems in the financial sector than this. Among other things Excel's thread-safety is flakey, which when you're importing a realtime price feed into excel turns out to be fairly important.

Most of the time your spreadsheet even if it has perfect logic will fine, until some weird race condition gets hit the price being loaded gets transposed and you model gives you a wildly wrong answer which you then trade on. "

" 1. No audit trail 2. No workflow of how you go from the original data to the calculation. This is very important, you need to tie to the original source of data ALWAYS 3. You use crappy templates 10 years old with things nobody knows anymore 4. You can't read well .txt files and you can't process big amounts of data (what if you want to test for frauds in petty cash in a 20 billion multinational? For example)

I mean, there are tons of inefficiencies, the points are 2:

1. How you sell your shiny new perfect product to people used to abuse data in this way? 2. How do you build such a product from a programmer background? And here I have my rant about ideas not being useful, they are if they come with lots of market insight focus.

And that's about it. I think, coming to your point: yes, there are inefficiencies, no, you will not get there if you don't have insight into the specific profession you are trying to provide tools to.

reply

mickeyp 1 day ago

Excel and Word do have a lot of these "paper trail" functionalities you talk about; in fact, Excel has amazing database support thanks to ODBC as well -- you can easily separate your business logic from your data by using Access and Excel together.

So how come users still manually set the font size for their headings instead of using styles; how come they ignore Track Changes; why do they copy spreadsheets instead of enable the Share Worksheets functionality -- and so on -- well, it's simple: they don't know about them. Most Excel/Word users are self taught or are given a very rudimentary training course by their peers or one of those cash-generating "Certificate Farms" you pay out the nose for.

I definitely agree that to solve the Excel woes you're going to have to write domain-specific software; Excel's already the Swiss army knife. "

"

From the interesting submitted article, "After subtracting the old rate from the new rate, the spreadsheet divided by their sum instead of their average, as the modeler had intended. This error likely had the effect of muting volatility by a factor of two and of lowering the VaR? . . ."

This astounding story is a really nasty example of why "always comment your code" is a good idea--but only if someone else with knowledge of business logic reviews the code.

reply

rbanffy 2 days ago

Good luck commenting your Excel formulas...

reply

nhebb 2 days ago

Right-click on the cell and select Insert Comment.

reply

mdda 1 day ago

But this is really the nub of the problem. By default (and most of the time), Excel hides the formulae and comments - requiring clicking into cells to figure out what's going on. By only exposing a glimpse of the whole model at a time, it's very difficult to get an overview of how the model interacts.

reply

bgilroy26 2 days ago

I don't understand what you've said here.

reply "

"

Kurtz79 1 day ago

Very interesting article, but I belive one of the main assumpion is "Excel makes it easy to mess it up" because it's so easy to use that most non-technical people can use it, in a non-professional, careless way.

My question is : would the error in the model have been caught if the model would have been written in a more technical mean, say an R script ?

Excel does not give any feedback if the "error" is conceptual, but so it happens with more technical programming languages and environments. Excel puts out "errors" if you write a wrong formula, the equivalent of a syntax error for a programming language.

On the other hand, one would think the "on your face" presentation and ease of use makes it easy for other (non technical, but skilled in finance/math) people to review the numbers and calculations.

I agree with the author that I the fault lies in too many people trusting blindly the model, without reviewing the numbers/calculations, but I'm not sure Excel can be pointed out as the culprit.

reply

psionski 1 day ago

I wanted to say "a language with units of measure wouldn't have allowed this", but in this case it would have... I guess sometimes even the most restricted tools can't save you :)

reply

antirez 1 day ago

Excel is the best tool non programmers have in order to write code, that's why it wins. This is one of the problems startups should focus on in order to change the world, because making non programmers more able to program really changes the world, as Excel did, but is possible to improve over it of course.

reply "

"

The popularity of Excel speaks to what most common enterprise software lacks- flexibility and programmability. It's hard to construct what-if scenarios in most software.

In some ways, what has been the evolution of Excel in our company has been to switch to Google Spreadsheets, where you get the audit history, sharing, and version control solved, you can build web forms in less than five minutes to have people submit data, you get a decent amount of programability and web service API.

reply

mattmcknight 2 days ago

A second thought that occurs to me is why it is so important for business software to be flexible, is that it is important for the business to be flexible. If there is no way for a trader or an analyst to create a new model, or a new process, the current models and processes ossify. Once you go to SAP, your process starts to flow like sap.

reply "

"

mifeng 1 day ago

I used to build structured credit risk models at JPMorgan and other banks. A few observations:

• Excel models are usually built on a pre-existing template, probably created by someone else. Any errors/hidden assumptions carries through. This is he biggest source of errors.
• Any large model usually has a tab devoted to reference tables and assumptions based on external sources, a la dependency injection. Usually you have to use static numbers since you need to email the model around (you don't want your boss seeing a bunch of #NAs due to missing links. Keeping this data updated is another huge source of errors.
• A sanity check is your best defense. I'm positive that every CDO model I've ever built has contained at least one error, just like every large software application has at least one bug. The difference is that models have fewer output parameters, which allows you spot incongruities more easily.
• People who use big Excel models are not software engineers and will never be. In my opinion, solutions that implement concepts like testing, diffs, commits, etc are a waste of time, because it creates extra work and learning curve. "

" > One of the all-time most popular programming models is the spreadsheet. A > spreadsheet is the dual of a conventional programming language -- a language > shows all the code, but hides the data. A spreadsheet shows all the data, but > hides the code. Some people believe that spreadsheets are popular because of > their two-dimensional grid, but that's a minor factor. Spreadsheets rule because > they show the data. "

" nandemo 2 days ago

Reminds of this paper by Simon Peyton-Jones at al.:

Improving the world's most popular functional language: user-defined functions in Excel

http://research.microsoft.com/en-us/um/people/simonpj/Papers...

reply

Evbn 2 days ago

That was kind of a PR gimmick. Excel is declarative, but misses a lot of functional. For example, first class functions.

reply

gruseom 1 day ago

Can you imagine a spreadsheet with first-class functions, and possibly other functional features? What would such a thing look like?

Strictly speaking, Excel doesn't have functions at all, let alone first-class, since you can't define a new function using only cells and formulas. You have to go to VBA.

reply "

"

reply

thirsteh 1 day ago

Indeed. Some people have even used e.g. Haskell to provide more "real" logic, but with Excel as the user interface:

http://qconlondon.com/london-2008/speaker/Lennart+Augustsson

http://urchin.earth.li/~ganesh/icfp08.pdf

reply

" Quite simply, the financial world runs on Excel. For this reason we make sure all our APIs work just as well when being called from VBA as they do from Python, JavaScript?, Perl, etc. It's Excel's world, we just live in it. "

"

richardlblair 1 day ago

If you doubt the power of Excel, try to do the same thing in Numbers... I'll just wait here.

reply

Toenex 1 day ago

One limitation of the spreadsheet approach to rapid application development is that, in typical use* your code and data tend to be tied together. This creates a testing problem where you tend to confirm results in just one scenerio. I prefer to think of spreadhseets as electronic squared paper.

• while you can structure things to pull data in from other sources, in my experience people evolve a spreadsheet around existing data.

reply

protomyth 1 day ago

I've always wondered why someone hasn't done a Excel->C# or Excel->Java cross-compiler.

reply

taylorwcbrown 1 day ago

Hey Protomyth. Great thought. We are building a Java cross-compiler right now. We will be launching in a few weeks. Check it out fivetran.com

reply "

"

memracom 1 day ago

There has been an annual conference in London for quite a number of years all about spreadsheet mistakes and how to avoid disaster. A few years ago, some entrepreneurs from resolversystems.com built a better spreadsheet named resolver-one specifically to address the problems of the financial services industry. Essentially what it does is write code while you build a spreadsheet model. Then, when you recalculate, it runs the code. Once a model is built, it can be passed on to professional software developers to QA the code, document it, refactor it and build proper unit tests. However the spreadsheet model still appears to end users just like a normal spreadsheet model. I think this is the real solution to this problem. Use Excel to play around with numbers and do one-off analyses, but for serious business rely on a tool like Resolver One.

reply

tanzam75 5 hours ago

Resolver One was a great idea, but it has been discontinued:

"But while many people did buy Resolver One, we struggled to turn it into a commercial success. The vast majority of people are happy with either Microsoft Excel, or one of the free alternatives available from Google and the like. Since mid-2011, all of our development focus has been on a new product, PythonAnywhere?."

http://www.resolversystems.com/eol.php

reply

mickeyp 1 day ago

I think the main issue has always been that "The Business" -- that nebulous, supposedly all-knowing corporate entity -- never wanted their employees to rely on a spreadsheets stuck on somebody's shared network drive. The issue has never been "formalisation" -- we already have tools for that, though they could surely do with improvement -- but the ad hoc nature that is, over time, festooned with more and more features or blessed as the Official way of doing something -- and when that happens, it's not going anywhere; it's too late to change.

reply

sysmod 20 hours ago

That's the annual conference of the European Spreadsheet Risk Interest Group, always noted for the lively debates between academic researchers and hardened practitioners.

http://www.eusprig.org/call-for-papers.htm

University of Greenwich, London, on the 4th & 5th July 2013

reply "

"

Eric_Wells 8 hours ago

The problem isn't Excel.

The problem is that those who are developing complex Excel models are not necessarily taking into account the need for solid design, rigorous testing and model validation. The quant generally building an Excel model is generally thinking "how can I get this model built and running as quickly as possible?" Whereas, a software developer who is used to bugs, and the negative impact they cause, might take a more guarded and gradual pace - with the realization of the importance of testing.

Neither Excel nor the quants that use it in a fast, ad-hoc basis are going away. And so I believe the solution involves application of good design, QA and validation to the Excel modeling process.

reply

panko 9 hours ago

Spreadsheet error commission and detection rates are almost identical to those in programming, mathematics, and writing. And professionals make about the same number of errors as novices. What is different in spreadsheet development is the amount of testing. At companies like Microsoft, 40% of development time is spent in testing, and their usually is one tester for each Dev. In spreadsheet development, professional testing is rare. Here is a link on human and spreadsheet error. https://dl.dropbox.com/u/20338447/WhyThinkingIsBadPublicCopy...

reply

"

"

BackStar? 1 day ago

Check out the concept of 'enterprise spreadsheet management' as a different way of thinking about the issue of Excel in critical business environments.

This perspective recognises that nothing beats Excel as a source of financial innovation and 'can-do'. The problem is that few want to recognize this essential role. The large software vendors need to demonize the issue to sell licences to their replacement solutions. The result is denial at every level.

Much better to appreciate the core strategic value that Excel offers and not confuse it with the essentially tactical value that an individual spreadsheet offers. (Though a tactical spreadsheet can be valuably tactical for 10 years).

The answer is transparency. Once one buys in to wanting to know what is going on then enterprise spreadsheet management can really help.

reply "

"

Jim Dikelow

There is a rich literature (accessible through Google Scholar) on the deficiencies of Excel for ststistical calculation and the unwiilingness or inability of Microsoft to fix them. Much better is the open source (and free) Excel clone, Gnumeric. "

"

Dan Palanza

Let me add to Moses Herzog’s critique: Your topic today, whether you know it or not, is a bookkeeping problem/solution pattern. I discovered, 35 years ago, when I bought my first microprocessor-driven computer, that it is impossible to create a proper double-entry book-of-accounts using any spreadsheet, no matter how it is designed.

There are four fundamenta variables in a proper book-of-accounts: debtor-value, creditor-rights, cash, and capital. Pacioli calls the bookkeepers attention to these four variables in 1494 A.D. The four are isomorphically related to one another within the contract that the bookkeeping sets out to resolve; read as “sets out to balance.”

Until these bookkeeping fundamentals are understood by the software community, and the accounting software designers know how to create the four-way isomorphic balance by using a serious programming language, the banks will continue to steal from the monetary system.

Right now, James, your above essay has no right to take up the bandwidth that you are wasting by your not knowing where first base is located in the playing-field of software-driven accounting. "

" I don’t use Excel except when I am required to. There’s several reasons for this. First off, it’s ability to do complex mathematics is incredibly poor. If you’re not dealing with something that was linear in the first place Excel is not the best program for it. If you’ve got phenomena operating in opposing directions it’s not terrible if you can linearize them and run in very small steps. But if you’ve got coupled equations that aren’t necessarily positively or negatively reinforcing, Excel should be last on the list.

Second problem is documentation. Excel spreadsheets are notorious for being poorly documented. Shoot, it’s difficult to see who came up with it in the first place, then you’ve got to comb through the attached VBA and the cell formulas to find it.

Third, mobility of data. I’ve seen many instances where Excel doesn’t correctly or consistently pull from a database or update the database correctly. It was certainly a matter of poor coding, but Excel doesn’t seem to consistently write to databases from machine to machine.

What Excel is pretty good for is dumbed-down visualization, ie, when you’re presenting technical stuff but need to make it ultra pedestrian for the business school guys, nothing’s better than Excel’s cartoonish graphics. "

"

aronjohnson

I totally disagree with this disgusting comment!!! Excel has several powerful tools although it’s solver function could be improved for non linear responses and coupled models with over 4 degrees of freedom (a convergence issue). For after tax cash flow analysis (in Engineering Economics class), I once programmed in Excel the entire IRS Depreciation schedule with just two inputs and a lot of conditional logic. (I found an error in the published tables which they fixed.) It does not do PDE’s which economists seem to love and practical engineers regard as oversimplifications of reality since most of them ignore friction (economic parallel??). It doesn’t do computational fluid dynamics either but we have good programs like Fluent which do. In addition to the wonderful flow visualizations, they provide the data we need in spreadsheet format for further analysis. I like Excel because I can check the individual calculations which are hidden by most programs. "

"

Jack

Blaming Excel is like blaming guns for gun crime. There are many technical ways to potentially overcome the ‘copy and pasting’ problem. For example using templates, locked cells/sheets, Excel Services, document-level customizations etc. "

"enterprice spreadsheet management"

"

Raj Nagel

There is also spreadsheet process management software that exists to leverage existing spreadsheets, renovate them and automate them into a repeatable, auditable process. Kind of like an alternative to a full enterprise system that removes excel entirely.

"

http://www.eusprig.org/

sh

Attempts have been made to improve the spreadsheet concept… like http://www.ankhor.com/en/visual-analysis-and-dataflow-programming It is possible to get a complete audit trail there because the processing steps and all intermediate results are fully observable…

ExcelGuru?

Not sure why no one has mentioned that there are solutions out there to deal with Excel. In the last two investment banks I’ve worked in we’ve used http://www.clusterseven.com and there are other solutions out there as well. The lack of interest in spreadsheet management solutions reminds me of a quote from the hitchhikers guide to the galaxy:

“There’s no point in acting surprised about it. All the planning charts and demolition orders have been on display at your local planning department in Alpha Centauri for 50 of your Earth years, so you’ve had plenty of time to lodge any formal complaint and it’s far too late to start making a fuss about it now. …”

tvaught

Two points: 1. without a proper version control system, “you’re not doing it right.” 2. Excel does serve as an incredible UI model for exploring data, but because you can’t audit/review code easily, using it for anything more than prototyping is dangerous. So, just use Python, and you can solve both problems. There are enough scientific computing shops that use Python these days that there is no longer any excuse for doing it wrong.

 brenda | February 12, 2013 at 7:28 am |

You should familiarize yourself with current versions of excel, powerpivot and powerview before you make such negative assertions

"

@satovey nails it. The process that lead to the mistake could have been automated in a couple of days at most – cut and paste automation in PowerShell?, for example, is simple and easily tested. The article does not detail the makeup of the team reviewing the model but it seems pretty clear that they didn’t actually stress test the model – changing values and looking for expected v. actual movement in the parameters seems pretty fundamental to me and would have caught the error as reported immediately.

"

" Mr. Unexpec

Love the idea that Excel’s ubiquity is only due to everyone being too much of an idiot to use “real” “software”. By which I assume everyone means software that is delivered months late, horribly over budget, bug-riddled, and still can’t do the simple damn math right. Stupid users, trying to find answers to things! Most of them lose like 6 billy a day! "

---

a spreadsheet as a really really good IDE

---

hmm, how would you reimagine a spreadsheet for a smartphone?

i think this idea has legs.

• you dont have much space so you cant see many cells at once -- so you should be able to create hierarchies to navigate to cells (but heterarchical/faceted classification, e.g. multiple paths to a given node from the root)
• you dont have much space so you cant see many cells at once -- so has to be really really ez to make all sorts of graphs because you cant look at numerical patterns manually
• mb use grid of icons, like with Android app list, to select fns or operations in certain cases?
• it will allow people to truly do new numerical analyses on the spur of the moment during conversations using their mobile phone
  • this means it has to be a really good scientific calculator too
• mb use some jasperish ontology stuff to generalize functions into 'apps' which ask you to map things into roles, creating defaults using standard ontologies if you've tagged your variables ('cells') with them, and then perform some analysis on them. E.g. if you run the app 'present value of cash flow', it tries to guess which variables might be the cash flow predictions using the ontological labels. If you run the app 'plot of predicted profits over time' it makes a line chart of profits over time. Etc.

---

again back to the 'programming without loops (subturing)' stuff: maps and folds come to mind; most of what you'd want loops for in a spreadsheet can be done with maps and folds

i guess one key realization in why it gets so far without loops (not that spreadsheets dont let the user program loops, maybe they do, but it does seem to be outside their comfort zone) is that spreadsheet aren't supposed to be used for CONTROL; they are supposed to be used for data analysis. so dataflow/pipelines of processing steps make sense; writing a server (which requires an infinite loop to listen for the next response; can be thought of as an infinite loop over an infinite series of cells (Turing tape) representing inputs over time) does not make sense; writing a function to calculate pi or e does not make sense (again, not that spreadsheets dont have the exp() fn, or the sqrt fn, they do, but the point is that the implementation of those functions in a spreadsheet itself would be outside the sweetspot of the spreadsheet)