Amateur Protagonist Alister Sneddon

I once almost won a procrastinating contest, but I turned up.


Using Google Spreadsheets to stream FTSE 100 Data

10th May 2015
By Alister Sneddon

A long time ago you could use Google Finance API's to get a lot of information about the FTSE 100 (and beyond, it allowed full access with rate limiting.) However those golden days are gone.

This article is going to be a little bit of a special one in my eyes. Why is that? Well this is a system I have personally used to stream (using AJAX polling) 15 minute delayed FTSE 100 prices.

So let's get into the Google Spreadsheet side of things.

First thing that is important to know. You can inside a Google Spreadsheet use the following function:
=GoogleFinance

This allows you to query a number of different things about a symbol. We are going to get the following data:

  • Current Trade Price (also known as last trade price)
  • Last Trade Date Time
  • Opening Price
  • Current Day Trading Volume

Google has a great help page on the functions you can use and the types of data you can request. Google Finance usage in Google SpreadSheets, however you are limited in the number of times you can use this function inside a spreadsheet (1,000 requests per a spreadsheet, if you go over the limit just make another spreadsheet with different data sets.)

Open your Google Spreadsheet and give it a half decent name "Google FTSE 100" sounds good to me. Now we are going to need six columns for this to work. Let's try this with just two equities to get us started.

The only downside to using the spreadsheet method is you to know what symbols you are requesting.

The first row in our spreadsheet will make up our headers. Now do NOT include any spaces, this WILL cause you problems and stop you from being able to query the data using PHP (or whatever language you like.)

My headings are:

  • Symbol
  • Name
  • Price
  • Volume
  • PriceOpen
  • LastTrade

Now you have that setup let's put in some data so skip down to the next row (row 2 now people) and let's add our static data.

  • Symbol: AAL.L
  • Name: ANGLO AMERICAN

Now let's add our dynamic query data (note: you HAVE to use double quotes.)

  • Price: =GoogleFinance(A2, "price")
  • Volume: =GoogleFinance(A2, "volume")
  • PriceOpen: =GoogleFinance(A2, "priceopen")
  • LastTrade: =GoogleFinance(A2, " tradetime")

What this allows us to do is be very lazy. It is very easy to get the symbol and name of FTSE 100 companies, paste them into column A and B then you're formula will cleanly copy down and provide you with some nice data.

If this is all you was interested in, getting dynamic numbers into your Google Spreadsheets, then you have everything you need to get started.

The ones who want to now connect to this spreadsheet read on.

First thing you now need to do is make your document public. Click on the Share button and make sure the doc is publically accessible. Take note of the key parameter inside the share link. This key should also be inside the URL you are currently looking at anyway.

There are a number of ways to read a Google Document but I like the JSON method the most.

Here is the PHP code to read the JSON file and print each row. If you have been following my example it will be just the one row.

// Enter the key to complete the URL we will query
$key = 'Enter SpreadSheet Key Here';
$url = 'http://spreadsheets.google.com/feeds/list/' . $key . '/od6/public/values?alt=json';

// You could be fancy here but I am making a point, just get the content of this URL
$file = file_get_contents($url);
 
// This is a JSON file so you can expect decode to work
$json = json_decode($file);

// We only care about the rows
$rows = $json->{'feed'}->{'entry'};

// Loop each row and print it out as we go
foreach($rows as $row) {
  echo '<p>';
  echo $row->{'gsx$symbol'}->{'$t'} . '<br />';
  echo $row->{'gsx$name'}->{'$t'} . '<br />';
  echo $row->{'gsx$price'}->{'$t'} . '<br />';
  echo $row->{'gsx$volume'}->{'$t'} . '<br />';
  echo $row->{'gsx$priceopen'}->{'$t'} . '<br />';
  echo $row->{'gsx$lasttrade'}->{'$t'} . '<br />';
  echo '<\p>';
}

I am sure you can agree that is pretty amazing. You can either query this direcly as your AJAX call or make a page to handle just this logic and then query that page for a formatted slimmed down response.

Have fun!

You filthy comment whore, you love it don't you?
Lets not be forgetting to +1 it now... I am tracking your IP...