need help creating a stock chart (example attached)

[jaadu]

Hi - I just wanted to check if it is possible to create a stock chart like the one I have attached in this thread?

I copied it off a financial web-site, but I would like to use the same visual concept for my project.

If someone can help me understand if this can be done in excel or another software, I would appreciate it.

Basically, I want to make a kick-ass chart for my project.

Thank you.

[Ken Puls]

Maybe not exactly the same, but pretty darn close, yes. The only part I see being an issue is the labelling of the bottom axis... actually... no I think we could even make that work with some tricks.

You'd need the data first though, and tell us what version of Excel you're running so we can help you with the steps.

[jaadu]

That would be excellent if we can make this work. I have attached my historic stock price information and I am using Excel 2007.

If Excel 2007 is not good enough, can I get an add-on to install into excel to help me do what I would like to?

[Ken Puls]

Excel 2007 will be good enough.

I've taken a quick stab at this before we start tweaking it up, but I need you to confirm that I'm working with the right pieces.

Currently I've charted the Close and Volume. The Close is the line, the Volume is the column chart. This is going to be quick, but I need to get onto something here:

• Delete row 2, as it's blank and then makes a contiguous data table
• Plot the data as a line chart
• Remove all series but the two we'e concerned with
• Select the "Volume" series and format it to plot on a secondary axis
• Change the vertical axis to have lower boundaries of 17.5 and upper boundaries of 32.5

We can further tweak this to make it look more like the chart you supplied, but I want you to confirm the measures I'm using are correct.

File (with chart so far) is attached.

[jaadu]

Ken - you are phenomenal.

Yes, you are working with the right information (volume and closing stock price). I may not want to start the chart from October 2010 (have not decided for now), but I can edit the information in the chart and select my start point.

Also, I reviewed your attachment, and was able to generate that myself, except for the volume information.

The only trouble I am having right now is that my chart shows the volume similar to the stock information (one line going left to right), how can I show my volume information as bars, and not the auto generated line?

I might mess with the volume boundaries as well, but we are surely in the right direction.

Another question I have is, if I show the stock information for 5 days, can I add a space between each day on the chart, so the chart does not have 1 line, but instead, 5 broken lines?

[Ken Puls]

To show one as the column chart and one as lines, right click the one you want as column and select "Change Chart Type"

With regards to the broken line, I'll have to think on that one... We might have to fake it out by using a stacked column and separating the data.

I'm out tonight, but I'll try and look back on this tomorrow with you.

[jaadu]

Ken - thanks for the note on the bars, I was able to figure it out as well. Guess if you mess around enough, you get to know the ins and outs as well.....or some of them.

Well thanks to your help, I have made significant progress with the chart and have attached it as a pic below. As I continue to see the chart develop, I am starting to better determine which direction I would like to take the chart, and this is where I will need your help. Listed below are my challenges and am requesting if you can help me out here:

1. Can I separate/partition the chart by quarters? So instead of seeing 1 complete chart, there will be 5 broken sections (Q4-2010 - Q4-2011) with the price line at the top and volume bars at the bottom...as I already have it. This will help indicate the quarterly movement for the past few quarters. If that is too challenging, can we have the chart self-generate a vertical line (maybe in red) separating the quarters, instead of using the picture object or something and doing it manually?

2. Considering we can do one of the items mentioned in #1 above, I would like to show the quarterly change as a $$ and % change. I am assuming a text box can do this, but I am also assuming I will have to shift the text box as I continue to add more stock price information. Can we have the chart self-generate this information, instead of having to shift the 5 text boxes (1 for each quarter) every time I add more data?

3. "I believe" the volume bars that are in the chart show the actual stock information (even though it is only volume), because the volume bars represent information for the days the stock actually traded. There are no volume bars for weekends or holidays, however, the stock price line on the top of the chart seems to be running continuously from start to finish. Is there a way I can fill in the gaps for the volume bars.....I tried to make the lines thicker, but will that fill in all the gaps? Sounds odd to ask for this fix, considering the volume information is accurate.

4. Considering I separated the x-axis data by 3-months, why doesn't Dec 2011 show at the far right of the x-axis?

This is all I have for now and I hope my comments or questions have not confused you. If you have any questions, please feel free to ask and feel free to respond when time permits. I do not want to trouble you with my excel questions.

But most importantly, I appreciate all your help.

Thanks.

[Ken Puls]

Hi Jaddu,

I'm going to try and look at this over the weekend. A bit too many other things going on... may not be till Sunday though...

[Ken Puls]

Hi Jaddu,

Sorry for the late reply, things have been absolutely nutty here.

I've attached a version of the chart that is stripped down a bit more to only show 3 months of data. We do this by plotting with the NA() function, as #N/A values aren't charted.

I'll look into the quarter separation later, as I'm out of time for dealing with it now. I don't think you really want to fill the volume in though... if you do you should use a line chart. But that will imply stock trades even when the market is closed, which isn't true...