stock price period high formula

[Cory24]

[FONT=&quot]Hi all, first time poster here,[/FONT]
[FONT=&quot]
[/FONT]
[FONT=&quot]I am trying to build a spreadsheet where I can have a list of stocks I am tracking and notify me when a stock makes a new 20 day and 55 day high. I want to link yahoo finance data or google finance data.[/FONT]
[FONT=&quot]
[/FONT]
[FONT=&quot]I found a formula that others said this will work with 52 week highs. I though I could modify this to show new 20 day and 55 day highs. But when I tested this formula on some historical data, I keep getting the #N/A error. Below is the formula[/FONT]
[FONT=&quot]=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))[/FONT]
[FONT=&quot]
[/FONT]
[FONT=&quot]Honestly I'm not the greatest with excel and don't really understand this formula or how to make them.[/FONT]
[FONT=&quot]
[/FONT]
[FONT=&quot]Any help would be much appreciated. [/FONT]
[FONT=&quot]
[/FONT]
[FONT=&quot]Thanks in advance!!


[/FONT]

 

[Oscar Cronquist]

[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]

A​

[/th][th]

B​

[/th][th]

C​

[/th][th]

D​

[/th][th]

E​

[/th][th]

F​

[/th][th]

G​

[/th][th]

H​

[/th][th]

I​

[/th][th]

J​

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

1​

