NonZero Function

Author: Optuma Team Last updated: Jun 18, 2024 08:30

The NonZero function can be used to find when values are not equal to zero. This is particularly useful when looking for changes in Fundamental data, or boolean conditions where true results have a value of one and false a value of zero.

In this example the Short Interest data - which requires access to our Fundamental database - is plotted below the price chart of Microsoft. Because the Short Interest data is reported every two weeks the value doesn’t change every day, so the usual CHANGE() function can’t be used because it will return 0% (except on the day of the change). Instead, the NonZero() function can be used to find the difference from the previous value.

NonZero function

Here’s now to calculate Microsoft’s 28.9% increase in Short Interest from the previous reporting period:

//Get the Short Interest values;
//Find daily change;
X1 = D1 - D1[1];
Y1 = IF(X1==0, 0, D1);
//Get previous value of Y1 when Datafield value D1 changed (X1 wasn't 0);
P1 = NONZERO(Y1)[1];
//Calculate % change between current & previous values;

When displayed in a watchlist Microsoft’s 28.9% change was the largest increase of the 30 stocks in the Dow Jones Industrial index:

NonZero function

When looking for short interest changes from two reporting cycles ago, Travelers has fallen the most (-13.2%):

NonZero function

Save and open the workbook attached for the above example (requires US Equities and Fundamental Data).

Workbook Requirements Download
NonZero.owb Trader Services, US Equities & Fundamentals