Category Archives: Techie Stuff

Q&A A Fistful of Spreadsheets

[Note from the author: after some fact checking I discovered that I used the Red Card in a wrong way in this blog post. The red card must be used to raise issue like low volume or high temperature in the room, which can lower the quality of the gathering of information. For normal interruptions the yellow card should be used.

This basically means that I had to rewrite the blog post on certain points. This task was more complex than I had expected. The whole flow of arguments had to be restructured while preserving the spirit of this post. So I only added this note.]

Facilitator: Welcome back to another round of questions and answers. This Q&A will focus on the serie Smart Use of Spreadsheets of Mindful Tester.   K-cards will be used in order to structure the flow of thoughts.

I’ve got green cards from numbers 43, 25, and 9. Number 43, you can ask your question.

Attendee number 43: I only read instructions. Why do you not add more clarification?

Writer: It is important to discover things myself. This way I get a better understanding of spreadsheets. In this serie I wanted to share this experience with you, the reader.

Facilitator: I’ve got yellow cards from numbers 31 and 16. Number 31 it’s your turn.

Attendee number 31: That’s pretty abstract. I don’t get that. Would you provide an example?

Writer: In the last episode I described the use of Remove duplicates in order to get a set of pizza brands or a list of names, which are mentioned only once. During the test I needed a way to make a set. I explored the spreadsheet program to find an easy way. By letting the reader follow my steps she or he might see some functions of spreadsheets, which are unknown and useful in the future.

Facilitator: Number 16.

Attendee number 16: How did you find it?

Writer: By selecting a tab, which might contain an interesting set of functions.

Attendee number 16: Just curious. Did you know the function before the test?

Writer: No.

Attendee number 16: So you stumbled upon it during the test?

Writer: Yes, I did.

Attendee number 16: Is it not strange to start testing without an exact set of instructions?

Writer: No. Let me elaborate.

Attendee number 16: Sure.
[Facilitator relaxes.]

Writer: Is it possible to make a test strategy, which does not change? I think: “Not”, so making test cases should be postponed as long as possible or skipped when possible. Nothing is worse than hours spent for nothing. The same is also applicable for the use of tools to determine the most easy way to test the system under test.

Facilitator: A yellow card from number 33. Number 33 it’s your turn.

Attendee number 33: When do you skip test cases?

Writer: There are other alternatives like checklists and exploratory testing.

Facilitator: this is going off topic, so I am closing this thread.

I’ve got green cards from numbers 25 and 9. Number 25, you can ask your question.

Attendee number 25: In your blog post you are using a spreadsheet program. Why did you not use a database tool or Python? They are better equipped to handle data.

Facilitator: I see a red card from number 493. You have a question about the question?

[Attendee number 493 nods.]

Facilitator: Number 493.

Attendee number 493: what is Python?
[Facilitator looks for a snake.]

Writer: For me is Python a strong light weight programming language. Do you need more information?
[Attendee number 493 shakes his head. Facilitator relaxes.]

Writer: The question was: “Why did you not use a database tool or Python?”I have to take additional steps: install tools and figure out, how to use them. It would have taken me extra time.

Facilitator: I’ve got yellow cards from numbers 1 and 24. Number 1 it’s your turn.

Attendee number 1: This could have been a nice starting point to explore another tool?

Writer: Yes, but… The but is to look for long term benefits. For simple data manipulation spreadsheets are good enough. I did not expect complex data analysis in the near future, so I did not even consider using other tools.

Facilitator: Number 24

Attendee number 24: why did you not use the experience report to tell your story? This would provide more insight in the way you work.

Writer: it is nonlinear and it would confuse the reader, who is looking for a fast and linear solution.

Attendee number 24: Could you be more specific? I mean: could you share an experience?

Writer: The first time I used Remove duplicates I got the wrong combination of pizza brands and phone numbers. So I did a manual check for the remaining wrong numbers. Later I discovered that I ordered the phone numbers from A to Z instead of Z to A. So I used System 1.

Facilitator: I see a red card from number 493. Number 493.

