STOCKHISTORY function not working

Question

Hi,

I’m having trouble with the Excel STOCKHISTORY function.

I’m trying to use STOCKHISTORY to retrieve historical stock prices in two ways:

1. Pull a time series of historical prices between two dates.
2. Pull the stock price for an individual date, where the date is linked to a date stored in a cell.

Neither seems to work correctly. In particular, when I use a cell reference for the date rather than entering the date directly in the formula, I don't get the expected result.

For example, I would like to be able to use something along the lines of:

=STOCKHISTORY("AAPL",A1,B1)

where A1 and B1 contain the start and end dates, and also retrieve the value corresponding to a specific date in a cell.

Is this a general issue with STOCKHISTORY, or is it specific to my workbook or Excel installation? Is there a recommended way to make it work with dates referenced from cells?

Thanks!

Answer 1

Dear @Arne,

Based on your description, STOCKHISTORY should support cell references for both the start_date and end_date arguments, so a formula such as: =STOCKHISTORY("AAPL",A1,B1) is expected to work, provided that A1 and B1 contain valid Excel date values and not text strings. According to the function documentation, date arguments can be entered directly, generated by a formula such as TODAY(), or referenced from a cell containing a date.

If the formula works when dates are typed directly into STOCKHISTORY but fails when referencing cells, I recommend checking the following:

1/Verify that the cells contain actual Excel dates:

=ISNUMBER(A1) 

A result of TRUE indicates that Excel recognizes the value as a date serial number.

2/Temporarily format the date cells as General. Valid Excel dates should display as serial numbers rather than date text.

3/ Test with a simple example:

=STOCKHISTORY("AAPL",DATE(2024,1,1),DATE(2024,1,31))

and compare the result to:

=STOCKHISTORY("AAPL",A1,B1)

If applicable, specify the exchange explicitly:

=STOCKHISTORY("XNAS:AAPL",A1,B1)

For retrieving the closing price on a specific date stored in a cell, you can use:

=INDEX(STOCKHISTORY("AAPL",C1,C1,0,0),1,2)

where C1 contains the target date.

For reference STOCKHISTORY function

I hope information above meet your expectations, feel free to reach out if you have any other questions.

---

If the answer is helpful, please click "Yes". If you have extra questions about this answer, please click "Comment".   

Note: Please follow the steps in the forum documentation to enable e-mail notifications if you want to receive the related email notification for this thread.