proj-oot-old-150618-ootSpreadsheetNotes1

how can Oot 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?