Annotating line graphs using arrows

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
0
rewb2 (15)
9/20/2008 3:42:40 PM
excel.charting 18370 articles. 0 followers. Follow

2 Replies
362 Views

Similar Articles

[PageSpeed] 36

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

rewb2@yahoo.co.uk wrote:
> 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, Microsoft MVP - Excel
http://www.andypope.info
0
andy9699 (3616)
9/20/2008 3:54:19 PM
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
-- 

Petr Bezucha



"rewb2@yahoo.co.uk" wrote:

> 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
> 
0
PBezucha (46)
9/22/2008 6:43:01 AM
Reply:

Similar Artilces:

sending mail using an alias email address
We are using Exchange Server 2007 with Outlook 2007 clients. I have several email addresses listed under my mail account on the Exchange Server for which I "receive" email. However, the server will not allow me to "send" email using any of these email addresses - as it only allows me to send using the primary address for my email account. I get a message saying "You are not allowed to send this message ... on behalf of another sender without permission to do so." What settings do I need to change on the Exchange Server so that it allows Outlook 2007 ...

using parameters
I have a form which the user selects the BlockNo. The other information that is entered in the form is : 1) NoOfRecordedTrees - RT 2)NoOfSurroundingTrees - ST When the BlockNo is entered, a query runs which picks up fertiliser rates for this Block for all sectors within that block. With RT and ST - it should do a calculation such that it uses Rate/ sector * (RT+ST) to find how much fertiliser is needed for each sector in each block. I would like to add a column to the existing query showing FertiliserAmt using these parameters. How do I specify them within the query. Thanks for your great...

What is 'Align Text to Base Line Guide'?
Trying to make sure all lines have the same amount of space between them I selected paragraph from the format menu and under the line adjustments there is a box 'align text to base line guides' Ok I did that, now there are lines at the margines of my newsletter. What are these? Also selecting this pushed my text down from the top edge of the text box. What is this? The baseline guide measurements are in the Arrange, Layout Guides, Baseline tab. There is help in the Help menu. -- Mary Sauer http://msauer.mvps.org/ "Go_Girl3647" <GoGirl3647@discussions.microsoft.com...

how can i set up my pop and smtp account using proxy server in ou.
"Abhishek" <Abhishek@discussions.microsoft.com> wrote in message news:90EE645B-97D9-4145-A368-E26743C547F9@microsoft.com... You'll need instructions from your ISP. -- Aloha, -Ben- Ben M. Schorr, OneNote-MVP Stockholm Consulting Group/KSG http://www.scgab.com Microsoft OneNote FAQ: http://home.hawaii.rr.com/schorr/computers/onenotefaq.htm **I apologize but I am unable to respond to direct requests for assistance. Please post questions and replies here in the newsgroup. Mahalo! ...

Line Chart with dates in 5 day working week only
Hi, Trying to format a chart so that only the 5 working days of the week are displayed on the x axis. The source data only has the five days (e.g. 05/09/2005 down to 09/09/2005 and then on to 12/09/2005 down to 16/09/2005 etc etc) So I have missed out the weekend dates. When I create the line chart however, the weekend dates appear automatically and just show no point on the chart, therefore there is a longer line between Fridays and Mondays!! Hope this makes sense. Does anyone have any ideas on how to change this? I have tried looking at Tools-options-chart and cannot seem to turn...

Re: Using alias address
Brian Tillman wrote: > Vince <vinresp*@swhome.com> wrote: > > >>We use an exchange server at work for e-mail. I have set up an alias >>that I use for receiving mail but I wanted to use the alias for >>sending all the mail as well. I was told that it cannot be done in >>exchange but I can use a POP server. > > > I don't believe that's always true. For example, I have several accounts in > Outlook where I work, all within the same profile. Only one of those > accounts, the Exchange account, uses my work address. The ot...

Finding all queries which use a table
Hi, Does anyone know of a tool that can scan all queries in a database and find if a certain table is used? I have a table called tblCustomerRollup which is old and outdated. I want to see which of the 500 queries in my database use this table without opeing every single one of them? Thanks, -- Chuck W Chuck Sounds like a variation on Search/Replace. Try searching online for "Database Documenter" as a starting point. A couple of the commercial tools I've used include FMS, Inc.'s Total Access Analyzer and Black Moshannon's Speed Ferret. There are a lot of fr...

