How to Calculate Quarterly Performance in Optuma Watchlists

by | Jun 30, 2017 | All Articles, Technical Analysis, Education

As we have reached the end of the second quarter in 2017 a client requested if there was a way to show performance for the first and second quarters of this year in a watchlist, along with the year-to-date performance. As we’ll see, the formula for the latter is relatively straight forward, but the quarterly stats require a bit of work to define the starting and end dates (by default the watchlist will use the latest daily data available).

As seen in this example of global indices (sorted by YTD%) the Polish Index has returned over 35% this year, split fairly evenly over the two quarters (17.5% in Q1 and 15.3% in Q2), whereas the majority of the Argentinian Merval index positive performance this year occurred in the first quarter (19.8% vs 5.9% in Q2):

The year to date formula uses a straightforward PERFORMANCE function, set to the end of last year:

PERFORMANCE(STYLE=Fixed Date, FIXEDDATE=30/12/2016)

The quarterly percentage columns are a bit more complicated, as we need to define the static starting and end dates because the PERFORMANCE function is a rolling calculation based on the last data available. 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:

V1 = VALUEWHEN(M1);

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;

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

 

Putting it all together for Q1 2017:

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

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

V1 = VALUEWHEN(M1);

V2 = VALUEWHEN(M2);

((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;

V1 = VALUEWHEN(M1);

V2 = VALUEWHEN(M2);

((V1/V2)-1) * 100

Optuma clients can click the button below to save a workbook with the example for global indices, 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, 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 individual monthly performance rather than by quarters), so if you have any questions or comments please let us know.

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, UK and currently USA, Darren has a broad understanding of the individual needs of traders and investors utilising a wide range of methodologies.

In 2014 Darren passed the UK-based Society of Technical Analysts diploma course, and is looking forward to soon becoming a candidate for the Market Technicians Association's CMT programme.

When not looking at charts, Darren keeps a keen eye on England's cricket team—especially if they are playing against Australia. He recently moved to Charlotte, North Carolina with wife Wendy and their labrador, Gabba.

0 Comments

Submit a Comment

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

13 − eight =

Blog Signup

Pin It on Pinterest

Share This