Stock Performance Charting

[ELCADAROSA]

I have a spreadsheet tracking a stock's weekly performance and want to effectively graph it as described below.

My spreadsheet is setup as follows:
Column A: Date of last trading day of the week - usually a Friday - format; Short Date (mm/dd/yyyy).
Column B: Closing price of the stock for that trading week - format; Accounting.
Column C: Total volume of trading of that stock for the week - format; Number.
Column D: Earnings Per Share (EPS) per Quarter, entered on the same row as the date the quarter ended - format; Accounting.

I have a chart setup as follows, but it isn't quite what I want:
Left Y-axis (primary axis): arithmetic scale, reflecting the EPS dollar amounts.
Right Y-axis (secondary axis): arithmetic scale, reflecting the stock price.
Horizontal X-axis: associated with week-ending date.

I want to change the Y-axis' such that one reflects the EPS amount (preferably the left axis), and the other the Stock Price such that each grid line Stock Price amount indicated is equivalent to 20x the EPS amount; i.e. a $.50 EPS gridline amount is the same as $10 stock price, a $1.00 EPS gridline is equivalent to $20 stock price, the $2.00 EPS = $40 price, $3.00 EPS = $60 price, $4.00 EPS = $80 price, ad-nauseum.

To put it more simply, each grid line should reflect:
Either: Stock Price = 20 * EPS
Or: EPS = .05 * Stock Price

I've seen charts like this before, so it must be able to be done. Using Microsoft Access is also a possibility, but I prefer to prove calculations, etc. in Excel before moving on to Access.

Using Office 2007/2010 on Windows XP & 7.

Any help is greatly appreciated.

 

[tusharm]

Format the right y-axis so that the major gridlines are 20 times the left y-axis gridline value. Also make sure that the right y axis min. value is 20 * the left y axis min. value.

 

[ELCADAROSA]

Thank you for your reply, and I apologize for not getting back sooner.

I'm not sure I follow you. I right-click the right axis and selected 'Format Axis', but I can't get the right combination of Min/Max values and Major/Minor units to work the way I want them.

The left axis (EPS) begins at $0.00 and has its upper level presently at $0.40, but this can increase automatically if the stock being tracked has a jump in EPS.

The right axis (Share Price) is also begins at $0.00 and has an upper level at $30.00, but this could also rise automatically if the stock price increases.

I guess I'm asking - based on the above - how do I set the major gridlines for the right axis to be consistantly 20-times the left axis, regardless of EPS or share price? The baselines for both vertical axes start at 0, which is default of course, but anything times 0 is 0.

I tried changing the left axis minimum to .05, and the right axis minimum to 1.0. With these settings, the top end of the left scale is at 0.40, but the top end of the right scale is 28.00, not 8.00 (0.40 times 20 = 8.00). Now, how do I format the major gridlines for the right axis to be consistantly 20-times the left?

 

[tusharm]

If your EPS axis has a range of 0 to 0.4 and the share price axis a range of 0 to 30 then the ratio of the 2 is not 20 but 75 (30/0.4).

If I plot some dummy data to get these min and max values, the left axis major grid lines are 0.05 apart. So, the right axis major gridlines should be 0.05 * 75 or 3.75 apart. When I specify this value for the right axis major unit value, the major grid marks on the left and the right align.

 

[ELCADAROSA]

Thank you again for your reply.

I did as you suggested, but I'm still not getting the results I want. The right axis should look more like a logarithmic scale; every major gridline measured on the right axis must always be 20-times the value of the left side.

For example:
when the left axis is 0.05, the right axis must be 1.0
when the left axis is 0.10, the right axis must be 2.0
when the left axis is 0.15, the right axis must be 3.0
when the left axis is 0.20, the right axis must be 4.0
.
when the left axis is 0.40, the right axis must be 8.0

But the stock prices are higher than that, so I want the price axis to expand accordingly with corresponding EPS prices on the left side, even if my EPS data does not go so high; meaning, if the stock's price is $30, I want the EPS axis to have the equivalent grid line at $1.50 even if the EPS never comes close to that.

Perhaps I'm going about this in reverse ... Perhaps I should have the stock price show on the left (dominant) axis, and the EPS show on the right axis. Then I might be able to adjust the major gridline spacing be 1/20th of the left (stock price) axis.

 

[Jon Peltier]

Its really not as compex as you've made it. Format the axis that makes everything fit, and note the min, max. and spacing. Then format the opposite axis using that factor of 20. If the price axis is the one you formatted first, then format the EPS axis such that:

min(EPS) = min(price)/20
max(EPS) = max(price)/20
major(EPS) = major(price)/20

If the EPS axis is the one you formatted first, then use these relationships:

min(price) = min(EPS)*20
max(price) = max(EPS)*20
major(price) = major(EPS)*20