Your feedback is appreciated ...

Hands-On

Hacking the Google Chart API from Excel

Science
By ⋅ January 18, 2009 ⋅ Print This Post Print This Post ⋅ Post a comment

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

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=100x35&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

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>Data runs from  through </p>

<P>Integration</P>
<img src='http://chart.apis.google.com/chart?
chs=90x30
&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=90x30
&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=90x30
&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>

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

Previously ...

Filed Under  , , , , , , , , , , , , , , , , , ,

Discussion

No comments for “Hacking the Google Chart API from Excel”

Post a comment

CAPTCHA Image
*