copy-paste from excel to powerpoint
Office 97 When I copy a number of cells from Excel to powerpoint, I can't get rid of the grid lines. There are no borders. When I'm in Excel, I see the faint grid lines as you normally would. Unfortunately, these lines also display in powerpoint. How do I stop that behaviour. Thanks so much for any help. Diana Select all the cells you are copying. Then: Format > Cells... > Patterns Then select white color ( bottom right) When the backgound color is set the gridlines vanish unless borders are turned on -- Gary''s Student "Cowtoon" wrote: > Off...

Can Outlook 2003 use MSN Messenger INSTEAD of Windows Messenger?
I didn't get an answer to this question last week so I'm re-posting... I recently purchased a new computer (Windows XP Home Edition w/SP2) and loaded up all of the available updates to the OS, Internet Explorer, etc. Next, I installed Office 2003 Professional. I disabled Messenger integration with Outlook 2003 as discussed in other posts here. Next I installed MSN Messenger 6.2 and it seemed to run properly as a stand-alone application. So I re-enabled Messenger integration on Outlook 2003. The next time I booted up and ran Outlook, the Messenger icon appeared in the taskbar...

Page break preview & blue line
I using Excel 2003 In page break preview I have always do some thing wrong I'm just pull the blue line by using mouse all page was destroyed It there any tips to return the blue line to the Default pages size setting As far as I know the 'default' print area is all the data, and once you have changed the print area and saved the file you can not get it to go back to the previous setting. If you have problems with this or any other proceedure do try to remember to save your file before you do the awkward bit and then you can go back to the saved copy it it does not work. ...

Creating Contacts For Accounts Using...
Hopefully as Microsoft CRM matures, many small time saving features will be added. One that should be a priority is the ability to add a new contact for an existing account using the common account information, i.e. address, phone number, web address, etc. Retyping the same company address in each time is not very productive. Thank you, Ed Podowski ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, f...

Scroll horizontaly with mouse, create same system used to scroll .
Hi, I think it would be great if mouses adopted a second scroll button, for horizontal scrolling, just like the vertical one .... Indeed, when you work with wide Excel spreadsheets, you can easily scroll down but to scroll from left to right or vice-versa, you have to use the scroll bar or arrows and it's annoying... So, am I a millionnaire yet??? Hi Frederic, > I think it would be great if mouses adopted a second scroll button, for > horizontal scrolling, just like the vertical one .... Indeed, when you work > with wide Excel spreadsheets, you can easily scroll down b...

outlook in sub-domain to set use root-domain question!!!
Dear Sir Please see below more details,(We are using special railway line between Head office in Taipei and branch office in Tao-Yuan) Head office in Taipei: aaa.com(Root domain) Dc server * 2(One of it is GC Server), Front-End Exchange 2003 *1, Back-End Exchange 2003 * 2(One is named mail1, another is named mail2 ) Branch office in Tao-Yuan: bbb.aaa.com(sub-domain) Dc Server *1(No GC Server,No Exchange Server) After using ADMT v3 Tool, when I transfer an account from root named aaa.com(ou) to bbb.aaa.com. After I ins...

Outlook first use
Hi, when I open Outlook in a workstation for the first time, Outlook open setup and ask user to create a new profile. I have a utility to create profile automaticaly and this setup is deleting existing profile. Is it possible to Outlook don�t ask to create a new profile for the first time? Tks. Alex ...

Online Restore using NT Backup has no edb.chk or edb.log files
I have a single site with four servers running Exchange 5.5 SP4 on NT4 SP6a. I am using an internal 35/70 Compaq DLT. When I back up two servers at the same time using online method, I am missing the edb.log and and edb.chk files when trying to restore the db's. Is there a known issue for this? Thanks, Jim When you make online backup, you are backing up the database content perse, the logs files will be skipped because ntbackup cannot back up open files. I recomend you to adquire a third party backup software with open files and exchange database options, like Veritas to ensu...

Calculating Correlation using arrays
I have a sheet full of data for many products in chronological order. Column A is Date of manufacture Column B is time of manufacture Column C is the product Column D is a measurement from the automatic control system Column E contains occasional manual measurements for calibratio checks Up till now I have split the data out by grade and checked calibratio using pivot tables and also checked slope and offsets. After gainin exposure to all kinds of clever functions via this board I now wonde whether it would not be possible to automate these checks in some way ie on a separate sheet I list t...

can I use 11 x 14 paper in office documents?
Is it possible to use a larger sheet of paper when using publisher? Yes. First select the paper size in the Printer Setup. -- Don Vancouver, USA "Prairie Inn" <Prairie Inn@discussions.microsoft.com> wrote in message news:FC032907-D965-4DE9-891D-40DF9C6E9C8C@microsoft.com... > Is it possible to use a larger sheet of paper when using publisher? If your printer can handle it, yes. -- JoAnn Paules MVP Microsoft [Publisher] ~~~~~ How to ask a question http://support.microsoft.com/KB/555375 "Prairie Inn" <Prairie Inn@discussions.microsoft.com> w...

