How to Calculate Quarterly Performance in Optuma Watchlists

by Jul 1, 2020All Articles, Technical Analysis, Education

It’s been quite a wild ride in global markets so far this year, with many global indices rebounding strongly since the March lows (for a list of index performances around the world click here).  As you may know, Optuma watchlists allow you to add custom columns containing any 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, or from a specific date), but we can also create quarterly statistics. The following article will show you how to add these types of calculations, or you can skip to the bottom to download a workbook!

Here’s an example of the quarterly performance of the Australian ASX 200 Sectors since 2019 to the close of Q2 2020, along with year-to-date and the 3 year annual rate of return. It’s sorted by Q2 performance, so Technology $XIJ gained 48.7% following Q1’s 25% fall. Health Care $XHJ was the only sector to gain in both Q1 and Q2, and is currently up 7.4% this year.

Here’s how to calculate these values in any watchlist. The year-to-date column is very straight forward, using the Rate of Change function set to use yearly data:

ROC(Year(PERIODAMOUNT=1), BARS=1)

The quarterly percentage columns are a bit more complicated, as we need to define the beginning 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 2019

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 second quarter in 2020 we need to create a  variable (M1) for the end of June 2020:

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

Note: in scripting 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:

V1 = VALUEWHEN(M1);

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

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

V2 = VALUEWHEN(M2);

 

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 Q2 2020 column:

To calculate the column for the Q1 2020 performance we just change the month and year values for M1 and M2 to March and December 2019:

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

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

 

Annual Rate of Return

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

ARR(PERIODAMT=3)

Custom Column Colours

To change the watchlist colours to green 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, but we can change the label for XRE to N/A as there isn’t enough data for the 3 year annual rate of return to be calculated). Once changed, click on Clone Column to duplicate the column and then Edit Column to open the Script Editor window and change the formula for subsequent columns as required.

Optuma clients can click the buttons 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 Watchlist Layout to another list.

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.

[optuma-page-stats]

2 Comments

  1. Avatar

    Thank you for including the opportunity to quickly download an example. as a newer, non-programmer type, i learn by replicating. by having an example to use as a base-case, this accelerates my learning almost exponentially. i have become very loyal to optuma and love to talk your work up.

    Reply
    • Darren Hawkins, MSTA

      Thanks Josh – appreciate the feedback!

      Reply

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.

Close