Formula For Stock Entry/Exit Points

kivikatz

New Member
Joined
Sep 12, 2020
Messages
25
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
Hi All. I have a two-part question regarding what's the best formula to use in figuring out entry/exit points for a stock, as well as recalculating cumulative profit/loss when entering a position. I have tried multiple formulas, using INDEX/MATCH and XLOOKUP, but have had no success at all. I have struggled over this and I think is way beyond my ken of understanding. I would appreciate your help in figuring out the most effective formulas.

Attached is the Excel sheet for reference (I have removed several rows just for expediency sake and the data is hard coded, except or the entry, exit and profit-and-loss columns). The following is what I am trying to accomplish. I would like to recalculate a new ENTRY in column H after there is an EXIT signal in column I (in this case it would be cell H17). I would also like to recalculate a new EXIT in column I after the new ENTRY signal in column H (in this case it would be cell I26).

For the PROFIT & LOSS column, I would like to end the calculation and leave the cells blank after the first EXIT signal (F13:F16), recalculate PROFIT & LOSS after the subsequent ENTRY signal (F17:F26), and then end the calculation again after the subsequent EXIT (F27:F28).

I think the Excel sheet better illustrates what I am trying to accomplish. Thanks for all your help.

KKHKHK.xlsm
ABCDEFGHIJKLMN
5DateCloseChangeCumulativeNew HighsP&LMACDEntry SignalExit Signal
6Fri 12/13/1971.680.00%0.00%1.94
7Mon 12/16/1976.306.45%6.45%TRUE0.00%1.94ENTRY ENTRY
8Tue 12/17/1975.80-0.66%5.75%-0.66%2.37  
9Wed 12/18/1978.633.73%9.70%TRUE3.05%2.64  
10Mon 03/09/20121.60-13.57%69.64%59.37%3.57  
11Tue 03/10/20129.076.14%80.06%69.16%1.07  
12Wed 03/11/20126.85-1.72%76.97%66.25%-0.31 EXITEXIT
13Thu 03/12/20112.11-11.62%56.40%46.93%-1.56  P&L should be blank
14Wed 06/03/20176.590.16%146.36%131.44%7.58  P&L should be blank
15Thu 06/04/20172.88-2.10%141.18%126.58%7.80  P&L should be blank
16Fri 06/05/20177.132.46%147.11%132.15%7.59  P&L should be blank
17Mon 06/08/20189.987.25%165.04%TRUE148.99%7.68  P&L should be recalculatedNew ENTRY
18Tue 06/09/20188.13-0.97%162.46%146.57%8.69  P&L should be recalculated
19Wed 06/10/20205.018.97%186.01%TRUE168.69%9.23  P&L should be recalculated
20Thu 06/11/20194.57-5.09%171.44%155.01%10.90  P&L should be recalculated
21Fri 06/12/20187.06-3.86%160.97%145.16%11.25  P&L should be recalculated
22Mon 06/29/20201.875.17%181.63%164.57%8.48  P&L should be recalculated
23Tue 06/30/20215.966.98%201.28%TRUE183.04%8.47  P&L should be recalculated
24Wed 07/01/20223.933.69%212.40%TRUE193.49%9.50  P&L should be recalculated
25Thu 07/02/20221.98-0.87%209.68%190.93%7.80  P&L should be recalculated
26Fri 07/03/20212.76-4.15%196.82%178.85%-0.05  P&L should be recalculatedNew EXIT
27Mon 07/06/20198.50-6.70%176.93%160.16%-1.56  P&L should be blank
28Tue 07/07/20205.003.27%185.99%168.68%1.07  P&L should be blank
Chart Data (2)
Cell Formulas
RangeFormula
H7:H28H7=IFERROR(IF(MATCH("ENTRY",$H$6:H6,0),"",IF(AND(E7=TRUE,G7>0),"ENTRY","")),IF(AND(E7=TRUE,G7>0),"ENTRY",""))
I7:I28I7=IFERROR(IF(MATCH("EXIT",$I$6:I6,0),"",IF(AND(MATCH("ENTRY",$H$6:H7,0),G7<0),"EXIT","")),IF(AND(MATCH("ENTRY",$H$6:H7,0),G7<0),"EXIT",""))
F7:F28F7=IF(OR(H7="ENTRY",NOT(ISBLANK(F6))),(B7-$B$7)/$B$7,"")
 

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

Forum statistics

Threads
1,213,497
Messages
6,113,999
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top