STOCKHISTORY not working

STOCKHISTORY requires both a supported Excel version and a supported subscription, plus valid instruments that have historical data in Microsoft’s provider.

Key points to check on Mac:

1. Subscription and version

• STOCKHISTORY only works with:
  • Microsoft 365 Personal
  • Microsoft 365 Family
  • Microsoft 365 Business Standard
  • Microsoft 365 Business Premium
• It must be used in a version of Excel that supports dynamic arrays (Excel for Microsoft 365 on Mac, not older perpetual versions).

2. Function behavior

• STOCKHISTORY returns historical data for a financial instrument (e.g., stock, fund, currency pair) and spills results into multiple cells.
• The function does not apply any special formatting to the spill range; if the formula is deleted, the cells revert to General format.

3. Instrument limitations

• Some instruments that are available as Stocks data types do not have historical data exposed via STOCKHISTORY. This is explicitly true for many popular index funds (e.g., S&P 500) and can also apply to some other instruments.
• Historical FX rates (USD to PHP) must be available from the provider (LSEG Data & Analytics) for the specific symbol or pair being requested; if not, STOCKHISTORY will fail or return no data.

4. Date arguments

• Dates can be:
  • A literal date in quotes, e.g. "01-01-2020"
  • A formula such as TODAY()
  • A reference to a cell containing a date
• Returned dates may be earlier than the requested date when using monthly or weekly intervals (for example, requesting 31-Dec-2019 with monthly interval returns 1-Dec-2019 as the period start).

5. Properties argument

• When specifying which columns to return, the properties are numeric codes:
  • 0 = Date
  • 1 = Close
  • 2 = Open
  • 3 = High
  • 4 = Low
  • 5 = Volume
• The order of numbers controls the order of columns. For example, 0,2,1 returns Date, then Open, then Close.

If STOCKHISTORY is not working at all (e.g., giving a formula error or not recognized) on a Mac with Microsoft 365 Family:

• Confirm that Excel is the Microsoft 365 version (not Excel 2019/2021 perpetual).
• Confirm that the account with the Family subscription is signed in and activated in Excel.
• If Excel cannot resolve the formula, follow the general broken-formula guidance: edit the cell, let Excel highlight the error location, and correct the syntax or arguments.

If the function is recognized but returns no data or errors for USD→PHP:

• The specific FX pair or symbol being used may not have historical data exposed by the provider, even though other stock/FX data works.
• In that case, STOCKHISTORY cannot be used to retrieve that particular conversion history in Excel.

References:

• STOCKHISTORY function
• Get a stock quote
• About the Stocks financial data sources
• Excel data types: Stocks and geography
• How to avoid broken formulas in Excel