Calculate Averages over Custom Date Ranges with custom_dates_average
The custom_dates_average function from MarketXLS allows you to pull historical values for a selected data field (e.g., close price) between two specified dates and calculate the average of those values directly in Excel. This can be especially helpful for investors and analysts looking to compare average price, volume, or other historical metrics over a certain period without resorting to manual data exports or multiple formulas.
Why Use This Function?
- Ideal for analyzing average daily price (or volume) across custom date windows.
- Quickly compare different date ranges (e.g., Q1 vs. Q2 averages).
- Simplifies portfolio decisions by showing average historical performance.
- Helps identify seasonal trends or patterns in a stock’s historical data.
- Eliminates the need for manual data imports or multi-step calculations.
How to Use in Excel
=custom_dates_average(Symbol, StartDate, EndDate, [DataField])• Enter the function into any cell in Excel.
• Provide the ticker symbol, the start date, and the end date.
• Optionally, specify which DataField you’d like (defaults to "close" if omitted).
- In Excel, select a cell where you want the average result.
- Type the formula, for example:
=custom_dates_average("AAPL","1/1/2023","6/1/2023","volume") - Press Enter to see the calculated average over that period.
MarketXLS will reach out to its data engine, collect the historical data for those dates, and compute the average of the specified field.
Parameters Explained
| Parameter | Description | Example Values | Notes |
|---|---|---|---|
| Symbol | The stock ticker or symbol you want data for. | "AAPL", "MSFT", "TSLA" | Must be a valid ticker. If invalid, returns "NA". |
| StartDate | The beginning date of the range for which you want an average. | "1/1/2022", "3/15/2023" | Must be in a valid date format recognized by Excel. |
| EndDate | The ending date of the range for which you want an average. | "12/31/2022", "6/1/2023" | Must be in a valid date format recognized by Excel. |
| [DataField] | The specific data field to average. Possible values include "close", "adj_close", "volume", "open", "high", "low", "dividend", or "split". | "close" (default), "volume", "open" etc. | If omitted or unrecognized, defaults to "close". If the field isn’t built into MarketXLS or unavailable, returns "NA". |
Example Usage
Basic Examples
-
Average Closing Price for AAPL from January to June 2023:
=custom_dates_average("AAPL","01/01/2023","06/01/2023","close")- This returns the average closing price for Apple between January 1 and June 1, 2023.
-
Average Volume for MSFT from March to May 2022:
=custom_dates_average("MSFT","03/01/2022","05/31/2022","volume")- This returns the average daily trading volume for Microsoft between March 1 and May 31, 2022.
Advanced Scenarios
-
Comparing Two Time Periods:
- Place the function in different columns to compare average prices over back-to-back quarters. For instance:
• =custom_dates_average("TSLA","01/01/2022","03/31/2022")
• =custom_dates_average("TSLA","04/01/2022","06/30/2022") - Helps spot any changes in average price from one quarter to the next.
- Place the function in different columns to compare average prices over back-to-back quarters. For instance:
-
Checking Dividend Averages:
- Some dividend-focused investors might average dividend payouts (if available in the historical data). For example:
=custom_dates_average("T","01/01/2019","12/31/2019","dividend") - Useful if you want to understand typical dividend payouts over a certain year.
- Some dividend-focused investors might average dividend payouts (if available in the historical data). For example:
-
Integrating with Other Excel Functions:
- Combine custom_dates_average with other formulas (like IF, ROUND, or cell references). For example:
IF(custom_dates_average(A1,B1,C1,"volume")>10000000, "High Volume", "Low Volume") - Dynamically categorize or filter your results based on average volume thresholds.
- Combine custom_dates_average with other formulas (like IF, ROUND, or cell references). For example:
Common Questions and Troubleshooting
• What if I get "NA"?
- Check if your symbol is typed correctly. Also ensure the date format is valid and that you have a valid MarketXLS license. If MarketXLS finds no data in that date range or for that symbol, it will return "NA".
• What if the function doesn’t update immediately?
- MarketXLS may temporarily return "Refreshing" if data is still being fetched. Wait a moment or force a worksheet refresh.
• Can I use non-US tickers or symbols?
- MarketXLS does support many international symbols, but availability may vary depending on your subscription. If the symbol or data is unsupported, expect "NA".
• What if the date range has only a single data point?
- You’ll still get a valid average as long as there’s at least one price record for that symbol and date. If no data is found, "NA" is returned.
With its focus on flexibility and simplicity, custom_dates_average in MarketXLS gives you a quick way to analyze historical trends and averages for your chosen tickers, making it indispensable for your investment workflows.
Get Access to 1 Billion Usable Market data points IN YOUR EXCEL SHEETS WITH EASY TO USE EXCEL FUNCTIONS
Get started today