proj-oot-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?