Excel 2003: Daily Stock Chart AND Random Scatter Chart Data


New Member
Jan 14, 2014
I need to plot various data on top of stock charts.

This data could be irregular in date: it could be weekly, or simply random.

To plot this data by itself requires a Scatter Chart.

From what I know so far, you CANNOT DO THIS.

However, I suspect this could be done if I build a Stock Chart from scratch using a Scatter Chart.

Error bars can be used to make the tails, however, I don't know how they built the body bar which has the characteristics of a bar (border, and interior.)

But since I don't really need those two characteristics, I just need a wider error bar line that is provided in the chart edit window. I'm guess through a macro, there are wider line widths assignable.

Any thoughts?

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
What are the other values you need? If each series has one point per date, you can still use a line chart (a stock chart is a fancy line chart). Select all the data (OHLC plus additional series), insert a stock chart, then select each of the additional series, and plot them on the secondary axis. You'll have to format the series, because series on a stock chart are by default drawn with no lines or markers. Then delete the secondary axis or axes that Excel added. Since the additional series are on the secondary axis, they won't interfere with the stock chart on the primary. Since there are no secondary axes, all data (primary and secondary) will be drawn based on the primary axis scales.
Upvote 0
I don't have "One point per date" - I've done that a lot in the past.

Only problem now is that now there are "holes" in the data with regard to dates.

For now, I only need to plots points and have resigned myself to the following method:

1) load a daily sized array with my sparse data leaving many blank cells.
2) copy and paste this array to the stock chart.

Seems to work OK.

If I would need lines w/o symbols, I could use a linear interpolator macro I use to fill in the blank cells.

Very messy but would work.

If I would need symbols where the data actually was, I guess I'd have to write a macro to add them.


Have any of the later versions of Excel removed this limitation?

Excel is a very limited scientific plot package and maybe I should move on to something else?
Upvote 0
Limitation? Excel plots whatever data you give it. If your data has lots of empty cells, Excel does what it can.

Hint: if you need a line connecting points on either side of one or more blanks, right click the chart, click on Select Data, then click on the Hidden and Blank Cells button in the bottom left of the dialog, and select the option todraw a line across the blank cells.

And, sure, Excel is not really a scientific graphing package, and it has shortcomings, but it's also way more flexible than other graphing programs, if you're willing to experiment with it.
Upvote 0
I'm at home and don't have 2003 here (only 2000). That Hidden and Blank Cells button - is that in 2003? (If it is, wow, I sure missed it.)

Regarding Excel, they did all the work to make 3D charts and contour charts too. But the one thing they failed was a simple 3D line chart - a trajectory through points. Or even simply plotting Points in 3D.
Upvote 0
Sorry, I didn't notice "2003" in the subject until now. The hidden and blank button is in 2007 and later. In 2000 and 2003, select the chart, go to Tools menu > Options, click on the Chart tab, and choose Interpolate for how it should treat blank cells.
Upvote 0

Forum statistics

Latest member
Muhammad Tauqeer

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back