soulaco
asked on
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?
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?
ASKER
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
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.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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!
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!
ASKER
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.
(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.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
soulaco, do you still need help with this question?
eg:
Bob ?? IBM 100
Would you still report the other rows or no?