Formula for calculating Stock Option rolls

chunt006

New Member
Joined
Jan 8, 2022
Messages
3
Office Version
  1. 365
Platform
  1. Windows
I'm trying to keep track of my cost basis when rollinjg stock options out to the next month. Column S is what column R should look like. I tried using sumifs by using the entry/closing time as a criteria since that is indicative of a roll. All rolls have the same date and time for each trade. A mini sheet is added below. Thanks in advance if you can help me.


Rolling Options1.xlsx
ABCDEFGHIJKLMNOPQRSTUVWX
1Trade #Trade Leg #Opened Leg / Closed LegStrategyEntry Date / Close DateEntry Time / Close TimeTicker SymbolBuy to Open (BTO) / Sell to Open (STO) / Buy to Close (BTC) / Sell to Close (STC)QTYLot SizeExpiration DatePut / CallStrike PriceCurrent Ticker PricePrice Paid / Received for OptionPremium Received / (Paid) per legRealized Profit / (Loss) per legRollIntended outcome for Col RUnrealized Profit / (Loss) per legUnrealized Profit / (Loss) TotalRealized Profit / (Loss) TotalOPRA #Current Option Contract Price
211OpenStrangle11/4/20219:59GLDSTO110012/10/2021P160$170.40$0.64$64.00$34.00$231.00----.GLD211210P160$0.01
311CloseStrangle11/9/202115:50GLDBTC110012/10/2021P160$170.40$0.30($30.00)-$69.00----.GLD211210P160$0.01
412OpenStrangle11/4/20219:59GLDSTO110012/10/2021C171$170.40$1.67$167.00($264.00)$231.00$231.00---.GLD211210C171$0.01
512CloseStrangle11/10/202111:46GLDBTC110012/10/2021C171$170.40$4.31($431.00)-$290.00----.GLD211210C171$0.01
613OpenRoll11/9/202115:50GLDSTO110012/10/2021P166$170.40$0.99$99.00$37.00$69.00$69.00---.GLD211210P166$0.01
713CloseRoll11/10/202111:46GLDBTC110012/10/2021P166$170.40$0.62($62.00)-$290.00----.GLD211210P166$0.01
814OpenRoll11/10/202111:46GLDSTO11001/21/2022P165$170.40$1.48$148.00($57.00)$290.00----.GLD220121P165$0.01
914CloseRoll12/13/20219:54GLDBTC11001/21/2022P165$170.40$2.05($205.00)-($377.00)----.GLD220121P165$0.01
1015OpenRoll11/10/202111:46GLDSTO11001/21/2022C170$170.40$6.35$635.00$463.00$290.00$290.00---.GLD220121C170$1.04
1115CloseRoll12/13/20219:54GLDBTC11001/21/2022C170$170.40$1.72($172.00)-($377.00)($377.00)--$213.00.GLD220121C170$1.04
1221OpenIron Condor1/13/202210:20SMHBTO11002/18/2022P275$279.62$2.23($223.00)$1,805.00$360.00----.SMH220218P275$4.53
1321CloseIron Condor1/28/202215:39SMHSTC11002/18/2022P275$279.62$20.28$2,028.00-($93.00)----.SMH220218P275$4.53
1422OpenIron Condor1/13/202210:20SMHSTO11002/18/2022P285$279.62$3.44$344.00($2,480.00)$360.00----.SMH220218P285$9.15
1522CloseIron Condor1/28/202215:39SMHBTC11002/18/2022P285$279.62$28.24($2,824.00)-($93.00)----.SMH220218P285$9.15
1623OpenIron Condor1/13/202210:20SMHBTO11002/18/2022C335$279.62$2.55($255.00)($251.00)$360.00----.SMH220218C335$0.03
1723CloseIron Condor1/28/202215:34SMHSTC11002/18/2022C335$279.62$0.04$4.00-$12.00----.SMH220218C335$0.03
1824OpenIron Condor1/13/202210:20SMHSTO11002/18/2022C325$279.62$4.94$494.00$487.00$360.00$360.00---.SMH220218C325$0.05
1924CloseIron Condor1/28/202215:34SMHBTC11002/18/2022C325$279.62$0.07($7.00)-$12.00----.SMH220218C325$0.05
2025OpenRoll1/28/202215:39SMHBTO11003/18/2022P275$279.62$24.05($2,405.00)-($93.00)-$1,352.50--.SMH220318P275$10.53
2125CloseRollSMHSTC11003/18/2022P275$279.62$0.00-----.SMH220318P275$10.53
2226OpenRoll1/28/202215:39SMHSTO11003/18/2022P285$279.62$31.08$3,108.00-($93.00)($93.00)($1,600.50)--.SMH220318P285$15.08
2326CloseRollSMHBTC11003/18/2022P285$279.62$0.00------.SMH220318P285$15.08
2427OpenRoll1/28/202215:34SMHSTO11003/18/2022C325$279.62$0.40$40.00-$12.00-$45.50--.SMH220318C325$0.86
2527CloseRollSMHBTC11003/18/2022C325$279.62$0.00------.SMH220318C325$0.86
2628OpenRoll1/28/202215:34SMHBTO11003/18/2022C335$279.62$0.25($25.00)-$12.00$12.00($19.00)--.SMH220318C335$0.44
2728CloseRollSMHSTC11003/18/2022C335$279.62$0.00--$0.00-$217.50($439.00).SMH220318C335$0.44
2831OpenPut11/24/20219:30UALSTO210012/31/2021P42$49.14$1.08$216.00($418.00)$216.00$216.00---.UAL211231P42$0.01
2931ClosePut12/15/202111:44UALBTC210012/31/2021P42$49.14$3.17($634.00)-$222.00----.UAL211231P42$0.01
3032OpenRoll12/15/202111:44UALSTO21001/28/2022P42$49.14$4.28$856.00$518.00$222.00$222.00---.UAL220128P42$1.11
3132CloseRoll12/22/202115:11UALBTC21001/28/2022P42$49.14$1.69($338.00)-($338.00)($338.00)--$100.00.UAL220128P42$1.11
3241OpenPut1/13/202213:08ROKUSTO11002/25/2022P170$165.00$15.60$1,560.00($855.00)$1,560.00$1,560.00---.ROKU220225P170$17.15
3341ClosePut2/3/20229:39ROKUBTC11002/25/2022P170$165.00$24.15($2,415.00)-$280.00----.ROKU220225P170$17.15
3442OpenPut2/3/20229:39ROKUSTO11003/18/2022P170$165.00$26.95$2,695.00-$280.00$2,695.00($625.00)--.ROKU220318P170$20.70
3542ClosePutROKUBTC11003/18/2022P170$165.00$0.00--$0.00-$230.00($855.00).ROKU220318P170$20.70
3651OpenIron Condor2/2/202212:55FBBTO11003/18/2022P280$226.92$4.60($460.00)-$360.00-($4,822.50)--.FB220318P280$52.83
3751CloseIron CondorFBSTC11003/18/2022P280$226.92$0.00------.FB220318P280$52.83
3852OpenIron Condor2/2/202212:55FBSTO11003/18/2022P290$226.92$6.50$650.00-$360.00-$5,582.50--.FB220318P290$62.33
3952CloseIron CondorFBBTC11003/18/2022P290$226.92$0.00------.FB220318P290$62.33
4053OpenIron Condor2/2/202212:55FBSTO11003/18/2022C360$226.92$5.40$540.00-$360.00-($525.50)--.FB220318C360$0.15
4153CloseIron CondorFBBTC11003/18/2022C360$226.92$0.00------.FB220318C360$0.15
4254OpenIron Condor2/2/202212:55FBBTO11003/18/2022C370$226.92$3.70($370.00)-$360.00($370.00)$357.50--.FB220318C370$0.13
4354CloseIron CondorFBSTC11003/18/2022C370$226.92$0.00--$0.00-$592.00$0.00.FB220318C370$0.13
Rolling Trades
Cell Formulas
RangeFormula
P2:P43P2=IF(OR($H2="BTO",$H2="BTC"),-1*($I2*$J2*$O2),$I2*$J2*$O2)
Q2:Q43Q2=IF($E3="","-",IF(AND($A3=$A2,$B3=$B2),SUMIFS($P$2:$P$314,$A$2:$A$314,$A2,$B$2:$B$314,$B2),"-"))
R2:R43R2=IF($E2="","-",SUMIFS($P$2:$P$314,$F$2:$F$314,$F2))
T2:T43T2=IF($A3="","-",IF($E3="",IF(OR($H2="BTO",$H2="BTC"),(($O2-$X2)*$I2*$J2),-1*($O2-$X2)*$I2*$J2),"-"))
U2:U43U2=IF($A3=$A2,"-",IF($O2<>"","-",SUM(SUMIFS($T$2:$T$314,$A$2:$A$314,$A2)-SUMIFS($Q$2:$Q$314,$A$2:$A$314,$A2))))
V2:V43V2=IF($G3=$G2,"-",SUMIFS($Q$2:$Q$314,$A$2:$A$314,$A2))
W2:W31,W36:W43W2=CONCATENATE("."&$G2&TEXT($K2,"YYMMDD")&$L2&$M2)
X2:X31,X36:X43X2=RTD("tos.rtd",,"MARK",$W2)
W32:W35W32=IF($L32="S","STOCK","."&$G32&TEXT($K32,"YYMMDD")&$L32&$M32)
X32:X35X32=IF($W32="-","-",RTD("tos.rtd",,"MARK",$W32))
S4,S10:S11,S6S4=SUMIFS($P$2:$P$11,$F$2:$F$11,$F4)
S18S18=$P$12+$P$14+$P$16+$P$18
S22S22=$P$13+$P$15+$P$22+$P$20
S26S26=$P$17+$P$19+$P$24+$P$26
S28,S42:S43,S34:S35,S31:S32S28=P28
S30S30=P29+P30
N2:N43N2=RTD("tos.rtd", , "MARK", $G2)
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
Hi Chunt006,

This may be what you want but disagrees with your expected results for rows 34 and 42.

Excel Formula:
=IF(AND($E2>0,COUNTIFS($E$2:$E$9999,$E2,$F$2:$F$9999,$F2)=COUNTIFS($E$2:$E2,$E2,$F$2:$F2,$F2)),SUMIFS($P$2:$P$9999,$E$2:$E$9999,$E2,$F$2:$F$9999,$F2),"-")
 
Upvote 0
Solution
Toadstool,
Your solution worked. I had incorrect results in row 32 and 42. I hadn't thought of using countifs. Thank you for your quick reply.
 
Upvote 0

Forum statistics

Threads
1,223,157
Messages
6,170,419
Members
452,325
Latest member
BlahQz

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