Stock price 52 week highs and lows

Hi,

I am trying to do some back testing of stock prices looking at 52 week highs and lows.

I have the standard Yahoo stock data so with date in column A and close in column G.

I would like to calculate the preceding 52 week high in column H and the 52 week low in column I.

I found a formula that is almost there for the high:
=MAX(INDIRECT("G"&MATCH(A2-365,A:A,0)&":"&"G"&ROW()),0)

But it doesn’t quite work as if A?-365 isn’t a trading day (e.g. weekend) then you get #N/A. I would be happy to use the previous 260 data values (5 days * 52 weeks) as a proxy to calculate the 52 week range.

I have to say I don’t understand how the formula works and swapping MAX to MIN didn’t work to find the 52 week lows!

Any help would be much appreciated. Many thanks in advance…

Post a sample file.

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

Hi,

Sample data set attached. Column H has the 52 Week high formula that almost works - first valid value in H255.

You can see the #N/A issue - first example in H259.

Thanks

Try ...

=IFERROR(MAX(INDIRECT("G"&MATCH(A2-365,A:A,0)&":"&"G"&ROW()),0),MAX(INDIRECT("G"&MATCH(A2-365,A:A,1)+1&":"&"G"&ROW()),0))

Thanks Phuocam - that has solved the problem.

Is there a way to adapt the formula so that it will calculate the 52 week lows?

Thanks

Change MAX to MIN

Thanks - I had assumed swapping MAX to MIN would work but that returns 0 for everything rather than the 52 week low

Try this:

=IFERROR(MIN(INDIRECT("G"&MATCH(A2-365,A:A,0)&":"&"G"&ROW())),MIN(INDIRECT("G"&MATCH(A2-365,A:A,1)+1&":"&"G"&ROW())))

Many thanks - I need to get myself on an advanced Excel course!