Calculate returns across multiple rows and columns

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.

Workflow

Accepted answers

Yoshiro_Fujimori

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

1271904.yxmd

All comments

binu_acs

@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

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

1271904.yxmd

student123

Thank you!

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703