Google Docs question

Investment Analysis Clubs Saul’s Investing Discussions
1

I apologize upfront because my question is not about a specific stock. However, it is something that will help me (and possibly others) in our stock analysis.

I am trying to create a formula in my Google Docs spreadsheet that pulls historical closing price data. For example, the closing price of SKX on June 30 (or any other date). I’ve looked everywhere and I cannot find anything that will give me this seemingly simple formula.

Does anyone know how to do this? I can get the current price no problem, but for the life of me I cannot figure out how to pull historical prices. I’ve built the formula that I thought should work based on the help and support files, but I must be doing something wrong.

Thanks!
Fletch

2

Hi Fletch,

Try this.

GOOGLEFINANCE(ticker, [attribute], [start_date], [num_days|end_date], [interval])

GOOGLEFINANCE(“GOOG”, “price”, DATE(2014,1,1), DATE(2014,12,31), “DAILY”)

Chad

2 Likes
3

And, if that doesn’t work, you might try http://discussion.fool.com/spreadsheet-advice-100002.aspx?mid=32…

4

Hi Fletch,

Try this.

GOOGLEFINANCE(ticker, [attribute], [start_date], [num_days|end_date], [interval])

GOOGLEFINANCE(“GOOG”, “price”, DATE(2014,1,1), DATE(2014,12,31), “DAILY”)

Chad

Hi Chad,

Thanks for the response. Is there anyway to just isolate one day? That gives me the price for each date of 2014. What if I just wanted 6/30/2014? What would that formula look like?

Thanks.

5

Fletch,

The formula Chad gave you is on the right track, unfortunately that returns multiple cells. The trick is to index into those cells returned.

Here’s an example…

=index(GoogleFinance(A2,“price”,C$1),2,2)

In this case, cell A2 holds the ticker symbol and C1 has a date value. This was copied from a working example.

Steve

1 Like
6

Thanks Steve! That worked perfectly.

Fletch

7

Steve -

Thanks. That is a much simpler formula than the one I had been using. I am going to try switching over to that one and see if it works better. The formula I had been using would occassionally return #N/A and other times work just fine. Annoying. No idea why. Does that ever happen to you with this formula?

best,
michael

8

The formula I had been using would occasionally return #N/A and other times work just fine. Annoying. No idea why. Does that ever happen to you with this formula?

Michael,

I can’t really say that it won’t return #N/A sometimes. My suspicion is that it will since even the current price function even does that at times. The formula I gave is not one I use in an active sheet that I watch, but one I set up once upon a time for some reason. I struggled with getting a “clean” number until I ran across the INDEX suggestion somewhere.

Steve

9

Try this:

=importhtml("http://finance.yahoo.com/q/hp?s=AAPL",“table”…)

Here’s a URL with specific dates:

http://finance.yahoo.com/q/hp?s=YHOO&a=11&b=1&c=…

parameters:

a,b,c start date Month = 0-11, Day, Year
d,e,f end date
g = “d” “w” “m” “v” daily, weekly, monthly, dividends only.