0

I am generating a set of data in Google Sheets with a query and then want to get a running max of that data:

My query is as follows:

=query(GOOGLEFINANCE("SPX","price",TODAY()-365,TODAY()),"Select Col2 offset 1",0)

It generates a single column of data, and I want to return a single column of data which is just the running max.

I could easily do this outside of a query with MAX(A$1:A1) and drag that down, but don't know how to reference the individual rows of a query. Please help!

Improve this question

3 Answers 3

Reset to default
1

You can also do this using SCAN:

=SCAN(-9^99,
   QUERY(
     GOOGLEFINANCE("SPX","price",TODAY()-365,TODAY()),
     "Select Col2 offset 1",0),
   LAMBDA(a,c,MAX(a,c)))
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

0

Thanks, all. I was able to get the following to work well:

=SCAN(0,
      query(GOOGLEFINANCE("SPX","price",TODAY()-365,TODAY()),
        "Select Col2 offset 1",0),
      LAMBDA(currmax,value,MAX(currmax,value)))

Credit to https://support.google.com/profile/24340643...

Improve this answer

Comments

-1

Here's one approach you may test out:

=let(Σ,query(googlefinance("SPX","price",today()-365,today()),"Select Col2 offset 1",0),
 reduce(tocol(,1),sequence(rows(Σ)),lambda(a,c,vstack(a,max(a,chooserows(Σ,c))))))

enter image description here

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.