Hacking the Google Chart API from Excel
a bit of code on a Saturday night ...
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
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)
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
Print #1, "<body>"
Print #1, "<p>Sparklines for last 12-months spend, IT Projects,
' Loop thru the lines in the table to generate the separate
For i = iStartRow To iStopRow
Print #1, "<P>" & Cells(i, iStartCol).Value &
Print #1, "<img src='http://chart.apis.google.com/chart?"
Print #1, "chs=100x35" ' Size (length x height) of final
sDataString = "&chd=t:"
For j = (iStartCol + 1) To (iStopCol - 3)
sDataString = sDataString & Cells(i, j).Value & ","
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
Print #1, sDataString
Print #1, "&cht=bvs"
Print #1, "&chbh=a,2"
Print #1, "&chco=
Print #1, "&chds=0,100,0,100'"
Print #1, "title='" & Cells(i, iStartCol).Value & "'
Print #1, ""
Print #1, "</body>"
Print #1, "</html>"
The output file looks something like this (a simplified version ...)
<p>Sparklines for last 12-months spend, IT Projects, by
title='Cost Reduction' />
Some things I noted when constructing this stuff ...
- The Google Chart API seems to be picky about the order of
the various parameters. I had some troubles getting the charts to
work unless I output things just so
- I can control a lot about these graphs, but I couldn't get
rid of the x-axis. Yes, there is a chart type for "sparklines" (cht=ls), but that's for line graphs only
- I am calling out the last three months spend in the table,
so I want to highlight them in the charts, hence the little hiccup in
the j loop
I can publish a version of my spreadsheet that puts it all together,
just let me know ...