Visualise the structure of a spreadsheet(slateforexcel.com)
slateforexcel.com
Visualise the structure of a spreadsheet
http://www.slateforexcel.com
21 comments
I'm not sure I agree with the second point. Excel has tons of debugging features, too many. It means most people don't know about all the tools and they're all really badly designed. This is about getting rid of all that and creating one new feature, the tree view. Which offers way more functionality on it's own.
While Excel has SOME debugging features, they really don't work well at ALL for large formulas. I don't need something to help me debug some multiplication.
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.
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.
I've been working on this myself and would really love to get some feedback.
Would this be useful for the problems you have with Excel?
it's a great idea - much better than the old built-in formula dependency tracker. definitely loving your idea! I've been in the Excel business myself for years (having worked in finance) and always love when someone comes up with a fresh tool for the mighty Excel (yeah, I know most developer's views on Excel...). I've recently started www.spreadgit.com which is a GitHub-like service for spreadsheets. looking forward to playing around with your service!
It certainly is a good idea.
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.
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.
It looks really cool, I think the two tools kind of go hand in hand. Sharing sheets and understanding them.
yup. feel free to drop me an email if you want to touch base (i've just done the sign-up)
Cool. I'm [email protected] btw
Great idea! Do you have to upload the spreadsheet? If yes then a big downer. Better if you could release and sell as Excel Plugin
hmm, what's wrong with uploading it? I would actually prefer the no-Addin solution: the Excel Addin 'deployment' is a bit messy (with references stuck in the Addin-manager) + no Addins in the Mac world (though I guess that >95% of Excel users are on windows)
Would you expect a financially sensitive document of a company to be uploaded on a third party site?
We were definitely pursuing the add-on route. Although add-ons can be clunky we felt people would want to switch between the two views quickly and often. For example, if you make a change in the spreadsheet view you might want to see what effect that had on the tree. What do you think?
Dual-view, in two windows.
Also, uploading would make it an instant non-starter for me, due to data sensitivity issues.
Also, uploading would make it an instant non-starter for me, due to data sensitivity issues.
hmm, guess I got a bit ahead of myself - you are right, you wouldn't want to switch views/apps as you would indeed expect people to use it in quite a dynamic way. really looking forward to it!
Would you expect a financially sensitive information of a company to be uploaded on a third party site?
Proprietary information
Our product is a reverse auction procurement tool used by large business customers. As you can imagine, we work with a lot of spreadsheets. We have knowledge workers who receive (sometimes complex) Excel documents from customers. A not insignificant amount of time is spent interpreting these sheets so we can understand how a customer evaluates pricing data. This evaluation leads to a bid model, which we use to collect pricing on line items that roll up in to a total-value number.
Really looking forward to trying it out.
Really looking forward to trying it out.
How do you plan to support links between documents. The place I work at now has a very complex budgeting process where each department sends my office their budget spreadsheets and we link them all into an overview that also does things like calculating benefits payments and taxes. There's a lot that can go wrong in that process, and every time I go back and look at a submitted version I find some mistake someone missed.
Slate will show the cells that are in another document but referenced by the current sheet. However, it would not show any further levels of referenced cells.
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.
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 love the way you've presented the underlying structure so much, I found myself wishing you would replace the spreadsheet itself!
Well, maybe one day...
You are just a small step away from the concepts in Subtext:
http://www.subtext-lang.org
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!
http://www.subtext-lang.org
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!
...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.
I use Haskell, but for many data manipulation problems it is still clearer to do them in Excel, mostly because of the bigger emphasis on the data itself, easier visualisation and eyeball-verification intermediate results, and simpler data-garbage-fixing in case a data point "raises an exception".
Interesting stuff. Yeah whats great about excel is there is zero learning needed to get started. Anyone can start typing numbers in the cells then move on to more complex tasks. The problem is, although each step towards building a model in Excel is quite simple the end results can be very unwieldy.
That Subtext screencast is fascinating - thanks for posting the link. Reminds me of the Bret Victor talks.
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?
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?
I'm not sure if anything will displace Excel (or spreadsheets in general).
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..
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..
Yes this is close to what I was thinking of. Basically I want a tool for manipulating ad-hoc collections (lists/sets) of data. Something that is as powerful as SQL (but easy to set up for throwaway pipelines), as accessible as Excel (but not as restrictive - Excel forces you to work in two-dimensional projections of your actual data), that interfaces well with standard formats (JSON, CSV, Excel) and data sources (grep, find, REST, SQL, log files).
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, 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.
All these suggestions would make for a really powerful tool. The issue is how to make it all approachable to non-coders. Almost all the excel users I've talked to don't know how to make macros let alone any more complex code. I think the issue is trying to make these concepts understandable and usable to everyone so users can get started straight away.
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.
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.
I've been dreaming of a general purpose tool using Excel as a front-end and being able to dynamically manipulate backend data for different presentation.
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.
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.
I actually worked on this exact solution a few months ago.
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.
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.
Please do this. I've dreamed of something like this for a long time.
I think you will really love this video : http://worrydream.com/dbx/
It's a conference about programming languages in general. It's interesting, with cool materials, and fun to watch.
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.
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.
Oh good. An autoplaying YouTube video with audio.
closes tab
closes tab
OK I've disabled the autoplay again. I was just trying to get people to watch the video.
Oh don't you dare! Run an a/b test first, before you disable autoplay. If autoplay gets more signups then keep it. You're not here to be our friend and drink tea, you're running a business.
From a design point of view, I LOVE your desaturated forest look with the logo. It's all very memorable
From a design point of view, I LOVE your desaturated forest look with the logo. It's all very memorable
Fair point. My miserableness may not be typical.
I know what you mean though. I have chrome plugins installed that prevent autoplay, flash, widgets, facebook, and a whole lot of other things. It's awesome I can have 40+ tabs open and not have to worry about lag.
It's a pet peeve ;-)
Good luck with the product though!
Good luck with the product though!
Is this an Excel plugin or an independent/standalone software?
Interesting. As I understand it, you use a spreadsheet as input and then create the data flow diagram. I certainly see how seeing the data flow diagram means better clarity and error-finding. So here is a question for you? What if we take it a step further and have users actually entered their complex spreadsheets in a tidy graphical data flow diagram which mapped back?
That is where I would love to take this. I think thats a bit more of a leap for the user as it forces them to think in the flow diagram way from the very beginning. I'd really like to see how users react to building a spreadsheet from scratch in this way.
The problem is users like the presentation of a spreadsheet. So far, there seems to have been no evidence of this changing.
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.
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.
WARNING: CONTAINS AUTOPLAY SOUND.
We would certainly use this at our company...In the real estate finance world we exclusively use Excel for financial underwriting, and at times, cells and formulas get lost in the fray. As the analyst around here, I know how to audit a spreadsheet just looking at the formula (if I built it...), but the guys in the field using our models have no clue how cell A links to cell B.
I vote for online solution, as us corporate types have no ability to download or add software (even as Excel add-ins...) Ugh.
Do you not have any issues with uploading proprietary information?
I don't personally, but I could imagine our company might. That being said, we use Box and other "insecure" places to upload files. I argue that as long as you have a policy in place that what is uploaded isn't shared without permissions given, I'm sure we wouldn't have problem uploading.
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.
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 looks awesome! I quite often stumble upon the task of automating or porting spreadsheets into code. This is always a pain as it might in some ways be hard to visualize the flow in a chart. This tool would greatly simplify the task. Looks really sweet!
The problem with Excel spreadsheets is that the overwhelming majority of users are not programmers or engineers, they are clerical workers with no real understanding of what they are doing. Knowledge, such as it is, is passed on in a monkey see, monkey do, fashion, producing imitation without insight.
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.
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.
Almost all Excel users are not programmers, you're definitely right. If by clerical workers you mean people who are just entering data into the sheet or reading an output then, yes, they don't need to understand what the sheet is doing.
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.
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 do financial modelling at a Big4 and can easily see the value of this type of visualization tool.
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?
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?
-A formula such as INDEX or MATCH is shown as a box with an arrow coming in for each parameter of the function. Hovering over the box gives a breakdown of which cell is being looked up for each row.
-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.
-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.
We have some pretty powerful in-hourse tools for visualizing information flow within a given worksheet. It will overlay borders around contiguous blocks of identical (R1C1) formulas and color code based on whether the cell has a value, a formula an off-sheet formula and cross-file formula, etc...
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)
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)
I was JUST thinking about something like this a week ago. I termed it "formula explorer" and was going to ask some people if something like it was in Excel 2013 (at work we're on 2003). This would be great for my mechanical engineering work. We often have formulas that reference many sheets with many constituent formulas. To check them I usually have to create another sheet that shows the flow of values concisely on one sheet, basically a manual version of what Slate does. This tool would automate that, looks fantastic.
An Excel to LabVIEW converter. ;-)
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.
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.
I can be contacted on [email protected] if anyone wants to get in touch btw
This doesn't seem worlds away from LiveSheets.
http://livesheets.com/
http://livesheets.com/
Great! I hope one day someone will make a similar plugin for LibreOffice Calc.
Any idea of the price ?
We haven't really decided yet. What would businesses be willing to spend?
mmm they wouldn't unless many individuals push for it. This wouldn't be used by management, so people below would have to push for it. I would recommend some type of freemium model.
Don't just make a price up. Buy Strategy and Tactics of Pricing immediately, it's a relatively short textbook that will teach you what you need to know.
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.
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.
Wow! Microsoft needs to buy this. Great addition to excel.
This looks great!
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.