Home MarketXLS
Dashboard MarketXLS
Screener MarketXLS
Options Profit Calculator MarketXLS
Stock Ranks MarketXLS
Spreadsheet Builder MarketXLS
Documentation MarketXLS
Logout MarketXLS

Retrieve Historical Ask Size for Deeper Market Insight

The Ask_Size_Historical function in MarketXLS lets you retrieve the historical ask sizes of a stock or other supported instruments (e.g., options, Indian equities) for a specific date. By tracking how many shares/contracts were available at the ask price, you gain insights into the liquidity at a particular point in time and can employ more informed trading or investment strategies.

Why Use This Function?

  • See Past Liquidity: Quickly check the “ask size” on a particular past trading day, shedding light on how much supply was in the market.
  • Historical Research: Compare the ask size trends over various dates to judge liquidity or gauge market depth.
  • Strategy Validation: Validate intraday or end-of-day strategies by analyzing how ask sizes fluctuate before and after corporate actions or market events.
  • Wide Coverage: MarketXLS supports multiple equity markets and certain international/Indian symbols; if recognized, the function fetches relevant data.

How to Use in Excel

Use the function directly in any Excel cell once the MarketXLS add-in is installed. Type it into a cell, providing the required symbol and date parameters:

=Ask_Size_Historical("MSFT", "12/15/2022")

After entering your symbol and date, press Enter to retrieve the ask size for that trading day. If data isn’t found, the function returns "NA."

• Make sure your MarketXLS license is valid.
• Ensure the symbol is valid (e.g., “MSFT” for Microsoft).
• Provide the date in a recognized date format. MarketXLS automatically handles day/month padding.

Parameters Explained

Parameter Description Example Values Notes
Symbol A valid stock or supported instrument symbol "MSFT", "AAPL", "INFY:IN" Must be recognized by MarketXLS; otherwise returns “NA”.
OnDate The historical date you want ask size for "12/15/2022", "2022-12-15" If the date is incorrect or a non-trading day, might return "NA".

Example Usage

Below are practical examples of retrieving historical ask sizes for different market scenarios. When the function finds valid data, it returns the ask size as a numeric string. Otherwise, it often returns “NA.”

Basic Examples

  1. Simple US Stock Example
    • In cell A1, enter:
    =Ask_Size_Historical("MSFT", "12/15/2022")
    • This fetches the ask size for Microsoft on December 15, 2022.

  2. Checking a Recent Date
    • In cell A2, enter:
    =Ask_Size_Historical("AAPL", "3/1/2023")
    • Returns Apple’s ask size on March 1, 2023 if available.

  3. Indian Equity Example
    • In cell A3, enter:
    =Ask_Size_Historical("INFY:IN", "2/10/2023")
    • Retrieves the ask size for Infosys on February 10, 2023 (assuming the symbol is recognized).

Advanced Scenarios

  1. Options Symbol Query
    • If you use an options symbol recognized by MarketXLS, you can attempt:
    =Ask_Size_Historical("@SPX 230317C04000000", "3/15/2023")
    • It may return “NA” if the data is not available or if the date falls after expiration.

  2. Weekend or Holiday Data
    • If the requested date falls on a weekend or market holiday, the function often returns “NA.”
    • Some markets may shift to the nearest date if special parameters exist, but typically, “NA” is the expected output for days with no trading.

  3. Handling Refreshing Data States
    • If MarketXLS is actively refreshing data, the function might return “Refreshing.” Check back once the refresh completes.

  4. Comparing Multiple Dates
    • Use a range of dates in Excel (e.g., in cells B2 to B10) to set up:
    =Ask_Size_Historical($A$1, B2)
    • Drag down to retrieve ask sizes for multiple days at once.

Common Questions and Troubleshooting

  1. Why do I get “NA” for some symbols on a valid date?

    • The symbol might not be recognized, or data for that date is unavailable or was never recorded. Double-check spelling and ensure the symbol is within MarketXLS coverage.
  2. Why does it sometimes say “Refreshing”?

    • MarketXLS might be in the middle of refreshing its data cache. Wait briefly and re-check the cell.
  3. How do I ensure my date is understood correctly?

    • Use a standard date format (MM/DD/YYYY or YYYY-MM-DD). MarketXLS automatically pads single-digit months and days internally.
  4. Can I apply this function to non-US stocks or Indian equities?

    • Yes, if the symbol format is recognized by MarketXLS (e.g., “TCS:IN”), the function attempts to retrieve data. Otherwise, it returns “NA.”
  5. What about historical data on illiquid or lightly traded securities?

    • For such securities, the ask size might not be reported or may be zero, leading to unexpected or “NA” results if data is missing.

With Ask_Size_Historical, you gain powerful retrospective insights into market liquidity. Combine it with your pricing and volume analyses to build a robust trading or investment strategy—right in Excel with MarketXLS!

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 Historical Ask  (Options) and Other Financial Formulas
How does MarketXLS work?