Managing Complex Spreadsheets — The Story of PerfectXL

This week we finished grading of the software architecture course I’m teaching.

Like many teachers, I use a spreadsheet for grading together with my co-teachers and teaching assistants. In this case, we concurrently worked with five people on a Google Spreadsheet. The resulting spreadsheet is quite interesting:

  • The spreadsheet currently has 22 sheets (tabs)

  • There are input sheets for basic information on the over one hundred students in the class, the groups they form, and the rubrics we use.

  • There are input sheets from various forms the students used to enter course-related information

  • There are input sheets for different sub-assignments, which the teachers and assistants use to enter subgrades for each rubric: Some grades are individual, others are per team. Such sheets also contain basic formulas to compute grades from rubrics.

  • There are overview sheets collecting the sub-grades from various sheets, combining them to overall grades. The corresponding formulas can become quite tricky, involving rounding, lookups, sumproducts, thresholds, conditional logic based on absence or presence of certain grades, etc.

  • There are various output sheets, to report grades to students, to export grades to the university’s administrative systems, and to offer diagrams showing grade distributions for educational assessments of the course.

The spreadsheet used has a history of five years: Each year we take the existing one, copy it, and remove the student data. We then adjust it to the changes we made to the course (additional assignments, new grading policies, better rubrics, etc).

Visualization of sheet dependencies

All in all, this spreadsheet has grown quite complex, and it is easy to make a mistake. For example, I once released incorrect grades — a rather stressful event both for my students and myself. And all I did wrong was forgetting the infamous false argument needed in a vlookup — despite the fact that I was well aware of this “feature”. For the this year’s spreadsheet we had duplicate student ids, in a column where each row had to be unique, leading to a missing grade, and again extra effort and stress to resolve this as soon as possible.

I suspect that if you use spreadsheets seriously, for example for grading, you recognize the characteristics of my spreadsheet — and maybe your sheets are even more complicated.

Now I have an interest in spreadsheets that goes beyond that of the casual user: As a software engineering researcher, I have looked extensively at spreadsheets. I did this together with Felienne Hermans, first when she was doing her PhD under my supervision in the context of the Perplex project (co-funded by Microsoft) and then in the context of the Prose project (funded by the Dutch STW agency). From a research perspective, these projects were certainly successful, leading to a series of publications in such venues as ECOOP 2010, ICSE 2011-2013, ICSM, EMSE, and SANER.

But we did our research not just to publish papers: We also had (and have) the ambition to actually help the working spreadsheet user, as well as large organizations that depend on spreadsheets for business-critical decision making.

To that end, we founded a company, Infotron, which offers tooling, services, consultancy, and training to help organizations and individuals become more effective with their spreadsheets.

After several years of operating somewhat under the radar, the Infotron team (headed by CEO Matéo Mol) has now launched an on line service, PerfectXL, in which users can upload a spreadsheet and get it analyzed. The service then helps in the following ways:

  • PerfectXL can visualize the architectural dependencies between sheets, as shown above for my course sheet;
  • PerfectXL can identify risks (such as the vlookup I mentioned, interrupted ranges, or overly complex conditional logic);
  • PerfectXL can assess the structure and quality of the formulas in your sheet.

If this sounds interesting, you can try out the service for free at perfectxl.com. There are various pricing options that help Infotron run and further grow this service — pick the subscription that suits you and your organization best!

Even if you decide not to use the PerfectXL service, the site contains a lot of generally useful information, such as various hints and tips on how to create and maintain well-structured spreadsheets.

Enjoy!

Novels I Loved Reading in 2016

I enjoy reading a few pages after work, or listen to an audiobook when commuting. Here are the five novels I loved most in 2016.

Arthur Japin: De Gevleugelde (The Winged, 2015)

