# 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

Thanks
LT.xlsm
LVL 8
###### Who is Participating?

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Microsoft MVP ExcelCommented:
Hello,

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.

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)
``````

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
LT.xlsm
0

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.

Thanks
0
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
LT.xlsm
0

Experts Exchange Solution brought to you by

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.