MarketXLS

Ask Historical

Returns the ask (offer) price for a symbol on a specific historical date. This function is useful for backtesting and historical analysis.

Parameters

Parameter Type Required Description
Symbol string Yes Stock or option symbol
OnDate date Yes Historical date

Date Formats

  • Excel DATE function: DATE(2024,1,15)
  • String format: "2024-01-15"

Notes

  • For options, use the OCC option symbol or OptionSymbol() function
  • Data availability depends on historical data coverage

Syntax

=mxls.Ask_Historical(Symbol, OnDate)
Excel Online (Mac & Windows)

Examples

=mxls.Ask_Historical("AAPL", DATE(2024,1,15))
Apple ask on Jan 15, 2024
=mxls.Ask_Historical("MSFT", "2024-03-01")
Microsoft ask on Mar 1, 2024
=mxls.Ask_Historical(mxls.OptionSymbol("AAPL",DATE(2024,3,15),"Call",170), DATE(2024,1,10))
Option ask
=mxls.Ask_Historical(A1, B1)
Symbol and date from cells

When to Use

  • Backtesting trading strategies
  • Historical spread analysis
  • Options historical analysis
  • Transaction cost analysis
  • Price slippage research

When NOT to Use

Scenario Use Instead
Current ask price Ask()
Historical bid price Bid_Historical()
Historical mid price Mid_Historical()
Historical close price Close_Historical()

Common Issues & FAQ

What date format should I use?

Either Excel DATE(year,month,day) or string "YYYY-MM-DD".

Why am I getting NA?

Check that the date is a valid trading day and within data coverage period.

Does this work for options?

Yes, use the OCC option symbol or generate it with OptionSymbol() function.

Excel Templates Using Ask Historical

These ready-made MarketXLS templates call Ask_Historical() in their worksheet formulas. Open one to see the function working inside a complete model.

Related Formulas

More MarketXLS Price History formulas you can use in the same worksheet:

See Ask_Historical used in a complete workbook: SPX Options Historical Data: Complete Guide to In-Depth Analysis and Trends

Get Access to 1 Billion Usable Market data points IN YOUR EXCEL SHEETS WITH EASY TO USE EXCEL FUNCTIONS

Get started today
MarketXLS Excel Add-in Tutorial - How to Use Ask Historical and Other Financial Formulas
▶
How does MarketXLS work?

Related Functions