Chart In Excel

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.

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)

Select allOpen 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
LT.xlsm

Madam.teylyn,

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

Excellent ... May I Ask Follow Up?

Surly New Question

great work teylyn