Chart In Excel

Hi Experts,

I have One Excel File In Which It Produce Stock Chart. I want to implement logarithmic scale to Vertical axis as if i zoom on chart it dosent show perfect picture of price movement. any one have idea how to do 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.


As far As I know, the logscale can only show the powers of the base

for example with base 10 = 1, 10, 100, 1000 for your chart if min automatic
if min 50 then = 50, 500

Naresh PatelFinancial AdviserAuthor Commented:

it cant be possible - if we zoom the chart  vertical axis change accordingly to the prices in chart window?

Could you send an example of your attempt and tell what is wrong?
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Naresh PatelFinancial AdviserAuthor Commented:

See attached in my original question.i had clicked on vertical axes in chart area & change to log scale to 100 but in that case seen is must worst then actual. Good ChartBad Chart as in above chart 1 look perfect and giving better picture of price movement but after clicking one time to zoom it goes worst in chart 2 ...just need to over come this kind of situation... so i thought that log scale is the key but in log scale situation goes more worst then actual.

any idea how to tackle this?

Thank You
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:

a stock  chart will default the minimum Y axis value to 0, regardless how useful or useless that is. To change the setting dynamically, you will need VBA. Since your file is already a macro-enabled .xlsm type, this will be very easy.

First we need to determine the minimum value for the Y axis, based on the current data set. For this, we calculate in a spreadsheet cell


It determines the minimum data value for all the candle stick series and then rounds that down to the nearest 100. If you want a different rounding factor, play with the last parameter of the Floor function.

For ease of use let's give this cell the range name "chtYMinimum". You can hide the column with this helper cell or put it on another worksheet if it upsets your spreadsheet design.

Now we can use the following code to set the value.

Sub SetYAxisMinimum()
Dim ws As Worksheet

Set ws = ThisWorkbook.Worksheets("Chart")
'this sets the Y axis minimum value on the upper chart
ws.ChartObjects("Chart 1").Chart.Axes(xlValue).MinimumScale = Range("chtYMinimum")

'this sets the secondary Y axis minimum value on the lower chart
ws.ChartObjects("VolumeChart").Chart.Axes(xlValue, xlSecondary).MinimumScale = Range("chtYMinimum")

End Sub

Open in new window

We can now bind this code to the two controls. Right-click a control, select "Assign Macro" and select the macro called "SetYAxisMinimum". Repeat with the other control.

Now a click on the controls will change the source data range, the worksheet cell will recalculate and the macro will apply the new value to the Y axes of the charts.

See attached. (I renamed the lower chart to "VolumeChart" to avoid confusion)

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
Naresh PatelFinancial AdviserAuthor Commented:
Supra Perfect Thank You Very Much.
Naresh PatelFinancial AdviserAuthor Commented:
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.