Stock Performance Charting

ELCADAROSA

New Member
Joined
Jul 24, 2010
Messages
3
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.
 

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

tusharm

MrExcel MVP
Joined
May 28, 2002
Messages
11,029
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

New Member
Joined
Jul 24, 2010
Messages
3
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

MrExcel MVP
Joined
May 28, 2002
Messages
11,029
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

New Member
Joined
Jul 24, 2010
Messages
3
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

MrExcel MVP
Joined
May 14, 2003
Messages
4,966
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
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
 

Watch MrExcel Video

Forum statistics

Threads
1,133,249
Messages
5,657,609
Members
418,403
Latest member
icedwateriscold

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top