Annotating charts in Excel

[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 which are 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

 

[Thomas Hardy]

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 which are 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

Robert,

The only way I can think of is to use the fact that when the mouse is moved
over a point on an Excel chart, the point number is shown. You can use this
as a reference to the explanation of the particular points.

There will be no link to the explanation (you can not click on the point to
get to it) and the points with additional comments will not be highlighted,
but the problem of changing data values will be solved.

Thomas