A novel based on the life of Alberto Santos-Dumont, Brazilian aviation pioneer. Inspired by Jules Vernes, the young Alberto sets out to invent flying machines, building his own hot air balloons, dirigibles (Zeppelins), and eventually planes. In 1901 he is the winner of a competition to fly a route of 11km around the Eiffel Tower, and in 1906 he is the first in Europe to make a flight with an “aircraft heavier than air”. Japin describes what drives the engineer Santos-Dumont to make his inventions and reach his fame. But most of all he tells the story of Santos-Dumont’s forbidden love for his mechanic Albert Chapin.

I listened to the audiobook narrated by the author (who is a trained actor). Many of Japin’s novels are translated and I expect a Portuguese and English version to appear soon, maybe in 2017?

In real life, Alberto Santos-Dumont refused to file any patents (he “open-sourced” his designs), as he wanted the world to benefit from the ability to fly as soon as possible. Later, suffering from multiple sclerosis and hugely disappointed by the military use of planes in world war I and during the Brazilian São Paolo revolution of 1932, he burned all his designs and committed suicide. See also this santos-movie for a short biography.

John Green: The Fault in Our Stars (2012)

Love story of 16 year old Hazel Lancaster and 17 year old Augustus Waters, both cancer patients. Heartbreaking and beautiful. Also features the Amsterdam Anne Frank house and Westerkerk.
“Young Adult Literature”: Amazingly well done novel about love and death that is meaningful to parents and kids alike.

Chances are you read the Fault in Our Stars before me: If you loved it, also consider John Green’s “Looking for Alaska” (2006). Most commonly banned book at US schools and libraries: Can you imagine a better recommendation?

John Green and his brother Hank Green set an example to universities around the world by their crash course initiative offering free on line courses on such topics as astronomy, psychology, world history, physics, and (soon) computer science.

Griet op de Beeck: Kom hier dat ik U Kus (Come Here so that I Can Kiss You, 2014)

Griet op de Beeck is a Belgian author who was to be appointed as TU Delft “cultural professor” in 2016. Unfortunately she had to cancel due to personal circumstances. Before that, she featured in the highly regarded Dutch TV show Zomergasten where she gave an open and optimistic account of her life and her mental health struggles.

Her novel tells the story of three stages in the life of Mona. Told by Mona herself, the first part is set in the simple and compelling language of a 10 year old. Mona tries to make sense of the world after the death of her mother. In the later parts, 24 and 34 year old Mona seeks to find and understand herself, and her relationship to her parents, stepmother, and her brother and stepsister.

I listened to the audiobook narrated by the author in wonderful Flemish. Translations in German (“Komm her und lass dich küssen”) available, and forthcoming in French and other languages in 2017.

Andy Weir: The Martian (2011)

A delightful page turner written by a software engineer. It is the year 2035, and when his mission to Mars gets into trouble, astronaut Mark Watney gets left behind all alone. Thanks to his knowledge of potatoes, farming, and chemistry, and thanks to his amazing optimism, perseverance, and improvisation skills, Mark manages to travel across Mars on his own to reach a place where he might be picked up to return to earth.

Thomas Mann. Buddenbrooks: Verfall einer Familie (The Decline of a Family, 1901)

My classic of choice in 2016 was Mann’s first novel, telling the story of four generations of a 19th century German merchant family. Thomas Buddenbrook runs the family business, optimistic at first, but more and more exhausted and depressed as life goes on. His brother Christian suffers from mental health problems; His sister Anthonie has bad luck in her marriages, and her daughter Erika is unfortunate in marrying a merchant who ends up in prison. And young Johann’s fate is covered in a heartbreaking chapter just describing the symptoms of typhoid.

German edition on Project Gutenberg. In case you want to start exploring Mann with a shorter novel, consider his “Death in Venice” (1912).

A South African Perspective on Privacy and Intelligence

The Dutch government has proposed a new law on intelligence and security services (“Wet op de inlichtingen- en veiligheidsdiensten” — Wiv20XX).

