18

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
-2

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
38

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
2

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

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

F530 = cell with date (formatted to date)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.