# 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

Thanks
Log-Chart.xlsm
LVL 8
###### Who is Participating?
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.

Commented:
HI,

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

Regards
0
Sir.Rgonzo1971,

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

Thanks
0
Commented:
Could you send an example of your attempt and tell what is wrong?
0
Sir.Rgonzo1971,

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. 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
0
Microsoft MVP ExcelCommented:
Hello,

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

=FLOOR(MIN(chtOpen,chtHigh,chtLow,chtClose),100)

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

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
LT--1-.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.

Supra Perfect Thank You Very Much.
0