Downloading Stock Prices into Google Spreadsheet

This is a sister post for “Downloading Stock Prices into Excel Spreadsheet” http://amourtan.com/2011/06/downloading-stock-prices-into-excel-spreadsheet/

Google Spreadsheet used to be a joke in the spreadsheet arena – it’s slow, unreliable and not that feature rich. However, Google Spreadsheet has now come of age; it has become a bad concept done well! Nowadays, if you want to do heavy numerical crunching, the natural choice would be to use Excel. OTOH, if you’ve structured data which you want to share and collaborate with colleagues or friends, GS is an excellent choice.

GS has means to retrieve stock/ETF/mutual funds data into the spreadsheet. I shall introduce 2 approaches. If your investments are all USA based, you can import it from Google Finance. For example typing =GoogleFinance(“GOOG”; “price”) in a cell will retrieve the price of the ticker ‘goog’ into the cell. Typing =GoogleFinance(“GOOG”; “volume”) will likewise import the volume of Google. You can view the documentation from Google here http://support.google.com/docs/bin/answer.py?hl=en&answer=155178

If you’ve foreign (outside USA) investments such as Singapore stocks, you can import them from Yahoo! Finance. Try typing in the string =ImportData(“http://finance.yahoo.com/d/quotes.csv?s=XOM+BBDb.TO+JNJ+MSFT&f=snd1l1yr”). The cells will import the ticker, stock name, date, last trade price, dividend yield and PE ratio for the symbols XOM, BBDb.TO, JNJ and MSFT. The structure of the data is encoded in the string ‘snd1l1yr’. As for the symbol list, your can check with Yahoo! Finance at http://finance.yahoo.com/ . The documentation of the stock quotation URL is listed here http://www.gummy-stuff.org/Yahoo-data.htm .

On some browsers, you might discover that the cells contain stale data upon subsequent reloads. A quick and dirty fix is to change the URL in the function, e.g. to =ImportData(“http://finance.yahoo.com/d/quotes.csv?s=XOM+BBDb.TO+JNJ+MSFT&f=snd1l1yr&var”) where the string ‘var’ is a random number or text. You can automate the process of changing the random string by referencing the random string in the URL to another cell and change the content of that cell. GS will be smart enough to detect the change and update the quotes accordingly.

If you want to import historical quotes into your spreadsheet, try the following instead, i.e. =ImportData(“http://chart.yahoo.com/table.csv?s=MSFT&a=0&b=1&c=2010&d=0&e=1&f=2013&g=w&q=q&y=0&x=.csv”).

  • s=MSFT specifies the symbol MSFT
  • a=0 specifies the start month (0 for Jan, 1 for Feb, 2 for Mar, etc.)
  • b=1 specifies the start day of the month (1st of the day in this case)
  • c=2010 specifies the start year
  • d=0 specifies the end month (0 for Jan, 1 for Feb, etc.)
  • e=1 specifies the end day of the month (1st of the day in this case)
  • f=2013 specifies the end year
  • g=w specifies the granularity of the data (d for daily, m for monthly, w for weekly)

Enjoy managing your finance!

NB: It seems like WordPress/Word mangled the double quotes. Please replace the “ as shown in the post with ” on your keyboard.  Even though they look the same, they are really of different character set.

26 thoughts on “Downloading Stock Prices into Google Spreadsheet”

  1. It’s nice to have stock prices on Google spreadsheets. But, I have seen some players which are showing a lot of traction when it comes to sharing and collaboration of data. Tools such as Collatebox http://www.collatebox.com/ are creating a lot of buzz in the market. It’s time to have a look at these tools now striving with Google Docs.

  2. Hi there,

    I try doing this in GS but it gives me a ERROR.
    Is it just as simple as inserting the formula into one cell?
    Or am I over-simplfiying?

    Thanks!

  3. Seems like WordPress/Word mangled the double quotes. Please replace the “ as shown in the post with ” on your keyboard. It seems like even though they look the same, they are really of different character set.

  4. I would like to investment in google.
    but i am from Myanmar/Burma, Aung San Suu Kyi’s country.
    I very interesting google stock shares.
    how i can buy it?

  5. Great tip! This has the potential to be more useful then any other stock software. Wishing for a faster update, perhaps there is trick to pull straight off of kitco.com?

  6. Very useful tool – have been playing with it and sometimes values return “N/A” (for example on PE even on stocks that return price info fine.

    Even with this, the app is a great time saver – set up your sheet, enter a stock ticker and it should return most relevant info you need to appraise/track a stock. Very good!

  7. The Yahoo links seems to work with US ticker symbols but not SG symbols. Anyone else facing this problem?

  8. Your article has been very helpful. I have a couple of questions you might be able to help me with:

    1. My spreadsheet has a lot of N/A errors for stocks – and those field change often. one minute it is displaying the price correctly for a stock price from 2006, the next hour it is N/A. Here is the code: =index(GoogleFinance(D$1,”close”,A33),2,2)

    where D$1 is the symbol “KR” and A33 is the date: 3/1/2012 … i have a big spreadsheet and about 20% of the fields are N/A and constantly changing which ones have a price or NA.

    2. Is there a way to bring in historic P/E Ratios? i’ve tried the above but replacing “close” with “pe” but get an an error.

    Thanks!

  9. Hi Amourtan
    I’d like to be able to import dividend yield in, alongside price etc to my google spreadsheet. do yo know if it’s yet possible?
    Thanks
    Andy

  10. Thanks for a v speedy reply.

    Don’t think this does what I’d ideally like. I’m managing some family money and have a google spreadsheet which I share with siblings showing state of our investments – it picks up realtime UK share prices. I’d also like to show dividend yield to be able to see a wider picture – but google doesn’t seem to have an attribute for that.

    I’m an advanced basic user of spreadsheets, if that makes sense?! Completely at home with basic functins, but not into macros etc…..

    Any help would be appreciated!
    Andy

  11. Hi Andy,

    Google Finance does not provide dividend yield for UK stocks but the Yahoo! Finance method I described above does what you want.

  12. Hi Everyone,
    Going back to this post:
    ——-
    amourtan on June 6, 2013 at 6:07 am said:

    I’ve SG stocks in my portfolio. Not a problem here. Did you include the .SI extension?
    ——-
    I am facing this problem. For example, cannot get the data for ABERDEENSPMA.SI.

    Thank you.

    Selva

  13. obviously like your web-site however you have to check the spelling on quite a few of your posts.
    Several of them are rife with spelling issues and I find it very troublesome to inform the reality nevertheless I’ll surely come
    back again.

  14. Great article – very useful. I’m also getting this #N/A response on VMW for a lot of data ranges. Any idea’s what might be causing this?

  15. I would like to get the historical values for each stock in a column of symbols using another column of dates. For example:
    =ImportData(“http://chart.yahoo.com/table.csv?s=H1&a=I1&b=J1&c=K1&d=L1&e=M1&f=N1&g=d&q=q&y=0&x=.csv”)
    I can’t seem to get your formula to pick these values from the cells. Can it do this?
    Thanks for a great formula,
    Merle

  16. Watch your cell’s formula construct.

    =ImportData(“http://chart.yahoo.com/table.csv?s=”&H1&”&a=”&I1&”&b=”&J1&”&c=”&K1&”&d=”&L1&”&e=”&M1&”&f=”&N1&”&g=d&q=q&y=0&x=.csv”)

    As stated previously, replace the double quotes with the one on your keyboard.

  17. Hi, Amour. You seem to be “the guy” when it comes to this subject. Thanks for all of your contributions.

    I’m trying to do a simple 10-day lookback of close prices for a given stock. The problem that I am running into is that if the formula says to take TODAY’s date (in A1) and subtract a day, then it does not skip weekends. I tried using NETWORKDAYS to get around this, but I cannot construct the proper formula.

    I am using:
    =GOOGLEFINANCE(A3,”close”,A1-1,TODAY())

    So, this say, “go to google finance, look up the ticker listed in A3, then give me the closing price for the date range of today’s date minus 1” (and 2 and 3 and 4, and so on) in the other cells. The output is:
    =CONTINUE(F2, 2, 1)
    meaning, it populates with the “date and price” in two cells, horizontally.

    This simply doesn’t work because the formula cannot ignore holidays or non-market days.

    How can I work horizontally like this to give:

    Today’s price, yesterday’s close, the day before close, day before that, day before that, etc.?

    Thank you for any thoughts.

Leave a Reply