1. We’re here at Code Mesh 2015 in London. I’m sitting here with Felienne. So, who are you?
I’m Felienne Hermans. I’m assistant professor at Delft University of Technology where I research spreadsheets.
Werner: Spreadsheets. I thought they were a solved problem.
Yes. That’s what people think but spreadsheets are actually riddled with errors, so they need good tool support, and that’s what we work on.
2. When you say tool support, what aspect are you working on?
The simplest way to summarize it will be that we make an IDE for spreadsheets. So we build a smell detection tool, a refactoring tool, a testing tool. We try to bring all the things that we know in software engineering, making software easier and try to bring them to spreadsheets.
3. Smell detection, can you give me some examples of really bad smells?
Yes, of course. If you look at Fowler's smells, a smell would be a long method. We know it’s very to comprehend if a method grows very long. We can easily transfer that to spreadsheets where you have a very long formula, a formula that references a lot of different cells, or uses lots of different functions, a sum and a divide and an average maybe. So the more you squeeze into one little formula, the harder it gets to comprehend. So in that way, it’s very easy to transform those existing smells to spreadsheets.
4. I think you’re using Excel mainly or others too?
Yes. We made a plug-in for Excel. Actually, our smell detection tool is an online tool. So you upload your spreadsheet and you get your smell detection in a browser and that means it’s platform-independent. You only need to have your spreadsheet in the XLS or XLSX file format, but all the major spreadsheet platforms export to that. So that is a bit more generic. And the refactoring tool and the testing tools are plug-ins to Excel.
5. How do you analyze the code in the spreadsheet? Do you parse the formula language?
Yes. We actually made a parser and we open sourced that parser it's on Github. So if you ever want to parse your own spreadsheet formulas, you can go right ahead. There is an official specification of the grammar that Microsoft released, but it is 30 pages of production rules. It’s very hard to reimplement. So we made a grammar that’s a little bit more simple, but has 99.9% coverage of a lot of formulas that we test thereof.
Yes. It’s a lot more complicated than you think. There are a lot of funny ways, things that normal spreadsheet users don’t use. For example, to name something really strange, there is an Intersect operation. So you have two ranges and it results in the intersection of those two ranges. It’s not, as you might think, Intersect and then the two ranges. It’s the range, the first range then a space and then the second range, which is very inconvenient because in many different places in the formula language, whitespaces don’t matter. You can do a Sum and an open bracket and a few spaces and then a range. That’s fine. But here, suddenly, whitespace matters. So it’s a lot harder to parse than you might think. It’s quite a challenge.
Yes. We used LALR(1) parser generator. So we only made the grammar and then we used a parser generator to generate the actual parser.
I don’t know, especially... Microsoft if you’re listening, give me a call, I’d like to know, especially that space operator in the paper we describe a few other scenarios that are are just very inconvenient or a bit cumbersome. I don’t know. I mean they probably never imagined that it would be so huge as it was, and some of the conventions, some of the functions like there’s a lookup function, which has a very funky syntax. I also did some research into that and that just comes from VisiCalc.
They sort of copied the same things that they did in VisiCalc. Well, maybe to get people into the platform because it was something, at that point, that people knew. They sort of inherited some legacy as well; at the point where Excel was the new guy in town, you want to look like other people. So it makes sense that some of the things are a bit strange.
Of course if you have such a huge user base, I think they say they have 750 million people using Excel; you can’t go back. You can’t say, “Well, we’re really sorry. We’re going to change the grammar of the formula language because we made a few mistakes in the ‘80s.” It’s very hard because people would have to take the spreadsheets. You can see how that is a problem if you took the wrong turn somewhere.
Werner: Yes. We all made mistakes in the ‘80s.
Yes, but we don’t all have millions and millions of users that will blame us for fixing our mistakes.
9. You mentioned refactoring, how do you go about that? How do you implement that?
We have a few very simple refactorings, like A1 + A2 + A3. You can rewrite that to the sum of A1 to A3. Actually, we implemented that with a set of rewrite rules. We have language that’s very much like the formula language but has a few placeholders and you can describe, “Well, this type of formula, if you find something like that then you can transform it into something like that.” Those transformation rules are open for users of our refactoring tool. So we prepack it with a number of refactorings but if you want to add your own, you can write these transformations in that language and then you can use your own transformations on the spreadsheet as well.
Werner: It’s like a pattern language, essentially.
It’s like a pattern language, absolutely. We do pattern matching in the backend.
Werner:You can do pattern matching and then, I guess, generate your own outputs programmatically.
Yes.
10. Interesting! What other refactorings have you come up with? Or, actually your students?
My students, yes. One of my students has implemented a really nice refactoring. What we saw is that people often manually sum a number of rows. So suppose you have a few products, product A and B and C, and you have all of them in a list, then people would do the total of product A and they would manually click every row that has A in it. Maybe they don’t know about a conditional sum or they just think it’s easier. So we can detect that. We look at, “Hey, everything that you’re summing, it happens to have those same things in every row, and then we can suggest a refactoring to a SUMIF where we sum the entire range if it’s equal to product A.”
That’s a pretty involved refactoring. That could also be, we believe, educational because if you have never seen the SUMIF, if you don’t know it’s a thing, you’ll probably never go out and Google, “How could I do this easier?” Whereas if we, in the context that you know your products A, B, and C, you know what you’re doing, suggest “Maybe use this formula”, we could actually also increase people’s knowledge of spreadsheet formulas.
Werner: You could give hints. You have to bring back Clippy and say, “Hey, your --”
Yes. Well, we try to make it a little bit nicer than Clippy like, “Oh, it looks like you could be using a SUMIF.” Yes, probably we don’t want that but something like that where we suggest this is a more powerful or simpler function you could use instead.
Werner: Well, that’s definitely a big problem with languages. There are easier ways to do something but how would you know, because there are so many ways, particularly, for I guess spreadsheets where you have lots of novice or inexpert users.
Yes, definitely.
Yes. We have a number of metrics to define smells. As I said, those long formulas are smelly. So we have metrics like the number of ranges that a formula refers to, the number of distinct functions in a formula, and we just set thresholds for -- we have three different smelliness levels, moderately smelly and low smell, moderate smell and very super smelly. You can chart that in your spreadsheet. So we make it yellow and orange and red. And then you know these are the cells that are iffy and then either you manually change them or you can use one of our refactorings to make them better.
Yes. We definitely try to reach out in practice. I mean there is no sense in sitting in your ivory tower and thinking, “What would be your problem here?” When I did my PhD, I worked at an investment bank for two years. I was there one day a week doing research, talking to people, looking at the spreadsheets, analyzing their issues, and that really taught us a lot about what people in practice do with spreadsheets.
Now I have my own students, I try to also force them to go to companies, do an internship. Many of them, three of them are part-time PhD students. So they work for a company part-time and they do a PhD part-time to make sure that we keep solving relevant problems and we don’t just build stuff that no one really cares about.
Yes. So, that’s one of the newer research that we worked on. The smell detection and refactoring, that’s from my dissertation that I finished in 2013. In terms of research for me, that’s a long time ago. So some of the newer work is that we try to make an algorithm where you put in the spreadsheet and you get a description of the business logic, the business rules that are inside, or something like “You’re calculating the interest rate of a mortgage based on people’s income and their age and their marital status”. You can extract that.
And then once you have such documentation in terms of the semantics, you can do really nice things with it. So you can use it, for example, to reimplement the spreadsheet in another programming language. You could use it as documentation that is generated. So it’s always in sync. But we even envision that if you have different spreadsheets in your company, you can use this too and then you can validate what’s happening in it.
You can see, “Okay. This is how we do it. Is that actually how we want to do it.” You could even compare those descriptions to each other. So if you have different departments, maybe with our own calculation rules, you can extract this type of documentation and then compare that at a higher level. So those are application visions that we see.
Yes. So now we just translate it to natural language. What we do how it works on the inside is we try to label every cell. So we look at a cell, let’s say A5 is here. We look at what describes it. So okay, this seems to be -- and then we look at, for example, the first row and the first column, okay, so A5 is not a good example. C5. So then we see, “Okay. It appears to be product A in January, something like that.” So we say, “Okay. C5 represents product A in January.”
And then if there would be a formula somewhere else saying C5 x 4, we could transform that into the product A in January times four. And in this way, we try to take the formula, replace all the cell references by meaningful things and then we have a list of all the formulas, and then we also flatten it a little bit, because if you have 12 products and all of them are multiplied, it doesn’t make sense to output it 12 times. So we make it a little bit simpler.
It’s a very good question, what format will it be? This is something we don’t know yet. So if there are people out there who are listening saying, “Oh, we have these spreadsheets and we would really like to extract these business rules for one of the scenarios that I sketched”, do reach out to us. We don’t really know what a good format is. Should it be more natural language like? Are people happy with some mathematical looking description that still has equations in it? This is something we don’t know. We are figuring that one out.
Werner: As you explained, I just realized, you have to basically do a kind of natural language parsing or just understand what is actually in the spreadsheet.
Yes. So one of the things that we do that’s actually a smart idea that one of my students came up with, for the labels, there are some words that are way more likely to be labels than other words. So “Total” or “Sum”, things like that, “Balance” or “Budget” is very likely to be a label. We can take into account what the word is. So if it’s not a word we think makes sense then we look a little bit further until we think, “Okay. This is probably really the label,” so we do a little bit of natural language processing.
Werner: I was wondering if we can export it to some sort of graph or flow graph because I think spreadsheets are essentially kind of flow --
Dataflow programming. Actually, one of the things that we did and I did in my dissertation is extract a graph, a higher level graph from a spreadsheet. So it’s a graph that shows how the different worksheets in the spreadsheet relate to each other, and that’s really handy if you need to understand the spreadsheet that someone else made. So you get this graph and at least you can see, “Okay, these are the worksheets that look like input worksheets. This is calculation and this is output,” and then what you could do is open the worksheet and zoom in to the calculation on the lower level. That’s absolutely something that’s proven to be very useful in comprehension of spreadsheets.
Werner: Okay. I guess myself and the audience will be surprised there's so much stuff going on in spreadsheets.
Yes. People are often very confused or impressed if I say I did a whole PhD dissertation on spreadsheets and I have all these students all working on spreadsheets now. So it’s something people expect.
Werner: I think you mentioned you were doing a MOOC...?
Yes, Massive Open Online Course.
Werner: Yes. On spreadsheets or on your research, can you explain that?
Yes. So one of the challenges we have in spreadsheets that other software engineering researchers don’t have is access to data. If you are analyzing Java code, you can just go to GitHub or SourceForge and you can download maybe a billion lines of Java and do all kinds of cool analysis on it. But unfortunately, there’s no GitHub for spreadsheets and it’s an issue for us to get real life spreadsheets with users because companies are not going to email us their business critical calculation models.
One of the things we tried to get more access to people and data is do a massive online course. We’ve run on the edX platform and there was no, of course on Excel yet. So that was a nice niche that I could fill. And it’s a combination of basic Excel skills and talking about my research. So firstly, I just explain what is a pivot table and what is an array formula, things people need to know. But then we also talk about how to organize your spreadsheet, how to keep it smell free. So that’s where the smells come into play, and we have a whole chapter, a whole week about how to test your spreadsheet, and then I encourage people to actually download our test tool.
Now, many people have, a few hundred people have already installed our plug-in and we can see what they do so we can monitor their behavior. Are they often testing? What type of formulas do they test? Do they continue to use the tool or is the fun over after a while? So it turned out for us that that online education was a very good way to get access to users and spreadsheets that solve the problem for us of finding data.
Werner: Okay. So that’s basically feeding a few PhDs for a year or so.
Yes, that is the idea. The documentation extraction that I mentioned before, we had a game within the MOOC where we ask people, we gave them a cell and we asked them to click labels, and this is what we could use for our algorithms, our labeling algorithms to benchmark again. So that dataset we got, I think like 150,000 data points from that. One of my students is crunching that as we speak.
Yes. What we observed is really interesting. First, I thought that it was going to be very hard to get normal people, spreadsheet users to test. It's already sometimes hard to get professional developers to test. But when we looked at spreadsheets, we saw many people already test. They test using spreadsheet formulas. So they write formulas like, “If the sum of A1 to A5 is a hundred, then okay, else, error.” This is common practice already. That test we found that about 10% of formulas look like that. They make a decision and they output some sort of string based data. So it’s like a test.
So that is a starting point for a tool. What our tool can do is it can extract those tests into a test suite, and then you can run them and you can measure the coverage of your spreadsheet. So we try to really start simple. And once you have a test suite, or also if you start without one, we have three buttons in the tools saying, “Find me a complex cell, find me a cell with a large value, or find me a cell that’s very much downstream so that it has lots of cells before it.”
And then we just suggest you one cell to test. We get the most complex one or the largest one and you get a few options, so you can by default test it for, it should be text, it should never be empty, you have some thresholds, or you can customize your own testing formula. So we’re trying to make it very, very simple, one suggestion instead of saying, “Well, here’s the test suite. You can run it. You just first have to put something in it.” It is a similar idea. It is the best practice from software engineering but it has to be a bit simpler, a bit more approachable, we think, in order for it to succeed with spreadsheet users.
Werner: So you’re basically trying to get the user to specify what the output should be?
Should look like, yes.
Werner: Kind of, can you say declarative?
Something like an assertion. This should always hold, like an invariant or like a precondition. This should always be zero, and then if it’s not, something somewhere is wrong.
Werner: I find it interesting that you say that users have come up with this self-testing or sort of reinvented assert in these spreadsheets.
Yes. It probably has to do with the domain as well, because in finance, it’s very common, for instance, if you have a money overview, I don't know a good finance word for it. But to sum it this way and use also sum that way and then you make sure that the two ways are the same, you do a cross-check. This is very common in finance, and we would call that a test. You have two definitions and they should match. But that’s very common, and also in other finance worlds, if you have income and money you spent, it should balance to zero. That’s also very much like a test. So probably it has something to do with the domain where it’s common in finance to have many checks and cross-checks and balances on the things you write.
Werner: So redundancy or double book keeping -- yes.
Double book keeping, yes. Do things two ways.
Werner: Yes. I was tortured for five years in school with double book keeping.
So you should know that.
Yes. But also in general, it makes sense. If you’re writing, it’s also a good idea to keep your sentences short, not too long, not with seven subsentences, because no one will understand. I think a general idea of good communication is to keep it small, keep it short, don’t in the text, it’s also not very nice if you referred to something that happens seven pages ago.
These concepts I think are so generic, they are applicable to spreadsheets. They are applicable to software. It’s not that the software principles apply to spreadsheets. It’s that there’s some sort of common core of human communication. Don’t make it too big. That applies to everything we write and build.
Werner: As a software engineer I think “Everything is like us”.
Yes. It’s nice. Also, I always pitch it that way. I always say, “We do software engineering on spreadsheets because that goes down easily with the software engineering community.” “You guys are so smart. Look at what you built.” It’s even applicable to something totally different. But of course it’s more generic than that.
Werner: It’s a great way to pitch it.
Yes. I know. It works for me.
Werner: We've already mentioned the MOOC, do you have a name for it? Can we Google for it?
The course code is called EX101x. That’s Excel101 and then “x” is for edX. And the name is “Data Analysis to the MAX().”
Werner: Oh I see. So everybody who wants to learn about data analysis with spreadsheets can take it.
Yes, and it’s free. Everyone can take it.
Werner: Oh okay. So everybody should take it.
Yes, please. Give me more research data.
17. Finally, I think you’re organizing conferences. Can you tell us about that?
Well, it’s just one, not multiple. Well, I have one every year. It’s called “Joy of Coding,” and what we’re trying to do there is bridge the gap between academia and professional developers, because if you go to academic conferences, I go there often, not a lot of developers show up there because well, the papers are hard to read. It’s very much an in-crowd. So what can we do to make developers aware of the cool things that happen in academia? But also very much the other way around, how could we, as academics, learn about what’s hot and new in software development, because sometimes there can be a disconnect between what happens in the university and what happens in real life?
What we’re trying to do with Joy of Coding is really celebrate the joy of coding. It’s in the name. So it’s really not about any platform. It’s not Java or .NET or Erlang. It’s just ideas, big ideas. This year we had a talk, for instance by Crista Lopez about her book where she does the same program in many different languages. That’s a very good idea, a very good example of the type of things that we’re trying to do, introduce ideas and not talk about the next framework or the next platform you need. We like programming.
What’s also unusual about Joy of Coding is that it’s not just presentations. We have one track with presentations, but two other tracks are hands-on coding sessions, so two-hour workshops where we did Arduino and we did programming music, programming art. You bring your laptop and you go do something, you go build something. If you want to go to the talk, you can, but if you want to build something, you can also do the workshops. That’s because the Joy of Coding of course is not listening to people or watching people code. The Joy of Coding is coding, doing it yourself. So that’s how we like to stand out from other conferences.
Werner: Okay. That sounds pretty exciting.
Yes.
Werner: When is it? When is the next one, in 2016?
The next edition? It isn’t announced yet but it’s fine if we announce it now. The next date will be June 17. I promise the website will be open really soon.
Werner: Okay. By the time this video is out, it will definitely --
Probably will be there, I hope it’s there.
Werner: Definitely. It’s in the summer in 2016, we’ll watch out for Joy of Coding. We’ll also watch out for the MOOC, and thank you for the interview.
Thanks. Thanks for having me.
/filters:no_upscale()/sponsorship/topic/25afca57-6bcb-4c42-9a70-fbea10ce4fc7/MicrosoftLogoRSB-1678874949003.png)
Community comments