Should I use .NET LINQ grouping or just looping constructs to solve this?

I have a program that has a list of traders who have executed trades through a list of brokers. I have to sum the trade quantities only if the stock symbol is the same and there is a negative position and positive position executed from different brokers.

So, for instance,
The list could look like this:

Trader        Broker       Symbol       Quantity
Bob             MS              IBM             -100
Bob             GS               IBM             200
Bob             MS              AAPL           50
Bob             GS              AAPL            75
Jane            MS              IBM             100
Jane            GS              IBM              200

So, in this scenario - I would sum up only Bob's quantities for IBM because there is a negative position in one broker and a positive position with another broker only for that symbol. His Apple position would be ignored and since Jane has no negative positions she will also be ignored.

Is there a LINQ query that could return the traders who would need have their quantities summed or would I have to resort to looping constructs and some way of keeping track of whether the symbols are the same and the brokers are different, etc.?

Since this list is in a CSV file - I was even thinking of accessing the file via OleDbConnection and running a SQL query that would get me the data I need. But I wasn't able to construct a proper query.

Anyone have any good ideas on how to approach this problem?
soulacoAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Kyle AbrahamsSenior .Net DeveloperCommented:
what would you do with the brokers in that case?

eg:

Bob             ??            IBM           100

Would you still report the other rows or no?
soulacoAuthor Commented:
The brokers don't need to be in the output.

The output just needs to return the trader, symbol and the absolute value of the summed negative quantities or the summed positive quantities (whichever one is smaller).

The only way I can think of doing this is in 2 or 3 steps. I was thinking of first loading my data into my object hierarchy and then looping through to see which traders have a positive and negative position for a symbol from different brokers -- setting those values to an object and adding those objects into a temporary list and then looping through that list and doing a LINQ query to get the sum values I need.

This is probably not the best approach - but I honestly can't think of a way of doing this in a 1 step solution. I really hate having to loop through different collections but I don't know another way of approaching this.

If anyone has any better ideas - I would love to hear them.

Thanks in advance.
Fernando SotoRetiredCommented:
Hi soulaco;

See if the sample code below gives you what you are looking for.

// Test data from the question
List<Trade> trades = new List<Trade>() {new Trade() { Trader = "Bob", Broker="MS", Symbol="IBM", Quantity=-100},
  new Trade() { Trader = "Bob", Broker="GS", Symbol="IBM", Quantity=200},
  new Trade() { Trader = "Jane", Broker="MS", Symbol="IBM", Quantity=100},
  new Trade() { Trader = "Jane", Broker="GS", Symbol="IBM", Quantity=200},
  new Trade() { Trader = "Bob", Broker="GS", Symbol="AAPL", Quantity=75},
  new Trade() { Trader = "Bob", Broker="MS", Symbol="AAPL", Quantity=50}
} ;

var result = (from t in trades
              group t by new { t.Trader, t.Symbol } into symGroup
              where symGroup.Any(q => q.Quantity < 0)
              let Neg = Math.Abs(symGroup.Where(q => q.Quantity < 0).Sum(q => q.Quantity))
              let Pos = symGroup.Where(q => q.Quantity > 0).Sum(q => q.Quantity)
              select new {
                symGroup.Key.Trader,
                symGroup.Key.Symbol,
                AbsoluteValue = (Neg < Pos) ? Neg : Pos
              }).ToList();
  
// This class is where the data from the CSV file is needs to fill.             
public class Trade 
{
    public string Trader {get; set;}
    public string Broker {get; set;}
    public string Symbol {get; set;}
    public int Quantity {get; set;}
} 

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

PortletPaulEE Topic AdvisorCommented:
This result:
| trader | symbol | num_brokers_involved | gains | losses | net_qty |
|--------|--------|----------------------|-------|--------|---------|
|    Bob |    IBM |                    2 |   200 |   -100 |     100 |

Open in new window

From this query:
select
     t.trader, t.symbol
   , count(distinct t.broker) as num_brokers_involved
   , sum(case when t.quantity > 0 then t.quantity end) gains
   , sum(case when t.quantity < 0 then t.quantity end) losses
   , sum(t.quantity) as net_qty
from Table1 AS t
inner join (
  select Trader, Symbol
  from table1
  where Quantity < 0
  ) as negs on t.Trader = negs.trader and t.symbol = negs.symbol
group by
  t.trader, t.symbol
; 

Open in new window

I've thrown in a few columns you didn't ask for just in case they are of interest.

Details:
CREATE TABLE Table1
    ([Trader] varchar(4), [Broker] varchar(2), [Symbol] varchar(4), [Quantity] int)
;
    
INSERT INTO Table1
    ([Trader], [Broker], [Symbol], [Quantity])
VALUES
    ('Bob', 'MS', 'IBM', -100),
    ('Bob', 'GS', 'IBM', 200),
    ('Bob', 'MS', 'AAPL', 50),
    ('Bob', 'GS', 'AAPL', 75),
    ('Jane', 'MS', 'IBM', 100),
    ('Jane', 'GS', 'IBM', 200)
