GoogleFinance formula to find previous historical trading day of a stock

Question

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.

Answer 1

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.