0

I have been searching for a solid formula to find stock prices of "previous trading days" of a large batch of historical dates.

The formula I started with:

=googlefinance(A5, "close", B5-1)

where A5 refers to a ticker, and B5 refers to a date in the past.

I get #NA results for previous days that fall on weekends or holidays.

I need a formula that will work reliably for 365 days per year, no matter the day of the week or number of non-trading days prior. I have tried something like this:

=index(googlefinance(A5, "close", (B5-1)-2,2),2,2)

That formula works if the B5 date is the first trading day of the week, but not if it falls anywhere midweek.

I tried using WORKDAY(B5,-1) in a few ways but I realize that function can only calculate a number of workdays, not produce a date.

I need a formula that I do not have to edit and adjust cell by cell.

Rubén
  • 34,714
  • 9
  • 70
  • 166
Jared G
  • 27
  • 1
  • 1
  • 6
  • I tried the formula from the documentation `GOOGLEFINANCE("NASDAQ:GOOG", "close", DATE(2014,1,1), DATE(2014,12,31), "DAILY")` and it returns all the days, including the holidays. Can you show which date format are you using in B5 and prior? – Jescanellas Apr 28 '20 at 08:49
  • The format I am using in the date cells is: 2020-04-27 – Jared G Apr 28 '20 at 17:03

1 Answers1

4

Here is one way that gives

  • the closing price of the date if it was a trading-day

  • the closing price of the previous trading day, if it was not a trading day.

    =vlookup(B5+16/24,googlefinance(A5, "close", B5-7, 7),2,true)

Here is how it works:

  • googlefinance(A5, "close", B5-7, 7) gets the data for 7 trading days starting from the date 7 days ago.
  • vlookup get the close price on the date closest to the date in B5 if B5 is a non-trading day.
  • B5 + 16/24 is to match the date with the closing price date stamp of 4pm.

screenshot

ADW
  • 4,177
  • 1
  • 14
  • 22
  • Tested the formula above. Produced the same problem result as some other formulas. That is, for an original date like 2020-04-27, the formula returns the closing price of 2020-04-27, and not the previous trading day of 2020-04-24. – Jared G Apr 28 '20 at 17:34
  • 1
    Made an adjustment to your formula above, and it seems to work as intended. I think you had the right idea but might have made a typo. The working version of your formula seems to be: `=vlookup(B5,googlefinance(A5, "close", (B5+16/24)-7, 7),2,true)` – Jared G Apr 28 '20 at 17:49
  • 1
    Working formula for the closing price of the NEXT trading day, regardless of weekend or holiday: `=index(googlefinance(A5, "CLOSE", (B5+16/24)+1,2),2,2)` – Jared G Apr 28 '20 at 21:16