;

Open in new window

http://sqlfiddle.com/#!3/0e51fb/1
Ioannis ParaskevopoulosCommented:
Hi,

Here is a solution in LINQ:

Full Code ran on LINQPad
void Main()
{

	//Dummy Data
	//You should load the records from your data source
	var records = new List<Record>
	{
		new Record{Trader = "Giannis", Broker = "MS", Symbol = "IBM", Quantity = -100},
		new Record{Trader = "Bob", Broker = "MS", Symbol = "IBM", Quantity = -100},
		new Record{Trader = "Bob", Broker = "GS", Symbol = "IBM", Quantity = 200},
		new Record{Trader = "Bob", Broker = "MS", Symbol = "AAPL", Quantity = 50},
		new Record{Trader = "Bob", Broker = "GS", Symbol = "AAPL", Quantity = 75},
		new Record{Trader = "Jane", Broker = "MS", Symbol = "IBM", Quantity = 100},
		new Record{Trader = "Jane", Broker = "GS", Symbol = "IBM", Quantity = 200},
	};
	
	records.Dump("List Of all records");
	
	var filter = records
		.GroupBy (r => new {r.Trader,r.Symbol}) //Group records by Trader and Symbol
		.Where(g=>g.Min (x => x.Quantity)<0 && g.Max (x => x.Quantity>0))//Get only the groups that have both negative and positive quantities
		.Select(x=>new{x.Key.Trader,x.Key.Symbol})//Get only the Trader name and the Symbol for those groups
		.Distinct();//Get only distinct values (though this is not necessary)
	
	filter.Dump("The filters to aply");
	
	//From the initial list of records get only those who have a match on the previous list
	var filtered = records.Where (r => filter.Any (f => f.Trader == r.Trader && f.Symbol == r.Symbol));
	
	filtered.Dump("List of records after filters applied");
	
	//Now that we have our filtered rows we do our sum
	var results = filtered
		.GroupBy (f => new{f.Trader,f.Symbol})
		.Select(f=>new {Trader = f.Key.Trader,SymbolDocumentInfo = f.Key.Symbol,Quantity = f.Sum (x => x.Quantity)});
	
	results.Dump("Results");
}

//Class for each record
public class Record
{
	public string Trader {get;set;}
	public string Broker {get;set;}
	public string Symbol {get;set;}
	public int Quantity {get;set;}
}

Open in new window


Results on LINQPad
Results
soulacoAuthor Commented:
Thanks for the help but neither solution is correct. No record should return if the positive and negative values are for the same broker. So, for instance, if the data was set up like this:

Trader Broker Symbol Quantity Price
Bob      MS       IBM        100          10
Bob      MS       IBM         -200        20

Then this would not be in the final result because the positive and negative values are from the same broker.    

It should only return a record if it was like this:

Trader Broker Symbol Quantity Price
Bob      MS       IBM        100          10
Bob      GS       IBM         -200        20

This would return 100 for Bob for IBM because he executed a long position and a short position for the same symbol through different brokers.
Ioannis ParaskevopoulosCommented:
In this case you could change a bit the code of the filter:

	var filter = records
		.GroupBy (r => new {r.Trader,r.Symbol}) //Group records by Trader and Symbol
		.Where(g=>g.Min (x => x.Quantity)<0 && g.Max (x => x.Quantity>0)
		&& g.Where (x => x.Quantity<0).Select(x=>x.Broker).Except(g.Where (x => x.Quantity>0).Select(x=>x.Broker)).Count ()>0
		)//Get only the groups that have both negative and positive quantities
		.Select(x=>new{x.Key.Trader,x.Key.Symbol})//Get only the Trader name and the Symbol for those groups
		.Distinct();//Get only distinct values (though this is not necessary)

Open in new window



I have added the following in the Where:
&& g.Where (x => x.Quantity<0).Select(x=>x.Broker).Except(g.Where (x => x.Quantity>0).Select(x=>x.Broker)).Count ()>0

Open in new window


In order to explain:
g.Where (x => x.Quantity<0).Select(x=>x.Broker)

Open in new window

gets a list of brokers for the specified Trader/Symbol which have negative quantities.

g.Where (x => x.Quantity>0).Select(x=>x.Broker)

Open in new window

gets a list of brokers for the specified Trader/Symbol which have positive quantities.

Then with the Except i get whatever does not exist in both, so if the Count is more than 0, then i have brokers that has only negative or positive values.

Giannis
soulacoAuthor Commented:
First of all - thank you very much for doing this.

But 2 things -

1) I have to get the absolute value of the sum of the negative quantities or the sum of the positive quantities (whichever is less) as well.

2) I don't understand lambda expressions that well - is there any way you could re-type your query using the Linq syntax (i.e. - from t in something, etc.)?

