Window Data Calculation Question

Optuma Scripting
1

I have an issue understanding what is being calculated on the 3rd and 4th window below where there is no data.

I am calculating the ratio of VX9D to VX and then looking at the historical percentage range of the ratio in windows 3 and 4 below.

image
image3303Ă—1252 373 KB

VX9D data only goes back to 2013, but the ratio below is going all the way back, as does the range percentage of the ratio.

What is the best way to fix this so that the calculations below, being done on the correct data series?

VIX9D Ratio

VX9D = GETDATA(CODE=VIX9D:CBOEI);
VX = GETDATA(CODE=VIX:CBOEI);

VX9Ratio = (VX9D / VX) ; 

VX9Ratio;

VX9D Percent Range

// Highest Value
ATH = HIGHESTHIGH(script(SCRIPTNAME=VX9D Ratio), RANGE=All Time) ;

// Lowest Value
ATL = LOWESTLOW(script(SCRIPTNAME=VX9D Ratio), RANGE=All Time) ;

// Percentage Range
VX9DPercent = ( (  CLOSE() - ATL ) * 100 ) / ( ATH - ATL );

VX9DPercent;

Thanks as always,

Eric

2

Thanks Eric. The ratio from 1990 - 2013 is being calculated in the Show View using the first available price in VIX9D. To ignore this time period, set the Vix data to load from the same first date as VIX9D:

image

You can then set the Show View to start from the same date to hide the horizontal line:

image
image1719Ă—522 64.9 KB

3

Thank you Darren.

My other question is if there is no numerator for the ratio before 2013 (there was no data for VX9D), how is the ratio plotted prior to that time frame? 0 divided by any n-value should still be 0.

What could I be missing?

4

It’s using the first available price for VIX9D ie 15.81 and backfilling to 1990.

Instead of creating the ratio in a Show View, another option would be to create a new symbol under Data > Custom Codes using the same formula, and it will only calculate it from 2013:

image
image1993Ă—1052 105 KB

5

I see. Thank you for the clarification.

6

Hi Darren,

I have with a few charts the same issue as Eric and used the Show View property “Start Date” as in your chart example above.

Here is your chart example:

image
image1719Ă—522 64.9 KB

It works perfectly when I enter the start date.

BUT when I close and reopen the workbook or activate other charts in the workbook and then reactivate the chart in which I have entered the start date, the date entered is replaced by the date “12/30/1899”.

It seems that the date I have entered is not “saved”!

Any suggestions or did I make something wrong?

Thanks,
Thomas

7

Hi Thomas,

This is an issue with the Show View having to be recalculated when the page is reopened. One solution would be to set the main price chart to start on the first bar date of the Show View by changing the Date Range property:

image

Alternatively, create a dataset using the ratio formula under Data > Custom Code and add that to the chart in a new window instead of the Show View.

1 Like
8

Hi Darren,

I have used your “alternatively” recommendation and created a Custom Code for the formula and it worked perfectly.

Thanks,
Thomas

1 Like