Simple C# DLL to download data from Google Finance using an alternative, undocumented method

• Download demo - 20.3 KB
• Download source code - 9.81 KB

Introduction

Google started a finance service in 2006 that is currently accessible at http://www.google.com/finance. It offers pretty interesting data and includes the option, for some stocks, to download historical prices through a "Download to spreadsheet" link located precisely at the stock's "Historical prices" page. The article Visualizing Live and Historic Stock Data Using Silverlight analyzes in depth how this can be automated to retrieve historical data automatically. The idea consists of parameterizing a URL of the form http://www.google.com/finance/historical?q={stockCode}&startdate={startDate}&enddate={endDate}&output=csv.

However, this option is not available for many stocks, such as those belonging to some non-American exchanges, or for currencies. Fortunately, there exists an alternative (undocumented) method, the one this article explores, that can be used for almost any security "listed" in Google.

The idea behind it consists of harnessing another source of historical quotes that is accessible through URLs of the type http://www.google.com/finance/getprices?q={code}&x={exchange}&i={interval}&p={period}&f={fields}. With this method, it is possible to download information from any security (as far as I know) provided that Google includes a live chart for it. In fact, I believe that its main aim is to feed the site's interactive charts.

In order to take advantage of this second method, the article introduces a C# .NET 2.0 class library that automates two operations:

1. Generating the download URL from some input parameters.
2. Transforming the raw data as it is received from Google into a more readable format.

Building the URL and downloading the data

As it has been said, the URL matches the format: http://www.google.com/finance/getprices?q={Code}&x={Exchange}&i={Interval}&p={Period}&f={Fields}. The meaning of the parameters is:

• Code. The code of the security. For example, GOOG for Google or EURUSD for the Euro/Dollar currency pair. This parameter is case sensitive and must be capitalized to be recognized.
• Exchange. The exchange where the security is listed. For example, NASDAQ for GOOG or CURRENCY for EURUSD. The exchange must be in upper case and can be left blank for American exchanges.
• Interval. Google groups the data into intervals whose length in seconds is defined by this parameter. Its minimum value is 60 seconds.
• Period. The period of time from which data will be returned. Google always returns the most recent data. Examples of this parameter are 1d (one day), 1w (one week), 1m (one month), or 1y (one year).
• Fields. The fields to return. This parameter seems to be ignored by Google, as it always returns the date, open, high, low, close, and volume of every interval.

As an example, the URL http://www.google.com/finance/getprices?q=LHA&x=ETR&i=60&p=1d&f=d,c,h,l,o,v means: Download the fields date, close, high, low, open, and volume (f=d,c,h,l,o,v) for the last day (p=1d) grouping the data into 60 second intervals (i=60) for the security LHA (q=LHA) belonging to the exchange "ETR" (x=ETR).

Upon invoking that URL, something similar to this would be downloaded:

EXCHANGE=ETR
MARKET_OPEN_MINUTE=540
MARKET_CLOSE_MINUTE=1050
INTERVAL=60
COLUMNS=DATE,CLOSE,HIGH,LOW,OPEN,VOLUME
DATA=
TIMEZONE_OFFSET=120
a1306998060,14.84,14.95,14.83,14.93,54359
2,14.84,14.84,14.84,14.84,97
3,14.865,14.865,14.84,14.84,5584
4,14.875,14.875,14.875,14.875,1230
5,14.865,14.885,14.85,14.88,14962
6,14.845,14.86,14.84,14.86,7596
7,14.855,14.855,14.84,14.845,20912
8,14.845,14.85,14.845,14.85,9833
9,14.85,14.85,14.85,14.85,2358

...

As it can be seen, the information returned is composed of two parts:

• A header. It ends with the TIMEZONE_OFFSET entry. The most interesting fields are:
1. COLUMNS: Defines the order in which data line fields appear.
2. INTERVAL: The number of seconds that every line of data represents.
• Data. This part is made of several lines with the close/high/low/open/volume values for a period of length 'interval' for the line's date. The date is represented in two ways:
1. Absolute. Format: 'a' + number of seconds elapsed since January 1, 1970 (the beginning of the UNIX era). In the example, only the first line of data uses it ('a1306713600').
2. Relative. Format: number of intervals elapsed since the last "absolute date".

The class library

The class library contains two classes:

• DownloadURIBuilder. Its mission is to create the URL to download the data. It exposes these constructor and methods:
C#

