+ Reply to Thread
Results 1 to 3 of 3

need help charting a time progression for investments

  1. #1
    Elwood
    Guest

    need help charting a time progression for investments

    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

  2. #2
    Jon Peltier
    Guest

    Re: need help charting a time progression for investments

    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/
    _______


    "Elwood" <[email protected]> wrote in message
    news:[email protected]...
    > 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




  3. #3
    Jon Peltier
    Guest

    Re: need help charting a time progression for investments

    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/
    _______


    "Jon Peltier" <[email protected]> wrote in message
    news:uYQ%[email protected]...
    > 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/
    > _______
    >
    >
    > "Elwood" <[email protected]> wrote in message
    > news:[email protected]...
    >> 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

    >
    >




+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1