Data360 Analyze

Hi Support/Smarties

I am hoping that someone can help me develop a graph that would be equivalent to excels' SOLVER add-in but a little better.

What this add-in can do is cycle through a list of amounts and summing these combinations together trying to balance back to an original total amount

I have large selection companies who pay me a total amount, but they tend to forget to send me allocation details.  These companies have multiple account numbers assign to them, so when they pay me it's a grand total amount.

So, I have been using excel's SOLVER to help me in finding what possible accounts these customers were paying, I have been able to get my hands on a report named "outstanding customer" that captures my customers at an account/invoice level and current amount outstanding. 

I then use the "payment report" that is sent to me by the bank and using these 2 reports and excel's SOLVER it cycles through using the "outstanding customer" report and suggests/finds the outstanding accounts/invoice that balance back to my "payment report".

However, it only looks at 1 value at a time and I have to copy and paste new values each time and I need to do the copy/paste new data at least 50-60 times.

I was hoping that someone could help me in building this process into D360 so I can load these reports and it would look at multiple customers/payments in one go..

Example of reports attached.

End Result:

Example 1:

TransAct:ABC123

The Target Amount $14318.44 (blue cell) which equals the amount in "payment report.csv attached."

The Outstanding Customer Report values (green cells) which equals the data within the 'outstanding customer.csv attached" will contain multiple "TransAct"=customers

Solver then cycles through the "outstanding customer" data going over multiple possible combinations that has to equal to the target amount per "TransAct"=customer.

End result are the (orange cells) that solver has marked as equal to the target amount.

Example 2:

TransAct:ABC124

The Target Amount $6987.36 (blue cell) which equals the amount in "payment report.csv attached"

The Outstanding Customer Report values (yellow cells) which equals the data within the 'outstanding customer.csv attached" will contain multiple "TransAct"=customers

Solver then cycles through the "outstanding customer" data going over multiple possible combinations that has to equal to the target amount per "TransAct"=customer.

End result are the (pink cells) that solver has marked as equal to the target amount.

Depending on the number of outstanding accounts per customer it can take around 10mins to run per "TransAct", I am fine with that :) as anything would be better than copy/paste for each "TransAct"

Output fields would need to contain the following fields:  DepositRef, TransAcct, Invoce_Arrangement, Amount, Source_System

I really hope that someone can help me!!

------------------------------
andrew darnell
Knowledge Community Shared Account
------------------------------

Hi Andrew, 

This is certainly possible, but even a back of the envelope implementation is a bit of work. As you know, what you are trying to do is linear optimization. You would have two approaches in general: a library based approach or a native one one. 

For the library based approaches - there are several python implementations out there on the web. I would google for that, implement your task as python and then later try to figure out how to import the relevant libraries in a way that can then be used by data360 e.g. in the transform node.  For the native approach, you probably have the tools in data360 but if I'm honest I wouldn't attempt it unless you have a desire to understand what you are doing. (there are many videos on youtube regarding solving linear problems, minimizing for 0). It probably wouldn't even be too hard.

I would personally look into py-excel-solver or something similar to write a standalone python script to chew through your csv. I'm not sure I would bring it back into data360 unless that gives you some advantage later. I have not looked at the very latest Analyzes, but the version we use still is python 2.7 based which is.......... not what most people are using out there these days and would probably be a hassle to backport something you find (3.x based) back into. Also - and I might be wrong about this, the python in data360 appears to be somewhat slower than the performance you'll get from standalone python. 

Best
Peter

------------------------------
Peter Sykes
Vontobel Holding AG
Zurich
------------------------------

Original Message:
Sent: 07-06-2023 19:04
From: andrew darnell
Subject: D360 - Excel SOLVER Equivalent

Hi Support/Smarties

I am hoping that someone can help me develop a graph that would be equivalent to excels' SOLVER add-in but a little better.

What this add-in can do is cycle through a list of amounts and summing these combinations together trying to balance back to an original total amount

I have large selection companies who pay me a total amount, but they tend to forget to send me allocation details.  These companies have multiple account numbers assign to them, so when they pay me it's a grand total amount.

So, I have been using excel's SOLVER to help me in finding what possible accounts these customers were paying, I have been able to get my hands on a report named "outstanding customer" that captures my customers at an account/invoice level and current amount outstanding. 

I then use the "payment report" that is sent to me by the bank and using these 2 reports and excel's SOLVER it cycles through using the "outstanding customer" report and suggests/finds the outstanding accounts/invoice that balance back to my "payment report".

However, it only looks at 1 value at a time and I have to copy and paste new values each time and I need to do the copy/paste new data at least 50-60 times.

I was hoping that someone could help me in building this process into D360 so I can load these reports and it would look at multiple customers/payments in one go..

Example of reports attached.

End Result:

Example 1:

TransAct:ABC123

The Target Amount $14318.44 (blue cell) which equals the amount in "payment report.csv attached."

The Outstanding Customer Report values (green cells) which equals the data within the 'outstanding customer.csv attached" will contain multiple "TransAct"=customers

Solver then cycles through the "outstanding customer" data going over multiple possible combinations that has to equal to the target amount per "TransAct"=customer.

End result are the (orange cells) that solver has marked as equal to the target amount.

Example 2:

TransAct:ABC124

The Target Amount $6987.36 (blue cell) which equals the amount in "payment report.csv attached"

The Outstanding Customer Report values (yellow cells) which equals the data within the 'outstanding customer.csv attached" will contain multiple "TransAct"=customers

Solver then cycles through the "outstanding customer" data going over multiple possible combinations that has to equal to the target amount per "TransAct"=customer.

End result are the (pink cells) that solver has marked as equal to the target amount.

Depending on the number of outstanding accounts per customer it can take around 10mins to run per "TransAct", I am fine with that :) as anything would be better than copy/paste for each "TransAct"

Output fields would need to contain the following fields:  DepositRef, TransAcct, Invoce_Arrangement, Amount, Source_System

I really hope that someone can help me!!

------------------------------
andrew darnell
Knowledge Community Shared Account
------------------------------