+ Reply to Thread
Results 1 to 13 of 13

grouping chart and lines

  1. #1
    Registered User
    Join Date
    06-05-2012
    Location
    India
    MS-Off Ver
    Excel 2003 Excel 2007
    Posts
    9

    grouping chart and lines

    I have a stock-chart in Excel.
    Certain highs & lows are connected by drawing a line manually (from the drawing toolbar).
    When I change the data-range or resize the chart, the lines obviously stay in their original position.
    And the bars in chart get resized.

    Is there any way to maintain the relative positions of chart & hand-drawn-lines?
    Maybe, merging the bars in chart with the lines drawn by hand?

    Thanks,

    Vineet
    Last edited by d_vineet; 06-05-2012 at 03:55 AM. Reason: added further info

  2. #2
    Valued Forum Contributor
    Join Date
    06-09-2010
    Location
    Australia
    MS-Off Ver
    Excel 2010
    Posts
    1,069

    Re: grouping chart and lines

    Could you upload a workbook showing what you're trying to achieve?

  3. #3
    Registered User
    Join Date
    06-05-2012
    Location
    India
    MS-Off Ver
    Excel 2003 Excel 2007
    Posts
    9

    Re: grouping chart and lines

    chart and hand-drawn-lines.xls

    I have attached the file & written a note in the excel file itself for explanation.

    Thanks,

    Vineet

  4. #4
    Valued Forum Contributor
    Join Date
    06-09-2010
    Location
    Australia
    MS-Off Ver
    Excel 2010
    Posts
    1,069

    Re: grouping chart and lines

    Hi
    you can't merge lines in chart with shapes, so far as I know. And the stock chart formats are quite inflexible.
    Others may come up with a better solution, but the only thing I can think of is to write a macro that adjusts the lines.

    Are there always 3 dates in your chart, or do you add more - in which case, more lines will be needed

    also, which are the points you're trying to connect - it looks the the high from day 1, the low from day 2 and the high from day 3. Is that right?

  5. #5
    Registered User
    Join Date
    06-05-2012
    Location
    India
    MS-Off Ver
    Excel 2003 Excel 2007
    Posts
    9

    Re: grouping chart and lines

    Thanks for replying.
    There are going to be a number of dates.
    For the sake of simplicity, I have included a few.

    The logic for joining tops & bottoms differs. Again, for simplicity, I have connected the low & high of consecutive days.

    In a 0.5% swing chart, after 0.5% or more decline and then 0.5% or more rise in up-move, low of decline is connected with high of earlier move.

    In short, it requires manual intervention.

    Sad to learn that the line and chart object can't be combined.

    Regards,

    Vineet
    Last edited by d_vineet; 06-05-2012 at 05:12 AM. Reason: grammer corrected

  6. #6
    Valued Forum Contributor
    Join Date
    06-09-2010
    Location
    Australia
    MS-Off Ver
    Excel 2010
    Posts
    1,069

    Re: grouping chart and lines

    If you can create a column which includes the value for the start or end of the line for that day, perhaps based on the formula you mention, I can write a macro that will draw lines connecting those points on your chart.

  7. #7
    Registered User
    Join Date
    06-05-2012
    Location
    India
    MS-Off Ver
    Excel 2003 Excel 2007
    Posts
    9

    Re: grouping chart and lines

    That would be great.
    I can keep a column which would mark start or end of line.

    For your macro, I would be grateful to you.

    Thanks,

    Vineet

  8. #8
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    2003, 2007, 2010 & 2013
    Posts
    16,179

    Re: grouping chart and lines

    Add a series and move to the secondary axis. Delete the secondary value axis so the values align.
    Attached Files Attached Files
    Cheers
    Andy
    www.andypope.info

  9. #9
    Valued Forum Contributor
    Join Date
    06-09-2010
    Location
    Australia
    MS-Off Ver
    Excel 2010
    Posts
    1,069

    Re: grouping chart and lines

    as ever, Andy's solution is far better than mine!

  10. #10
    Registered User
    Join Date
    06-05-2012
    Location
    India
    MS-Off Ver
    Excel 2003 Excel 2007
    Posts
    9

    Re: grouping chart and lines

    I think that it is near to what I am looking for.
    Why I am saying "near to" because NOT all the bars are to be connected.
    The start & end points of lines are determined by swing technique.
    I have briefly explained it in reply to "NickyC".

    If I wish to connect only the selective bars (and NOT all bars), how would I do it?
    I have attached the file with a note explaining what I wish to achieve.

    chart with my question.xls

    Pl. advise on the same.

    Thanks,

    Vineet
    Last edited by d_vineet; 06-05-2012 at 06:33 AM. Reason: formatting

  11. #11
    Registered User
    Join Date
    05-28-2012
    Location
    India
    MS-Off Ver
    Excel 2003
    Posts
    8

    Re: grouping chart and lines

    Dear Andy, maybe you can solve my excel charting problem too! .... Pl see my post on Stock Charts...

    reg
    ketan

  12. #12
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    2003, 2007, 2010 & 2013
    Posts
    16,179

    Re: grouping chart and lines

    replace the value in G3 with =NA()

  13. #13
    Registered User
    Join Date
    06-05-2012
    Location
    India
    MS-Off Ver
    Excel 2003 Excel 2007
    Posts
    9

    Re: grouping chart and lines

    @Andy, many thanks for your input.
    Your tip worked.

    Thanks again for sharing your knowledge & time.

    Regards,

    Vineet
    Last edited by d_vineet; 06-06-2012 at 12:40 AM. Reason: Thanks

+ 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