Appending to excel database and doing calculations

Question

Hi All,

I have the following output below.

Daily ChangeDate Alpha  Beta  Gamma Alpha1Beta1Gamma1#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.834/2/2023 $ 56,396,722 $ 679,720,521 $ 4,386,142,120 $     56,396,722 $        679,720,521 $        4,386,142,120-64593.860074/3/2023 $ 56,332,128 $ 685,563,743 $ 4,391,059,429 $     56,332,128 $        685,563,743 $        4,391,059,429-248931.37194/4/2023 $ 56,083,197 $ 669,330,920 $ 4,340,604,657 $     56,083,197 $        669,330,920 $        4,340,604,657-118736.17524/5/2023 $ 55,964,461 $ 665,209,817 $ 4,193,038,307 $     55,964,461 $        665,209,817 $        4,193,038,307-87646.167484/6/2023 $ 55,876,815 $ 636,883,647 $ 4,202,623,225 $     55,876,815 $        636,883,647 $        4,202,623,225-10847.63114/7/2023 $ 55,865,967 $ 638,190,027 $ 4,158,792,138 $     55,865,967 $        638,190,027 $        4,158,792,13804/8/2023 $ 55,865,967 $ 638,190,027 $ 4,158,792,138 $     55,865,967 $        638,190,027 $        4,158,792,13804/9/2023 $ 55,865,967 $ 638,190,027

$ 4,158,792,138 $     55,865,967 $        638,190,027 $        4,158,792,138-6370551.8994/10/2023 $ 49,495,415 $ 641,837,274 $ 4,176,900,699 $     49,495,415 $        641,837,274 $        4,176,900,699-7428180.6374/11/2023 $ 42,067,234 $ 645,626,860 $ 4,199,504,616 $     42,067,234 $        645,626,860 $        4,199,504,616337141.0984/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

ericren · Answer

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

I have a historical data set.

ABC343434346756533565234234563434343434343434452343523235645235645645232323234556

Each day I get a new data set.

ABC3424334617

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.

ABC  A - (A-1) (A-(A-1)) / (A-1) B - (B-1) (B-(B-1) / (B-1)C - (C-1)(C - (C-3))/(C-3)3434343467 #VALUE!#VALUE!#VALUE!#VALUE!########VALUE!56533565 -2869-0.835468841-3401-0.990390215498#DIV/0!23423456 -331-0.5858407082016.090909091-509#VALUE!343434343434 320013.67521368320013.67521368337850.41791045343445 -3400-0.99009901-3400-0.99009901-3389-5.9982300882343523 -11-0.32352941240111.79411765-22-0.392857143235645 00-379-0.871264368220.006406523235645645 00564001007.14285700232323 00-56433-0.999592603-22-0.956521739234556 00220.956521739330.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.

Shifty · Answer

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:

ericren1.yxzp