How to get a real-time stock quote using Google API

January 5th, 2009 Posted in Development, Internet, Mac, Stocks, bash

After resolving the problem with authentication, I continued playing around with Google Finance API, using HTTP and XML. The API offers some nice functionality to retrieve user portfolio content, but it doesn’t take long to realize that there is no support for retrieving (real-time) stock quotes, probably for some good reason like licensing. But then, there is a way of getting real-time stock quotes on your Google Spreadsheet using the GoogleFinance() formula. Can this fact get us closer to retrieving real-time stock quotes using Google Data API?


Yes it can. It is relatively straightforward to write a simple program to retrieve real-time stock quote using just Google Spreadsheet Data API. Below is a simple proof-of-concept script which does the job. To make it work, create a new Spreadsheet on Google Docs and int the cell A1 fill in a formula for your favorite stock quote, something like:

=GoogleFinance("GOOG")

You will see the price in your spreadsheet:

Firefox Google Spreadsheet Sample

The script to retrieve the quote:

#!/bin/bash

wget -O ClientLogin.txt --no-check-certificate --post-file=post.txt \
"https://www.google.com/accounts/ClientLogin" >/dev/null 2>&1

TOKEN=`cat ClientLogin.txt | grep Auth \
| sed "s#Auth=##" | xargs echo -n`

wget -O Spreadsheets.txt --header="Authorization: GoogleLogin auth=${TOKEN}" \
--header="GData-Version: 2" \
http://spreadsheets.google.com/feeds/spreadsheets/private/full >/dev/null 2>&1

WORKSHEET=`cat Spreadsheets.txt | \
sed "s#\(.*\)src=\([']*\)\([^']*\)\([']\)\(.*\)#\3#"`

wget -O Worksheet.txt --header="Authorization: GoogleLogin auth=${TOKEN}" \
--header="GData-Version: 2" ${WORKSHEET} >/dev/null 2>&1

cat Worksheet.txt | sed 's#\>#\>\
#g' >WorksheetFormatted.txt

CELLSFEED=`cat WorksheetFormatted.txt | grep cellsfeed | \
sed "s#\(.*\)href=\([']*\)\([^']*\)\([']\)\(.*\)#\3#"`

wget -O Cells.txt --header="Authorization: GoogleLogin auth=${TOKEN}" \
--header="GData-Version: 2" ${CELLSFEED} >/dev/null 2>&1

cat Cells.txt | sed 's#\>#\>\
#g' >CellsFormatted.txt

REALTIMEQUOTE=`cat CellsFormatted.txt | grep numericValue | \
sed "s#\(.*\)numericValue=\([']*\)\([^']*\)\([']\)\(.*\)#\3#"`

echo "Quote = ${REALTIMEQUOTE}"

where post.txt is something like:

POST /accounts/ClientLogin HTTP/1.0
Content-type: application/x-www-form-urlencoded

accountType=HOSTED_OR_GOOGLE&Email=__EMAIL__&Passwd=__PASSWD__
&service=finance&source=yken.org-GoogleStockQuote-0.1


A few notes on the script:

  • Replace __EMAIL__ and __PASSWD__ in post.txt with your Google credentials
  • This will only work when you have just one spreadsheet on your Google Docs
  • The script does not parse the XML replies, it just gets the values it needs using brute force (as it is just a proof of concept).
  • Three requests (four with login) just to get a quote is definitely over the top. The Google API allows batch operations, too.
  • I tried this on Max OS X 10.5 only


To make the script actually useful, it would be good to modify it to take some parameters, for instance at least the desired stock symbol and then to use that stock symbol to write the =GoogleFinance() formula to A1. Google Data API allows it. This way, the script could be used for retrieving any quote (available on Google Finance), not only just the hardcoded one. Also, there are many Google Data API Client Libraries available, allowing to rewrite this script into something proper. I will try both and will post the result here.

Important note: all stock data obtained by the above script can be used for personal informational purposes only, see the Google disclaimer.

6 Responses to “How to get a real-time stock quote using Google API”

  1. Saurabh Says:

    How to get a real-time stock quote using Google API

    Does this mean that if i create an online google spreadsheet with all stock symbols (200) i can get the real time quotes displayed in my website?

    Regards
    Saurabh


  2. Ivo Says:

    Yes, this is definitely a possibility although there are two problems with such solution: firstly, it appears that not all stock symbols are available for =GoogleFinance(). More important, such solution would not be compatible with terms under which Google makes this information available (i.e. it would not be legal).


  3. Saurabh Says:

    Then maybe we can parse portfolio, i read that Google allows 3rd party usage either for commercial or non commercial purposes. The cap is at 200 queries at one go.

    The work around maybe that 5-10 portfolios with 50 stocks each and data pulled in.

    can you pls adapt the above script to run with G-finance portfolio instead of G-spreadsheer.


  4. Ivo Says:

    In portfolios, you cannot get (real time) stock quotes, hence the whole spreadsheets solution.


  5. Saurabh Says:

    Dear Ivo,

    Just wanted your comments, as there would be authentication(login)before the quotes are pulled from spreadsheet, would that lead to delay in pulling quote?

    Secondly what if we just publish the spreadsheet and get data without authentication using csv? And parse it with php script?


  6. Kalpesh Says:

    Hello,

    I have been looking for ways to use real-time data for our home grown applications, can you please confirm one thing that all the solutions mentioned here are based on client request, to get the data feed in the application we ideally need something like ’streaming’ request where the data gets ‘pushed’ from server. Yahoo API (although is a chargable one) has ’streaming’ request options, i could not see any such option with Google API.

    Would you agree with this or if you are aware about any other option, can you please mail me to kalpesh.shah@vaagha.com

    I would be happy to share what i have gained through yahoo api.

    Thanks

    -Kalpesh.


Leave a Reply