Click here to Skip to main content
15,868,016 members
Articles / Desktop Programming / Windows Forms

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

Rate me:
Please Sign up or sign in to vote.
4.67/5 (12 votes)
6 Jul 2011CPOL5 min read 110.1K   8.9K   54   16
Download historical and real time prices from Google Finance using an alternative method to the "Download to spreadsheet" link in the "Historical prices" page.

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.

Sample Image - maximum width is 600 pixels

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

  • 4th July, 2011
    • Initial version.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Spain Spain
I'm a Spanish Telecommunications Engineer. I currently work developing software applications in Java although I'm very interested in Microsoft's technologies.

Comments and Discussions

 
QuestionI NEED THE SETUP(EXE) FILE OF THIS DEMO TOOL Pin
BAGAABHISHEK3-Oct-17 22:09
BAGAABHISHEK3-Oct-17 22:09 
AnswerRe: I NEED THE SETUP(EXE) FILE OF THIS DEMO TOOL Pin
OriginalGriff3-Oct-17 22:11
mveOriginalGriff3-Oct-17 22:11 
GeneralRe: I NEED THE SETUP(EXE) FILE OF THIS DEMO TOOL Pin
BAGAABHISHEK3-Oct-17 22:28
BAGAABHISHEK3-Oct-17 22:28 
GeneralRe: I NEED THE SETUP(EXE) FILE OF THIS DEMO TOOL Pin
OriginalGriff3-Oct-17 22:44
mveOriginalGriff3-Oct-17 22:44 
Praise6 years after you wrote it and it's still excellent! Great job Pin
Gonzalo Rodriguez17-May-17 17:42
Gonzalo Rodriguez17-May-17 17:42 
QuestionCheck out YaMasuta.com - A better engine with Algorithmic trading engine Pin
baseldayyani23-Apr-15 19:34
baseldayyani23-Apr-15 19:34 
GeneralMy vote of 5 Pin
Mike Magill27-Jun-14 5:56
Mike Magill27-Jun-14 5:56 
Questiondownload Pin
ritarita19177-Jun-14 7:42
ritarita19177-Jun-14 7:42 
QuestionI clicked the 5 stars before writing, "thanks for your amazing article!" Pin
thready31-Mar-14 14:46
thready31-Mar-14 14:46 
QuestionHow to run demo Pin
Member 34314363-Feb-14 22:44
Member 34314363-Feb-14 22:44 
AnswerRe: How to run demo Pin
Juan1R24-Feb-14 22:45
Juan1R24-Feb-14 22:45 
QuestionCould this tool be extended to download real time quotes from google finance and store it in Amibroker Pin
Raj23230-Nov-13 21:08
Raj23230-Nov-13 21:08 
Hello Juan1R,

Firstly, thank you or the good work you are doing already !

I'm looking for a tool/program/software that connects to Amibroker (http://amibroker.in) using any software/code/script (DDE/C#/Excel or whatever) to retrieve real-time stock quotes from google finance that is updated every minute ( or maybe 2 minutes).

So basically there are paid softwares to do the job, but i'm looking to see if anyone can provide a free interface.

[There is a software DataFeeder from stocklive.in which is free and downloads data from yahoo finance and feeds it to amibroker (i.e. writes the stock quote minute by minute it into the amibroker database/txt file. Amibroker updates the chart as soon as it find the new info automatically). However, Yahoo quotes are 10-15 delayed and therefore looking for an alternative from google finance.

Thanks in advance.
Raj
AnswerRe: Could this tool be extended to download real time quotes from google finance and store it in Amibroker Pin
Juan1R1-Dec-13 5:47
Juan1R1-Dec-13 5:47 
QuestionSimple C# DLL Pin
Sandeep P Sawant2-Aug-13 21:30
Sandeep P Sawant2-Aug-13 21:30 
Questiongetting more data out of the url? Pin
Thinksamuel16-Nov-12 10:19
Thinksamuel16-Nov-12 10:19 
QuestionPrice Delays Pin
George Swan8-Jul-11 22:00
mveGeorge Swan8-Jul-11 22:00 
AnswerRe: Price Delays Pin
Juan1R9-Jul-11 2:03
Juan1R9-Jul-11 2:03 
GeneralMy vote of 5 Pin
liu32601@hotmail.com6-Jul-11 20:20
liu32601@hotmail.com6-Jul-11 20:20 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.