Text boxes on line graph (Excel 2000 SR-1)

Topic

New Reply

I’m using Excel to chart a line graph of the closing values of the S&P 500 each day. At the end of each month I have the opportunity to move my investments around among the funds of my 401K. At the end of each month I manually place a small text box containing a single character on the chart next to the data point for that day (see attached gif image) to indicate whether I bought (, sold (S), or held (H) my stock positions. This works OK except that:

1. Adding the text boxes is labor intensive (they also contain hyperlinks and screen tip pop-up text). Is there any way to automatically place a text box on a chart in a precise location using VBA or other Excel facilities? I could add a column to the data sheet to indicate my actions (Bought, Sold, Held) if that would help.

2. The text boxes aren’t really attached to the chart. As I add new data values each day, the line graph compresses to fit within the chart space, but the text boxes remain in fixed positions. This necessitates shifting each text box to the left slightly. Is there any way to force the text boxes to move with the data points on the line?

Thanks in advance,

Reply | Quote

Replies

Maybe the best way is to use data labels. Add an extra column and put the single character B, S or H next to the data-point you want to label. Then use an data label add-in that you can download free of charge: Have a look at this MVP Page for a free add-in to add labels to data points (XYChartlabeler.exe). This add-in requires your graph to be an XY scatter plot.

Edited Mar 13th 2004 to update link

Reply | Quote

Thank you. The data label add-in worked well even on a line graph. It’s a bit of a trade-off though. Data labels provide automatic positioning, but text boxes allow for hyperlinks and screen tips which are immensely useful. Thanks again.

Reply | Quote