# LAMBDA or ARRAYFORMULA with GOOGLEFINANCE

Working on a formula in Google Sheets to return True/False based on searching through an array of data using:

``````=TO_DATE(INDEX(SORT(
A2,"high",TODAY()-365,
TODAY()),2,FALSE),2,1))
``````

This formula returns the highest date in the previous 365 day period.

Next I need to identify what the largest percentage gain was during that same 365 day time period. It may or may not be during that 52 week high.

Stuck finding a solution so thought I'd ask the community to see if you can help?

Thoughts: Maybe use LAMBDA function and build a custom script like the following to identify. Just not sure how to build this within Sheets as the following example is VBA and just learning Sheets now.

``````Function jumpfound(rar As Range)
rr = rar.Value
Jlim = 1.75

For i = 5 To UBound(rr, 1)
maxv = 0
minv = (2 ^ 31) - 1
jumpfound = False

For j = 0 To 4
If minv > rr(i - j, 1) Then minv = rr(i - j, 1)
If maxv < rr(i - j, 1) Then maxv = rr(i - j, 1)
If ((maxv - minv) / minv) > Jlim Then
jumpfound = True
Exit For
End If
Next j

If jumpfound Then Exit For
Next i

End Function
``````

Use `reduce()` and `filter()`, like this:

``````=let(
now, today(),
data, googlefinance(A2, "high", edate(now, -12), now),
highs, choosecols(data, 2),
gains, reduce(tocol(æ, 2), sequence(rows(highs)), lambda(a, i, let(
c, index(highs, i),
p, index(highs, i - 1),
gain, iferror((c - p) / p),
vstack(a, gain)
))),
filter(hstack(data, to_percent(gains)), gains = max(gains))
)
``````