Change from prior in a dynamic array

[DRSteele]

Can someone please help me?

I need a dynamic formula to report the change from the prior day's price, provided there is a prior price (or else report a blank). It's probably BYROW or some such function.

BN BAM.xlsx
	A	B	C	D	E
2	Start	8/1/2024
3	End	8/8/2024
4
5
6			Brookfield Corporation (XTSE:BN)
7			Date	Close	Change
8			8/1/2024	$ 65.99
9			8/2/2024	$ 62.87
10			8/6/2024	$ 61.56
11			8/7/2024	$ 59.00
Sheet1

Cell Formulas
Range	Formula
B3	B3	=TODAY()
C8:D11	C8	=STOCKHISTORY(C6,B2,B3,0,0)
Dynamic array formulas.

 

[RobP]

Hi,

as I think your stock history gets imported as text, rather than $ values, maybe something like this could help ?
If you do get actual values (as you show in your file) rather than text that looks like $ values imported, then you can remove the text handlers as I did in cells H8/H9 as example.
Its not Dynamic, as you would still need to know the last row of your data, so it means a copy down.

It may help, but also may not be exactly what you are looking for I appreciate.

cheers
Rob

Book1
	A	B	C	D	E	F	G	H	I
1
2	Start	08/01/2024
3	End	08/08/2024
4
5
6			Brookfield Corporation (XTSE:BN)
7			Date	Close	Change		in case values not text :
8			08/01/2024	$ 65.99	3.12		65.99	3.12
9			08/02/2024	$ 62.87	1.31		62.87
10			08/06/2024	$ 61.56	2.56
11			08/07/2024	$ 59.00
12
13
Sheet1

Cell Formulas
Range	Formula
H8:H9	H8	=IF(G9>0,G8-G9,"")
E8:E11	E8	=LET(a,VALUE(MID(D8,3,LEN(D8)-2)),b,IF(D9="",0,VALUE(MID(D9,3,LEN(D9)-2))),IF(b=0,"",a-b))

 

[RoryA]

One way:

=LET(d,INDEX(C8#,0,2),VSTACK({""},DROP(d,1)-DROP(d,-1)))

 

[DRSteele]

One way:

=LET(d,INDEX(C8#,0,2),VSTACK({""},DROP(d,1)-DROP(d,-1)))

Thanks, Rory, for this formula - it works as intended.

 

[DRSteele]

Thanks for helping Rob.

Stockhistory reports the data as values, and it does it as a dynamic array. So that must be why it appears as text instead of values.