Dennisss11
New Member
- Joined
- Nov 25, 2022
- Messages
- 9
- Office Version
- 365
- Platform
- MacOS
Hi everyone!
Im starting to go crazy. I'm feeling quite handy with excel and its lovely IF formulas, but I bumped into a problem I can't seem to solve!
I am building a backtesting model for our option trading strategy, where we have a couple of constants:
- Friday at the end of the day we open a position, which is always "Day 0". This position always has an expiry date the next Friday.
- The next Friday is always the closing day. This closing day does not require a specific input, but the days in between do!
- On the opening of a position we pick a specific Strike Price. This Strike Price should always stay the same right until the next Friday.
Problem 1 (days to expiration)
- For option trading the days to expiration is important. So lets say I open a position on Friday (Day 0) and we have a regular week with Monday till Friday, my output should look like my first screenshot.
- Now with regular weeks this is not a problem. But we also have weeks in which the Friday is a free day (Christmas for example). In that case the days to expiration on the first Friday is 14 days away. Then, everything changes since for example the next Monday should have 11 days till expiration, the Tuesday 10 days, etc. My second screenshot shows a week like this.
Problem 2 (Strike Price should remain the same Friday to Friday)
- When the strike price is for example 1000 on Friday, it should stay 1000 right up until the next Friday.
If anyone could help me, that would be highly appreciated!!
Kind regards,
Dennis
Im starting to go crazy. I'm feeling quite handy with excel and its lovely IF formulas, but I bumped into a problem I can't seem to solve!
I am building a backtesting model for our option trading strategy, where we have a couple of constants:
- Friday at the end of the day we open a position, which is always "Day 0". This position always has an expiry date the next Friday.
- The next Friday is always the closing day. This closing day does not require a specific input, but the days in between do!
- On the opening of a position we pick a specific Strike Price. This Strike Price should always stay the same right until the next Friday.
Problem 1 (days to expiration)
- For option trading the days to expiration is important. So lets say I open a position on Friday (Day 0) and we have a regular week with Monday till Friday, my output should look like my first screenshot.
- Now with regular weeks this is not a problem. But we also have weeks in which the Friday is a free day (Christmas for example). In that case the days to expiration on the first Friday is 14 days away. Then, everything changes since for example the next Monday should have 11 days till expiration, the Tuesday 10 days, etc. My second screenshot shows a week like this.
Problem 2 (Strike Price should remain the same Friday to Friday)
- When the strike price is for example 1000 on Friday, it should stay 1000 right up until the next Friday.
If anyone could help me, that would be highly appreciated!!
Kind regards,
Dennis
