Using a dynamic POWER function calculation

Optuma Scripting
1

Hi Forum,

I’d like to plot the long term trend of SPX on a monthly chart using a script. (It’s easy enough to use the Trend Line drawing tool when the chart has a logarithmic scale, but I’d like to be able to mathematically calculate the long term trend value month-by-month, so I have a value each month to work with in other scripts).

I’ve developed a script that almost gets there, but not quite. Here it is:

// Calculation of compound monthly growth rates of SPX
// Use Compound Monthly Growth Rate (CMGR) to be able to plot growth rate on SPX monthly chart.
// @constant {integer} The average number of days per month.
daysPerMonth = Abs(365.25 / 12); 
// @constant {integer} The starting date for the CMGR calculation, being Friday 6 January 1950.
startDate = Abs(18269);
// @constant  {integer} The ending date for the CMGR calculation, being Friday 28 June 2019.
endDate = Abs(43644);
// @constant  {number} The closing price of the market on the starting date.
startValue = Abs(16.98);
// @constant  {number} The closing price of the market on the ending date.
endValue = Abs(2941.76);

// {number} The duration in months between the starting and ending dates.
numberOfMonths = (endDate - startDate) / daysPerMonth;

// {number} The power value to be used in the CMGR calculation.
$powerValue = 1 / numberOfMonths;

// {number} The Compound Monthly Growth Rate calculation: CMGR = (endValue / startValue) ^ (1 / numberOfMonths).
CMGR = POWER(endValue / startValue, POWER=$powerValue);

// {number} The number of months between the current BARDATE and January 1950, to be used as the power value in the month-by-month growth path of SPX.
$months = FLOOR((BARDATE() - startDate) / daysPerMonth) + 1;

// {number} The month-by-month value of the compounding monthly growth rate of SPX.
startValue * POWER(CMGR, POWER=$months);

I think the issue is in the last line, where the bar value ($months) in the POWER function is dynamic, that is, it changes with each month. Just prior to the last line I used a bar value ($powerValue) in the POWER function but it is static (always being the same value regardless of date), and that seemed to work OK.

A show plot on the script gives a horizontal line that is equivalent to the variable endValue in the script. This sort of suggests that the last line of the script always evaluates as if BARDATE() is today’s date, not the value of the date at that specific point in the chart.

Has anyone had any experience with using the POWER function with a dynamic power value that changes as the date changes? Or is there another clever way to achieve the same?

Many thanks, Dean

2

Hi Dean,

The way we set up variables was to treat them as single numbers (not an array of numbers). Because of that the prior values are ignored and it will only show as the last value.

Something to keep in mind with Optuma is that we do not run each line in the script for each bar but we fully calculate each line. So this line

$months = FLOOR((BARDATE() - startDate) / daysPerMonth) + 1;

is fully calculated before the final line is calculated.

The use of the variables was so we can have $a = 50 and that would allow me to have bars=$a through my code.

Side note: if you make it #$a = 50; then the “a” will show up in the properties panel.

So what is the solution?

Have a running total of the number of months:
M1 = FLOOR((BARDATE() - startDate) / daysPerMonth) + 1;
This is an array instead of a single value.

Use an IF to compound when the month changes
CMGRTotal = If(M1 <> M1[1], CMGRTotal[1] * CMGR, CMGRTotal[1]);

What that is saying is if M1 is different from the previous value (M1<>M1[1])
then multiply our previous value by the CMGR (CMGRTotal[1] * CMGR)
else Set the value to the same value as last time (CMGRTotal[1]).

Then you can multiply that by the start value.

All of this is untested but it should set you in the right direction.

All the best

Mathew

3

Thanks Mathew, really appreciate the advice, and the time you have taken. One part of your response:

"Something to keep in mind with Optuma is that we do not run each line in the script for each bar but we fully calculate each line..."
…is possibly one of the most important insights into how Optuma works that I've ever encountered. That has enlightened my understanding of how Optuma scripting works considerably. Thank you.

I understand the logic of the recursive solution you provided and gave it a shot. After a fair bit of fiddling around I was able to get the script to evaluate as desired but there were other unwanted side-effects if it was applied to a chart (e.g. existing Time Price Measures all bunched up in a single vertical line on the left of the chart; on the initial open of the chart, a plot of the script was a horizontal line at zero, until the script was re-applied to the plot; any changes to the script after it was plotted on a chart crashed Optuma).

In essence we were able to achieve a mathematical representation of a trend line using the recursive method, but it seems that recursion can be a fairly dangerous technique in Optima scripts. I’m not skilled enough to take the recursion concept any further for other calculations but thank you for the suggestion - at a fundamental level, it worked.

Cheers, Dean