need help charting a time progression for investments

  • Thread starter Thread starter Elwood
  • Start date Start date
E

Elwood

Here's the data that I have:

A B C D 1
Investments Initial current purchases
-----------------------------------------------------------
2 BigCorp1 9681.03 11108.07 10-21-04
3 Another1 7761.28 8914.30 10-21-04
4 Oboy1 5564.50 6789.10 2-5-05

What I need to show is, for each investment, to show the change (up or
down), and across the X-axis, to show the time passed. So, for
example, I can see that the person bought BigCorp1 on 10-21, and it's
grown n-amount, and they bought Oboy1 on 2-5, and it's grown n-amount,
etc.

Is there a way to do this in one chart? I'm lost as to what data
series(s) to select and how to set it up. Any help would really be
appreciated!

Thanks,
E
 
You want to show time along the horizontal axis and price along the
vertical. To make a line, you need two points, and each point requires an X
and a Y value, or in your case, a date and a price. So for BigCorp, your
data would be:

BigCorp1
10-21-04 9681.03
02-10-06 11108.07

Avoid the obsession with putting a header over every column, and leave the
cell above the dates blank. This helps Excel parse your data. Select the
range including the blank cell and name of the stock, and create a line
chart. Copy the next block of data:

Another1
10-21-04 7761.28
02-10-06 8914.30

Select the chart, and use Paste Special from the Edit menu to add this data
as a new series. Repeat for any additional stocks.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______
 
If the dates are not all the same, a Line chart isn't appropriate. You
should use an XY chart. Line charts take the X values (dates) for the first
series and apply them to all series.

Excel's line charts have nicer options for nice date axis scales than XY
charts, though, so it's nice to use a line chart. An advanced trick is to
create the chart with the first series as a line chart, to produce the nice
line chart time-scale axis. Double click the date axis, and on the scale
tab, change the base unit to days, then pick better endpoints than simply
the first and last date of the series.

Add the second series as described in the previous post, select it, and use
Chart Type on the Chart menu to change this series to an XY series. Then
double click this new series and on the Axis tab, select primary, so it gets
plotted on the same axes as the first series.

When you copy and paste the third series into the chart, Excel remembers how
you changed the settings for the second series, so this one is pasted as an
XY series without you having to change it.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______
 
Back
Top