How to Calculate Quarterly Performance in Optuma Watchlists

by Jun 30, 2018All Articles, Technical Analysis, Education

As you may know, Optuma watchlists allow you to add custom columns containing tool or indicator values (eg daily/weekly RSI, percent from a moving average or 52 week high) or true/false results based on any condition (is the close above the 34 exponential moving average, or is the Gann Swing trending up?). You can also create performance statistics based on time frames (eg over the last month, year-to-date), but we can also create quarterly statistics.

Here’s an example of the Australian ASX 200 Sectors from Q1 2017 to the close of Q2 2018, along with year-to-date and the 3 year annual rate of return:

The year-to-date column is very straight forward, using the Rate of Change function set to use yearly data:


The quarterly percentage columns are a bit more complicated, as we need to define the static starting and end dates. We can do this by combining the following functions:

MONTHNUM() – assigns a number for each month: 1 = January, 12 = December

YEARNUM() – eg 2017

VALUEWHEN() – gives the required value (the close by default) when a certain condition has been met.


So to get the closing value at the end of the first quarter in 2017 we need to create a  variable (M1) for the end of March 2017:

M1 = MONTHNUM() == 3 and YEARNUM() == 2017;

Note: a single ‘=’ sign is used to assign a variable so when looking for equality we need to use the double ‘==’ sign.


Next we use VALUEWHEN to get the closing value of M1 by placing it in the parentheses:


So now we have March’s closing values (V1) we need to compare it to the end of December 2016 by creating two more variables:

M2 = MONTHNUM() == 12 and YEARNUM() == 2016;



With these two closing values (V1 and V2) we can now calculate the percentage change between them using the following calculation:

((V1/V2)-1) * 100

Note that to display the columns in percentage format do not multiply by 100, and then you can right-click on the column heading and change the Column Type to Percentage.


Putting it all together for Q1 2017:

M1 = MONTHNUM() == 3 and YEARNUM() == 2017;

M2 = MONTHNUM() == 12 and YEARNUM() == 2016;



((V1/V2)-1) * 100


To calculate the column for the Q2 numbers we just change the month and year values in M1 and M2 to June and March:

M1 = MONTHNUM() == 6 and YEARNUM() == 2017;

M2 = MONTHNUM() == 3 and YEARNUM() == 2017;


Annual Rate of Return

This is calculated using the ARR() function, set to 3 years in this example:


Custom Column Colours

To change the watchlist colours to geen for positive and red for negative right-click on the column heading and select Custom Labels, and select green > 0 and red > 0 (delete the Label text as it’s not required in this example). Once changed, click on Clone Column to duplicate the column and then Edit Column to open the Script Editor window and change the formula as requested.

Optuma clients can click the button below to save a workbook with examples for Australian and US sectors, but if you would like to apply these columns to other watchlists click the No Layout label in the header and give it a name and then apply the layout to another list, or save the entire Page Layout.

Of course, other columns can be added to your watchlists as required (for example you may want to show the last rolling month CHANGE(INT_TYPE=Month)), so if you have any questions or comments please let us know, or visit our scripting forum for help and ideas.

Share Link
Darren Hawkins, MSTA

Darren Hawkins, MSTA

Senior Software Specialist at Optuma

Darren is the senior Software Specialist at Optuma. He joined the company in 2009 after attending an introductory technical analysis course. Darren now instructs users all over the world, from experienced Wall Street traders and professional money managers to individual traders drawing their first trendlines.

Darren grew up in the UK and attended college in the USA where he earned a BA in Economics from St Mary's College of Maryland. He went on to spend a few years working at the Nasdaq Stock Market in Washington DC. Going on to live and work in Australia, the US and currently the UK, Darren has a broad understanding of the individual needs of traders, portfolio managers and investors utilising a wide range of methodologies.

In 2014 Darren passed the UK-based Society of Technical Analysts diploma course, and when not looking at charts he keeps a keen eye on England's cricket team - especially if they are playing against Australia. He lives in the Essex countryside in England, with wife Wendy and their labrador, Gabba.


Submit a Comment

Your email address will not be published. Required fields are marked *

Blog Signup

Pin It on Pinterest

Share This

By continuing to use the site, you agree to the use of cookies. more information

The cookie settings on this website are set to "allow cookies" to give you the best browsing experience possible. If you continue to use this website without changing your cookie settings or you click "Accept" below then you are consenting to this.