Retrieve the Highest Historical Stock Data in Excel with custom_dates_high

The custom_dates_high function in MarketXLS is designed to help you quickly retrieve the highest (maximum) value for a specified data field—such as close price, volume, open, high, low, etc.—within a given date range. This is incredibly useful when analyzing the peaks or highest records of a stock’s performance over a custom span of time.

By specifying a ticker symbol, the start date, the end date, and your desired data field, you can seamlessly pull and evaluate the maximum price or volume right inside Excel. This ultimately speeds up your trading or investment analysis, offering quick insights and reducing the need for manual calculations.

Why Use This Function?

• Easily track the peak price or volume for a stock in a specified date range.
• Quickly compare maximum values of different symbols to identify trends or anomalies.
• Save significant time by automating data retrieval, which otherwise can be laborious.
• Integrate with your Excel workflow—no specialized or external tools required once MarketXLS is installed.
• Perfect for creating dynamic reports or dashboards where historical highs are crucial (e.g., the highest closing price for AAPL in the last year).

How to Use in Excel

Use this custom function directly in your Excel cells after installing the MarketXLS Add-in.

=custom_dates_high(Symbol, StartDate, EndDate, [DataField])

• Symbol (text): The stock ticker or symbol you’re interested in (e.g., "AAPL", "MSFT").
• StartDate (date): The beginning of your query period (Excel date or text date like "1/1/2023").
• EndDate (date): The ending of your query period (Excel date or text date like "6/30/2023").
• DataField (optional text): The historical field to evaluate. Defaults to "close" if omitted (other valid fields include "adj_close", "volume", "open", "high", "low", "dividend", "split", or "returns").

Behind the scenes, custom_dates_high calls the MarketXLS API to retrieve historical data. It automatically calculates the maximum value of the chosen field within the specified time range. If the symbol is invalid, your license is not valid, or the data is otherwise unavailable, it safely returns "NA".

Parameters Explained

Example Usage

Basic Examples

1. Retrieve highest closing price of AAPL between January 1, 2023 and June 30, 2023:
   =custom_dates_high("AAPL", "1/1/2023", "6/30/2023")
   This will return the maximum closing price for Apple in that period.

2. Get the highest trading volume for Microsoft between two dates:
   =custom_dates_high("MSFT", "2/1/2023", "3/1/2023", "volume")
   This will fetch the peak trading volume during February 2023.

3. Check the highest "low" value for Tesla in Q1 of 2023:
   =custom_dates_high("TSLA", "1/1/2023", "3/31/2023", "low")
   This returns the maximum “low” value (which may sound ironic, but it can help you track how the “low” performed over that range to see its absolute peak).

Advanced Scenarios

• Combine custom_dates_high with other Excel functions for deeper insights. For instance, you can compare the highest daily volume for two different stocks by placing results side by side and using standard Excel comparisons like =IF(...).

• Use dynamic date references (e.g., referencing cells containing user-defined start and end dates). This is especially helpful when building interactive dashboards that automatically update the highest recorded values as you change the date range.

• Integrate with trading or rebalancing strategies. For instance, you could set up a conditional rule to check if the highest recorded price in the last 30 days is above a certain threshold, then highlight a cell or trigger another formula.

Common Questions and Troubleshooting

• “Why am I getting NA?”

• The symbol might be invalid.
• The date range might not contain any valid data.
• Your MarketXLS license may not be active or valid.

• “How do I ensure I’m getting the correct dates?”

• Make sure StartDate <= EndDate.
• Use recognized date formats in Excel (e.g., "1/1/2023" or DATE(2023, 1, 1)).

• “Can I get other aggregations like sum or average with this function?”

• No. This particular function specifically retrieves the maximum (“high”) value for the specified field. For other aggregations, you’d typically use a different MarketXLS function.

• “What if I need the maximum adjusted close price?”

• Simply pass "adj_close" as your DataField to get the highest adjusted close in the specified period.

Remember, the custom_dates_high function saves you time by automating the manual process of sifting through historical data. Whether you’re tracking record highs in price or volume, it’s a powerful piece of your MarketXLS toolkit for analyzing US stock markets right in Excel.