My experiments with sparklines

For the uninitiated, Sparklines are “data-intense, design-simple, word-sized graphics” according to its inventor Edward Tufte. I always wanted to include them in my trend reports. The challenge: How and Which tool to use?

The data came from this report came from a complex query using SQL server and Access that had this format:

College Major 2005 2006 2007 2008 2009
A B 10 15 14 18 25

Some options I investigated and tried (be sure to read this page, you might find many more):

  • Google’s Chart API (http://code.google.com/apis/chart/types.html): you can embed these in Google spreadsheets, create HTML pages using Excel via VBA, or embed them in Excel sheets. Alas, none would work the way I would like them to work: In-cell graphics
  • Use Sparklines for Excel add-in: this add-in will create great in-cell charts (bar, bullet graphs, sparklines, etc), but copying them down is difficult and resource intensive, and any change you make in the column size will alter the shape of that chart. In addition, I had more than 400 rows to populate–these would be too many objects in a spreadsheet for Excel to handle. My machine froze when I tried to copy it for 10 rows.
  • Use R’s implementation by Jason Dieterle (search on this page for Jason Dieterle (email), January 28, 2008. Works very good. I modified the code to print max and mins only, but the function generates a graphic, which needed to be embeded in LaTex file. It did not work nicely. Charts were too big to fit in a cell of a table.
  • Create bar charts in Excel using REPT function. I tried different font sizes and styles, but it didn’t look pretty, for the range of data varied. (Hint: make the alignment of text 90, use pipe signs, create columns and graphs for each data value, remove gridlines, keep the columns very close. It almost worked.)
  • Use spark package for LaTex, doesn’t work in pdflatex, and you have to play a lot with the settings
  • Use sparklines package for LaTex. Problem was that data needed to be normalized (or scaled) from 0 to 1, and needed extra parameters for min and max points. Solution: create a normalize function in Excel, and write a big formula to produce the exact needed string for the sparklines to work i.e.:
    \begin{sparkline}{5}
    \sparkdot 1 1 blue
    \sparkdot 0.2 0 red
    \spark 0.2 0 0.4 0.0625 0.6 0.5625 0.8 0.75 1 1 /
    \end{sparkline}

Here’s the normalize function:

Public Function Normalize(cell2Normalize As Range, WholeRng As Range)
       Normalize = (cell2Normalize.Value - WorksheetFunction.Min(WholeRng)) / (WorksheetFunction.Max(WholeRng) - WorksheetFunction.Min(WholeRng))
End Function

Here’s the big formula to convert the range for data values from 2005 to 2009 (in the range E2:I2) to the sparkline LaTex environment.

="\begin{sparkline}{5} " & "\sparkdot " & CHOOSE(MATCH(MAX(E2:I2),E2:I2,0),0.2,0.4,0.6,0.8,1) & " " & 1 & " blue " & "\sparkdot " & CHOOSE(MATCH(MIN(E2:I2),E2:I2,0),0.2,0.4,0.6,0.8,1) & " " & 0 & " red " & " \spark 0.2 " & Normalize(E2,E2:I2) & " 0.4 " & Normalize(F2,E2:I2) & " 0.6 " & Normalize(G2,E2:I2) & " 0.8 " & Normalize(H2,E2:I2) & " 1 " & Normalize(I2,E2:I2) & " / \end{sparkline}"

This last option worked beautifully. I dragged the formula down. I selected the data, and clicked on “Convert Table to LaTex” button (using this add-in). Copied the LaTex code to clipboard and pasted it in my LaTex editor. Manually merged the rows for colleges (using \multirow), and generated a beautiful looking pdf.

I was very happy. Printed it in color. Got a request back very soon that there should be total rows.

I forgot about the sparklines and created a report in Access with plain old numbers in less than 15 mins. Gave it back.

I tried to repeat this – Sparkline in Cognos -in Access with no luck.

(I later tried it one more time: Got the data in Excel using External data> Access, created a pivottable with rows and all, did some formatting, copied and pasted values and formats, inserted sparkline code, converted it to LaTex, copied and pasted in LaTex editor, and here’s the beautiful looking sample pdf of 16)

I wish there were simple reporting solutions that included awesome data visualization tools. (BTW, Excel 2010 will have sparklines: link) For this report, I did try Sweave, R, and LaTex, but because of the time constraints I could not investigate it further.

Please comment if you know any other way which meet (or don’t, Tableau is certainly one) these conditions: inexpensive (read free), efficient, and repeatable.

About the Author

The author of Tableau Data Visualization Cookbook and an award winning keynote speaker, Ashutosh R. Nandeshwar is one of the few analytics professionals in the higher education industry who has developed analytical solutions for all stages of the student life cycle (from recruitment to giving). He enjoys speaking about the power of data, as well as ranting about data professionals who chase after “interesting” things. He earned his PhD/MS from West Virginia University and his BEng from Nagpur University, all in industrial engineering. Currently, he is leading the data science, reporting, and prospect development efforts at the University of Southern California.

  • Fabrice says:

    H there,

    I have made some changes on the Sparklines for Excel add-in, in order to make faster and more stable when rendering lots of shapes…

    If you want to give it a try, it’s available on my blog : http://sparklines-excel.blogspot.com/

    Thanks for this intersting post… maybe Excel 2012 will provide stability and comprehensive features…

  • a7n9 says:

    thanks for that update, Fabrice. I still have some problems from time to time in Excel 2007. But I really like your add-in. Thanks for the great work.

  • Rahul says:

    The sparklines concept in excel is simply great. Its new concept for but you explained it very easily. This add-in creates great in-cell charts and its easy to understand. Thanks for giving such a great excel guide.

    http://godwinsblog.cdtech.in/2010/12/microsoft-office-excel-add-in-plug-in.html

  • Lejbl says:

    Nice experiments. You save me a lot of time trying with Latex packages. I think that R-Sweave may be an interesting approach, carrying data directly from the source to the PDF, but I have a very little experience with it.
    @Fabrice: I really like your add-on!

  • >