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
-
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. -
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. -
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
-
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. -
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. -
Handling Refreshing Data States
• If MarketXLS is actively refreshing data, the function might return “Refreshing.” Check back once the refresh completes. -
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
-
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.
-
Why does it sometimes say “Refreshing”?
- MarketXLS might be in the middle of refreshing its data cache. Wait briefly and re-check the cell.
-
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.
-
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.”
-
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!