help with calculating average prices and profit/loss

[monkeytrader]

i hope someone can help me with trying to sort and calculate an enormous set
of data (25k rows) automatically with a function(s).

here is an example of the data i am using:

Symbol Side Quantity Price Principal Net
AA Buy 1000 8.07000 8070.00 8080.28
AA Buy 1000 8.06000 8060.00 8070.28
AA Buy 1000 8.00000 8000.00 8010.03
AA Sell 1000 7.81000 7810.00 7799.57
AA Sell 1000 8.00000 8000.00 7989.59
AA Sell 1000 7.99000 7990.00 7979.59
AAPL Buy 100 81.56000 8156.00 8157.00
AAPL Buy 100 81.50000 8150.00 8151.00
AAPL Buy 100 81.60000 8160.00 8161.00
AAPL Buy 100 81.25000 8125.00 8126.00
AAPL Sell 100 80.95000 8095.00 8093.93
AAPL Sell 100 81.50000 8150.00 8148.94
AAPL Sell 100 81.28000 8128.00 8126.94
AAPL Sell 100 81.20000 8120.00 8118.94
AAR Buy 300 16.90000 5070.00 5073.08
AAR Sell 300 15.90000 4770.00 4766.85

i want to know if it's possible to find the average prices of my "buys" and
"sells" and then calculate how much profit or loss i realized on the trade
without manually calculating for each symbol. for example, i believe these
examples are all losses. AA was a -330.00 loss, AAPl was a -98.00 loss, and
AAR was -300.00 loss.

i have 25,000 rows of data to parse through and don't know how to do this
without manually going through each symbol and side.

thank you in advance if someone can help. please reply if there is anything
that i can provide to make this more clear. i really appreciate anyone's
input.

 

[HelpExcel.com]

You could use a formulaic array similar to the following:

=SUM(IF(A2:A17&B2:B17="AABuy",E2:E17,0))-SUM(IF(A2:A17&B2:B17="AASell",E2:E17,0))

Make certain you press Ctrl-Shift-Enter after entering the formula. You
will also have to change the AA to reference the symbol and the range to
correspond to the thousands of rows you are referencing.

 

[HelpExcel.com]

You could use a formulaic array similar to the following:

=SUM(IF(A2:A17&B2:B17="AABuy",E2:E17,0))-SUM(IF(A2:A17&B2:B17="AASell",E2:E17,0))

Make certain you press Ctrl-Shift-Enter after entering the formula. You
will also have to change the AA to reference the symbol and the range to
correspond to the thousands of rows you are referencing.

 

[monkeytrader]

apologies, but i am unable to make the formula work for me, even on a small
sample data set.

to clarify what i am seeking. per symbol: i am subtracting the average price
of all of the "sells" from the average price of all of the "buys". then i am
multiplying 1/2 of the total "quantity" of shares by this difference to come
up with a loss or profit per symbol.

should i just sort my data-set by "symbol" and then "side", and then
auto-filter them to make it slightly less annoying to manually figure out my
results?

 

[monkeytrader]

apologies, but i am unable to make the formula work for me, even on a small
sample data set.

to clarify what i am seeking. per symbol: i am subtracting the average price
of all of the "sells" from the average price of all of the "buys". then i am
multiplying 1/2 of the total "quantity" of shares by this difference to come
up with a loss or profit per symbol.

should i just sort my data-set by "symbol" and then "side", and then
auto-filter them to make it slightly less annoying to manually figure out my
results?

 

[Iriemon]

Have you considered setting up a pivot table similar to this?

You can quickly determine the gain/loss by comparing the Average of Price
for Buy and Sell. (I inserted the blank rows for ease of reading the post.
The actual pivot table would not have the blank rows.)




Side
Symbol Data Buy Sell
AA Sum of Quantity 3000 3000
Average of Price 8.04 7.93
AAPL Sum of Quantity 400 400
Average of Price 81.48 81.23
AAR Sum of Quantity 300 300
Average of Price 16.90 15.90

 

[Iriemon]

Have you considered setting up a pivot table similar to this?

You can quickly determine the gain/loss by comparing the Average of Price
for Buy and Sell. (I inserted the blank rows for ease of reading the post.
The actual pivot table would not have the blank rows.)




Side
Symbol Data Buy Sell
AA Sum of Quantity 3000 3000
Average of Price 8.04 7.93
AAPL Sum of Quantity 400 400
Average of Price 81.48 81.23
AAR Sum of Quantity 300 300
Average of Price 16.90 15.90