How to use Alteryx to prepare a Current Transactions calculator ?

[Mackenzie_1]

The calculator should calculate the closing balance of each account using the interest rate, transactions, accrued amount and monthly capitalised interest. The date period for transactions is between 23 Jan 2022 to 31 March 2022. This current workflow exports the interest accrual and imports it to use to calculate the Cumulative interest per day (Test Cumulative). The Test cumulative is used to calculated the monthly capitalised interest which is used to calculated the final opening balance. Is this calculation accurate in calculating the opening balance? And if not, what tools can be used to improve this?

I've attached the workflow below

[oly]

Hi @Mackenzie_1 ,

Not sure exactly what you are trying to achive.

1. Are you trying to run the workflow repeatedly with different Interest Accrual values - it's both input and output in your workflow.

2. You generated 3 month of dates - but then filtering to 01/23/22 only. Is that filter for testing purpose only?

If you are trying to recalculate daily/monthly interests, you might look into iterative macros - that's one of the easy step by step processes described here: https://www.thedataschool.co.uk/sreekanth-arathil-condoth/macros-in-alteryx-iterative-macro

Best,

Oly

[Mackenzie_1]

Hi @oly 

1. So the reason why there is an input and output accrued interest amounts is because the cumulative interest amounts are calculated using the daily accrued interest but the issue is that the daily accrued interest can only be calculated using the balance (opening balance field in our case). So the only way I could do that was by calculating the interest using the generated balance after transactions had been taken into account, then inputting the interest back into the model to calculate the accrued interest amounts which was used to regenerate the correct balance amount using the calculated accrued interest.

2. This was to find out what the difference is between the calculated closing balance vs the actual closing balance calculated by another system. So the purpose of this exercise is to regenerate current account transactions for validation. The filtering was made to 03/31/22.

From this would you still consider iterative as the go to method to solve this problem?

[oly]

@Mackenzie_1 ,

Do I understand corectly that you need to run this workflow twice? First, calculate the balance. Second, calculate the interest and add it back to the balance. In that case you run the workflow only twice and not iterative. By generating dates, you will be able to calculate Daily rates and accumulate changes for every row in a single step.

Since you shared only the workflow without the Interest Accrual, I'm not able to run workflow unless I add some simulated lines manually. The workflow overwrites this table. If you are trying to avoid file overwrite, probably you could keep those tables separately, for example calling the input table as Interest Rate? If you could share your sample, would be great.

I believe it would be more reliable to calculate your first part of the balance and then continue the workflow from there - same connection from the previous tool before output to continue the calculation even though part of the process will be repeated, it will be more clear what's happening through the flow.

Does that make sense?

Best,

Oly

[Mackenzie_1]

Hi @oly 

Yes I run it twice in order to get correct results. The issue is that the model is not flexible and i am not very sure of its accuracy at times.

So I've been continuously overwriting the file. I would remove the input interest join connection to be able to overwrite the file, then rejoin the connection afterwards to update the balance. I found this to be very ineffective because I need to change the data constantly. The issue with having two files that the model is that I want to keep it dynamic in the sense that if I adjust interest rates in the source files, I will not need to overwrite the interest file to push the new interest accrued back into the model. I have shared my sample.

I am sorry but this isn't very clear to me. What do you mean by calculate the first part and then continue the workflow ? Could you possible show me what you mean exactly ?

[oly]

@Mackenzie_1 ,

please make accurate calculations comparing Running Balance first. I noticed that as you transposed LCY, you forgot about Credit Interest. Just aggregating Credit Interest, Deposit Fixed and Sms_fee you should get correct Running Balance ending on Closing Balance for the end of the month.

Don't overwrite your Opening Balance variable, create new variable to add Capitalized Interest to get the new month Opening Balance.

Works pretty well for me right now.

On your test data, rows 7-12 should be a different account number, as the time period overlaps with first 6 rows giving you completely different opening/closing balance.

For multi-row tool make sure you use Group By Account checked, otherwise you mix different accounts to run running balances.

Best,

Oly

[Mackenzie_1]

Hi @oly  yes that is true, aggregating Credit Interest, Deposit Fixed and Sms_fee give the correct running Balance ending on Closing Balance for the end of the month. But let me just give further context. I am trying to substitute the Credit Interest transaction amounts with my own calculated interest, which is the monthly capitalized interest.

My apologies, yes you are correct line 7-12 should be a different account number. 

I have attached two seperate workflows one that involves the calculated interest excluding credit interest in the balance calculation and the other that only involves all the transactions. Theoretically the opening balance difference for the latter should be zero for all accounts but I must be missing/mistaken somewhere in my workflow which is the reason why I have 2,2 ,2 as the differences. 

Does this make my issue any clearer for you?

[oly]

@Mackenzie_1 , 

Thank you for sharing files. That explains now why original credit Interest would match the Closing Balance while the re-calculation doesn't...

I'm looking at "OWN interest" workflow on the account 7022...

You calculate monthly interest 01/31 as 34,671 and 02/28 30,956 that looks like 4.10% correctly. However, on your input file I don't see any credit interest on this account except for 03/14 15,860. There's sms and deposit on 02/01 that's 34,892 that could be approximate to  34,671  but I don't see any explanation for end of February nor March. I'm not sure if your original input data are consistent with interest calculations.. Unless you can explain how 15,860 was calculated over 3 month. Even on other 2 accounts we can see that Interest was credited and then debited, though monthly amount looks more or less correct.

I brought columns to review side by side - original credit interest vs new monthly credit interest to narrow it down. Didn't make any other changes on your workflows.

Best,

Oly

[oly]

@Mackenzie_1 

on the without workflow...

You had European format, I changed , to . before Select Tool type conversation and I got 0,0,0 as result.

Workflow is attached!

best,

Oly

[Mackenzie_1]

Hi @oly  

Yes thats okay, the input credit interest was calculated incorrectly due to a systematic error. So I am trying to find out what the interest should have been with my newly calculated interest. Would you say my calculator is effective in doing this ? (That is reaching the truth). My biggest issue is the effeciency of my calculator considering I need to run it twice to get correct results. Is there a better way of doing this?