Chart In Excel

Hi Experts,

Will Any One Help Me Stock Chart In Excel.

I have in WS - Open High Low Close Volume
I had tried to use candle chart in excel but I don't know why it not up to the mark. I need to attach volume bares below stock chart. as well as need zoom button to see particular time period in chart as I selected whole so it shows me whole data in chart but cant make it anything from this kind of look. so is there any way to zoom these chart via some button or something like that?

See attached

Naresh PatelTraderAsked:
Who is Participating?
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
re A: Excel recognizes the date and plots them on a time axis. That means that days with no data will be gaps in the chart.

If you don't want that, format the X axis and set it to a Text or Category axis. Then there will be no gaps in the chart.

re B: I cannot see a good way to add the volume numbers to the chart. You can plot a stock chart including Volume:

see attached file.

cheers, teylyn
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:

I'm not sure I understand your question. Excel can plot a candle stick chart from your data and your file shows that, but there are so many days of data that all the data points blur into something that looks like a line chart.  If you want to see a candle stick chart, you need to select only a few days of data.

What are "volume bares" and what do you want to do with them?

It is possible to create a dynamic chart that only shows a sub-set of the data.

Please see the attached file.

On the Chart sheet, I have added user input to determine the start date of the chart and the number of days to show in the chart.

These two cells are bound to named ranges.

DaysInChart      =Chart!$B$4
StartDate      =Chart!$B$2

I also created named formulas for the chart based on the values entered in the sheet. This is the list of ranges and their formulas:

chtLabels	=INDEX(Data!$A:$A,MATCH(StartDate,Data!$A:$A,0)):INDEX(Data!$A:$A,MATCH(StartDate,Data!$A:$A,0)+DaysInChart)
chtClose	=OFFSET(chtLabels,0,4)
chtHigh	=OFFSET(chtLabels,0,2)
chtLow	=OFFSET(chtLabels,0,3)
chtOpen	=OFFSET(chtLabels,0,1)

Open in new window

I selected the chart and replaced the absolute cell ranges of each series and the X category labels with these range names.

Now the user can enter a start date and select how many days of data to show in the chart.  The chart will show as a candle chart unless the user selects so many days that the candle blurs into a line chart.

See attached.

cheers, teylyn
Naresh PatelTraderAuthor Commented:

Seems Perfect. but...

A) In chart there is also shown dates which is not in data sheet column 1

B) About volume - need to attached below candle chart in same chart pane.

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Naresh PatelTraderAuthor Commented:
Excellent ... May I Ask Follow Up?
Naresh PatelTraderAuthor Commented:
Surly New Question
great work teylyn
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.