I'm trying to figure out the cost basis of some stock that I sold last year and I can't quite get GOOGLEFINANCE to do what I want. The stock was purchased through a DRIP so there are about five shares that get added every quarter over a long period of time.

I have the settlement dates and number of shares but, before 2003, I don't have the purchase price. So, I'm trying to use the GOOGLEFINANCE function to figure out the price but if I write

=GoogleFinance("GOOG", "PRICE", 3/3/2000)

I get a 2x2 array rather than just the historical price.

Is there any way to chain GOOGLEFINANCE into another function that would return just the value in the lower right cell?

  • 3
    Visitors are advised to read this answer, not the one that is on currently on top. – user135384 Apr 13 '18 at 4:28

No, it is not possible. You can however just reference the lower right cell in another cell.

so if you got back from Google:

A1: "Date"      B1: "Close"
A2: "3/3/2000"  B2: "55.22"
A3: "Date"      B3: "Close"
A4: "3/4/2000"  B4: "58.44"

Set cell C1 =B2 and cell C2 =B4

and the results will be:

C1: 55.22
C2: 58.44

you could also put the query in one google doc and then reference it from another google doc if you really wanted to separete the data.

  • Thanks for the response. I think I'll have to do something like have sheet 2 contain all of the data and do a vlookup to get the price into sheet 1. It'll be a lot of wasted data because the purchases were made every quarter but the timing isn't consistent enough to use interval to only get the data I need. – spinlock Apr 23 '11 at 18:50
  • 7
    The answer from @tic is simpler than this one. – Laizer Mar 5 '13 at 16:38
  • This is obviously not the right answer. See below. – Kalle Feb 21 at 13:29

Use =INDEX(GoogleFinance("GOOG", "price", "3/3/2000"),2,2). Maybe you will need to close date between parenthesis.

  • This answer put me on the right track. The only issue was GOOG was not trading in 2000. So I just needed to amend the date. e.g "3/3/2010" Thanks @tic! – user14654 Nov 7 '11 at 4:41
  • 8
    this definitely works better then the chosen answer – Damien Sep 21 '12 at 13:24
  • Yeah this works. You can call a range of dates with this method and perform aggregation as if it was a QUERY function. – daneshjai Oct 19 '17 at 3:46
  • 1
    @spinlock, you might consider changing your accepted answer to this one. – J Walters Feb 8 at 14:00

I found a hack that works here as well.
Use =min(GoogleFinance("Goog", "PRICE", "3/3/2000")).

=INDEX(GoogleFinance("USDIDR", "PRICE", F530),2,2)

F530 = cell with date (formatted to date)

Your Answer

