Historical Bid Price
Returns the bid price for an option contract or stock on a specific historical date. For options, this represents the highest price a market maker was willing to pay at close.
Parameters
| Parameter | Required | Description |
|---|---|---|
| Symbol | Yes | Option symbol (OCC format) or stock ticker |
| OnDate | Yes | Historical date (DATE function or string) |
Input Requirements
For options, use OptionSymbol() to generate the option symbol:
| Parameter | Source | Example |
|---|---|---|
| Symbol | OptionSymbol() output |
OptionSymbol("AAPL",DATE(2026,3,15),"Call",170) |
Option Symbol Format (OCC)
| Component | Description | Example |
|---|---|---|
| Root | Underlying symbol | AAPL |
| Expiry | YYMMDD format | 240315 |
| Type | C=Call, P=Put | C |
| Strike | Price x 1000, 8 digits | 00170000 |
Full example: AAPL240315C00170000 (AAPL Mar 15 2024 $170 Call)
Notes
- For stocks, use standard ticker symbol (AAPL, MSFT)
- For options, use OCC format or
OptionSymbol()function - Returns end-of-day bid quote
Examples
Using OptionSymbol() for options - RECOMMENDED
=Bid_Historical(OptionSymbol("AAPL",DATE(2026,3,15),"Call",170),DATE(2025,12,15))Using raw OCC symbol
=Bid_Historical("AAPL240315C00170000", DATE(2025,12,15))For stock historical bid
=Bid_Historical("AAPL", DATE(2025,12,15))Using cell references
=Bid_Historical(A1, B1)With dynamic expiration
=Bid_Historical(OptionSymbol("AAPL",ExpirationNext("AAPL",1),"Call",StrikeNext("AAPL",1)),DATE(2025,12,15))When to Use
- Retrieve historical option bid prices
- Backtest option strategies
- Analyze bid-ask spreads over time
- Study option pricing on specific dates
- Build historical options analysis
When NOT to Use
| Scenario | Use Instead |
|---|---|
| Need current bid | Bid() |
| Need historical ask | Ask_Historical() |
| Need historical mid | Mid_Historical() |
| Need historical close | Close_Historical() |
Common Issues & FAQ
Q: How do I get the option symbol?
A: Use OptionSymbol() function: =OptionSymbol("AAPL",DATE(2026,3,15),"Call",170) returns the OCC symbol.
Q: Why am I getting NA or error? A: Check that:
- The date is a valid trading day
- The option contract existed on that date
- The symbol format is correct (OCC for options)
Q: Can I use this for stocks too? A: Yes, just use the stock ticker symbol instead of an option symbol.
Get Access to 1 Billion Usable Market data points IN YOUR EXCEL SHEETS WITH EASY TO USE EXCEL FUNCTIONS
Get started today
▶
How does MarketXLS work?