The Excel Scatter Plot Chart

This post details using the Microsoft® Excel “Scatter Plot” Chart to track market performance. In this example, RTD formulas are used to pull in the percent net change of the 30 stocks that are in the Dow Jones Industrial Average from CQG. Two charts are provided. One, is the percent net change using the symbols in alphabetical order, the second chart is the ranked performance sorted by best to worst.

The RTD formula for pulling in net percent change used in column B:

=RTD("cqg.rtd", ,"ContractData",A2, "PerCentNetLastTrade",, "T")/100

The first chart is column B. Select cells B2 through B31 then Insert/Scatter from the Excel ribbon.

scattermainpage_and_f1

The net percent change can be ranked. Here is a modified Excel Rank function, which will not result in ties:

=RANK($B2,$B$2:B$31)+COUNTIF($B$2:B2,B2)-1

The above Excel formula is copy and pasted down from C2 to C31. Once the values are ranked and the associated symbol is displayed in column D. Values from 1 to 30 are in column E.

The VLookup Excel function then pulls the associated symbol by rank in column F:

=VLOOKUP(E2,$C$2:$D$31,2,FALSE)

The VLookup function returns the symbol and the RTD formula for net percent change is in column G. Column G is the data for the second Scatter chart.

scatterf2

Requires CQG Integrated or QTrader. Strongly recommended: Microsoft Office Professional Excel 2016, 2019, 32 or 64-bits installed on your computer, not in the Cloud.

Downloads

Disclaimer

Trading and investment carry a high level of risk, and CQG, Inc. does not make any recommendations for buying or selling any financial instruments. We offer educational information on ways to use our sophisticated CQG trading tools, but it is up to our customers and other readers to make their own trading and investment decisions or to consult with a registered investment advisor. The opinions expressed here are solely those of the author and do not reflect the opinions of CQG, Inc. or its affiliates.