Sparklines: can't resist

When I started looking at ways to automate the graphing of the bank's accounts opening data, I originally started out with a 3-D line chart powered by a PivotTable. But have since realised that this is a perfect area of application for sparklines, Edward Tufte's `intense, simple, word-sized graphics'. For example, see above.

They're usually supposed to be surrounded by more context, but basically that is their size and general appearance.

Sparklines have so much potential in charting huge amounts of data; couldn't resist spending a lot of thought and time trying to figure out what would be the best way to implement them. First decided on plain HTML and CSS generated by Python, and spent a lot of time on it before decided it was too tedious because I had to get Python to generate each and every dot making up the lines. Python is very good, but after a while I realised I should use an environment which already provided vector-based drawing tools which could be automated.

The obvious choice turned out to be Microsoft Word, because of how common it is, especially here in Bangladesh. After some hacking, came up with the following code:

Const theHeight = 50
Const widthMul = 1

Function scaleHeight(num, max) As Double
   num = Val(num)
 
   scaleHeight = theHeight - (num / max) * theHeight
End Function

Sub genSl()
   Dim c As Shape ' Holds the canvases one by one
   min = 0
   max = 0

   Dim theArray()
   howMany = Selection.Range.Paragraphs.Count
   ReDim theArray(howMany - 1)
   Dim canvasNames()
   ReDim canvasNames(howMany - 1)
 
   For i = 0 To howMany - 1
       theArray(i) = Strings.Split(Selection.Range.Paragraphs(i + 1).Range.Text)
       For j = 1 To UBound(theArray(i))
           If Val(theArray(i)(j)) < min =" theArray(i)(j)"> max Then max = theArray(i)(j)
       Next j
   Next i
   max = max - min
 
   For i = 0 To howMany - 1
   ' For each paragraph in the selection a sparkline is drawn
       Set c = ActiveDocument.Shapes.AddCanvas(100, i * (theHeight + 20) + 200, widthMul * (UBound(theArray(i)) + 1) + 55, theHeight + 15)
       canvasNames(i) = c.Name
     
       With c.CanvasItems.BuildFreeform(msoEditingAuto, 0, scaleHeight(theArray(i)(1), max) + 7.5)
           For j = 2 To UBound(theArray(i))
               ' j starts from 1 because the first point was plotted in the BuildFreeform function
               .AddNodes msoSegmentLine, msoEditingAuto, j * widthMul, scaleHeight(theArray(i)(j), max) + 7.5
           Next j
           .ConvertToShape
       End With

       j = j - 1
       With c.CanvasItems.AddShape(msoShapeOval, j * widthMul - 2, scaleHeight(theArray(i)(j), max) + 7.5 - 2, 4, 4)
           .Fill.Visible = msoTrue
           .Fill.Solid
           .Fill.ForeColor.RGB = RGB(51, 102, 255)
           .Line.ForeColor.RGB = RGB(51, 102, 255)
       End With
     
       With c.CanvasItems.AddTextbox(msoTextOrientationHorizontal, j * widthMul + 5, scaleHeight(theArray(i)(j), max) + 7.5 - 7.5, 50, 15)
           .TextFrame.TextRange.Text = Strings.Trim(Str(theArray(i)(j)))
           .TextFrame.TextRange.Font.Size = 8
           .TextFrame.TextRange.Font.Color = RGB(51, 102, 255)
           .Fill.ForeColor.RGB = RGB(255, 255, 255)
           .Line.Visible = False
       End With
   Next i
 
   ActiveDocument.Shapes.Range(canvasNames).Select
End Sub

Sub showMarkers(n As Integer)
   pWidth = ActiveDocument.PageSetup.PageWidth
   pHeight = ActiveDocument.PageSetup.PageHeight
 
   Dim l As Shape
   For i = 1 To Int(pWidth / n)
       Set l = ActiveDocument.Shapes.AddLine(i * n, 0, i * n, 10)
       Set l = ActiveDocument.Shapes.AddLine(i * n, pHeight, i * n, pHeight - 10)
   Next i
   For i = 1 To Int(pHeight / n)
       Set l = ActiveDocument.Shapes.AddLine(0, i * n, 10, i * n)
       Set l = ActiveDocument.Shapes.AddLine(pWidth, i * n, pWidth - 10, i * n)
   Next i
End Sub

Sub doShowMarkers()
   Call showMarkers(10)
End Sub

If you're interested in using them, put them in some module in one of your documents templates (if in the Normal template, it will be available to all documents). Then put some data and numbers in the document itself, arranged in a certain way. The above sparklines were generated from the following data:

DSE 2 3 4 7 3 7 4 119 3
DSEGeneralIndex 749.11 768.03 795.05 763.7 752.91 792.56 874.57 870.46 874.22 842.36 845.07 848.41 807.6 806.92 750.84 787.94 791.7
DSE20Index 942.46 958.2 1004.56 963.88 920.73 973.88 1134.34 1094.45 1085.97 1052.47 1051.48 1054.89 1004.61 1021.5 948.27 964.13 964.32
RandomIndex 642.2 221.5 2

That is, each series is on its own paragraph (paragraphs not separated by blank lines), each item in the series separated from the other by a single space. To chart the data, select it all. If the selection contains a single data series, then a single sparkline will be drawn, and so on.

Need to work more on the code and especially on the GUI front-end. But for now it works OK.
Will upload it to a public server after working on it some more.