Home 
Search 
Today's Posts 
#1




Profit and Loss Formula
I have my data like so:
ColA ColB ColC ColD ColE ColF Bid Ask PL Side QTY TradePrice 12.7 12.8 100 Sell 10 12.7 12.7 12.8 0 Buy 10 12.7 12.7 12.8 100 Sell 10 12.9 12.7 12.8 200 Buy 10 12.9 ColD thru E has my trade data while ColA and ColB has the realtime market prices. I am trying to come up with a formula for ColC which will calculate my Profit or Loss for each trade. For example on the last line, I bought 10 shares at 12.90. To close out the position at that moment in time I would need to sell them on the "bid" side at 12.70, thus creating a $200 loss. I think the formula needs to look at the trade side. If the trade was a buy, then the "bid" side of the realtime market is used in the Profit and Loss Formula. If the trade price is greater than the "bid" price, then there is a profit. If the trade price is less than the "bid" price, then there is a loss  as in the example. Similar but opposite for trade side = "sell". The formula will need to look at the "ask" side of the real time market. If the trade price is greater than the "ask" price, then there is a gain. If the trade price is less than the ask price, there is a loss. Thank you in advance. 
#2




Profit and Loss Formula
I couldn't really follow everything you were saying, but the following
formula seems to work, except the quantity needs to be 1000 rather than 10. Or it might mean the formulae results needs to be multiplied by 100. Since I was totally guessing, you'll probably want to check to make sure that this really is what you intended. =IF(D2="sell",(E2*F2)(E2*B2),(E2*A2)(E2*F2))  Kevin Vaughn "carl" wrote: I have my data like so: ColA ColB ColC ColD ColE ColF Bid Ask PL Side QTY TradePrice 12.7 12.8 100 Sell 10 12.7 12.7 12.8 0 Buy 10 12.7 12.7 12.8 100 Sell 10 12.9 12.7 12.8 200 Buy 10 12.9 ColD thru E has my trade data while ColA and ColB has the realtime market prices. I am trying to come up with a formula for ColC which will calculate my Profit or Loss for each trade. For example on the last line, I bought 10 shares at 12.90. To close out the position at that moment in time I would need to sell them on the "bid" side at 12.70, thus creating a $200 loss. I think the formula needs to look at the trade side. If the trade was a buy, then the "bid" side of the realtime market is used in the Profit and Loss Formula. If the trade price is greater than the "bid" price, then there is a profit. If the trade price is less than the "bid" price, then there is a loss  as in the example. Similar but opposite for trade side = "sell". The formula will need to look at the "ask" side of the real time market. If the trade price is greater than the "ask" price, then there is a gain. If the trade price is less than the ask price, there is a loss. Thank you in advance. 
Reply 
Thread Tools  Search this Thread 
Display Modes  


Similar Threads  
Thread  Forum  
Profit & Loss Account Costs shown as a percentage of Sales  Excel Discussion (Misc queries)  
Calculating Profit and Loss in one column  Excel Discussion (Misc queries)  
Calculating Profit and Loss in one column  Excel Discussion (Misc queries)  
Calculating Profit and Loss in one column  Excel Worksheet Functions  
Line or bar graphs for tracking stocks profit and loss.  Charts and Charting in Excel 