Building a formula contingent on non-consistent signals

D

Dennisss11

New Member
Joined
Nov 25, 2022
Messages
9
Office Version
  1. 365
Platform
  1. MacOS
Hi all,

I have been struggling with this excel puzzle for a while now. Hopefully someone can help me out!

I am testing a trading strategy where sometimes we have to 'get out' and later 'get back in' again based on some rules that I would like to put together in a formula. I've tried to highlight some relevant cells, which I will explain now:

Step 1: In column T, we see the Put payoff. As soon as this turns negative (first at T33), we have to "roll" the option, meaning that on that (end of) day, we get out of the market. We got out on a strike of 435 (Q32) at a closing price of 430,73 (F33).
Step 2: The days after the "roll" signal, we check whether the "close" (column F) exceeds our 435 (Q32) strike. This could take a day, but also 200 days..... If it doesn't, we should see "out" in column W and a 0 in column X.
Step 3: If the closing price exceeds our strike, which happens in this example at 435,94 (F38), column W should give a subtotal of the difference between our get out closing price 430,73 (F33) and the 435,94 (F38) which I just mentioned.
Step 4: After this, so the day after "subtotal" in column W, we can start at step 1 again. In the attached example, I highlighted 3 such events of getting out and back in again. The next event starts at W41. The one after that at W47.

I know my way around if formulas, but I can't seem to figure out how to use them with these kind of constraints. I would be very greatful!

Thank you all in advance.

Test xxxx.xlsx
ABFQTWXY
29DateCloseStrikePut payoffPreferred in formula
301/4/93Monday435.3843500.00
311/5/93Tuesday434.344340.250.25
321/6/93Wednesday434.524350.920.92
331/7/93Thursday430.73431-2.93Roll-2.93
341/8/93Friday429.05429-0.62out0
351/11/93Monday430.954311.87out0
361/12/93Tuesday431.044311.06out0
371/13/93Wednesday433.034331.01out0
381/14/93Thursday435.944360.99Subtotal5.21
391/15/93Friday437.154371.011.01
401/18/93Monday436.844371.381.38
411/19/93Tuesday435.13435-0.85Roll-0.85
421/20/93Wednesday433.37433-0.71out 0
431/21/93Thursday435.494350.81out0
441/22/93Friday436.114360.73out0
451/25/93Monday440.014401.52Subtotal4.88
461/26/93Tuesday439.954400.880.88
471/27/93Wednesday438.11438-0.92Roll-0.92
481/28/93Thursday438.664390.93out0
491/29/93Friday438.784390.94out0
502/1/93Monday442.524431.85Subtotal4.41
512/2/93Tuesday442.554430.83
522/3/93Wednesday447.204471.25
532/4/93Thursday449.564500.92
542/5/93Friday448.934490.20
552/8/93Monday447.854480.74
562/9/93Tuesday445.33445-1.49
572/10/93Wednesday446.234460.97
582/11/93Thursday447.664481.01
592/12/93Friday444.58445-2.18
602/16/93Tuesday433.91434-9.11
612/17/93Wednesday433.304330.63
622/18/93Thursday431.904320.05
632/19/93Friday434.224341.31
642/22/93Monday435.244351.99
652/23/93Tuesday434.804350.88
662/24/93Wednesday440.874411.33
672/25/93Thursday442.344421.28
SPX
Cell Formulas
RangeFormula
Q30:Q67Q30=ROUND(P30,0)
T31:T67T31=(R30-MAX(0,(Q30-F31)))*$B$12
X30:X33,X46:X47,X39:X41X30=T30
X38X38=F38-F33
X45X45=F45-F41
X50X50=F50-F47
B30:B67B30=TEXT(A30,"dddd")
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.
Sort by date Sort by votes
How about this (change all column Y references to column W):

