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 ...
Previously ...
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 ...
- 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
Previously ...
- Measuring and Reporting IT Value (1 of 2) (November 16, 2007)
- There ain't much IT in IT Management (May 7, 2008)
- Where to Start? (2 of 2) Metrics & Measurements (August 12, 2008)
- Linkfest: Data Visualization (September 6, 2008)
- Excel 2007 is a BOB system (Bag'O'Bugs) (October 5, 2008)
- I Think I'm Learning SAPanese ... (November 22, 2008)
Technorati Tags: application, business value of IT, development, documentation, hands on, presentations, Excel, VBA,
Labels: application development, business value of IT, development, excel, hands on, presentations, technology, vba, visualizations
posted by James P. MacLennan at 11:03 PM
| Permalink
| 0 Comments
