1

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.

1 Answer 1

0

Assuming column A = IDthe simplest way to do it is with a sumproduct formula -

=SUMPRODUCT(--(F:F="MSFT"),--(J:J="BUY"),M:M,L:L)/SUMIF(F:F,"=MSFT",L:L)

This is assuming a weighted average is (Price*Size)/(Sum of all Sizes)

This isn't a VBA solution, but it's simple and it works.

2
  • Unfortunately this does not work, it Sums all shares in the sheet. I need the weighted average price of the aggregate buys and aggregate sells of each security, based on that security only, not the whole sheet.
    – Eric R
    Feb 11, 2014 at 15:45
  • @EricR So use a sumif instead of a sum. See edited answer. If this isn't what you need, please express what you need in terms of column letter because I can't follow. Feb 11, 2014 at 15:55

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .