+ Reply to Thread
Results 1 to 8 of 8

How to create a clustered stock chart (High-Low-Close)?

  1. #1
    Registered User
    Join Date
    04-17-2013
    Location
    St. Louis
    MS-Off Ver
    Excel 2010
    Posts
    36

    How to create a clustered stock chart (High-Low-Close)?

    Hi there. I'm trying to create a clustered stock chart of the basic High-Low-Close variety. In the chart I'm making, I don't care about the close actually though, just the high and the low if that makes a difference. I've seen solutions to make a stacked chart clustered, but I'm having difficulty making a High-Low-Close one clustered. When completed, it would ideally look like:

    1. Dates across the X-Axis (Jan 5, Jan 6, Jan 7, etc).
    2. For each date, there would be two High-Low lines on that date. For example, if it were CSCO and INTC, you'd be able to see them right next to each other for Jan 5, then both right next to each other for Jan 6, etc.

    I hope this makes sense. If I can be any more specific, please let me know. I would really prefer the chart to be a high-low-close chart as opposed to redoing it as a stacked chart with adjustments made. Any help would be greatly appreciated.

  2. #2
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: How to create a clustered stock chart (High-Low-Close)?

    This sounds like a basic 2-column bar chart. Check this sample video, is this it? If not, does that get you to where you can get the chart type you do want? A little experimentation...

    View Example
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)

  3. #3
    Registered User
    Join Date
    04-17-2013
    Location
    St. Louis
    MS-Off Ver
    Excel 2010
    Posts
    36

    Re: How to create a clustered stock chart (High-Low-Close)?

    Hi JBeaucaire,

    Thank for the response! The data that I'm working with has a low and a high for each day, so I probably should have made that clearer. That's why I want to use the High-Low-Close chart which is the upper-left most chart type when you select "Other Charts". However, I can't figure out how to do the cluster effect (where the bars are right next to each other) or even insert a second series per day with that chart type. I do know that what I want MIGHT be possible by using the stacked column type chart,clustering it, and then making the lowest stack have no fill color. I've seen examples on how to cluster a stacked chart such as this: http://peltiertech.com/WordPress/clu...mn-bar-charts/

    Due to the nature of the data I'm using though, I'd like to use the High-Low-Close type chart if possible, and I'm not even sure that method would work on a High-Low-Close chart.

    So, to summarize, each day has a high and a low for each stock. The bar chart you did in the video is the look I'm going for, but with the High-Low-Close chart type. Hopefully this helps.

  4. #4
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: How to create a clustered stock chart (High-Low-Close)?

    I get it, Hi-Lo-Close.

    Meanwhile, here's what I would do, same chart type, Hi-Lo for each company with a blank "data set" inbetween to separate the company bars while keeping them in the group.

    After all, you did say don't really need the "close", so I'm still not chasing that dog.

    \1

  5. #5
    Registered User
    Join Date
    04-17-2013
    Location
    St. Louis
    MS-Off Ver
    Excel 2010
    Posts
    36

    Re: How to create a clustered stock chart (High-Low-Close)?

    Thanks JBeaucaire, I'll try to make something work from that!

  6. #6
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: How to create a clustered stock chart (High-Low-Close)?

    FYI, I did Google a bit and more experienced "chart wizards" seem to assert almost immediately that you can only have ONE Hi-Low in a Hi-Lo-Close chart, so it's not possible to use that to display multiple Hi-Low-Close for multiple stocks in groups, per day.

  7. #7
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: How to create a clustered stock chart (High-Low-Close)?

    And with the Columns, we can still get a usable visual: http://screencast.com/t/hecmA2lxmoG

  8. #8
    Registered User
    Join Date
    04-17-2013
    Location
    St. Louis
    MS-Off Ver
    Excel 2010
    Posts
    36

    Re: How to create a clustered stock chart (High-Low-Close)?

    Yeah, I figured that might be the case. I think what I'll either end up doing is:

    1. Creating a stacked clustered chart and making the bottom stacks "invisible" by not filling them with color to give the illusion of a Hi-Lo-Close chart. The problem with this is that because of some issues with data labels I'll want to use in the future, it probably won't work.
    2. More likely will be to create the chart with the Hi-Lo-Close chart type still but have them just not clustered together.

    I'll probably do both and see which looks the most visually appealing. Thanks again!

+ 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