STOCKHISTORY function not updating

[marshak]

Hi all,

I am new to STOCKHISTORY and need some guidance. For some reason, column M is not updating correctly (for example, there is no Daily Closing Price for 5/2/2022 and when there is one for 5/3/2022 I will need that to update also). Please help!!

Please let me know what is wrong with the current formula, and please help me correct it.

Thank you so much in advance!!

EDITED_2_034664-01__130090421v1_Apollo Partner - HSR Compliance Template.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	APOLLO GLOBAL MANAGEMENT, INC. (XNYS:APO)
2	HSR Equity Tracker			5/3/2022
3
4
5
6			Market Data
7							CIQ Ticker	APOLLO GLOBAL MANAGEMENT, INC. (XNYS:APO)
8			Ticker	APO
9
10			$51.95	Current Price								HSR	Daily Closing Price
11			#REF!	Shares Owned							0	5/3/2022	–
12			#REF!	Position Market Value							1	5/2/2022	–
13											2	5/1/2022	–
14			#REF!	Spend net of sales							3	4/30/2022	–
15											4	4/29/2022	$ 49.76
16			#REF!	Gross Entry Price							5	4/28/2022	$ 52.31
17											6	4/27/2022	$ 50.92
18			#REF!								7	4/26/2022	$ 51.15
19											8	4/25/2022	$ 54.39
20			Watch Levels								9	4/24/2022	–
21											10	4/23/2022	–
22			$101.0	HSR Threshold							11	4/22/2022	$ 53.93
23			$49.76	Low Price Over 45 Days							12	4/21/2022	$ 56.16
24			#REF!	Pos. value @ low price							13	4/20/2022	$ 59.16
25			#REF!	Remaining Availability							14	4/19/2022	$ 58.59
26			$51.95	Current							15	4/18/2022	$ 56.76
27			#REF!	Additional Shares							16	4/17/2022	–
28											17	4/16/2022	–
29			10.0%	16 Filing Threshold							18	4/15/2022	–
30			#REF!	Last Filing Shares							19	4/14/2022	$ 57.49
31			#REF!	Total Shares							20	4/13/2022	$ 57.75
32			#REF!	Current Owned							21	4/12/2022	$ 55.51
33			#REF!	Remaining Shares							22	4/11/2022	$ 56.87
34			$51.95	Last Price							23	4/10/2022	–
35			#REF!	Remaining Spend							24	4/9/2022	–
36											25	4/8/2022	$ 57.56
37											26	4/7/2022	$ 57.67
38			Target Levels								27	4/6/2022	$ 59.03
39											28	4/5/2022	$ 61.13
40			9.95%	Target Position							29	4/4/2022	$ 63.36
41			#REF!	Last Filing Shares							30	4/3/2022	–
42			#REF!	Total Shares							31	4/2/2022	–
43			#REF!	Current Owned							32	4/1/2022	$ 62.33
44			#REF!	Remaining Shares							33	3/31/2022	$ 61.99
45			$51.95	Target Price							34	3/30/2022	$ 63.04
46			#REF!	Remaining Spend							35	3/29/2022	$ 65.71
47											36	3/28/2022	$ 63.21
48			#REF!	Pro Forma Cost Basis							37	3/27/2022	–
49			#REF!	Per Share Cost Basis							38	3/26/2022	–
50											39	3/25/2022	$ 62.50
51											40	3/24/2022	$ 63.89
52											41	3/23/2022	$ 62.78
53											42	3/22/2022	$ 64.68
54			Target Levels								43	3/21/2022	$ 62.50
55											44	3/20/2022	–
56			9.99%	Target Position							45	3/19/2022	–
john doe

Cell Formulas
Range	Formula
D2	D2	=TODAY()
L11:L56	L11	=D2-K11:K56
C10	C10	=H7.PRICE
C11,C14	C11	=SUM(#REF!)
C12	C12	=+C11*C10
C16	C16	=MAX(#REF!)/MAX(#REF!)
C18,C30	C18	=#REF!
C23	C23	=MIN(M11:M56)
C24	C24	=-C23*C11
C25	C25	=+C22+C24
C26	C26	=+C10
C27	C27	=+C25/C26
C31	C31	=C30*C29
C32	C32	=-C11
C33,C44	C33	=SUM(C31:C32)
C34	C34	=+C10
C35,C42,C46	C35	=+C33*C34
C41	C41	=+C30
C43	C43	=-C11
C45	C45	=+C10
C48	C48	=C46-C14
C49	C49	=C48/C42
K13:K56	K13	=K12+1
M11:M56	M11	=IFERROR(STOCKHISTORY($H$7,$L11,,,0,1),"–")
Dynamic array formulas.

 

[KRice]

An easier approach is to designate two cells for the start and end dates, and then let the SPILL feature of STOCKHISTORY automatically generate the valid dates when the exchange was open as well as the closing price. Here I'm referencing only the top and bottom of your date range (not all dates in that range are valid). Those two date cells could be moved somewhere just above the listing. The formula is entered just once and allowed to spill down. Since you want most recent first, the STOCKHISTORY function is wrapped in a SORT.

=SORT(STOCKHISTORY("XNYS:APO",L56,L11),1,-1)

Then the only issue is extracting the appropriate ticker symbol from your H7 cell. The function accepts only the ticker symbol or the exchange:ticker symbol in double quotes, or a reference to that text. Here is an example:

Book1
	Q	R
4	Ticker	XNYS:APO
5	Start	3/19/2022
6	End	5/3/2022
7
8
9
10	Date	Close
11	5/2/2022	$ 51.92
12	4/29/2022	$ 49.76
13	4/28/2022	$ 52.31
14	4/27/2022	$ 50.92
15	4/26/2022	$ 51.15
16	4/25/2022	$ 54.39
17	4/22/2022	$ 53.93
Sheet1

Cell Formulas
Range	Formula
Q10:R40	Q10	=SORT(STOCKHISTORY(R4,R5,R6),1,-1)
Dynamic array formulas.