As several privacy-related organizations have made clear, this law proposes non-specific (bulk) interception powers for any form of telecom or data transfer without independent ex-ante review or court involvement (see the summary by Matthijs Koot, and reactions on the bill by Bits of Freedom, Privacy International, the Institute for Information Law of the University of Amsterdam IVIR, and the Internet Society ISOC).

This bill gives the Dutch government unprecedented power to violate the privacy of its citizens. Either the Dutch government does not recognize the crucial role of privacy in a well-functioning democracy, or it does not realize what enormous privacy infringements are made possible through Internet surveillance.

Book cover Sachs' Soft Vengeance

When discussing the importance of privacy, I am always reminded of South Africa’s anti-apartheid activist Albie Sachs and his autobiography “The Soft Vengeance of a Freedom Fighter” (first published in 1990, and turned into a film in 2014).

As a law student at the University of Capetown, Albie Sachs started fighting apartheid at the age of 17, in 1952. He was imprisoned from 1963-1964 (solitary confinement) and again in 1966, after which he was exiled from his home country South Africa.

In 1988, living in Maputo, Mozambique, he lost his right arm and an eye when his car was bombed by the South African secret police.

From 1991 until 1993, after Nelson Mandela’s release in 1990, Albie Sachs played a pivotal role in the negotiations leading to the new South African constitution.

In 1994 Nelson Mandela appointed him as judge of the highest court of South Africa, the Constitutional Court. He worked for the Truth and Reconciliation Commission between 1995 and 1998.

Albie Sachs wrote his Soft Vengeance in 1989. Nelson Mandela was still in prison, and the struggle against Apartheid was not won yet. Albie Sachs had just lost his arm and eye, and his book was his attempt to cope with his injuries.

For his recovery he was flown into a London hospital. He noticed that he was remarkably optimistic, and he was wondering why. Here is his reason (p.58):

“Perhaps part of my pleasure at being in this hospital room is that I am fairly sure it is not bugged. Sometimes I used to imagine my phone in Maputo being listened in to by at least three different secret services […]”

“Possibly my continuing sense of post-bomb euphoria comes from the fact that at least for the time being I am out of the net of hidden sensors, my spirit free from spying for the first time in three decades.”

He explains what it means to be surveilled:

“Ever since I was seventeen I have been politically active, I have lived with the notion that there are others accompanying every move I make, listening to every word I say.”

“Did the secret police really follow every up and down of my marriage, pick up the terms of our divorce, record automatically the names of our children even before they were entered in the birth register?”

And this gives rise to his dream for the future:

“I too have a dream, that there will one day be a world without police files, and bugged rooms, and tapped telephones, and intercepted mail, and that I will actually live in it.”

Albie Sachs is not alone in his dream. According to article 12 of the United Nations Universal Declaration of Human Rights, we all have a right to privacy:

“No one shall be subjected to arbitrary interference with his privacy, family, home or correspondence, nor to attacks upon his honour and reputation. Everyone has the right to the protection of the law against such interference or attacks.”

To date, the Internet has given us amazing possibilities to communicate with our family and friends, to search, read, and share information on almost any topic we find interesting, and to shop for almost any item we think we need. As a software engineering educator and researcher, I am proud to have played a tiny part in making this happen.

Unfortunately, the Internet can also be used as a place for massive surveillance activities, at levels that, for example, the South African apartheid regime could only have dreamed of. As a software engineer, I am terrified by the technical opportunities the Internet provides to governments wishing to know everything about their citizens.

A government aimed at drafting a modern intelligence bill should recognize this immense power, and take responsibility to safeguard the necessary privacy protection.

The Dutch government has failed to do so. It has proposed a bill with insufficient independent oversight, a bill that oppressive regimes, such as the former South African regime, would be happy to embrace.

Luckily, the present bill is still a draft. I sincerely hope that the final version will offer adequate privacy protection, and bring the world closer to the dream of Albie Sachs.