Historical Volume On A Day In Past Formula in Excel
Understanding Historical Volume On A Day In Past
The Historical Volume On A Day In Past formula in Excel, powered by MarketXLS, helps you quickly retrieve the number of shares (volume) traded for a given symbol on a specified date in the past. It is especially useful for:
- Backtesting trading strategies.
- Historical analysis of stock or asset performance.
- Research and reporting on market trends.
? Pro Tip: Use this formula to compare historical volumes on different dates to identify long-term trends or unusual trading activity.
Syntax and Parameters
=Volume_Historical(Symbol, OnDate)| Parameter | Description | Required | Example |
|---|---|---|---|
| Symbol | The ticker symbol of the asset whose volume you want to retrieve. | Yes | "MSFT", "^SPX", "@MSFT 110122C00020000", "BTCUSD:DEFAULT" |
| OnDate | The date (past) for which you want the volume. You can reference a cell, provide a string, or use a date function. | Yes | "2024-03-15", A1, TEXT(A1,"yyyy-mm-dd") |
• Return Value: Returns a numeric value representing the trading volume on that date. If an invalid ticker or date is provided, or if your MarketXLS license is not valid, it returns "NA".
?? Note: This formula queries volume data from the MarketXLS database. If the data is unavailable, or the date falls on a non-trading day (weekends/holidays),
"NA"may be returned.
Examples and Usage
-
Retrieve volume by referencing a symbol and a date cell:
=Volume_Historical(A2, A3)Where A2 contains "MSFT" and A3 contains a valid date (e.g., "2024-03-15").
-
Retrieve volume with a direct symbol and direct date:
=Volume_Historical("MSFT", "2023-09-03") -
Retrieve volume using Excel date functions:
=Volume_Historical("MSFT", TEXT(B2,"yyyy-mm-dd"))Where B2 is an Excel date that converts to the required format.
-
Retrieve volume for an index:
=Volume_Historical("^SPX", "2023-09-03") -
Retrieve volume for an option symbol:
=Volume_Historical("@MSFT 110122C00020000", "2023-09-03")
Common Questions
1. What happens if the date is not a trading day?
If the specified date is a weekend or holiday, the formula attempts to find the next available trading day’s data or returns "NA" if no suitable date is found.
2. Can I use this formula for cryptocurrencies?
Yes. For crypto symbols, use "BTCUSD:DEFAULT" or similar formatting. The function returns "NA" if the symbol type is unsupported or data is unavailable.
3. Are there any performance considerations?
When you use this formula multiple times, each call fetches data from MarketXLS servers. For larger spreadsheets, consider caching results or grouping calls to avoid performance bottlenecks.
4. Why am I getting “NA” even with a valid symbol and date?
Possible reasons include:
- Invalid or expired MarketXLS license.
- Issues with the API returning data for a particular date.
- Non-trading day or unavailable historical data.
In such cases, verify the symbol/date, check license validity, or try adjusting to the nearest available trading date.
? Pro Tip: If you frequently encounter “NA,” verify your MarketXLS subscription status or your data feed permission settings.
Get Access to 1 Billion Usable Market data points IN YOUR EXCEL SHEETS WITH EASY TO USE EXCEL FUNCTIONS
Get started today