Next Expiry Date
Returns the option expiration date at a specified offset from the nearest expiration. This function is essential for dynamically building option chains and option symbols.
Parameters
| Parameter | Required | Description |
|---|---|---|
| Symbol | Yes | Stock ticker symbol (e.g., AAPL, MSFT, SPY) |
| NumberOfExpiration | Yes | Offset from nearest expiry (1=next, 2=second, etc.) |
Understanding NumberOfExpiration
| Value | Description |
|---|---|
| 1 | Next upcoming expiration |
| 2 | Second upcoming expiration |
| 3 | Third upcoming expiration |
| 4 | Fourth upcoming expiration (often monthly) |
Expiration Types
| Type | Typical Schedule |
|---|---|
| Weekly | Every Friday (some stocks) |
| Monthly | Third Friday of month |
| Quarterly | End of quarter |
| LEAPS | January, 1-2 years out |
Common Use Cases
This function is typically used with OptionSymbol() and StrikeNext() to build dynamic option references:
=OptionSymbol("AAPL", ExpirationNext("AAPL",1), "Call", StrikeNext("AAPL",1))Examples
=ExpirationNext("AAPL", 1)=ExpirationNext("AAPL", 2)=ExpirationNext("MSFT", 4)=ExpirationNext("SPY", 1)=OptionSymbol("AAPL", ExpirationNext("AAPL",1), "Call", StrikeNext("AAPL",1))=TEXT(ExpirationNext("AAPL",1), "MMM DD, YYYY")=ExpirationNext("AAPL",1) - TODAY()=ExpirationNext(A1, B1)When to Use
- Find the next available expiration date
- Build dynamic option symbols
- Create option chains programmatically
- Calculate days to expiration
- Find specific expiration cycles (weekly, monthly)
When NOT to Use
| Scenario | Use Instead |
|---|---|
| Need all expiration dates | Expirations() |
| Need strike prices | StrikeNext() |
| Need option prices | Option_Last_Price() |
| Need full option chain | qm_option_chains() |
Common Issues & FAQ
Q: What does numberOfExpiration=1 return? A: It returns the next available expiration date after today.
Q: How do I find monthly expirations vs weekly? A: Use higher numbers (3, 4, 5+) to get monthly expirations. Weekly options typically have shorter-dated expirations.
Q: Why doesn't my stock have weekly expirations? A: Not all stocks have weekly options. Major stocks and ETFs (AAPL, MSFT, SPY) typically have weeklies.
Q: How do I build a complete option symbol?
A: Combine functions: =OptionSymbol("AAPL", ExpirationNext("AAPL",1), "Call", StrikeNext("AAPL",1))
Get Access to 1 Billion Usable Market data points IN YOUR EXCEL SHEETS WITH EASY TO USE EXCEL FUNCTIONS
Get started today