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 | ||||||||||||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | S | T | U | V | W | X | |||
| 1 | Trade # | Trade Leg # | Opened Leg / Closed Leg | Strategy | Entry Date / Close Date | Entry Time / Close Time | Ticker Symbol | Buy to Open (BTO) / Sell to Open (STO) / Buy to Close (BTC) / Sell to Close (STC) | QTY | Lot Size | Expiration Date | Put / Call | Strike Price | Current Ticker Price | Price Paid / Received for Option | Premium Received / (Paid) per leg | Realized Profit / (Loss) per leg | Roll | Intended outcome for Col R | Unrealized Profit / (Loss) per leg | Unrealized Profit / (Loss) Total | Realized Profit / (Loss) Total | OPRA # | Current Option Contract Price | ||
| 2 | 1 | 1 | Open | Strangle | 11/4/2021 | 9:59 | GLD | STO | 1 | 100 | 12/10/2021 | P | 160 | $170.40 | $0.64 | $64.00 | $34.00 | $231.00 | - | - | - | - | .GLD211210P160 | $0.01 | ||
| 3 | 1 | 1 | Close | Strangle | 11/9/2021 | 15:50 | GLD | BTC | 1 | 100 | 12/10/2021 | P | 160 | $170.40 | $0.30 | ($30.00) | - | $69.00 | - | - | - | - | .GLD211210P160 | $0.01 | ||
| 4 | 1 | 2 | Open | Strangle | 11/4/2021 | 9:59 | GLD | STO | 1 | 100 | 12/10/2021 | C | 171 | $170.40 | $1.67 | $167.00 | ($264.00) | $231.00 | $231.00 | - | - | - | .GLD211210C171 | $0.01 | ||
| 5 | 1 | 2 | Close | Strangle | 11/10/2021 | 11:46 | GLD | BTC | 1 | 100 | 12/10/2021 | C | 171 | $170.40 | $4.31 | ($431.00) | - | $290.00 | - | - | - | - | .GLD211210C171 | $0.01 | ||
| 6 | 1 | 3 | Open | Roll | 11/9/2021 | 15:50 | GLD | STO | 1 | 100 | 12/10/2021 | P | 166 | $170.40 | $0.99 | $99.00 | $37.00 | $69.00 | $69.00 | - | - | - | .GLD211210P166 | $0.01 | ||
| 7 | 1 | 3 | Close | Roll | 11/10/2021 | 11:46 | GLD | BTC | 1 | 100 | 12/10/2021 | P | 166 | $170.40 | $0.62 | ($62.00) | - | $290.00 | - | - | - | - | .GLD211210P166 | $0.01 | ||
| 8 | 1 | 4 | Open | Roll | 11/10/2021 | 11:46 | GLD | STO | 1 | 100 | 1/21/2022 | P | 165 | $170.40 | $1.48 | $148.00 | ($57.00) | $290.00 | - | - | - | - | .GLD220121P165 | $0.01 | ||
| 9 | 1 | 4 | Close | Roll | 12/13/2021 | 9:54 | GLD | BTC | 1 | 100 | 1/21/2022 | P | 165 | $170.40 | $2.05 | ($205.00) | - | ($377.00) | - | - | - | - | .GLD220121P165 | $0.01 | ||
| 10 | 1 | 5 | Open | Roll | 11/10/2021 | 11:46 | GLD | STO | 1 | 100 | 1/21/2022 | C | 170 | $170.40 | $6.35 | $635.00 | $463.00 | $290.00 | $290.00 | - | - | - | .GLD220121C170 | $1.04 | ||
| 11 | 1 | 5 | Close | Roll | 12/13/2021 | 9:54 | GLD | BTC | 1 | 100 | 1/21/2022 | C | 170 | $170.40 | $1.72 | ($172.00) | - | ($377.00) | ($377.00) | - | - | $213.00 | .GLD220121C170 | $1.04 | ||
| 12 | 2 | 1 | Open | Iron Condor | 1/13/2022 | 10:20 | SMH | BTO | 1 | 100 | 2/18/2022 | P | 275 | $279.62 | $2.23 | ($223.00) | $1,805.00 | $360.00 | - | - | - | - | .SMH220218P275 | $4.53 | ||
| 13 | 2 | 1 | Close | Iron Condor | 1/28/2022 | 15:39 | SMH | STC | 1 | 100 | 2/18/2022 | P | 275 | $279.62 | $20.28 | $2,028.00 | - | ($93.00) | - | - | - | - | .SMH220218P275 | $4.53 | ||
| 14 | 2 | 2 | Open | Iron Condor | 1/13/2022 | 10:20 | SMH | STO | 1 | 100 | 2/18/2022 | P | 285 | $279.62 | $3.44 | $344.00 | ($2,480.00) | $360.00 | - | - | - | - | .SMH220218P285 | $9.15 | ||
| 15 | 2 | 2 | Close | Iron Condor | 1/28/2022 | 15:39 | SMH | BTC | 1 | 100 | 2/18/2022 | P | 285 | $279.62 | $28.24 | ($2,824.00) | - | ($93.00) | - | - | - | - | .SMH220218P285 | $9.15 | ||
| 16 | 2 | 3 | Open | Iron Condor | 1/13/2022 | 10:20 | SMH | BTO | 1 | 100 | 2/18/2022 | C | 335 | $279.62 | $2.55 | ($255.00) | ($251.00) | $360.00 | - | - | - | - | .SMH220218C335 | $0.03 | ||
| 17 | 2 | 3 | Close | Iron Condor | 1/28/2022 | 15:34 | SMH | STC | 1 | 100 | 2/18/2022 | C | 335 | $279.62 | $0.04 | $4.00 | - | $12.00 | - | - | - | - | .SMH220218C335 | $0.03 | ||
| 18 | 2 | 4 | Open | Iron Condor | 1/13/2022 | 10:20 | SMH | STO | 1 | 100 | 2/18/2022 | C | 325 | $279.62 | $4.94 | $494.00 | $487.00 | $360.00 | $360.00 | - | - | - | .SMH220218C325 | $0.05 | ||
| 19 | 2 | 4 | Close | Iron Condor | 1/28/2022 | 15:34 | SMH | BTC | 1 | 100 | 2/18/2022 | C | 325 | $279.62 | $0.07 | ($7.00) | - | $12.00 | - | - | - | - | .SMH220218C325 | $0.05 | ||
| 20 | 2 | 5 | Open | Roll | 1/28/2022 | 15:39 | SMH | BTO | 1 | 100 | 3/18/2022 | P | 275 | $279.62 | $24.05 | ($2,405.00) | - | ($93.00) | - | $1,352.50 | - | - | .SMH220318P275 | $10.53 | ||
| 21 | 2 | 5 | Close | Roll | SMH | STC | 1 | 100 | 3/18/2022 | P | 275 | $279.62 | $0.00 | - | - | - | - | - | .SMH220318P275 | $10.53 | ||||||
| 22 | 2 | 6 | Open | Roll | 1/28/2022 | 15:39 | SMH | STO | 1 | 100 | 3/18/2022 | P | 285 | $279.62 | $31.08 | $3,108.00 | - | ($93.00) | ($93.00) | ($1,600.50) | - | - | .SMH220318P285 | $15.08 | ||
| 23 | 2 | 6 | Close | Roll | SMH | BTC | 1 | 100 | 3/18/2022 | P | 285 | $279.62 | $0.00 | - | - | - | - | - | - | .SMH220318P285 | $15.08 | |||||
| 24 | 2 | 7 | Open | Roll | 1/28/2022 | 15:34 | SMH | STO | 1 | 100 | 3/18/2022 | C | 325 | $279.62 | $0.40 | $40.00 | - | $12.00 | - | $45.50 | - | - | .SMH220318C325 | $0.86 | ||
| 25 | 2 | 7 | Close | Roll | SMH | BTC | 1 | 100 | 3/18/2022 | C | 325 | $279.62 | $0.00 | - | - | - | - | - | - | .SMH220318C325 | $0.86 | |||||
| 26 | 2 | 8 | Open | Roll | 1/28/2022 | 15:34 | SMH | BTO | 1 | 100 | 3/18/2022 | C | 335 | $279.62 | $0.25 | ($25.00) | - | $12.00 | $12.00 | ($19.00) | - | - | .SMH220318C335 | $0.44 | ||
| 27 | 2 | 8 | Close | Roll | SMH | STC | 1 | 100 | 3/18/2022 | C | 335 | $279.62 | $0.00 | - | - | $0.00 | - | $217.50 | ($439.00) | .SMH220318C335 | $0.44 | |||||
| 28 | 3 | 1 | Open | Put | 11/24/2021 | 9:30 | UAL | STO | 2 | 100 | 12/31/2021 | P | 42 | $49.14 | $1.08 | $216.00 | ($418.00) | $216.00 | $216.00 | - | - | - | .UAL211231P42 | $0.01 | ||
| 29 | 3 | 1 | Close | Put | 12/15/2021 | 11:44 | UAL | BTC | 2 | 100 | 12/31/2021 | P | 42 | $49.14 | $3.17 | ($634.00) | - | $222.00 | - | - | - | - | .UAL211231P42 | $0.01 | ||
| 30 | 3 | 2 | Open | Roll | 12/15/2021 | 11:44 | UAL | STO | 2 | 100 | 1/28/2022 | P | 42 | $49.14 | $4.28 | $856.00 | $518.00 | $222.00 | $222.00 | - | - | - | .UAL220128P42 | $1.11 | ||
| 31 | 3 | 2 | Close | Roll | 12/22/2021 | 15:11 | UAL | BTC | 2 | 100 | 1/28/2022 | P | 42 | $49.14 | $1.69 | ($338.00) | - | ($338.00) | ($338.00) | - | - | $100.00 | .UAL220128P42 | $1.11 | ||
| 32 | 4 | 1 | Open | Put | 1/13/2022 | 13:08 | ROKU | STO | 1 | 100 | 2/25/2022 | P | 170 | $165.00 | $15.60 | $1,560.00 | ($855.00) | $1,560.00 | $1,560.00 | - | - | - | .ROKU220225P170 | $17.15 | ||
| 33 | 4 | 1 | Close | Put | 2/3/2022 | 9:39 | ROKU | BTC | 1 | 100 | 2/25/2022 | P | 170 | $165.00 | $24.15 | ($2,415.00) | - | $280.00 | - | - | - | - | .ROKU220225P170 | $17.15 | ||
| 34 | 4 | 2 | Open | Put | 2/3/2022 | 9:39 | ROKU | STO | 1 | 100 | 3/18/2022 | P | 170 | $165.00 | $26.95 | $2,695.00 | - | $280.00 | $2,695.00 | ($625.00) | - | - | .ROKU220318P170 | $20.70 | ||
| 35 | 4 | 2 | Close | Put | ROKU | BTC | 1 | 100 | 3/18/2022 | P | 170 | $165.00 | $0.00 | - | - | $0.00 | - | $230.00 | ($855.00) | .ROKU220318P170 | $20.70 | |||||
| 36 | 5 | 1 | Open | Iron Condor | 2/2/2022 | 12:55 | FB | BTO | 1 | 100 | 3/18/2022 | P | 280 | $226.92 | $4.60 | ($460.00) | - | $360.00 | - | ($4,822.50) | - | - | .FB220318P280 | $52.83 | ||
| 37 | 5 | 1 | Close | Iron Condor | FB | STC | 1 | 100 | 3/18/2022 | P | 280 | $226.92 | $0.00 | - | - | - | - | - | - | .FB220318P280 | $52.83 | |||||
| 38 | 5 | 2 | Open | Iron Condor | 2/2/2022 | 12:55 | FB | STO | 1 | 100 | 3/18/2022 | P | 290 | $226.92 | $6.50 | $650.00 | - | $360.00 | - | $5,582.50 | - | - | .FB220318P290 | $62.33 | ||
| 39 | 5 | 2 | Close | Iron Condor | FB | BTC | 1 | 100 | 3/18/2022 | P | 290 | $226.92 | $0.00 | - | - | - | - | - | - | .FB220318P290 | $62.33 | |||||
| 40 | 5 | 3 | Open | Iron Condor | 2/2/2022 | 12:55 | FB | STO | 1 | 100 | 3/18/2022 | C | 360 | $226.92 | $5.40 | $540.00 | - | $360.00 | - | ($525.50) | - | - | .FB220318C360 | $0.15 | ||
| 41 | 5 | 3 | Close | Iron Condor | FB | BTC | 1 | 100 | 3/18/2022 | C | 360 | $226.92 | $0.00 | - | - | - | - | - | - | .FB220318C360 | $0.15 | |||||
| 42 | 5 | 4 | Open | Iron Condor | 2/2/2022 | 12:55 | FB | BTO | 1 | 100 | 3/18/2022 | C | 370 | $226.92 | $3.70 | ($370.00) | - | $360.00 | ($370.00) | $357.50 | - | - | .FB220318C370 | $0.13 | ||
| 43 | 5 | 4 | Close | Iron Condor | FB | STC | 1 | 100 | 3/18/2022 | C | 370 | $226.92 | $0.00 | - | - | $0.00 | - | $592.00 | $0.00 | .FB220318C370 | $0.13 | |||||
Rolling Trades | ||||||||||||||||||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| P2:P43 | P2 | =IF(OR($H2="BTO",$H2="BTC"),-1*($I2*$J2*$O2),$I2*$J2*$O2) |
| Q2:Q43 | Q2 | =IF($E3="","-",IF(AND($A3=$A2,$B3=$B2),SUMIFS($P$2:$P$314,$A$2:$A$314,$A2,$B$2:$B$314,$B2),"-")) |
| R2:R43 | R2 | =IF($E2="","-",SUMIFS($P$2:$P$314,$F$2:$F$314,$F2)) |
| T2:T43 | T2 | =IF($A3="","-",IF($E3="",IF(OR($H2="BTO",$H2="BTC"),(($O2-$X2)*$I2*$J2),-1*($O2-$X2)*$I2*$J2),"-")) |
| U2:U43 | U2 | =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:V43 | V2 | =IF($G3=$G2,"-",SUMIFS($Q$2:$Q$314,$A$2:$A$314,$A2)) |
| W2:W31,W36:W43 | W2 | =CONCATENATE("."&$G2&TEXT($K2,"YYMMDD")&$L2&$M2) |
| X2:X31,X36:X43 | X2 | =RTD("tos.rtd",,"MARK",$W2) |
| W32:W35 | W32 | =IF($L32="S","STOCK","."&$G32&TEXT($K32,"YYMMDD")&$L32&$M32) |
| X32:X35 | X32 | =IF($W32="-","-",RTD("tos.rtd",,"MARK",$W32)) |
| S4,S10:S11,S6 | S4 | =SUMIFS($P$2:$P$11,$F$2:$F$11,$F4) |
| S18 | S18 | =$P$12+$P$14+$P$16+$P$18 |
| S22 | S22 | =$P$13+$P$15+$P$22+$P$20 |
| S26 | S26 | =$P$17+$P$19+$P$24+$P$26 |
| S28,S42:S43,S34:S35,S31:S32 | S28 | =P28 |
| S30 | S30 | =P29+P30 |
| N2:N43 | N2 | =RTD("tos.rtd", , "MARK", $G2) |
