How to: Google Spreadsheets and external data

Alastair Aitken 2 November 2011 0




For a generation, Microsoft Excel has been the de facto tool of choice for slicing and dicing numbers. Microsoft has constantly updated Excel to keep it ahead of its desktop rivals, of which there are now very few. As the internet evolved Microsoft added online capabilities to Excel to allow retrieval of external data, such as stock market information.

Google Docs logo Google Spreadsheets is becoming a serious rival to the Microsoft Excel hegemony, providing all core spreadsheet functionality, collaboration and scripting whilst improving its financials functionality. However without the fanfare that used to accompanying a typical launch of a new version of Microsoft Excel this functionality can remain unheralded.  Coupled with its core strength of collaboration will these features win over die-hard Excel fans?

Populating a spreadsheet with live share prices is a useful reference work for demonstrating external data retrieval.

Retrieving share prices from Yahoo! Finance

To retrieve share prices a data source is required; whilst Google Finance provides share prices it only appears to do this for US stocks, Yahoo! Finance offers a greater range of equity quotes from a number of exchanges.

The Yahoo! Finance URL is relatively straightforward to start using but may require a bit of trial-and-error in order to retrieve all the desired data. The following URL:

http://finance.yahoo.com/d/quotes.csv?s=LLOY.L&f=snl1

will retrieve the last trade price for Lloyds TSB from the London Stock Exchange – symbols can be found at Yahoo! Finance, the “.L” indicates the LSE.

Adding additional symbols to the URL requires adding a plus sign (+) and the next symbol after the Lloyds TSB symbol. Thus a more complicated URL might be:

http://finance.yahoo.com/d/quotes.csv?s=^FTSE+LLOY.L+ISYS.L+BNC.L+IAG.L+BT-A.L+MSFT+GOOG&f=snl1w1rdyabgh

which will retrieve the following values for a number of shares:

  • Symbol
  • Name
  • Last trade price
  • Change in percentage
  • Pricing/Earnings ratio
  • Dividend
  • Yield
  • Ask price
  • Bid price
  • Day low
  • Day high

Retrieving shares prices from within Google Spreadsheets

The function importData is used to load data into a Google Spreadsheet. importData takes a URL as its only argument, retrieves data from the CSV or a TSV file referenced by the URL and imports it into the current spreadsheet. Therefore the URL above will be entered as a formula into a spreadsheet cell as:

=importData("http://finance.yahoo.com/d/quotes.csv?s=^FTSE+LLOY.L+ISYS.L+BNC.L+IAG.L+BT-A.L+MSFT+GOOG&f=snl1w1rdyabgh")

Retrieving foreign exchange rates from Google Finance

The above URL retrieves prices in both GBP and USD, so to get a complete portfolio valuation requires converting non-GBP valuations into GBP. Foreign exchange conversion requires a conversion rate, which Google Finance can supply. To retrieve the USD to GBP rate requires the formula:

=googlefinance("CURRENCY:USDGBP")

Putting it all together

Open the spreadsheet

Notes

Just as in Microsoft Excel, a dollar sign ($) represents an absolute cell reference.

The limit on the number of ImportData functions per spreadsheet is 50, so if there was a large number of FX lookups they might have to be moved into a single location.

London Stock Exchange prices are quoted in pence rather than pound sterling and the formula in column D takes this into account.

When looking up BT Group plc in Yahoo! Finance the ticker symbol is BT-A.L rather than BT.A.L




Alastair Aitken (124 Posts)

As a contract developer and manager I’ve worked in a wide range of enterprises in a variety of countries where I’ve encountered everything from great work, awful work, bizarre work, all the way down to quasi-legal work. If you think that you recognise your own organisation within my articles then you’re undoubtedly wrong, where you work isn’t that unique.

Leave A Response »