Calculate returns across multiple rows and columns

[student123]

I have a dataset with multiple firms and their stock prices on certain dates. I want to create the returns of these stocks across the rows and for all the firms (across columns). My goal is to have the same table as here, only with the returns on the corresponding days and not the stock prices.

[binuacs]

@student123 can you provide a sample input file and expected output for better understanding?

[student123]

My aim is to calculate the return across all rows and columns. So the return for MARKET from 02.01 to 03.01 and so on and the same for the firm with the ID 4122605 and so on.

Input
DATE	MARKET	4122605	4128969	4492850	4250588	4967971
02.01.18	2695.81	43.90	23.21	47.84	NA	2.50
03.01.18	2713.06	44.47	22.19	48.21	NA	2.58
04.01.18	2723.99	44.32	22.47	48.52	NA	2.74
05.01.18	2743.15	44.86	22.81	49.78	NA	2.73
Output	MARKET	4122605	4128969	4492850	4250588	4967971
02.01.18	NA	NA	NA	NA	NA	NA
03.01.18	0.0064	0.0130	-0.0439	0.0077	#VALUE!	0.0320
04.01.18	0.0040	-0.0035	0.0126	0.0064	#VALUE!	0.0620
05.01.18	0.0070	0.0122	0.0151	0.0260	#VALUE!	-0.0036

[Yoshiro_Fujimori · Accepted solution]

Hi @student123 ,

The rounding result is a little different from yours.

You may want to modify the expression in Multi-Row Formula tool if necessary.

I hope this helps.

Workflow

Input

DATE	MARKET	4122605	4128969	4492850	4250588	4967971
02.01.18	2695.81	43.9	23.21	47.84	NA	2.5
03.01.18	2713.06	44.47	22.19	48.21	NA	2.58
04.01.18	2723.99	44.32	22.47	48.52	NA	2.74
05.01.18	2743.15	44.86	22.81	49.78	NA	2.73

Output

Date	MARKET	4122605	4128969	4250588	4492850	4967971
2018-02-01
2018-03-01	0.0064	0.013	-0.0439		0.0077	0.032
2018-04-01	0.004	-0.0034	0.0126		0.0064	0.062
2018-05-01	0.007	0.0122	0.0151		0.026	-0.0036

[student123]

Thank you!