Attendee number 493: what is System 1?

Writer: System 1 is used for automatic and fast decisions like walking to the supermarket. System 2 is used for the complex operations, which need thoughtful thinking like buying items on the shopping list.

Attendee number 24: So?

Writer: When I heard the word Order, I picked the order from A to Z. For System 1 this is the default choice. I had to use System 2 (Is the order really from A to Z?), before continuing the test. Adding this experience is likely to add confusion for the reader.

Attendee number 24: I think that you are smart enough to make a smart formula to automate the manual check.

Writer: Thank you for your compliment. There were only foursome numbers to check. I actually made an attempt to use a formula, but that costed me too much time.

Facilitator: Are there any more questions on this thread? Apparently not.

I’ve got green cards from numbers 9, 10, and 11. Number 9, you can ask your question.

Attendee number 9: Why did you not use a programming language supported by the spreadsheet program?

Writer: I didn’t know the language.

Facilitator: Number 10.

Attendee number 10: Why did you not use a blink test to compare?

Facilitator: I see another red card from number 493. Number 493.

Attendee number 493: what is a blink test?

Writer: A blink test is a test, during which the tester processes a lot of information in incredible short time or in a blink of an eye. In this particular case you can switch fast between the two spreadsheets. Differences can be noticed: characters change.
[Attendee number 493 nods.]

Writer: The blink test has some disadvantages. Spreadsheets can be so big, that you need a lot manoeuvring. Some spreadsheets have cells with default width, so not the complete content is shown to the tester. Non-readable characters can be contained in cells. My favourite is that trailing spaces cannot be spotted with the naked eye. “Text” looks the same as “Text<space><space>”.

Facilitator: Number 11. You can go ahead.

Attendee number 11: You copy the calculated values instead of the formulas to the last worksheet. [Speaker nods.] That is a smart move, but it looks like a redundant step to me.

Writer: if everything is OK, then you are right. It there is more than 1 NOK, then odds might change for me. In this case I have to verify the formulas. If they are right, then I have to analyse the data. Values can better be manipulated than formulas.

Attendee number 11: Like?

Writer: Spreadsheet programs support auto filter, which contains nice features.

Facilitator: I’ve got green cards from numbers 21, 562, and 23. Number 21, you can ask your question.

Attendee number 21: You already advocated against the use of database tools and programming language in this context. Why did you not use spreadsheet comparison tools?

Writer: It would only save me one step: to learn the tool. I have to admit that I am not familiar with these tools.

Facilitator: I’ve got green cards from numbers 562 and 23. Number 562.

Attendee number 562: Why do you not change the background colour of the cell to red, if the cell contains a NOK?

Writer: It is an extra step. For analysis of complex patterns of NOKs it can be considered.

Facilitator: Number 23.

Attendee number 23: I found a neat trick. If I have to compare a new sheet with one in the map, then I copy the sheet in the map and rename the tab. What about that?

Writer: It is a tricky one: after months it is not clear, which spreadsheets have been compared. More particular: what were the sources of the sheets? Also there is a risk of different numbers of rows. You actually have to refactor the spreadsheet.

Facilitator: A yellow card from number 21.

Attendee number 21: Could you give an example of refactoring?

Writer: I would an extra row above the table and add a text with information over the source. You still have to watch the formulas though.

Facilitator: A green cards from number 372. Number 372.

Attendee number 372: Why do you not use the standard compare function of the spreadsheet programs?

Writer: Good question. I’ll have a look.

Attendee number 372: Sounds like a new blog post?

Writer: The last blog post about spreadsheets took a considerable amount of time.

Attendee number 372: Why?

Writer: I was refactoring the solution.

Attendee number 372: Is that not contradictory with your previous statements about being lean?

Writer: You’re a smart girl. I blog in my free time.

Facilitator [a bit surprised]: I see a green card from number 493. Number 493.

Attendee number 493: What do you mean with “=b”?
Writer: “=” is an underarm and “b” a hand.
Attendee number 493: Thanks. =b.
Writer: Thanks.

