Appending to excel database and doing calculations

[ericren]

Hi All,

I have the following output below.

Daily Change	Date	Alpha	Beta	Gamma	Alpha1	Beta1	Gamma1
#VALUE!	4/1/2023	$ 92,839,035	$ 679,720,521	$ 4,386,142,120	$     92,839,035	$        679,720,521	$        4,386,142,120
-36442312.83	4/2/2023	$ 56,396,722	$ 679,720,521	$ 4,386,142,120	$     56,396,722	$        679,720,521	$        4,386,142,120
-64593.86007	4/3/2023	$ 56,332,128	$ 685,563,743	$ 4,391,059,429	$     56,332,128	$        685,563,743	$        4,391,059,429
-248931.3719	4/4/2023	$ 56,083,197	$ 669,330,920	$ 4,340,604,657	$     56,083,197	$        669,330,920	$        4,340,604,657
-118736.1752	4/5/2023	$ 55,964,461	$ 665,209,817	$ 4,193,038,307	$     55,964,461	$        665,209,817	$        4,193,038,307
-87646.16748	4/6/2023	$ 55,876,815	$ 636,883,647	$ 4,202,623,225	$     55,876,815	$        636,883,647	$        4,202,623,225
-10847.6311	4/7/2023	$ 55,865,967	$ 638,190,027	$ 4,158,792,138	$     55,865,967	$        638,190,027	$        4,158,792,138
0	4/8/2023	$ 55,865,967	$ 638,190,027	$ 4,158,792,138	$     55,865,967	$        638,190,027	$        4,158,792,138
0	4/9/2023	$ 55,865,967	$ 638,190,027	$ 4,158,792,138	$     55,865,967	$        638,190,027	$        4,158,792,138
-6370551.899	4/10/2023	$ 49,495,415	$ 641,837,274	$ 4,176,900,699	$     49,495,415	$        641,837,274	$        4,176,900,699
-7428180.637	4/11/2023	$ 42,067,234	$ 645,626,860	$ 4,199,504,616	$     42,067,234	$        645,626,860	$        4,199,504,616
337141.098	4/12/2023	$ 42,404,376	$ 645,085,384	$ 4,237,608,057	$     42,404,376	$        645,085,384	$        4,237,608,057

I have an Input data set below that I receive daily. 

Date	Alpha	Beta	Gamma
4/13/2023	$ 92822390375	$ 679722270521	$ 423760888057

Task: Use daily input file and Append the input data to the Excel database. Then calculate the daily change in the first column. 

The end result should be the output with the appended dataset on the bottom with the daily change calculations. The file name would include the date from the new daily data set. 

Note: The daily change COL is for Alpha1 

[Shifty]

Hi @ericren - I hope you managed to sort this yourself but I hate seeing a question go unanswered so I'm nudging this back to the top by having a go at a solution.

I've made a few assumptions (e.g. Alpha always equals Alpha1 etc. - this seems to be the case from the dataset you included).  Here is a screenshot of how the methodology behind appending the new row and I have attached the workflow:

[ericren]

Hi, sorry about the confusion, let me restate the problem. 

I have a historical data set.

A	B	C
3434	3434	67
565	33	565
234	234	56
3434	3434	3434
34	34	45
23	435	23
23	56	45
23	56456	45
23	23	23
23	45	56

Each day I get a new data set.

A	B	C
3424	334	617

I would like to append this data set to the historical data set. 

Then calculate changes between rows and also % change between rows.

A is current row 

A-1 is previous row. 

Same for B and C. 

A	B	C		A - (A-1)	(A-(A-1)) / (A-1)	B - (B-1)	(B-(B-1) / (B-1)	C - (C-1)	(C - (C-3))/(C-3)
3434	3434	67		#VALUE!	#VALUE!	#VALUE!	#VALUE!	#######	#VALUE!
565	33	565		-2869	-0.835468841	-3401	-0.990390215	498	#DIV/0!
234	234	56		-331	-0.585840708	201	6.090909091	-509	#VALUE!
3434	3434	3434		3200	13.67521368	3200	13.67521368	3378	50.41791045
34	34	45		-3400	-0.99009901	-3400	-0.99009901	-3389	-5.998230088
23	435	23		-11	-0.323529412	401	11.79411765	-22	-0.392857143
23	56	45		0	0	-379	-0.871264368	22	0.006406523
23	56456	45		0	0	56400	1007.142857	0	0
23	23	23		0	0	-56433	-0.999592603	-22	-0.956521739
23	45	56		0	0	22	0.956521739	33	0.733333333

I would like to append the new data set to the first 3 rows and calculate the rest of the columns for daily change and % change. 

Please help I am stuck, thank you so much.