Visualise the structure of a spreadsheet
slateforexcel.com
slateforexcel.com
I think you'd want to target non-programmers who use a lot of excel (so people in "business") and just bring them more debugging features. An excel IDE would be an ambitious potential next step.
But when I'm offsetting to an array upon which I'm selecting and summing a subset of these cells with an if-driven sumproduct, and maybe dynamically looking up all of the ranges with more index/matches.... its impossible to look at in the little formula tracing box.
I can't wait to try this out.
Don't mean to hijack the thread, but I've been toying with the idea of having users use a data flow diagram to structure complex spreadsheets. Still have a pre-prototype stage and I'ld love to get your expert feedback on that idea. If interested please drop me a note.
Also, uploading would make it an instant non-starter for me, due to data sensitivity issues.
Really looking forward to trying it out.
So say I reference the value "Total Sales" which is in another sheet. That would show in the the tree but the individual sales that contribute to the total would not be shown.
We wanted people to know where the numbers are coming from but at the same time keep the trees for different sheets and files separate to prevent them becoming too unwieldy and hard to understand.
I highly recommend watching the screencasts, especially the demo:
http://www.subtext-lang.org/demo1.html
I have wanted a graphical interface for functional programming in environments like Excel for a long time. In my experience, most of the pseudo-programming that has dominated software engineering (object-oriented/MVC/message-based, patterns, best practices) goes away when you can model something directly.
Nobody has been able to successfully apply proven engineering concepts from, say, electrical engineering or VLSI to software engineering. I think it's because the leap from imperative code is too long.
But nobody has successfully made functional code approachable either, because too often it's contaminated by syntactic sugar that's too symbolic, various handcuffs that make even the simplest tasks arduous (poor handling of global state), or learning curves that can't be scaled when it comes to interacting with real-world time-based data (monads etc).
For the most part Excel has none of these issues. You can stick an imperative macro in the middle of your functional code and it works just fine, as it should in any other functional language (as long as you follow certain rules like, no globals). Then you can write another macro to load CSV data into your spreadsheet, or even spit it out to a folder that has a shell script watching it to transform the data again. I think it's more productive to work in that unix-like manner (of a bunch of small tools that do one thing well, pipelined together), than it would be to try to integrate notions like dynamic data into Excel. But the achilles heal of Excel was always obfuscation.
We need to get away from language when we're thinking about functional programming, and focus more on data and transformations. Slate looks like a great start, kudos!
I agree with your other points - this is something I'd love to build (or see built). But do you think it could displace Excel?
But what I'd like to see is a generalization beyond columns/rows/sheets for relating data of various dimensions. The underlying data type would probably be a list (so lisp) but have options to enable order, array access, key->value access, etc. Cell references would work more like in programming languages and operate over ranges that aren't just two dimensional.
So a spreadsheet would be a subset of this. So a set of columns with array access, a set of rows with array access, a notation like column-row for cell references, and all of the operators like $ for absolute references, etc.
I guess the underlying data structure could be a relational database but I'd prefer something human-readable like JSON with a thin notation over it for expressing relationships between structure members. You could use something like the Subtext editor to work on it but maybe if the syntax was easy enough you could write it manually.
I think the real power of this would be if relationships could be executables (like how Excel macros work but with stdin/stdout/stderr and black box scripts written in any language). Imagine being able to call sed/awk or really high level tools like Go/Erlang and Matlab/Octave per-cell the way we use SUM in Excel..
I translated excel into a javascript data structure, translating the formulas into javascript functions. The data structure could be saved to json, and them reloaded back into javascript. The cool thing is that changing a value (a2 = 3) automatically updates dependent values in the rest of the data structure. The data structure can be referenced much like the spreadsheet - it is a tree with branches for pages, rows, columns. There is a simple faster csv like API that makes it easy to go page by page, row by row. Also, cells can be looked up explicitly.
The code is proprietary, which is a shame. It was written as a library to quickly reverse engineer large, complex xlsx files. The trick in making it work is just having the right data structure.
Sorry if I am rambling or any misspellings. I am very sick right now, drowsy from medication, and trying to type thus on my phone.
For example, say I want to make a wiki page to track some kind of code migration project. Currently I might grep for usages of a term, and project out the (module name, term) pairs. I then run this query a few times for different terms, and use Excel to merge and pivot the data so the first column is a module name and the remaining columns mark occurrences of each term. I then copy paste into Vim and use a regex to mangle the data into wiki markup.
There is surely a better way of doing this, using a single tool to glue the steps together so that the pipeline is repeatable and the various steps are individually reusable.
For example, an asset management database. If I can use this to generate different free form queries, it would be something I would totally throw money at.
Right now companies don't send their employees on VBA courses even though they could work some much more efficiently with a basic knowledge of coding.
...nobody has successfully made functional code approachable
Yet I always have trouble recommending a specific functional language to someone who's curious. I'd say scheme if sexprs weren't so intimidating, ocaml if the syntax weren't so annoying, Haskell if it weren't, well, Haskell (not that I've really used Haskell myself). So for now I just tell people to learn Python and show them map() and closures.
Is there a semi-practical functional language with C-ish call syntax, especially with dynamic typing? And don't tell me Javascript, that's still mostly imperative. Rust seems close (closer than Python at least). I might start recommending that when it stabilizes.
Also if you need to have more ideas, you can take a look at the Unreal Engine and Cryengine. They built similar tools to allow artists to code graphically scripts for video games.
Cool idea anyway.
closes tab
Good luck with the product though!
From a design point of view, I LOVE your desaturated forest look with the logo. It's all very memorable
My question is why do they like it? My assertion would be because it is visual in how they want it. They are usually less interested in the complicated "how do I get this value" than they are "I want a value to be here." That is, even if it is wrong, they are more interested in seeing the data laid out in a way they specify than they are coming up with a data flow analysis of what they did.
And, really, I'm not sure this is that surprising. Having a tool show you how all of the data is linked together certainly can make for a good understanding of how the data was calculated. May even help fix mistakes. However, the table of data view of the spreadsheet is ultimately what you want. Just with no mistakes.
> id: 1100 the reason is: Event [Event type="fontLoadError" bubbles=false cancelable=false eventPhase=2]
Thinking about it more though, keeping it integrated into excel or offline has its merits. We have 20mb+ financial models that would take forever for your software to churn through.
This proposed tool is for engineers, not clerks. You download it and use it locally right? Because if it requires the spreadsheet to be uploaded then that is another technical hurdle and one which breaches most people's security requirements.
Excel already does most of this, the problem is that people can't comprehend what it is telling them, your tool is no different.
However there are loads of people who build there own sheets: analysts, consultants, managers, marketing, accountants and yes engineers. These are the people who spend the most time using excel. Excel let's you build a very complex model formula by formula. It's often a model more complex than you might be able to map out initially. Whilst they are not programmers they are still smart enough to understand the sheets they've built. The issue is Excel right now is so opaque about what its doing and where the numbers come from. It makes this task much harder than it needs to be.
I wonder if/how you are able to visualize INDEX/MATCH type lookups, SUMPRODUCT and array formulas? Obviously, these types of formulas are not in every model, but for my usecase it would be pretty important.
Also, since others have asked but the answer watn't too clear: is any data uploaded?
You mention that you have plans to move to a model where things happen in reverse: models would be defined as flows and the spreadsheet could be generated. What progress (if any) have you made on that front?
-None of the content of the spreadsheet is uploaded.
-We're not looking at building the model as a tree right now but it's a cool area to explore.
However, where our tools fail, and where your tool really shines is transforming the spreadsheet visual paradigm into something that more closely resembles the intent of the model. Very cool! (I signed up)
It seems like a really cool idea. For better or worse, Excel has emerged as the de facto "programming" tool for small scale computation and database tasks. It's an annoyance that you can't see your entire program at once, especially for checking correctness, finding bugs, and understanding someone else's work.
The headline here is that you're not selling to consumers. You are selling to businesses; if you can demonstrate value then you can ask for a generous fraction of it in your price. With some smart segmentation you can divide the market into a few different groups, some of whom are rare but prepared to pay much more, others who are more common but only prepared to pay less.