Is there any way to include in the query to get the absolute value of the sum of the negative values or the sum of the positive values (whichever is less) also or do I need to do separate queries for that.

I tried joining on my original list but I am unsure how to return the sum of the quantities in that:

(from p in results
                     join t in _TradesObj on p.TraderName equals t.TraderName
                     where (t.Symbol == p.Symbol && t.Quantity < 0)
                     orderby t.TraderName
                     select new { Trader = t.TraderName, Symbol = t.Symbol, Quantity = t.Quantity }).ToList();

I can only seem to return the quantity but I don't know how to do a sum in the select new.

Thanks again!
soulacoAuthor Commented:
I was able to get the sum in the join working like this:

(from tradesSum in results
                     join trade in _TradesObj on tradesSum.TraderName equals trade.TraderName
                     where (trade.Symbol == tradesSum.Symbol)
                     orderby trade.TraderName
                     group trade by new { trade.TraderName, trade.Symbol } into grouped
                     let NetShortPosition = Math.Abs(grouped.Where(q => q.Quantity < 0).Sum(q => q.Quantity))
                     let NetLongPosition = grouped.Where(q => q.Quantity > 0).Sum(q => q.Quantity)
                     select new { Trader = grouped.Key.TraderName, Symbol = grouped.Key.Symbol, Quantity = (NetShortPosition < NetLongPosition) ? NetShortPosition : NetLongPosition }).ToList();

I am not sure if there is a way to do it in one query but this seems to be working which is good.
PortletPaulEE Topic AdvisorCommented:
No record should return if the positive and negative values are for the same broker.
This business rule can be met by adding a "having clause", see lines 15&16 below
select
     t.trader, t.symbol
   , count(distinct t.broker) as num_brokers_involved
   , sum(case when t.quantity > 0 then t.quantity end) gains
   , sum(case when t.quantity < 0 then t.quantity end) losses
   , sum(t.quantity) as net_qty
from Table1 AS t
inner join (
  select Trader, Symbol
  from table1
  where Quantity < 0
  ) as negs on t.Trader = negs.trader and t.symbol = negs.symbol
group by
  t.trader, t.symbol
having
  count(distinct t.broker) > 1
;

Open in new window

Please remove any calculated columns you don't need in the output
{+edit}
note, contrary to popular belief you do not have to include a calculation used in a having cluase in the selec clause. i.e. this variant of the query still works the same, it just outputs less columns:
select
     t.trader, t.symbol
   , sum(t.quantity) as net_qty
from Table1 AS t
inner join (
  select Trader, Symbol
  from table1
  where Quantity < 0
  ) as negs on t.Trader = negs.trader and t.symbol = negs.symbol
group by
  t.trader, t.symbol
having
  count(distinct t.broker) > 1
;

Open in new window


details, including new test case data:
**MS SQL Server 2008 Schema Setup**:

    CREATE TABLE Table1
        ([Trader] varchar(40), [Broker] varchar(40), [Symbol] varchar(4), [Quantity] int, [Price] int)
    ;
      
    INSERT INTO Table1
        ([Trader], [Broker], [Symbol], [Quantity])
    VALUES
        ('Bob', 'MS', 'IBM', -100),
        ('Bob', 'GS', 'IBM', 200),
        ('Bob', 'MS', 'AAPL', 50),
        ('Bob', 'GS', 'AAPL', 75),
        ('Jane', 'MS', 'IBM', 100),
        ('Jane', 'GS', 'IBM', 200)
    ;
        
    INSERT INTO Table1
        ([Trader], [Broker], [Symbol], [Quantity], [Price])
    VALUES
        
        ('Bob2', 'MS', 'IBM', 100, 10),
        ('Bob2', 'MS', 'IBM', -200, 20)
    ;
    
**Query 1**:

    select
         t.trader, t.symbol
       , count(distinct t.broker) as num_brokers_involved
       , sum(case when t.quantity > 0 then t.quantity end) gains
       , sum(case when t.quantity < 0 then t.quantity end) losses
       , sum(t.quantity) as net_qty
    from Table1 AS t
    inner join (
      select Trader, Symbol
      from table1
      where Quantity < 0
      ) as negs on t.Trader = negs.trader and t.symbol = negs.symbol
    group by
      t.trader, t.symbol
    having
      count(distinct t.broker) > 1
    

**[Results][2]**:
    | trader | symbol | num_brokers_involved | gains | losses | net_qty |
    |--------|--------|----------------------|-------|--------|---------|
    |    Bob |    IBM |                    2 |   200 |   -100 |     100 |

  [1]: http://sqlfiddle.com/#!3/a74a2/3
  [2]: http://sqlfiddle.com/#!3/a74a2/3/0

Open in new window

Vitor MontalvãoMSSQL Senior EngineerCommented:
soulaco, do you still need help with this question?
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
C#

From novice to tech pro — start learning today.