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 PatelFinancial AdviserAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

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.

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 PatelFinancial AdviserAuthor 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.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

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

Start your 7-day free trial
Acronis Data Cloud 7.8 Enhances Cyber Protection

A closer look at five essential enhancements that benefit end-users and help MSPs take their cloud data protection business further.

Naresh PatelFinancial AdviserAuthor Commented:
Excellent ... May I Ask Follow Up?
Naresh PatelFinancial AdviserAuthor Commented:
Surly New Question
ProfessorJimJamMicrosoft Excel ExpertCommented:
great work teylyn
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.