Charts with Time as X-Axis

[Ed]

I would like to plot some stock prices for a stock during one day. In other
words, the X-axis will contain a series all with the same date but with
different times during the day, and the Y-axis will contain the prices.
However, it appears the smallest increment for either Line charts or Stock
charts is only one day (i.e. it plots all of my X-axis data as if it were one
point since it is all in the same day). Is there a way around this?

 

[Ed]

Incidentally, I'm using Excel 2007.

That's exactly what I've done. My X-axis series contains e.g. 6/3/2009
7:30:00 AM, 6/3/2009 7:30:30 AM, 6/3/2009 7:31:00 AM, etc. The first column
on the spreadsheet contains this time data, and the second column contains
the price. I highlight the two columns and click Insert->Line->2D Line, and
a chart comes up with only one point on the X-Axis labeled '6/3/2009 0:00'
(and a straight vertical line on the graph covering the price range). When I
highlight the X-axis on the chart, right-click, and click Format Axis, 'Major
unit' and 'Minor unit' are defaulted to 'Auto' (where it gives only one point
on the X-axis). If I click on Fixed, it only allows for Days, Months, or
Years, but not e.g. seconds.

 

[Joel]

Try scatter plot instead of line plot.

Incidentally, I'm using Excel 2007.

That's exactly what I've done. My X-axis series contains e.g. 6/3/2009
7:30:00 AM, 6/3/2009 7:30:30 AM, 6/3/2009 7:31:00 AM, etc. The first column
on the spreadsheet contains this time data, and the second column contains
the price. I highlight the two columns and click Insert->Line->2D Line, and
a chart comes up with only one point on the X-Axis labeled '6/3/2009 0:00'
(and a straight vertical line on the graph covering the price range). When I
highlight the X-axis on the chart, right-click, and click Format Axis, 'Major
unit' and 'Minor unit' are defaulted to 'Auto' (where it gives only one point
on the X-axis). If I click on Fixed, it only allows for Days, Months, or
Years, but not e.g. seconds.

 

[EricG]

You have to use a Scatter chart, not a Line chart. You should then get what
you want. And you can change the format of the cells to just show the time
if you want. You don't have to show the date also.

HTH,

Eric

 

[Ed]

That sort of works. In some cases, it looks like there are more than one
data point for the same time. My guess is because there are so many time
data points (every 30 seconds for several hours) that when the chart
compresses it, it looks like two points at the same time, esp. if I use a
Scatter with Smooth Lines.

It would be most handy if I could play a Line chart or Stock chart with
seconds or minutes on the X-axis, but apparently this is not available.

 

[Joel]

Try reformat the axis. Click on the x-axis to select the axis. then double
click on the axis. I box will appear that allows reformating of the axis.
If the box doesn't appear try a couple of times. It is a little tricky in
getting the format box to appear.

 

[EricG]

Since it's a scatter chart, you can actually have two y-values for the same
x-value. And yes, if your data is very dense compared to the axis scale, it
will look like the points are on top of each other.

If you want to run a line through the data that might improve the
appearance, try adding a trendline, using a moving average. Play with the
number of points in the average until you get something you like.

You could always try to simulate a Stock chart with the Scatter chart, but
it would probably involve some behind-the-scenes VBA help and might get
ugly...

Eric