Worksheet – Case #6 – Counting occurrences of events with the formula language

Finding Data

To find the Volatility Index for the S&P, you can use the Country & Region entry:

Go to:

Country & Region > United States > Equity Market > Volatility Indices > CBOE.

Locate and select:

  • S&P 500 Volatility Index (VIX), Close

Add the series to a new document.

You can also use the series short code in the Series List expression, by typing vix

Analysis

Formula

The aim of the document is to calculate how many times the VIX has been lower than 10 in history.

The first step is to know when the VIX has been lower than 10. You will have to formulas to achieve this:

  • Select “Time chart” in the tree
  • Click on “Insert” and choose Formula

Now, you should create a logical expression:

  • Click in the expression text-box
  • Type: fx:s1 < 10

This will create a binary time series, returning either 1 or 0:

  • 1 will mean TRUE
  • 0 will mean FALSE

So now you have identified when the VIX is lower than 10. The next step is to count its occurrences.

In other words, you need to sum the 1 and 0 of the previous logical series, over time.

While you could use the Aggregate analysis to do this (using Period: All as setting), we will stick to the formula language in this example. Create a second expression:

  • Aggregatesum(fx:s1<10)

This will create a new time series where the value at each observation is the sum of the values of all observations preceding it.

Charting

Graph Layout

To adjust the appearance of our graph line corresponding to the condition VIX < 10:

  • Open Graph Layout
  • On the right half of the window, select the condition series
  • Choose Fill as the graph type
  • To move the Fill indicator into the background, click and drag Fill above Line

Axis value label

To add the last value on the y-axis for the aggregate sum series

  • Click on this specific graph line
  • On the “Presentations Properties” tab locate the “Axis value label” adjustment
  • Select Show from the roll-down menu

Other changes

  • Title: United States: VIX
  • Subtitle: Very low volatility: number of occurrences
  • The legend labels has been changed
    • VIX < 10
    • VIX<10: nb. of occurrences