I want to use a formula to input a date and get the previous trading day's closing price.

I have been unable to find a solution online. When looking, I found the "closeyest" attribute, but it seems this doesn't allow you to enter a date.

I have tried subtracting 1 from the date to get the previous day. However, when the previous day is when markets are closed (such as a weekend), the closing price I get is the same as the date inputted.

For example, the closing price of Google on June 24 (Friday) was 2370.76 and June 27 (Monday) was 2332.45. I want a formula which would give the closing price of June 24 when given the date of June 27. I have tried the following formula:

=INDEX(GOOGLEFINANCE("GOOG","close","27/06/22"-1),2,2)

But this gives the closing price of June 27 (2332.45). (Note the date would be a reference to another cell.)

CodePudding user response：

• Get a range of data for the previous 7 days
• Use the end_date parameter to limit the final date
• Use dirty lookup with a high number as search_key as the data is sorted

=LOOKUP(9^99,GOOGLEFINANCE("GOOG","close","27/6/22"-8, "27/6/22"))

CodePudding user response：

try:

=INDEX(SORT(GOOGLEFINANCE("GOOG","close","27/06/22"-8, "27/06/22"-1), 2, 0), 2, 2)