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 ([img]/forums/images/smilies/cool.gif[/img], 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,