yken.org

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

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:

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.