Scatter Chart
Posted by Steve Wasylkowski on April 20, 2001 12:02 PM
HELLLLLP! I am trying to create scatter chart in Excel 2000. X axis will be S&P 500 return, y axis will be Portfolio return. Values will run from -20% to +20% for each axis. I would like these values reflected outside of the actual scatter chart. I would like to plot a straight 45 degree line to reflect equal returns for portfolio & S&P. Then I would like to plot (portfolio return - S&P return). This plot would just be dots showing the relative value of portfolio vs S&P. If the dot is above the 45 degree line it's good, meaning portfolio outperformed S&P. A dot below 45 degree line would be bad (underperformance).
How would you set up something like this?
Steve
Posted by Mark W. on April 20, 2001 12:13 PM
Steve, please provide a smidgen of raw data.
Posted by Steve Wasylkowski on April 20, 2001 12:34 PM
Portfolio S&P
-3.50% -5.09%
8.58% -2.01%
-1.58% 9.67%
-7.36% -3.08
-1.14% -2.19%
-0.34% 2.39%
-0.52% -1.63%
3.45% 6.07%
-1.09% -5.35%
-6.65% -8.01%
6.41% 0.41%
0.03% 3.46%
-8.73% -9.23%
Posted by Mark W. on April 20, 2001 3:28 PM
Steve, this should get you jump started...
Let's assume that cells A1:B14 contains your data...
{"Portfolio","S&P"
;-0.035,-0.0509
;0.0858,-0.0201
;-0.0158,0.0967
;-0.0736,-0.0308
;-0.0114,-0.0219
;-0.0034,0.0239
;-0.0052,-0.0163
;0.0345,0.0607
;-0.0109,-0.0535
;-0.0665,-0.0801
;0.0641,0.0041
;0.0003,0.0346
;-0.0873,-0.0923}
...which has been formatted as Percentage (with 2
decimal places), and cells D1:D2 contains {-0.2;0.2}.
Here we go...
1. Select cells A1:B14, choose the Insert Chart...
menu command, pick the XY (Scatter) chart type, and
press Finish. Ugly, isn't it! Well, let's clean it
up.
2. (Optional) Only because I dislike grey background
patterns... right-click somewhere on the grey Plot
Area and choose the Format Plot Area... command, and
click the [None] Area radio button.
3. Next, click on the legend and press the Delete key.
4. For now, do the same for the "S&P" chart title.
5. Right-click on the X-axis, choose the Format
Axis... command, and do the following:
a) On the Patterns tab click the [Low] Tick mark
labels radio button.
b) On the Scale tab set [Minimum] value to -0.2
c) On the Scale tab set [Maximum] value to 0.2
d) On the Scale tab set [Value (Y) axis Crosses
at] value to -0.2 ...then press OK
6. Right-click on the Y-axis, choose the Format
Axis... command, and do the following:
a) On the Scale tab set [Minimum] value to -0.2
b) On the Scale tab set [Maximum] value to 0.2
7. With the Chart Area selected paste this
formula in to the formula bar:
=SERIES(,Sheet1!$D$1:$D$2,Sheet1!$D$1:$D$2,2)
8. Right-click on the newly added data marker at
(-20%,20%), choose the Format Data Series...
command, and do the following:
a) Set the Patterns Marker radio button group to
[None].
b) Set the Patterns Line radio button group to
[Custom], and pick your desired color, style,
and weight... then click OK.
9. With the Chart Area selected choose the Chart
Chart Options... menu command, pick the Titles
tab, and set the [Value (X) Axis] label to "Portfolio"
and the [VALUE (Y) axis] label to "S&P"
Well, that should get you most of the way there!
Let me know how it goes.
Posted by Steve Wasylkowski on April 21, 2001 7:11 AM
Urica!
Mark,
Thanks loads. It works fine. This is a great site ... I worked on this thing for a week.
Steve W.
Posted by L. on April 24, 2001 12:19 PM
That would be EUREKA!
