How to Calculate Quarterly Performance in Optuma Watchlists

by Jul 2, 2021All Articles, Technical Analysis, Education

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?). It’s also possible to create performance statistics based on time frames (eg over the last month, year-to-date, or from a specific date), but you can also create quarterly (or monthly) returns. The following article will show you how to add these types of calculations to a watchlist – or you can skip to the bottom to download a workbook!

The example below shows the quarterly performance of the Australian ASX 200 index and sectors since 2020 to the close of Q2 2021, along with year-to-date and the 3 year annual rate of return. Sorted by Q2 performance, it shows Technology $XIJ rebounding 12.1% following Q1’s 11.5% fall. Financials $XFJ has been the best performer to the end of June, gaining 11.3% in Q1 and 7.5% in Q2 (click image to enlarge).

Here’s how to calculate these values in any watchlist by combining the following functions to specify the periods:

MONTHNUM() – assigns a number for each month: 1 = January, 2 = February, etc

YEARNUM() – eg 2021

VALUEWHEN() – returns 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 2021 we need to create a  variable (M1) for the end of June 2021:

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

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 2021 by creating two more variables:

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

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: to display the columns in percentage format (as per the image and workbook examples below) do not multiply the last line by 100. In the watchlist, right-click on the column header and change the Column Type to Percentage.

Putting it all together for Q2 2021 column:

2021 Performance

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

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

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

 

YTD

The year-to-date column is quite straight forward, using the Rate of Change function set to use yearly data, so a 1 bar change the end of the previous year. NOTE: This will calculate the performance to the latest date downloaded , not the end of the quarter:

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

 

Current Quarter-to-Date (QTD)

To calculate the performance for the current quarter we can use the CHANGE() function fixed to June 30th 2021. This will update every day as new data comes in (i.e. Energy has gained 1.33% in the first two days of Q3 at the time of writing), so will need to be amended using the above quarterly formula at the end of September.

CHANGE(INT_TYPE=Fixed, DATE=2021-06-30)

 

Annual Rate of Return

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

ARR(PERIODAMT=3)

So the ASX 200 $XJO index has grown an average of 5.76% every year for the last 3 rolling years (not calendar years) , with Technology gaining at 24.48% per year and both Energy $XEJ and Utilities $XUJ losing over 10% per year.

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 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 for subsequent columns as required.

Workbooks

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, columns can be added, removed, or dragged to a new position to your watchlists as required. If you have any questions or comments please let us know, or visit our Scripting Forum for help and more watchlist 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.

0 Comments

Submit a Comment

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

Blog Signup

Pin It on Pinterest

Share This