Excel 3-D Bubble Chart

The Microsoft® Excel 3-D chart is different from the Excel Scatter Plot chart. The Scatter chart uses a fixed size for the chart data points. The 3-D chart accesses an additional column to set the width of the chart data points. In this post setting up a 3-D chart is detailed for monitoring the S&P 500 and the sectors using the Select Sector SPDR® Fund symbols.

First, the downloadable Excel sample has this table of data in the top left-hand corner.

  • Column A: Symbol
  • Column B: Last Trade
  • Column C: Net Change
  • Column D: % Net Change
  • Column E: Last Trade

Column E repeats using the value of the Last Trade for setting the width of the chart data points.

The symbols include:

Symbol Description
SPY SPDR S&P 500
XLB Materials Select Sector SPDR
XLC Communication Services Select Sector SPDR
XLE Energy Select Sector SPDR
XLF Financial Select Sector SPDR
XLI Industrial Select Sector SPDR
XLK Technology Sector SPDR Fund
XLP Consumer Staples Select Sector SPDR
XLRE Real Estate Select Sector SPDR
XLU Utilities Select Sector SPDR
XLV Health Care Select Sector SPDR
XLY Consumer Discretionary Select Sector SPDR

At the time of this writing the market values generated by RTD formulas in Excel are displayed below.

f1

The RTD formulas:

Last: =RTD("cqg.rtd", ,"ContractData",A2, "LastTrade",, "T")

NC: =RTD("cqg.rtd", ,"ContractData",A2, "NetLastTrade",, "T")

%NC: =IFERROR(RTD("cqg.rtd",,"ContractData",A2,"PerCentNetLastTrade",,"T")/100,(RTD("cqg.rtd",,"StudyData",A2, "Bar", "", "Close","D","-1",,,,,"T")-RTD("cqg.rtd",,"StudyData",A2, "Bar", "", "Close","D","-2",,,,,"T"))/RTD("cqg.rtd",,"StudyData",A2, "Bar", "", "Close","D","-2",,,,,"T"))

The RTD formulas for %NC uses the difference between one day and two days back if the “percent net last trade” is not available, which occurs before the start of the next session.

To display the 3-D Bubble chart cells D2:E13 were selected.

f2

And, then from the X Y (Scatter) group 3_D Bubble was chosen.

f3

Column E (Last price) determines the width of the bubble data point. And, because the last price for SPY is more than two to three times the prices for the Sector ETFs the bubble is three times the width of the other symbols. Looking at the above image you see that three of the ETFs have a lower percent net change and the others have a higher percent net change. A more efficient display would be to rank the percent net changes of each instrument and then display the instruments by market performance.

f4

The image above shows the Excel spreadsheet ranking in cell A17:A19 the percent net change from cells D2: D13. The Excel function does not allow ties. Read more here.

=RANK(D2,$D$2:$D$13,0)+COUNTIF($D2:D$13,D2)-1

Column D uses VLookup to display the top to bottom performing instruments.

=VLOOKUP(C17,$A$17:$B$28,2,FALSE)

f5

Displaying the instruments by ranked performance is an improvement. Again, the SPY 3-D bubble is three times larger and you easily see which ETFs are out performing SPY and which are not.

Requires CQG Integrated Client or CQG QTrader and Excel 2010 or more recent installed on the computer, not in the cloud.

Downloads

Microsoft Excel is a registered trademark of Microsoft Corporation in the United States and/or other countries.