Sequentially number lines automatically
Version: v.X Operating System: Mac OS X 10.5 (Leopard) Processor: Intel How can automatically number the lines as I enter data. <br><br>so box A1 would automatically be 1 <br> and the next time I created an entry on another line that line would be 2, etc. <br><br>The reason is so that I can sort, delete and the numbers will remain, so I can also go back to the order than things were entered into. <br><br>Could do this manually, but a pain. <br><br>Or could possibly do this with a date and time? <br><br>Thanks Steevee Se...

IE 6/ActiveX assert in CTLNOWND.CPP line 305
Hello, I have a problem with an ActiveX inside a web page. A random ASSERT in CTLNOWND.CPP line 305 occours during a GetDC first call. It's seems wnd handle is still NULL (it begin valid immediatly after). If I retard calling putting a button in html page it work. CDC* COleControl::GetDC(LPCRECT lprcRect, DWORD dwFlags) { ASSERT((m_hWnd != NULL) || (m_bInPlaceSiteWndless && m_bInPlaceActive)); ..... This problem occours random (more times on faster machines and in release mode) and just on "init" time on my control. Probably my control does this operation in ...

use excel to create PP slides?
I'm not sure if this is possible but I have a list of information in an excel table and I'd like to auto matically generate a slide for each row of data. for instance, I have a list of products and each product is on it's own row with 6 product features in the following columns. How can I take that info from excel and create a features slide for each product using the information from the following columns. I don't mind cutting and pasting the list - it doesn't have to be fully automatic but as I have over 1000 products I desperately don't want to reype ...

Using BGInfo Command Line Parameters
I've read the Help file in BGInfo - but can't figure out how to use Command Line Parameters. When I click on my startup icon, I would like to invoke certain parameters. The line I came up with is: "C:\Program Files\BGInfo\BgInfo\Bginfo.exe /timer:99 /popup /taskbar /rtf:C:\DATA\BGINFO /nolicprompt" Obviously, this is wrong. My Target line reads: "C:\Program Files\BGInfo\BgInfo\Bginfo.exe" My Start in line reads: "C:\Program Files\BGInfo\BgInfo" Please help me by telling me what, exactly, should go in each line. I would also like t...

How to prevent line breaks in XML file?
I am creating XML from SQL2000 using a vb.net page, then posting it to a remote server as a string. The remote server requires that the xml contain no line breaks, but there are some ntext fields in the database that contain long text with line breaks. How do I get rid of the line breaks? Can I do this with the XmlTextWriter, or do I need to handle each affected field seperately before handing it to the XmlTextWriter? Thanks Leslie les wrote: > How do I get rid of the line breaks? Can I do this with the > XmlTextWriter, or do I need to handle each affected field seperately > bef...

Line Chart;Linear Trend; What's the Math behind it
Hi, Do you know what the math is behind the Linear Trendline which results when right clicking on a line chart? Thanks, John -- John Whyte The "Linest" function in Excel help has an explanation. -- Jim Cone Portland, Oregon USA http://www.realezsites.com/bus/primitivesoftware (Excel Add-ins / Excel Programming) "JohnW" <JohnW@discussions.microsoft.com> wrote in message Hi, Do you know what the math is behind the Linear Trendline which results when right clicking on a line chart? Thanks, John -- John Whyte Thanks, Jim. I did try help but I was search...

Use Your Brain
All, Here is an example of what extreme boredom can do to a person. Excel 2002 / Window XP :D Have fun. Attachment filename: use your brain.zip Download attachment: http://www.excelforum.com/attachment.php?postid=386422 --- Message posted from http://www.ExcelForum.com/ Ken, Very cute. You must have a lot of free time on your hands. John "Pyball" <Pyball.ynt5z@excelforum-nospam.com> wrote in message news:Pyball.ynt5z@excelforum-nospam.com... > All, > > Here is an example of what extreme boredom can do to a person. > > Excel 2002 ...

Creating and pasting using VB
I need to create an email and paste a current selection in microsoft excel using visual basic. I have just started to work on this so I don't know what all I have to include. If I need mapi add-ins or anything else added please let me know. I already have the sub routines set to copy the data that I need out of excel but now I need to email this data automattically. Any help would be very helpful. Thanks, Matt ...