/// <summary>
/// Define here the exchange and the ticker.
/// </summary>
public DownloadURIBuilder(string exchange, string tickerName);

/// <summary>
/// Constructs the URI, using the object's exchange and ticker and the 
/// given interval and period. 
/// </summary>
public string getGetPricesUri(int interval, string period) {

/// <summary>
/// Calls getGetPricesUri with Interval = one day and Period = the number of years
/// since 1970.
///
/// The parameter 'lastDate' must be the current date. DateTime.Now isn't used to
/// avoid dependencies with the system time.
/// </summary>
public string getGetPricesUriToDownloadAllData(DateTime lastDate);
        
/// </summary>
/// Calls getGetPricesUri with Interval = 1 day and Period = the number of 
/// days between 'startDate' and 'endDate'. The aim of this method is to return 
/// at least the data between 'startDate' and today.
/// Although the ending date is fixed (Google
/// doesn't allow to define it), it is passed
/// as an argument in order to avoid dependencies 
/// between the library code and the current system time. 
/// 
/// Evidently, this method's URL returns data prior
/// to 'startDate'. To avoid this, things 
/// like the number of holidays between 'startDate'
/// and 'endDate' should be taken into account. 
/// It seems that that would overcomplicate things for this simple example. 
/// </summary>
public string getGetPricesUriForRecentData(DateTime startDate, DateTime endDate);

/// <summary>
/// Calls getGetPricesUri with Interval = 1 minute (the minimum possible value) 
/// and Period = 1 day.
/// </summary>
public string getGetPricesUriForLastQuote();

• DataProcessor. Interprets the data and translates it into something more readable. This class contains two public methods:
C#

/// <summary>
/// Recovers the current (sometimes real-time) quote from the input stream.
/// To be used in conjunction with DownloadUriBuilder.getGetPricesUrlForLastQuote().
/// Extracts today's open, high, low, close and volume (OHLCV) from 'stream'. 
/// To do this, it scans the returned data. The 'open' is the first value, the 'close'
/// the last, the high and the low are the highest and lowest of all of the values
/// returned and the volume is the sum of all the volume fields.
/// </summary>
public String processStreamMadeOfOneMinuteBarsToExtractMostRecentOHLCVForCurrentDay(
       Stream stream, out string errorMessage) {

/// <summary>
/// To be used together with DownloadUriBuilder.getGetPricesUriToDownloadAllData() and 
/// DownloadUriBuilder.getGetPricesUriForRecentData(). Transforms the input into a CSV
/// file with Date, Open, High, Low, Close and Volume headers and a line per day. 
/// </summary>
public String processStreamMadeOfOneDayLinesToExtractHistoricalData(Stream str, 
                                                       out string errorMessage)

As an example, the procedure to download all the historical data for the Euro/U.S. dollar currency pair would be this:

1. Create a DownloadUriBuilder object setting the exchange parameter to CURRENCY and the tickerName parameter to EURUSD. Both parameters must be capitalized (that is, Google would not recognize CuRRency or EurUSD). The class library does not set this parameter automatically to upper case. This makes it more flexible, as it will be able to support possible future exchanges or tickers with lower case characters.
2. Invoke the class method getGetPricesUriToDownloadAllData() and use, for example, the returned URL to download the data using the WebClient class of the framework.
3. Create an object of type DataProcessor and call the method processStreamMadeOfOneDayLinesToExtractHistoricalData(Stream str, out string errorMessage). The stream can be obtained using the OpenRead() method of the WebClient class. If everything goes right, errorMessage is null or empty and you can use the returned data string. Else, you can use this output parameter to display what has gone wrong.

The demo tool

The demo tool is pretty simple and straightforward. It is a Windows application with a single form divided into three blocks:

• A first block allows the user to choose the URL building parameters: the exchange, the ticker, and the interval to download.
• The second block displays the current URL.
• The third and last block displays the data downloaded after pressing the "Download" button. It includes the possibility of displaying the data processed through a call to one of the methods exposed by DataProcessor or as it is returned from Google by clicking the "Raw Data" check box. There is also an option to save the results by clicking the "Save" button.

Conclusion

This article describes a simple way to download financial data by using one of the undocumented features of Google Finance. I hope that you will find it useful for your purposes.

History

• 4^th July, 2011
• Initial version.