A workaround that solves the above is getting the Close price for the stock for a range of days before and after the date in question, and then querying the result for the most recent price on or before that date, like this:

=QUERY(GOOGLEFINANCE("GOOG","Close",DATEVALUE("1/1/15")-5,6),"select Col2 where Col1 <= date '2015-01-01' order by Col1 desc limit 1",False)

To make this dynamic, you can reference the date to another cell (A1 in the example below) like this:

=QUERY(GOOGLEFINANCE("GOOG","Close",A1-5,6),"select Col2 where Col1 <= date '"&TEXT(A1, "YYYY-MM-DD")&"' order by Col1 desc limit 1",False)

I came up with this answer after trying multiple options, taking a hint from this post that was trying to find accurate moving average for historical data.

A workaround that solves the above is getting the Close price for the stock for a range of days before and after the date in question, and then querying the result for the most recent price on or before that date, like this:

=QUERY(GOOGLEFINANCE("GOOG","Close",DATEVALUE("1/1/15")-5,6),"select Col2 where Col1 < date '2015-01-02' order by Col1 desc limit 1",False)

To make this dynamic, you can reference the date to another cell (A1 in the example below) like this:

=QUERY(GOOGLEFINANCE("GOOG","Close",A1-5,6),"select Col2 where Col1 < date '"&TEXT(A1+1, "YYYY-MM-DD")&"' order by Col1 desc limit 1",False)

I came up with this answer after trying multiple options, taking a hint from this post that was trying to find accurate moving average for historical data.