Divide the current value by the next one on a row (Script)

[brunolelli87]

Hello guys,

How can I create a Script that divides the current value by the next row's value, as you can see below:

INDX	DVPG	VDIF	DIF
GGBR4	29/05/2020	R$ 13,43	0,992609
GGBR4	28/05/2020	R$ 13,53	1,020362
GGBR4	27/05/2020	R$ 13,26	1,109623
GGBR4	26/05/2020	R$ 11,95	0,966828
GGBR4	25/05/2020	R$ 12,36	1,065517

...

ITUB4	29/05/2020	R$ 13,43	0,992609
ITUB4	28/05/2020	R$ 13,53	1,020362
ITUB4	27/05/2020	R$ 13,26	1,109623
ITUB4	26/05/2020	R$ 11,95	0,966828
ITUB4	25/05/2020	R$ 12,36	1,065517

I have lots of INDXs, so, for each INDX, I want to calculate the DIF.

As you can see, I want to create the column DIF that is row #1 / row #2. 
Example: 13.43/13.53 = 0.992609

This is the script where you can find my code:

For each Index in 'BRL=X', 'EURBRL=X', 'CL=F', 'GC=F', 'SI=F', 'BTC-USD', '^BVSP'
	Add
	Indices:
	Load
		Date(Date#(@1,'YYYY-MM-DD'), 'DD/MM/YYYY')	 				as DVPG,	// Data a ser considerada						 
		'$(Index)' 													as INDX,	// Índice
	    Num#(@2, '#,##0,00;-#,##0.00', '.', ',')					as VDIA, 	// Valor do índice na abertura
	    Num#(@3, '#,##0,00;-#,##0.00', '.', ',')					as VMAI, 	// Valor mais alto do índiceno dia
	    Num#(@4, '#,##0,00;-#,##0.00', '.', ',')					as VMBI, 	// Valor mais baixo do índice no dia
	    Num#(@5, '#,##0,00;-#,##0.00', '.', ',')					as VDIF, 	// Valor do índice no fechamento
	    Num#(@6, '#,##0,00;-#,##0.00', '.', ',')					as VIFA, 	// Valor do índice no fechamento ajustado
	    Num#(@7, '#,##0,00;-#,##0.00', '.', ',')					as VDIN		// Volume negociado do índice
	FROM
		[https://query1.finance.yahoo.com/v7/finance/download/$(Index)?period1=1431043200&period2=1593907200&interval=1d&events=history] 	//5 Years Period
		(txt, codepage is 1252, no labels, delimiter is ',', msq, filters(
		Remove(Row, Pos(Top, 1)),
		Remove(Row, RowCnd(CellValue, 2, StrCnd(contain, 'null')))
	));

Next Index

How can I do that?
I want to do it by Script

Thank you so much!

[jwjackso]

Load the table in reverse order and use the Previous function

[brunolelli87]

Thanks!

[Saravanan_Desingh · Accepted solution]

One solution is.

tab1:
LOAD RowNo() As RowID,* INLINE [
    INDX, DVPG, VDIF
    GGBR4, 29/05/2020, 13.43
    GGBR4, 28/05/2020, 13.53
    GGBR4, 27/05/2020, 13.26
    GGBR4, 26/05/2020, 11.95
    ITUB4, 29/05/2020, 13.43
    ITUB4, 28/05/2020, 13.53
    ITUB4, 27/05/2020, 13.26
    ITUB4, 26/05/2020, 11.95
];

tab2:
LOAD RowID, INDX, VDIF As VDIF1
Resident tab1
Where Odd(RowID);

Left Join (tab2)
LOAD RowID-1 As RowID, INDX, VDIF As VDIF2
Resident tab1
Where Even(RowID);

tab3:
LOAD INDX, VDIF1/VDIF2 As DIF
Resident tab2;

Drop Table tab1, tab2;

[Saravanan_Desingh]