Close Value Three Months Ago

[Thomas]

Hi,

I have the following simple script:

// Get S&P 500 Data Line1 = GETDATA(CODE=SPX:WI) ; // Calculating the Tool Line2 = CHANGE(Line1, INT_TYPE=Month, INT_COUNT=3) ; Line2

1 2 3 4 5 6 7 8

// Get S&P 500 Data Line1 = GETDATA(CODE=SPX:WI) ;   // Calculating the Tool Line2 = CHANGE(Line1, INT_TYPE=Month, INT_COUNT=3) ; Line2

This simple script calculates the rolling three-month percentage change.

Now I want to create two “Chart element” tools. One should display the closing reference value of the above calculation and the other should display the date of the closing reference value.

I have tried a number of “settings” using the OFFSET() and VALUEWHEN() functions, but never got what I wanted. In most cases I tried, I got the closing value at the end of February.

BUT since the CHANGE() function is a rolling percentage calculation, I need the closing value from 02/11/2022 and not the closing value at the end of February if the calculation is made with closing value from 05/11/2022.

Using the S&P 500 closing value from 05/11/2022 which is 3935.18, the correct reference values would be 02/11/2022 and 4418.64.

Any help would be greatly appreciated.

Best wishes,
Thomas

[Matthew]

Hi,

There’s no simple way to do this currently, but in the upcoming v2.1 update the Offset() function is adding an option to swap between a BAR offset and a CALENDAR DAY offset.  Once this is available the script should be straight forward.

1 user thanked author for this post.

Thomas

[Darren]

If the Chart Element is placed on a daily chart then your script will look 3 months back from the last bar ie to Feb 11th from May 11th. If the chart timeframe is monthly, then only the open, high, low, and closing values for the month are loaded in to the chart, which is possibly why you are seeing the change from the close of February.

1 user thanked author for this post.

Thomas

[Thomas]

Hi,

I am coming back to my script issue again since I have updated to v2.1 with the new OFFSET() function.

My situation:
I calculate on a weekly chart the rolling 3-month percent change of the S&P 500.
Now I want use two “Chart Element” tools, one should display the date and the other one should display the closing value of the S&P 500 from which the calculation is made.
For example, today Wednesday 06/22/2022 the close is 3759.89. Optuma uses as the reference date Friday 03/18/2022 with the closing value of 4463.12. Optuma uses the Friday because my chart is a weekly chart.

Problem / Question:
How can I calculate the reference date and closing value?

I use the following script for the calculation of the rolling 3-month percent change:

// Get S&P 500 Data Line1 = GETDATA(CODE=SPX:WI, TIMEFRAME=1 Week) ; // Rolling 3-Month %-Change Line2 = CHANGE(Line1, INT_TYPE=Month, INT_COUNT=3) ; Line2

1 2 3 4 5 6 7 8

// Get S&P 500 Data Line1 = GETDATA(CODE=SPX:WI, TIMEFRAME=1 Week) ;   // Rolling 3-Month %-Change Line2 = CHANGE(Line1, INT_TYPE=Month, INT_COUNT=3) ; Line2

For the calculation of the reference date I use the following script:

// Get S&P 500 Data Line1 = GETDATA(CODE=SPX:WI, TIMEFRAME=1 Week) ; // Rolling 3-Month %-Change and displaying the reference date Line2 = CHANGE(Line1, INT_TYPE=Month, INT_COUNT=3) ; Line3 = BARDATE(Line2) ; Line4 = OFFSET(Line3, Month(PERIODAMOUNT=1), TYPE=Calendar Days, OFFSET=3) ; Line4

1 2 3 4 5 6 7 8 9 10

// Get S&P 500 Data Line1 = GETDATA(CODE=SPX:WI, TIMEFRAME=1 Week) ;   // Rolling 3-Month %-Change and displaying the reference date Line2 = CHANGE(Line1, INT_TYPE=Month, INT_COUNT=3) ; Line3 = BARDATE(Line2) ; Line4 = OFFSET(Line3, Month(PERIODAMOUNT=1), TYPE=Calendar Days, OFFSET=3) ; Line4

I have set the OFFSET() function to “Calendar Days” but I get as the result the number 44,736.00, using “Field Type – Date” I get the date 6/24/2022. That is the Friday this week BUT NOT the Friday 03/18/2022.

What did I made wrong?

Many thanks for any assistance,
Thomas

[Author]

Posts