Facilitator: Thank you for all questions about spreadsheets, testing, and… Hey, I miss the pizzas. Anyways I hope you enjoyed this Q&A.

Keep on questioning things!

A Fistful of Spreadsheets

In this episode of Smart Use of Spreadsheets I focus on comparing spreadsheets and an example of basic set algebra. It is time for Techie Stuff.  [On the background a nice tune starts and the smell of hot pizza and cola is entering the nostrils of the reader. That’s you.]

The Mood, the Mad, and the Muggle

Data is useless, unless it is used for other uses than only occupying space on a disk. Computer programs can be used to show the information on screen, but also to tag information. E.g. give me the phone numbers of pizzerias, which deliver at my home more than 6 meters below sea level. (The strange fact is that they do exist in the Netherlands. I say: “Cheese.” )

In this imaginary sanitised example I introduce a program, which compiles a list of all pizzerias in the neighbourhood of my home. It can export the list to a spreadsheet.

In the software development community there are really smart people. Another day I met a programmer, who refactored my pizza list program. The functionality remained the same, but the code was in better shape and maintainable. That last word sounded like music from my smartphone.

So I only had to test it (and like that). You know that thumb sign: =b. But comparing spreadsheets is quite boring and can affect my mood in a negative way (=p). Fortunately I do not have to be a wizard to get this tested fast and fully.

  1. Start the current version of pizza list program.
  2. Make a list and export it to spreadsheet with the name List Current Version.
  3. Start refactored version of pizza list program.
  4. Make a list and export it to spreadsheet with name List Refactored Version.
  5. Open the spreadsheet List Current Version.
  6. Rename the tab to Current Version.
  7. Make a copy of the worksheet to a new map.
  8. Save the map as Comparison Map spreadsheet.
  9. Open the spreadsheet List Refactored Version.
  10. Rename the tab to Refactored Version.
  11. Make a copy of the worksheet to Comparison Map.

Now it is time to add some smart things in the map:

  1. Add a third worksheet in the Comparison Map and call it Comparison Lists.
  2. Copy the heading from the worksheet Current Version to the first row of the sheet Comparison Lists.
  3. Use the wizard tool to make the following formula in cell A2 in  the sheet Comparison Lists by clicking on the right cells:=IF(‘Current Version’!A2=’Refactored Version’!A2;”OK”;”NOK”)
  4. Copy the formula by placing the mouse on the right lower corner of the cell with the formula, press the left mouse button, move the mouse right until the most right cell of the heading of the table and release the left mouse button.
  5. Determine the number of rows in the tab Current Version or the tab Refactored Version.
  6. Select the cells with the formula in the second row in  the sheet Comparison Lists, place the mouse on the right lower corner of the selected cells, press the left mouse button, move the mouse the previous determined number of rows downwards  and release the left mouse button.
    The formula has been copied to all cells.

And finally it is time to determine the results of the test:

  1. Select all cells in worksheet Comparison Lists.
  2. Add new worksheet Results.
  3. Paste by selecting Values.
  4. Search for “NOK”.

For a Few Pizzas More

My nifty pizzeria list program works as follows:

  • Find all pizzerias in the Netherlands.
  • Tag all pizzerias within 20 kilometres of my home.
  • Put the tagged pizzerias on the list.

In this extension of the imaginary situation there was a customer, who wanted to know all the pizzerias in Amsterdam. So he asked my company to modify the program. And of course to test it. So the program will work as follows:

  • Find all pizzerias in the Netherlands.
  • Tag all pizzerias in Amsterdam.
  • Put the tagged pizzerias on the list.

