Is this something that could be done in Excel?

[Hmmm...]

I need to create a charting application that works in the following way:

1. User enters a date range, and enters the ticker for a stock symbol.
2. Application uses the date range and symbol to access a database.
3. Application displays a list of convertible bonds corresponding to the
stock symbol.
4. User selects up to 6 convertible bonds to plot.
3. Application accesses the database again, and displays a graph that plots
the stock price changes during the date range. The plot is a simple line
showing stock price vs. time.
4. Application displays Buy/sell/hold recommendation for each selected
convertible bond by placing symbols on the line graph showing the time the
recommendations were made or changed.
5. Application shows a legend that lists details for each
Buy/Sell/Underperform recommendation for each selected convertible bond.

The line plot of the stock price against time is a no brainer. Placing
symbols on that graph is the part I am not sure about. Thanks in advance
for any information or suggestions.

 

[Debra Dalgleish]

Yes, it's possible to have a chart with different line series.

1. Create a line chart with markers.
2. Select the series that you want to change
3. Choose Chart>Chart Type
4. Select the line subtype without markers
5. Click OK

 

[Hmmm...]

Thanks. Can the markers of a series be changed programatically (in VBA)
based on how many series there are, and what values are in the series?

For example, there may be from 1 to 6 series, each series representing a
convertible bond. Each series can have from 1 to 5 distinct values. The 5
values represent different types of recommendations. On the chart, they
indicate when a recommendation was made on a convertible bond (The bottom
axis represents time).

So I would want to have each series use a different shape (square, circle,
triangle, ...) to represent each convertible bond, and have different colors
represent the different recommendations (red=buy, blue= hold,
yellow=underperform,...). Could I program this using VBA? I imagine that
I would be settting the properties of some marker and series objects, or
something like that. Is there a web site or book that would explain how to
do this? Thanks.

 

[Hmmm...]

Maybe that wasn't clear. Basically, my question is: Can I set the shape,
color, text of a data point marker using VBA? Thanks!

 

[Debra Dalgleish]

You can record the steps as you manually change a marker (select the
series, then select a marker, choose Format>Selected Data Point)

If the markers should change based on a value, you could use the
technique that Jon Peltier describes here:
http://www.geocities.com/jonpeltier/Excel/Charts/format.html#CondChart

 

[Jon Peltier]

You can download this sample file from my web site:

http://www.geocities.com/jonpeltier/Excel/Zips/ControlChart.zip

It's a control chart (SPC), which you don't care about, but it has VBA
in it that goes point by point along a series, and based on the point's
value, changes the shape and color of the marker. For one series
without too many points, it goes pretty quickly. On my old W95-XL97
box, which was probably underpowered even for Win95, a series with 100
points seemed to take forever, but it was probably only 10 or 15 seconds.

- Jon

 

[agolfer]

Thanks Jon. Actually, I already figured it out, but I will look at your
file.