Book1
ABFPQTWXYZ
29DateCloseStrikePut payoffPreferred in formula
3033973.66Monday435.3843543500 0
3133974.66Tuesday434.344344340.250.25 0.25
3233975.66Wednesday434.524354350.920.92 0.92
3333976.66Thursday430.73431431-2.93Roll-2.93Roll-2.93
3433977.66Friday429.05429429-0.62out0Out0
3533980.66Monday430.954314311.87out0Out0
3633981.66Tuesday431.044314311.06out0Out0
3733982.66Wednesday433.034334331.01out0Out0
3833983.66Thursday435.944364360.99Subtotal5.209991Subtotal-5.20999
3933984.66Friday437.154374371.011.01 1.01
4033987.66Monday436.844374371.381.38 1.38
4133988.66Tuesday435.13435435-0.85Roll-0.85Roll-0.85
4233989.66Wednesday433.37433433-0.71Out0Out0
4333990.66Thursday435.494354350.81out0Out0
4433991.66Friday436.114364360.73out0Out0
4533994.66Monday440.014404401.52Subtotal4.880005Subtotal-4.88001
4633995.66Tuesday439.954404400.880.88 0.88
4733996.66Wednesday438.11438438-0.92Roll-0.92Roll-0.92
4833997.66Thursday438.664394390.93out0Out0
4933998.66Friday438.784394390.94out0Out0
5034001.66Monday442.524434431.85Subtotal4.410004Subtotal-4.41
5134002.66Tuesday442.554434430.83 0.83
5234003.66Wednesday447.24474471.25 1.25
5334004.66Thursday449.564504500.92 0.92
5434005.66Friday448.934494490.2 0.2
5534008.66Monday447.854484480.74 0.74
5634009.66Tuesday445.33445445-1.49Roll-1.49
5734010.66Wednesday446.234464460.97Out0
5834011.66Thursday447.664484481.01Out0
5934012.66Friday444.58445445-2.18Out0
6034016.66Tuesday433.91434434-9.11Out0
6134017.66Wednesday433.34334330.63Out0
6234018.66Thursday431.94324320.05Out0
6334019.66Friday434.224344341.31Out0
6434022.66Monday435.244354351.99Out0
6534023.66Tuesday434.84354350.88Out0
6634024.66Wednesday440.874414411.33Out0
6734025.66Thursday442.344424421.28Out0
Sheet2
Cell Formulas
RangeFormula
Y30:Y67Y30=IF(AND(T30<0,T29>=0,Y29<>"Out"),"Roll",IF(AND(F30>IFERROR(INDEX($Q$30:Q30,MAX(INDEX((ROW($29:29)-28)*($Y$29:Y29="Roll"),,))-2),""),Y29="Out"),"Subtotal",IF(OR(Y29="Roll",Y29="Out"),"Out","")))
Z30:Z67Z30=IF(OR(Y30="Roll",Y30=""),T30,IF(Y30="Out",0,IF(Y30="Subtotal",IFERROR(INDEX($F$30:F30,MAX(INDEX((ROW($29:29)-28)*($Y$29:Y29="Roll"),,))-1),"")-F30,"")))
X30:X33,X46:X47,X39:X41X30=T30
X38X38=F38-F33
X45X45=F45-F41
X50X50=F50-F47
B30:B67B30=TEXT(A30,"dddd")
Q30:Q67Q30=ROUND(P30,0)
 
Upvote 0
Solution
You must log in or register to reply here.

Similar threads

C
  • Question Question
Create code that sends emails and creates a log
Replies
1
Views
288
cokeoverpepsi123
C
M
  • Question Question
Auto adjust row heights based on specific column
Replies
1
Views
405
XLfriend777
XLfriend777
D
  • Question Question
VBA to Populate an Issue Status Cell
Replies
0
Views
383
Desiato34
D
C
  • Solved
Formula for calculating Stock Option rolls
Replies
4
Views
880
Toadstool
Toadstool
S
  • Solved
Formula or VBA to "round" numbers for sales prices + issues with number/text/...
2
Replies
14
Views
1K
RoryA
RoryA

Forum statistics

Threads
1,223,956
Messages
6,175,619
Members
452,661
Latest member
Nonhle

Share this page

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