This is an old link ... let me redirect you ...

(... or click here if the page does not automatically redirect)

Hacking the Google Chart API from Excel

a bit of code on a Saturday night ...

I've written before about a simple way to measure and report IT value to the business - quantifying alignment with strategic initiatives  project spend in context. It all culminated with a single, simple slide - numbers, with some Tufte-esque Sparklines thrown in.

Click on the picture for a full-size image!


Well, technologies come and go, and without going into the boring details, I've had to come up with a new way to generate the mini-bar charts along the left side there. It ended up being a relatively straightforward task in Excel VBA - yes, of course the table of data is being driven from a spreadsheet.

Here's the macro that does the trick - I just create a little HTML file that generate the bar charts in series (please excuse the hard-coding) ...

Sub CreateSparklinesDisplayFile()
  
   Dim sOutFile As String
   Dim iStartRow, iStopRow As Integer
   Dim iStartCol, iStopCol As Integer
   Dim i, j As Integer
   Dim sDataString As String

   sOutFile = "C:\Temp\BizUpdates.html"
   iStartRow = 45   ' First row of data to be graphed     <<< Evil hard coding!
   iStopRow = 51    ' Last row of data to be graphed
   iStartCol = 12   ' First column of data to be graphed (includes column of series names
   iStopCol = 24    ' Last column of data to be graphed

   Open sOutFile For Output As #1

   Print #1, "<html><head><title>BizUpdate Sparklines</title></head>"
   Print #1, "<body>"
   Print #1, "<p>Sparklines for last 12-months spend, IT Projects, by Initiative</p>"

   ' Loop thru the lines in the table to generate the separate sparklines

   For i = iStartRow To iStopRow
      Print #1, "<P>" & Cells(i, iStartCol).Value & "</P>"
      Print #1, "<img src='http://chart.apis.google.com/chart?"
      Print #1, "chs=100x35"       ' Size (length x height) of final graphic
  
      sDataString = "&chd=t:"
      For j = (iStartCol + 1) To (iStopCol - 3)
         sDataString = sDataString & Cells(i, j).Value & ","
      Next j
      sDataString = sDataString & "0,0,0|0,0,0,0,0,0,0,0,0"
      For j = (iStopCol - 2) To (iStopCol)
         sDataString = sDataString & "," & Cells(i, j).Value
      Next j
      Print #1, sDataString
 
      Print #1, "&cht=bvs"
      Print #1, "&chbh=a,2"
      Print #1, "&chco= CCCCCC ,FF3300"
      Print #1, "&chds=0,100,0,100'"
      Print #1, "title='" & Cells(i, iStartCol).Value & "' />"
      Print #1, ""
   Next i

   Print #1, "</body>"
   Print #1, "</html>"

   Close #1

End Sub

 The output file looks something like this (a simplified version ...)

<html><head><title>BizUpdate Sparklines</title></head>
<body>

<p>Sparklines for last 12-months spend, IT Projects, by Initiative</p>

<P>Cost Reduction</P>
<img src='http://chart.apis.google.com/chart?
chs=100x35
&chd=t:52.25,65.3,72.15,33.15,33.95,33.65,47.7,92.88,79.49,0,0,0|0,0,0,0,0,0,0,0,0,70.57,87.85,55.25
&cht=bvs
&chbh=a,2
&chco= CCCCCC ,FF3300
&chds=0,100,0,100'
title='Cost Reduction' />

<P>Growth</P>
<img src='http://chart.apis.google.com/chart?
chs=100x35
&chd=t:67.05,88.25,85.61,95.25,86.70,55.49,54.75,81.19,65.62,0,0,0|0,0,0,0,0,0,0,0,0,55.65,42.05,18.41
&cht=bvs
&chbh=a,2
&chco=CCCCCC,FF3300
&chds=0,100,0,100'
title='Growth' />

</body>
</html>

Some things I noted when constructing this stuff ...
I can publish a version of my spreadsheet that puts it all together, just let me know ...