If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
help with calculating average prices and profit/loss
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. |
#2
|
|||
|
|||
help with calculating average prices and profit/loss
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. -- Regards, Eddie http://www.HelpExcel.com "monkeytrader" wrote: 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. |
#3
|
|||
|
|||
help with calculating average prices and profit/loss
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? "HelpExcel.com" wrote: 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. -- Regards, Eddie http://www.HelpExcel.com "monkeytrader" wrote: 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. |
#4
|
|||
|
|||
help with calculating average prices and profit/loss
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 "monkeytrader" wrote: 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? "HelpExcel.com" wrote: 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. -- Regards, Eddie http://www.HelpExcel.com "monkeytrader" wrote: 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. |
Thread Tools | |
Display Modes | |
|
|