54

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?

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

5 Answers 5

-5

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.

3
  • 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, 2011 at 18:50
  • 7
    The answer from @tic is simpler than this one.
    – Laizer
    Mar 5, 2013 at 16:38
  • This is obviously not the right answer. See below.
    – Kalle
    Feb 21, 2019 at 13:29
102

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

4
  • 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, 2011 at 4:41
  • 9
    this definitely works better then the chosen answer
    – Damien
    Sep 21, 2012 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, 2017 at 3:46
  • 1
    @spinlock, you might consider changing your accepted answer to this one.
    – J Walters
    Feb 8, 2019 at 14:00
8

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

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

F530 = cell with date (formatted to date)

1
=BDMIN(GOOGLEFINANCE("Goog"; "PRICE"; "YourStarDate";"YourEndDate");"CLOSE";
       GOOGLEFINANCE("Goog"; "PRICE"; "YourStarDate";"YourEndDate"))

It works for me.

Example for the english locale:

=DMIN(GOOGLEFINANCE("Goog", "PRICE", "10/10/2019","10/11/2019"),"CLOSE",
       GOOGLEFINANCE("Goog", "PRICE", "10/10/2019","10/11/2019"))
1
  • Welcome. Please when posting try using the english locale as well as it is widely understood. Nov 14, 2019 at 14:16

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