[/td][td]Date[/td][td]Open[/td][td]High[/td][td]Low[/td][td]Close*[/td][td]Adj Close**[/td][td]Volume[/td][td]Above 20[/td][td]Above 55[/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

2​

[/td][td]

30-Jan-18​

[/td][td]

26,198.45​

[/td][td]

26,256.99​

[/td][td]

26,028.42​

[/td][td]

26,076.89​

[/td][td]

26,076.89​

[/td][td]

448,330,000​

[/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

3​

[/td][td]

29-Jan-18​

[/td][td]

26,584.28​

[/td][td]

26,608.90​

[/td][td]

26,435.34​

[/td][td]

26,439.48​

[/td][td]

26,439.48​

[/td][td]

421,000,000​

[/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

4​

[/td][td]

26-Jan-18​

[/td][td]

26,466.74​

[/td][td]

26,616.71​

[/td][td]

26,425.35​

[/td][td]

26,616.71​

[/td][td]

26,616.71​

[/td][td]

477,780,000​

[/td][td]New high[/td][td]New high[/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

5​

[/td][td]

25-Jan-18​

[/td][td]

26,313.06​

[/td][td]

26,458.25​

[/td][td]

26,259.72​

[/td][td]

26,392.79​

[/td][td]

26,392.79​

[/td][td]

401,390,000​

[/td][td]New high[/td][td]New high[/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

6​

[/td][td]

24-Jan-18​

[/td][td]

26,282.07​

[/td][td]

26,392.80​

[/td][td]

26,106.94​

[/td][td]

26,252.12​

[/td][td]

26,252.12​

[/td][td]

518,790,000​

[/td][td]New high[/td][td]New high[/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

7​

[/td][td]

23-Jan-18​

[/td][td]

26,214.87​

[/td][td]

26,246.19​

[/td][td]

26,143.90​

[/td][td]

26,210.81​

[/td][td]

26,210.81​

[/td][td]

433,490,000​

[/td][td]New high[/td][td]New high[/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

8​

[/td][td]

22-Jan-18​

[/td][td]

26,025.32​

[/td][td]

26,215.23​

[/td][td]

25,974.65​

[/td][td]

26,214.60​

[/td][td]

26,214.60​

[/td][td]

431,200,000​

[/td][td]New high[/td][td]New high[/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

9​

[/td][td]

19-Jan-18​

[/td][td]

25,987.35​

[/td][td]

26,071.72​

[/td][td]

25,942.83​

[/td][td]

26,071.72​

[/td][td]

26,071.72​

[/td][td]

585,000,000​

[/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

10​

[/td][td]

18-Jan-18​

[/td][td]

26,149.55​

[/td][td]

26,153.42​

[/td][td]

25,947.32​

[/td][td]

26,017.81​

[/td][td]

26,017.81​

[/td][td]

492,780,000​

[/td][td]New high[/td][td]New high[/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

11​

[/td][td]

17-Jan-18​

[/td][td]

25,910.78​

[/td][td]

26,130.45​

[/td][td]

25,865.02​

[/td][td]

26,115.65​

[/td][td]

26,115.65​

[/td][td]

522,720,000​

[/td][td]New high[/td][td]New high[/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

12​

[/td][td]

16-Jan-18​

[/td][td]

25,987.62​

[/td][td]

26,086.12​

[/td][td]

25,702.99​

[/td][td]

25,792.86​

[/td][td]

25,792.86​

[/td][td]

606,520,000​

[/td][td]New high[/td][td]New high[/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

13​

[/td][td]

12-Jan-18​

[/td][td]

25,638.39​

[/td][td]

25,810.43​

[/td][td]

25,633.08​

[/td][td]

25,803.19​

[/td][td]

25,803.19​

[/td][td]

376,390,000​

[/td][td]New high[/td][td]New high[/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

14​

[/td][td]

11-Jan-18​

[/td][td]

25,398.60​

[/td][td]

25,575.42​

[/td][td]

25,396.19​

[/td][td]

25,574.73​

[/td][td]

25,574.73​

[/td][td]

346,830,000​

[/td][td]New high[/td][td]New high[/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

15​

[/td][td]

10-Jan-18​

[/td][td]

25,348.13​

[/td][td]

25,404.92​

[/td][td]

25,256.99​

[/td][td]

25,369.13​

[/td][td]

25,369.13​

[/td][td]

341,470,000​

[/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

16​

[/td][td]

9-Jan-18​

[/td][td]

25,312.05​

[/td][td]

25,439.78​

[/td][td]

25,308.41​

[/td][td]

25,385.80​

[/td][td]

25,385.80​

[/td][td]

333,490,000​

[/td][td]New high[/td][td]New high[/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

17​

[/td][td]

8-Jan-18​

[/td][td]

25,308.40​

[/td][td]

25,311.99​

[/td][td]

25,235.41​

[/td][td]

25,283.00​

[/td][td]

25,283.00​

[/td][td]

341,390,000​

[/td][td]New high[/td][td]New high[/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

18​

[/td][td]

5-Jan-18​

[/td][td]

25,114.92​

[/td][td]

25,299.79​

[/td][td]

25,112.01​

[/td][td]

25,295.87​

[/td][td]

25,295.87​

[/td][td]

358,020,000​

[/td][td]New high[/td][td]New high[/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

19​

[/td][td]

4-Jan-18​

[/td][td]

24,964.86​

[/td][td]

25,105.96​

[/td][td]

24,963.27​

[/td][td]

25,075.13​

[/td][td]

25,075.13​

[/td][td]

403,280,000​

[/td][td]New high[/td][td]New high[/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

20​

[/td][td]

3-Jan-18​

[/td][td]

24,850.45​

[/td][td]

24,941.92​

[/td][td]

24,825.55​

[/td][td]

24,922.68​

[/td][td]

24,922.68​

[/td][td]

456,790,000​

[/td][td]New high[/td][td]New high[/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

21​

[/td][td]

2-Jan-18​

[/td][td]

24,809.35​

[/td][td]

24,864.19​

[/td][td]

24,741.70​

[/td][td]

24,824.01​

[/td][td]

24,824.01​

[/td][td]

341,130,000​

[/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

22​

[/td][td]

29-Dec-17​

[/td][td]

24,849.63​

[/td][td]

24,871.66​

[/td][td]

24,719.22​

[/td][td]

24,719.22​

[/td][td]

24,719.22​

[/td][td]

270,760,000​

[/td][td][/td][td][/td][td][/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Sheet42[/td][/tr][/table]

Formula in cell H2:
=IF(C2>MAX(C3:C22),"New high","")

Formula in cell I2:
=IF(C2>MAX(C3:C57),"New high","")

Copy cells and paste to cells below as far as needed.