Grouping across multiple dates

[adonis]

Hi All,
What is the best way to group and perform a sum by with like Quantities, Amounts and Security numbers across multiple dates. The summary funciton works if you take out the date parameter, however the user wants to see the date and delete the items that match up across different dates, and then delete them from the output.
Any ideas? much appreciated.

[OllyInMunich]

Hello adonis,

If a field like "SecurityNo" is floating around and you want to eliminate duplicates across different values. Select the other values as your keys in the summary. If there is a matching balance that will sum to zero, use it. That way you can filter out the values where the sum is zero (that should solve sums like Balance and Quantity).

For other data, a two-pass approach will solve it. You can use multiple measures (like (Unique(securityNo) and Max(SecurityNo)) in the first summary, export that as plain text, then use this as the report in the second model, and create a filter on Unique<>Max, for example.

Best wishes,

Olly

[adonis]

Hi Olly
I will try the second I guess, let me see how that works.
The problem is the darn Date is in the way or else it groups up nicely.
I want to see if this pass works, if anyone else has any thoughts, much appreciated for all of your efforts these past couple months, I apprecaite it.

I am not sure whether this one works because of the Date parameter, if anyone has any idea how to group up quantity and amount with same securities, and doing so with Date being another parameter, please advise

[OllyInMunich]

Hello adonis,

The best way depends entirely on your source data and your requirements. Rest assured, it's possible to solve the problem with Monarch, if not in one pass, then in two or three.

You might get some traction playing around with the Date field as key and item, and seeing how it works with Date as an across key.

Feel free to post some data here in CODE tags...

Best wishes,

Olly

[adonis]

Security Number Settlement Date Account Number Sec ID Share Trans Sign Qty Trans Total Sign Amount
2003121 10/7/2009 123-45678-1-2 63701J785 0 67.95
2003121 10/8/2009 123-45678-1-2 63701J785 0 -67.95
2329113 10/6/2009 123-45678-1-2 57584RCK8 8,000,000 8,000,000.00
2329113 10/7/2009 123-45678-1-2 57584RCK8 -8,000,000 -8,000,000.00
3942053 10/8/2009 123-45678-1-2 365136MC1 -25,000 -26,240.38
3942053 10/9/2009 123-45678-1-2 365136MC1 25,000 26,240.38
3BBFPH1 10/9/2009 123-45678-1-2 709221LF0 -15,000 -16,638.57
3BBGQV7 10/9/2009 123-45678-1-2 050683CP4 -35,000 -39,078.78
3BBSPG5 10/7/2009 123-45678-1-2 341602ZZ6 0 -312.50
3BBSPG5 10/8/2009 123-45678-1-2 341602ZZ6 0 312.50
3BCGQR8 10/7/2009 123-45678-1-2 12844PAE9 -10,000 -5,201.67
3BCGQR8 10/8/2009 123-45678-1-2 12844PAE9 10,000 5,201.67

[adonis]

I need to figure out how to do the grouping with the settlement date being a hurdle. Anyway to remove the securities where the amount & qty for a particular security = 0? thus getting rid of those items and showing only the items that do not match up?
let me know what you think, as grouping across different dates, is killing me.

basically removing the settlement date items that = 0 and only showing the ones that do not across the multiple settlement dates?

[adonis]

any one have any ideas?

[Grant Perkins]

Hi All,
What is the best way to group and perform a sum by with like Quantities, Amounts and Security numbers across multiple dates. The summary funciton works if you take out the date parameter, however the user wants to see the date and delete the items that match up across different dates, and then delete them from the output.
Any ideas? much appreciated.

Are you trying to do the matching and deletion or are you presenting the information to the user to enable them to do it themselves?

If you are doing the matching and deletion, drop the date.

If the user is doing it - are they using Monarch and you are building a model for them?

If so you could consider the following.


Add subtotals to the summary after each transaction group to enable easier visibility of net zero balance groups.
Export a summary WITH THE DATES as a 'report' (or a 'database' if the user has Monarch Pro) and allow them to open that in another model. Maybe add a User Entry field to the model and allow the user to flag the lines they feel match and then filter them from the final output.
Create a model with 2 versions of the summary - one WITH the dates and all entries, one without and with matched net zero trades excluded and let the user check the results of the shorter one by spot checking the longer one. (Or vice versa.)
There are likely to be variations on these ideas that would also be worth considering.

Olly's suggestion of using the date field as an ACROSS key may also be viable - depending on how many dates you are working with in the source report.


HTH.


Grant

[adonis]

Grant,

Basically the user wants me to delete the matched up items that = 0. (Systematically thru Monarch)
The date can't be removed, so not sure what is the best avenue to approach this as a normal group by or sum by, without the date, is very easy, but to have the date in the picture to collapse it makes it tougher, if anyone has a sample or example of how to do this, much appreciated.

[adonis]

the problem is if i make a key, i really need to include the settlement date.
I need to tie the quantity and amount back to the settlement date.
That is a problem because to find the corresponding settlement date, i won't be able to tie that back even if i try to reverse engineer it with creating the output and then trying to find the security afterwards because of a many to one relationship with the settlement date.
let me know what you think.

going to try and see if an external lookup works, if anything one else has any feedback let me know.
i was going to try and link one of the original files back to the sum file and see the items that didn't add up and get the settlement that way thru a lookup, not sure if it will work

[adonis]

any way to do a filter that would ignore settlement date and look to see if ie:
groups/sums up all the same Security Number + account number + qty + amt and the ones that = 0 then exclude all those, while only the items that talley up and do not = 0 show up but show the settlement date for the ones that don't match?

[Grant Perkins]

Hi adonis,

I quickly played around with Olly's suggestion about displaying the dates as an ACROSS key using your sample data (reduced to the useful columns).

You can get a summary to look like this (this is actually a fixed format export file for convenience)



Account Numbe Security Num 10/06/2009 10/07/2009 10/08/2009 10/09/2009 SUM(Trans Tota
123-45678-1-2 3BBFPH1 (Null) (Null) (Null) -16638.57 -16638.57
123-45678-1-2 3BBGQV7 (Null) (Null) (Null) -39078.78 -39078.78
123-45678-1-2 All Others 8000000.00 -8005446.22 -20794.16 26240.38 0.00


Or drilled down a level it can look like this:


Account Numbe Security Num Sec ID 10/06/2009 10/07/2009 10/08/2009 10/09/2009 SUM(Trans Tota
123-45678-1-2 3BBFPH1 709221LF0 (Null) (Null) (Null) -16638.57 -16638.57
123-45678-1-2 3BBGQV7 050683CP4 (Null) (Null) (Null) -39078.78 -39078.78
123-45678-1-2 All Others 12844PAE9 (Null) -5201.67 5201.67 (Null) 0.00
123-45678-1-2 All Others 341602ZZ6 (Null) -312.50 312.50 (Null) 0.00
123-45678-1-2 All Others 365136MC1 (Null) (Null) -26240.38 26240.38 0.00
123-45678-1-2 All Others 57584RCK8 8000000.00 -8000000.00 (Null) (Null) 0.00
123-45678-1-2 All Others 63701J785 (Null) 67.95 -67.95 (Null) 0.00


And several variants on this idea with different measures and so on.

I couldn't work out if the user would be using Monarch from your recent posts. If they are, and assuming the number of dates would not be extreme (every date in the report would have its own column ....) the summary drill up and down facilities and the ability to reference back to the data source could be a huge benefit to them.

If you don't like the NULLS you can change the setting in the Display Options to give something else. You can also hide the 'All Others' rows if you wish, as described in an earlier thread a couple of weeks ago.

I'm not sure how close this gets you to a useful solution for the user in their view but so long as you don't normally have a large number of date columns it would seem to work quite well.


HTH.


Grant

[adonis]

thanks for putting in the time Grant
i do appreciate it.
the user is not using monarch if that was the case, i wouldn't have the issues. They don't want noise, only the actual data that shows up with the amount and quantity that <> 0. I am still thinking, though I like Olly's suggestion, that would be good for some other processes perhaps, but for this one, they only want the dates rolled up into one where the actual date is shown and in some cases there is some data that may not match, that is where I am stuck in my process.

This one is tricky

[Grant Perkins]

thanks for putting in the time Grant
i do appreciate it.
the user is not using monarch if that was the case, i wouldn't have the issues. They don't want noise, only the actual data that shows up with the amount and quantity that <> 0. I am still thinking, though I like Olly's suggestion, that would be good for some other processes perhaps, but for this one, they only want the dates rolled up into one where the actual date is shown and in some cases there is some data that may not match, that is where I am stuck in my process.

This one is tricky

adonis,

I'm not at all sure I understand the background to the description in bold above. Can you provide an example of the information layout the user thinks they want to see?

If it is pretty much the same as the table format but with the net zero balance lines filter out then you need to create something to provide that filter mechanism - such as a flag against the record that indicates whether it is to be included (or excluded depending on how you wish to filter.)

So pick the fields that give you a unique key (probably EXCLUDING the date), run out a summary using those fields and subtotalling on the value(s) you need to check for net sero balance. That should give you the facility to ID those records which are not balanced and therefore to be seen by the user.

Now back to your original model (or a copy of it) and create the lookup(s) required to add the identify fields (typically created as a YES/NO or 0/1 value) and then the filter required to retain the unbalanced records and ignore the balanced records. Sort to taste and export for the user's pleasure.

Alternatively export the required fields in the table to Excel and see what you can do from there. (assuming you won't hit your Excel version's Max records limit of course.)

Does this help at all?



Grant

[adonis]

Grant in theory the data is on the first page, it would show up with the consolidated amount vs having non distinct securities and accounts which would be in essence a few records for the same item, which would have netted out to 0. I see what you are saying but still a little confused.
let me know what you think is the best approach to get the end result from your personal experience. I tried creating a summary and then netting the items there and doing an external lookup, most of the item net to 0 but the problem is some of the ones that don't net to 0 the amounts may differ because there may be an odd transaction which causes that amount to differ from original data set which i used again to tie back the netted numbers via the external lookup which i import the settlement date and amount and quantity to validate.

[Grant Perkins]

Grant in theory the data is on the first page, it would show up with the consolidated amount vs having non distinct securities and accounts which would be in essence a few records for the same item, which would have netted out to 0. I see what you are saying but still a little confused.
let me know what you think is the best approach to get the end result from your personal experience. I tried creating a summary and then netting the items there and doing an external lookup, most of the item net to 0 but the problem is some of the ones that don't net to 0 the amounts may differ because there may be an odd transaction which causes that amount to differ from original data set which i used again to tie back the netted numbers via the external lookup which i import the settlement date and amount and quantity to validate.

If the process is in a closed loop - in other words you are using the exact same report for the summary exported to lookup and linking back to the original report/extracted table I would not expect to see the sort of anomaly you are describing. If all the keys and the decisions about them are in place and unique then records will either match or not but you seem to be indicating that the some records which should be matched are slipping through. Have I misinterpreted?

That suggests that the unique linking keys are not totally correct. There could be a number of reasons for that but without full knowledge of the systems and process I am reluctant to speculate.

If you are working with a dynamic source (like a database of different versions of the report) then all bets are off.

The only other thing I can think of would be trades which span a date range selected report in such a way that the 2 (or more) parts of the trade never appear on the same report.

The one you can do something about is checking the keys are complete, are unique and match. So what can you tell us about that?


Grant