The Excel Price Feed Blog

We were recently contacted by a user who was trying to calculate the market high and low over an particular date period and more importantly the date that the high or low was reached.

Neither Excel Price Feed nor Excel have a single formula to calculate this, but we can use several Excel functions to achieve it.

For this example we will look at daily data, although the procedure and formulas used can be applied to any timeframe.

We start by using the Excel Price Feed “Historic Date Period” formula to retrieve the daily high/low/open/close over a specified date period for a specified resolution:

=EPF.Yahoo.HistoricDatePeriod(B1,"Daily",B2,B3,"DESC",1)

B1 is the stock ticker, B2 is the start date and B3 is the end date.

This formula outputs a table as shown below, the formula is in cell D1:

Finding the highest high over the period is easy, we simply use the MAX formula to find the maximum value of all the daily highs (column F):

=MAX(F:F)

To find the lowest low we use the MIN formula with column G:

=MIN(G:G)

The hard part is finding the date at which the highest high and lowest low was reached. We can achieve this by using a combination of Excel functions.

We will now walk through how to find the date of the highest high, which is in cell B5:

1. Using the MATCH function we can find the row where the highest high was achieved. The MATCH function searches a range, in this case the column (G:G) and finds the row where the highest high is, in this case the value in cell B5 (260.10).
2. We then take this row and use the ADDRESS function to combine it with the column where the date is located, column E (or the 5th column as this function requires a numeric column reference).
3. We now have an address for the location of the date, via a column and row number. To return the actual value at this address we must use the INDIRECT function, this will then provide the date value of “26 Dec 2024”

The full formula looks like this:

=INDIRECT(ADDRESS(MATCH(B5,G:G,0),5))

Below we can see the full formula in the formula bar at the top and the result is “26 Dec 2024”:

To find the date of the lowest low we just replace column G with column H and the low which is in cell B5.

We hope you find this useful and if you would like to try the spreadsheet for yourself you can download it from here.