auto range for chart y values is zero

If you normalize the data before charting it, the lowest value of the dataset being charted will be transformed into a 0 and the highest into a 1. Or, for a better looking chart, the lowest data point could slightly above 0 and the highest slightly below 1 so there is some room above and below on the chart. The chart would be set manually to have a minimum of 0 and a maximum of 1 and a given number of steps. All of this will be the same for whatever dataset you chart.

The Y values on the chart (0 up to 1) will have no relationship to the actual values being charted so you want to turn them off. With a 1-column table in place of the Y values, you can create your own Y values. It is simple to put a min and max on the axis but it would take some cleverness to create a min and max that would work out well for the number of steps. So in this example below I left off all the intermediate numbers. Actually, it takes a little more cleverness than my formulas to make a good min and max for the chart. The ones for "CDE" company aren't that great.

Columns A-D are all typed in data except the two footer rows that use the MIN and MAX formulas.

E2 =INDEX($2:$2,MATCH(TRUE,$1:$1,0))

looks for the checked box and copies over the company name

E3 =(INDEX(3:3,MATCH(E$2,$2:$2,0))−E$6)÷(E$7−E$6)

Brings over the value for this row and normalizes it using the min and max in the footer rows.

To complete the column, fill down from E3 to the last data row (not into the footer rows)

E6 =INDEX(6:6,MATCH(E$2,$2:$2,0))×90%

E7 =INDEX(7:7,MATCH(E$2,$2:$2,0))×110%

These are values used for normalizing the data for the chart.

The table to the left of the chart references the values in E6 and E7 of Table 1. The gridlines, table name, table outline are turned off. When you click off of the table it disappears other than the min and max values. I clicked on it so you could see it in the screenshot.

I did not know how your table was set up. The example above is just to show how it can be done.

There's a trade-off here, considering that Numbers is used to graph many other things besides stock prices.

See the "truncated" graph section at https://en.wikipedia.org/wiki/Misleading_graph.

Not relevant to stock prices, but if you're trying to compare series of wildly different values in the same chart, especially to compare rates of change, one thing you can do is set an axis scale to Logarithmic.

SG

Yes, I think a web-based system is the way to go with stock charts. There are many, and they're better designed for the purpose than Numbers or other spreadsheets.

SG

Looking at this again, it looks like it is row 4 that has AAPL so you need to make a new row 5 for the formulas and use it for the chart. I don't know what the best algorithm would be to calculate the min and max for the normalization.

Did you see my link to discussion of a "truncated" graph? In many situations not starting the axis from 0 is "cheating."😀

SG

The-Old-Guy wrote:

Because of that I assume this is an unsolvable problem.

Not unsolvable, just a difference of opinion on how it should work.

There is no way I know of to override the algorithm. Your statement about it not setting he min (low point) based on the data is incorrect, though. It does set the min (low point) based on the data but once the high point vs low point gets above a certain threshold, it drops the min to zero. Excel does pretty much the same thing as best I can tell. It looks to be roughly around a 16% difference but the algorithm may be more complex than just going by a percentage difference.

There is more than one problem with trying to plot wildly different values on the same chart. Aside from the min/max not always being what you might like, the number of gridlines (steps) is often not ideal either and the Y axis values often come out to unusual numbers. So, not only do you have to set max and min, you also need to set the number of steps when you are plotting wildly different values using the same chart.

One thing you could do is normalize the data so, regardless of the stock, the "normalized" data that will be charted is all in the same range. You would have to turn off your Y axis values because they won't make sense. You could replace the Y axis values with a 1-column table of values that you calculate for each gridline.

You need to chart normalized data. Values between 0 and 1 is typically what that means. For that you need to come up with a formula to determine how to normalize your data. In my example, I found the minimum and maximum of the dataset, multiplied the min by 90% and the max by 110%, then used this equation:

Normalized datapoint = (actual datapoint - min)/(max-min)

So, in your "50 column table", below the row that has AAPL (which I assume is row 3), add a new row (row 4), put this formula in B4 then fill right:

=(B3-90%*MIN(3:3))/(110%*MAX(3:3)-90%*MIN(3:3))

Make your chart from row 4 instead of row 3. Don't show the Y axis values. Set the min of the chart to 0 and the max to 1 and whatever number of steps you want. Make a 1-column table to use as the new Y values. Resize it so the text in the cells line up with the gridlines. You will probably only put the min and max values because it will be difficult if not impossible for all the steps to be "nice" numbers.

SGIII has an exceptional point about misleading charts. The simple normalization algorithm I am using here will create misleading charts. A stock that changes by hundreds of percent and one that changes by hundredths of a percent may look exactly the same on the chart. You should come up with a better algorithm. Really, this one is not good for what you want to do. I think you want to plot your data linearly. I think linear is typical for stock charts. That means you need a better method to calculate the min and/or max. Maybe you set the min similarly to how I did above and the max is a percentage (100%, 200%, or something else) above it. You might need to put that percentage in a cell that you can edit in case the stock you are looking at went wild.