I am importing data from a trading system that has buy and sell orders for multiple securities(stocks). I need to aggregate the same security buy orders and the same security sell orders, creating a total number of shares bought and sold, and then calculating a weighted average price for the sale and buy. Eliminating the multiple lines and keeping the aggregate positions.
Columns look like this:
ID /Trade Date/Settlement Date/Route ID/Ticker/Name/Country/Currency/Action/Type/ Size/Price/Broker/CUSIP/ISIN/SEDOL/Commission/FX
So I need to sum the Size column based on the Action and Ticker (ie MSFT Buy) and then get a weighted average of those (all of the MSFT "Buy" executions for example).
Any help would be greatly appreciated.