After a while the customer changed his mind he only wants to have one phone number for every pizza brand in Amsterdam. So no more pages of Best Praised Amsterdam United Pizza. Just one line of this brand in the complete list. So the programmer modified the program, that only the phone number, who has the first position in an alphabetical order from Z to A. The first version with complete Amsterdam list was good. This means, that every step was already tested except the tagging.

  1. Ask a programmer to make a list of every pizzeria in Amsterdam with the corresponding phone number by using a query in the database.
  2. Export the list to spreadsheet .
  3. Select the complete table without the heading.
  4. Sort the table first level on pizzeria name from A to Z and second level on phone number from Z to A. (Use the Sort function in the Data tab)
  5. Determine the minimal set of pizzeria names by removing duplicates. (Also a function in the Data tab)
  6. Start the single line version of Amsterdam pizzeria list program.
  7. Make a list and export it to spreadsheet .
  8. Compare the spreadsheets using the trick described from the previous paragraph.

NB some functions may not be available in all spreadsheet programs.

Who ordered the Q&A?

After three episodes about using spreadsheets in a smart way there is a Q&A for people, who want to get more in depth information.
So Use the For…..m Please.

See You Next Post.

The Taking of Spreadsheet 123

Mind maps are great, spreadsheets too. During testing my choice for tools is context driven.

Did you notice the dates, my dear Spreadsheet?

During testing I noticed some strange dates in a table. It was “01-01-0001”. That is more than two millennia ago. (One of the most influential men had his first birthday party on that particular date.)
I thought again:
“How would I mark an item with no end date?
01-01-0001.
But why are there more than 2 items with the same name and no end date?”
I found a bug. Next I scrolled through the table and found other pairs. This process should be automated.

  1. Export the table with the header in the database to a csv file.
  2. Import the file into a spreadsheet program.

If all data is still shown in one column, then

  1. Select the column.
  2. Use the spreadsheet function Convert text to columns.
  3. Select Separated.
  4. Choose the delimiting character ; (semi colon)
  5. Click Finish.

Now it is time to sift the information:

  1. Select all the columns of the table.
  2. Order the table on the item name and then on the end date.
    Column A is item name and column C is end date.
  3. Select the cell right to the heading  in the first row.
    Column G is the first empty column.
  4. Add an extra attribute to the heading e.g. “Same” (in cell G1).
  5. Select the cell under the attribute Same. (G2)
  6. Go to the formula editor and make the following formula:=IF(AND(A2=A3; C2=C3);”yes”;”no”)
    This means, when the item names of row 1 and 2 are the same and the end dates of row 1 and 2 the same, show the text “yes”;
    if this is not the case, show the text “no”.
  7. Copy the formula by selecting the cell with the formula (G2), placing the mouse on the right lower corner of the cell with the formula, press the left mouse button, move the mouse downwards until the lowest row of the table and release the left mouse button.
    The formula has been copied to all relevant cells in the column Same.
  8. Set a filter in the header of the table.
  9. Set the filter of the column Same to “yes”.
  10. Set the filter of the C column or End date to “01-01-0001”
    All rows with items with the  same name and no end date are shown.

Can you handle Big Data, Mister Spreadsheet?

A spreadsheet program is great to analyse csv files. But my first analysis of more than 70,000 lines of data was not so great: it took some time to load and convert the data in the spreadsheet program and it was not possible to load all data at once. There was a chance, that I might miss something. And I did not want to miss a bug.

Some people abhor the use of command lines. And if I mention UNIX some people think: “Run for the hills”. However there are some powerful commands, which make the life of a tester easier. grep is a Unix command, which can be used to filter information from ASCII files using pattern recognition like item id. So I searched for “Windows grep” on the Web and found an interesting open source tool.

I started with a csv file of table with no header. The table description was in a pdf file. I only had to examine all records with specified item id.

  1. Use a windows grep program to extract all lines containing the string with item id.
  2. Export the output to a csv file.
  3. Import the csv file in a spreadsheet program as described in the previous paragraph.
  4. Copy the column with the attribute names from the table description in the pdf file.
  5. Paste these cells in another worksheet of the spreadsheet.
  6. Transpose these cells into a decent heading.
    (Change a vertical row cells to a horizontal row cells using a standard spreadsheet function.)
  7. Copy and insert the heading above the table.
  8. Set a filter on the heading.
  9. Set the filter of item id in the heading to the item id to be analysed.
    All records containing the item id are shown.