Hacking the Google Chart API from Excel

90DaySpend
Click to enlarge ...

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.

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) …

[code lang=”vb”]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 = Range("rOutFileName").Value
iStartRow = Range("rStartRow").Value ‘ First row of data to be graphed
iStopRow = Range("rStopRow").Value ‘ Last row of data to be graphed
iStartCol = Range("rStartColumn").Value ‘ First column of data to be graphed (includes column of series names
iStopCol = Range("rStopColumn").Value ‘ Last column of data to be graphed

Open sOutFile For Output As #1

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

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

For i = iStartRow To iStopRow
Print #1, "</pre>
" & Cells(i, iStartCol).Value & "
<pre>"
Print #1, "</pre>
<img src="<a href=&quot;http://chart.apis.google.com/chart&quot;>http://chart.apis.google.com/chart</a>?&quot;
Print #1, &quot;chs=100×35&quot;       " alt="" height="" />
<pre>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, "</span></small>
<pre>"
Print #1, ""

Close #1

End Sub
[/code]

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

[code lang=”html”]

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

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

<P>Integration</P>
<img src=’http://chart.apis.google.com/chart?
chs=90×30
&amp;chd=t:29.3514,2.795,6.435,1.235,1.755,9.49,21.9661,33.7545,43.0014,0,0,0|0,0,0,0,0,0,0,0,0,104.1521,133.80185,176.267
&amp;cht=bvs
&amp;chbh=a,2
&amp;chco=6666FF,FF3300
&amp;chds=0,200,0,200′
title=’Integration’ />

<P>Growth & Innovation</P>
<img src=’http://chart.apis.google.com/chart?
chs=90×30
&amp;chd=t:68.57045,84.0723000000002,110.0905,97.9752800000002,126.0103,114.8720625,99.9466000000003,67.1827000000001,97.98165,0,0,0|0,0,0,0,0,0,0,0,0,113.22155,96.7057000000002,96.21365
&amp;cht=bvs
&amp;chbh=a,2
&amp;chco=6666FF,FF3300
&amp;chds=0,200,0,200′
title=’Growth & Innovation’ />

<p>Total IT Project Spend … </p>

<img src=’http://chart.apis.google.com/chart?
chs=90×30
&amp;chd=t:312.56875,304.185375,366.746575,328.214055,370.6534,368.8695075,396.3171225,354.440775,329.371575,0,0,0|0,0,0,0,0,0,0,0,0,418.8067,424.3772,464.768135
&amp;chd=t:312.56875,304.185375,366.746575,328.214055,370.6534,368.8695075,396.3171225,354.440775,329.371575,0,0,0|0,0,0,0,0,0,0,0,0,418.8067,424.3772,464.76813
&amp;cht=bvs
&amp;chbh=a,2
&amp;chco=333333,FF3300
&amp;chds=0,500,0,500′
<br>
<p>Generated on Sun, Jan 08, 2012 at 12:53:22 pm</p>
</body>
</html>

[/code]

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

(Updated January 8, 2012: I have published a sample spreadsheet with the code above on my Projects page – check out the VBA Excel section …)

This Post Has 0 Comments

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Related Articles
Digital Transformation Automation

Simple Ways to Quickly Build Valuable Digital Experience

Practical thoughts and examples - how to manufacture time and attention, to get hands-on, relevant skills in new technologies
read more

3D Printing Requires Wildly Different Thinking

Additive Manufacturing faces slow adoption in most industrial companies due to material costs - and the challenge for designers to think differently about what was previously impossible.
read more

James MacLennan

... is the Managing Partner at Maker Turtle LLC, a digital consultancy focused on creating value in ways that align with your strategy and drive engagement with employees, customers, and stakeholders. He is an active creator, providing thought leadership through on-line & print publications, and public speaking / keynotes.