line chart: editing symbol (excel 2002)

Topic

New Reply

Hi all. i already construct a line chart of 2000 rows data with date. my question is, at some dates, there is a symbol i wanted to put into the chart, either ‘B’ or ‘S’. since there are 2000 rows of data, there are over 200 symbols to put into. can a code do this for me? i mean i have the exact dates of the symbol location in the chart and the code just match the date and plot the necessary symbol on the chart. can someone help me here, can this task be done by a code? thanks.

Reply | Quote

Replies

Can you come up with a formula for the ones that need a B or an S? If so, you could add a column to your data with that formula (that is, write it once, and then copy it down all 2000 rows), and have your chart pick up that column so that if there is a B or an S, it appears automatically.

I don’t do much with Excel, but this seems to be the most Excel-like way to approach it.

Reply | Quote

Hi jscher, i seems not to get it done with your idea, maybe you could have a look where went wrong in attached file. there are Data tab, Line chart with symbol tab and Line chart tab. the Data tab basically is the data source with date, price and symbol information. Line chart is the basic chart from data source of date and price. and Line chart with symbol is the line chart included symbol column. the line chart seems reasonable but something is wrong with the line chart with symbol. if chart wizard alone can do it, it possible reduce other efforts in doing so. thank you.

Edit: although i realise data with rows like 2000 does cramp in a chart, with not nice looking graph, but i haven’t got any software that does this, and i just wanted to view the price trend and its symbol somewhere roughly, if at least this is the first step. thanks again.

Reply | Quote

There is an excellent free add-in for Excel that will do what you want: Rob Bovey’s XY Chart Labeler. Although primarily aimed at XY scatter charts, it can be used for any type of chart.

Download and install the add-in, then activate the chart sheet in Excel, and select Tools | XY Chart Labels | Add Chart Labels.
Click in the box “Select a Label Range”, then point to the column with the symbols (DATA!$C$2:$C$35 in your sample workbook)
Then click OK.

I have attached the result.

Reply | Quote

Here is a quick fix that I created.
The problem is that your symbols are not numeric and can not be shown in a graph.
I changed the date to have 4 columns(Date, Data, Split, Buy)
Then had the Split and buy dates = the Data column at the point of transaction.
I then formated the points to be unique and used the default colors in the symbols.

I have posted the modified chart for you to see what I have done.

Reply | Quote

Here is a quick fix that I created.
The problem is that your symbols are not numeric and can not be shown in a graph.
I changed the date to have 4 columns(Date, Data, Split, Buy)
Then had the Split and buy dates = the Data column at the point of transaction.
I then formated the points to be unique and used the default colors in the symbols.

I have posted the modified chart for you to see what I have done.

Reply | Quote

There is an excellent free add-in for Excel that will do what you want: Rob Bovey’s XY Chart Labeler. Although primarily aimed at XY scatter charts, it can be used for any type of chart.

Download and install the add-in, then activate the chart sheet in Excel, and select Tools | XY Chart Labels | Add Chart Labels.
Click in the box “Select a Label Range”, then point to the column with the symbols (DATA!$C$2:$C$35 in your sample workbook)
Then click OK.

I have attached the result.

Reply | Quote

Hi jscher, i seems not to get it done with your idea, maybe you could have a look where went wrong in attached file. there are Data tab, Line chart with symbol tab and Line chart tab. the Data tab basically is the data source with date, price and symbol information. Line chart is the basic chart from data source of date and price. and Line chart with symbol is the line chart included symbol column. the line chart seems reasonable but something is wrong with the line chart with symbol. if chart wizard alone can do it, it possible reduce other efforts in doing so. thank you.

Edit: although i realise data with rows like 2000 does cramp in a chart, with not nice looking graph, but i haven’t got any software that does this, and i just wanted to view the price trend and its symbol somewhere roughly, if at least this is the first step. thanks again.

Reply | Quote

Can you come up with a formula for the ones that need a B or an S? If so, you could add a column to your data with that formula (that is, write it once, and then copy it down all 2000 rows), and have your chart pick up that column so that if there is a B or an S, it appears automatically.

I don’t do much with Excel, but this seems to be the most Excel-like way to approach it.

Reply | Quote