Annotating line graphs using arrows

[rewb2]

I am creating a line chart in Excel, and want to annotate various
movements in the line. It is a share price graph, with price against
date (and volumes on a secondary axis), and I want to explain the
significant peaks, troughs, movements etc.

Ultimately I want arrows to point to the line, with the arrows
sequentially
numbered - I will have a separate table which shows the date and
narrative for each number (space is limited on the chart itself, as
I'll have about 10-15 arrows).


Rather than drawing the arrows on manually, and adding text boxes
with
numbers, is there any quicker way of doing it? The main problem
comes
with making changes (eg updating the chart) as arrows will need to be
moved (as there will be more data), new arrows added (if something
interesting has happened to the price) and the numbering may need to
be changed (older arrows may need to be removed, some may be no
longer
considered significant etc).


I have written some VBA to do this, but it doesn't work particularly
well and is difficult for some of my colleagues to use as they don't
know what to do when something goes wrong. The only way I could
think
of doing it was working out what X- and Y- co-ordinates were the
origin and maximum points on the two axes, calculating what
proportion
of these distances were equivalent to e.g. 3 Sep 07 on a scale of 1
Jan 06 to 31 Dec 08, and doing similar for the price itself. Once I
have the point on the chart I can draw an arrow, with an input
gradient and direction, and add a text box with the appropriate
number
on it (all done in VBA).


This all seems quite clumsy to me, and doesn't always do what I want
it to. Is there a more effective solution to this? - it takes a long
time to update manually, and I'm having to do quite a lot of these
charts.


Thanks
Robert

 

[Andy Pope]

Hi,

You should be able to use a another data series to highlight events in
your chart.
Here are a couple of examples.
http://www.andypope.info/ngs/ng47.htm
http://peltiertech.com/Excel/Charts/PointAtPoints.html

Cheers
Andy

 

[PBezucha]

I’d bet the most comfortable method how to mark the chart events is labeling.

The gist of the following procedure is

- location of label texts in a spreadsheet column and the rows corresponding
with point values (the column next to that with values is being offered
first),

- omitting all empty cells in the label column.

You can

- renew labeling on every change,

- shunt the labels in the chart arbitrarily to not interfere with curves and
markers.

This variant is comfortable further in copying colors and sub- and
superscript fonts of the original text. The alignments and framing of the
labels, and font size, have to be altered inside the procedure. The label
texts should be as short and apt as possible.

Sub AddLabels()
'Petr Bezucha, 2008
Dim RngLabels As Range, Labels As DataLabels, Ser As Series, I As Long, _
J As Long, StrSer As String
Const LabelFontSize As Long = 9
If TypeName(Selection) = "Series" Then
Set Ser = Selection
StrSer = Ser.Formula
StrSer = Left(StrSer, InStrRev(StrSer, ",") - 1)
StrSer = Right(StrSer, Len(StrSer) - InStrRev(StrSer, ","))
Set RngLabels = Range(StrSer).Offset(, 1)
RngLabels.Select
On Error GoTo ErrExit
Set RngLabels = Application.InputBox(Prompt:="Select labels array", _
Default:="=" & ActiveSheet.Name & "!" & RngLabels.Address, Type:=8)
Ser.HasDataLabels = True
Set Labels = Ser.DataLabels
With Labels.Border
.ColorIndex = 1
.Weight = xlThin
.LineStyle = xlLineStyleNone 'alternatively xlContinuous
End With
With Labels.Interior
.ColorIndex = 2
.PatternColorIndex = 1
.Pattern = xlSolid
End With
With Labels
.Shadow = False
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.Position = xlLabelPositionAbove
.Orientation = xlUpward
.AutoScaleFont = False
.Font.Size = LabelFontSize
End With
For I = 1 To Ser.Points.Count
If Not IsEmpty(RngLabels(I)) Then
Ser.Points(I).DataLabel.Text = RngLabels(I).Text
Ser.Points(I).DataLabel.Characters.Font.ColorIndex = _
RngLabels(I).Characters.Font.ColorIndex
For J = 1 To Len(RngLabels(I))
If RngLabels(I).Characters(J, 1).Font.Subscript Then _
Ser.Points(I).DataLabel.Characters(J, 1).Font.Subscript = True
If RngLabels(I).Characters(J, 1).Font.Superscript Then _
Ser.Points(I).DataLabel.Characters(J, 1).Font.Superscript = True
Next J
Else
Ser.Points(I).DataLabel.Text = ""
End If
Next I
Else
MsgBox "Select a series in the chart"
End If
Exit Sub
ErrExit:
On Error GoTo 0
End Sub