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?

Improve this question
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

Reset to default
-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.

Improve this answer
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.

Improve this answer
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")).

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

F530 = cell with date (formatted to date)

Improve this answer
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"))
Improve this answer
1
  • Welcome. Please when posting try using the english locale as well as it is widely understood.
    – marikamitsos
    Nov 14, 2019 at 14:16

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