analysis of ES Pct and Pts swing using Excel Data Analysis Tool

BI used the market delta zig zag tool and I found 525 instances of swing being more than 2% since 2006. The % figure was decided by myself by looking at the chart to see swings that would occur during many weeks. I was looking for something that would help estimate the length of swings and to help figure out whether a swing was above or below average. I used points also but this has of course reduced as the contract price increased.

I used the excel ABS function to invert negative swings (but I think I should separate them and study them separately). The scatterplot over time and a linear trendline are on the following:

PCT change (left), PTS (change) right


The following image shows the same data but it is categorised.

The green data on the left side of is the output of the Excel Data Analysis Histogram function. It automatically calculates the category sizes.

The chart at the right is just a different visualisation of the categorised data. I marked the area enclosing the [mean + 1st standard deviation] in red. I also plotted the cumulative % sloping up to 100% as the number of values increases.



30-70 pts swings are most common but 100-140 occur 12% of the time.

Swings over 90 can occur 25% of the time. The cumulative pct of them being under 90 is 75% – as output on the cumulative % calculation.

This is the type of basic visual analysis that can be done with excel. This is far from perfect and I am far from an expect so beware. I have just started to study statistics so there are lots of things I don’t know about the exact way to handle time series data or the correct way to choose the % in a series which has an upward bias. I am also looking for more info on the exact usage of the histogram function.

This is the chart I used to extract the data.


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s