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.