Google Finance - historical daily data retrieved programmatically

April 16th, 2014, 03:17 PM

Hi,

In this thread, I will propose several ways to retrieve historical data from Google Finance :
- Web Query 1 (from Java code),
- Web Query 2 (from Java code),
- YQL,
- Quantmod (with R).

If you have other or better ideas, do not hesitate to contribute.

Nicolas

April 16th, 2014, 04:46 PM

1) Web Query 1 (and related Java code)

Let's consider the following URL :
https://www.google.com/finance/historical?q=GOOG&histperiod=daily&startdate=Apr+1+2014&enddate=Apr+15+2014&output=csv

It generates a CSV file for Google stock, with daily data from April 1st to April 15th, 2014

Example of Java code:

Code

import java.io.BufferedReader;
import java.io.InputStreamReader;
import java.net.URL;
import java.net.URLConnection;

// inspired by http://stackoverflow.com/questions/9093000/read-csv-file-from-internet
public class Google_ReadCSVFromWebQuery1 {

    public static void main(String[] args) {
        try {
            URL url = new URL("http://www.google.com/finance/historical?q=GOOG&histperiod=daily&startdate=Apr+1+2014&enddate=Apr+15+2014&output=csv");
            URLConnection urlConn = url.openConnection();
            InputStreamReader inputStreamReader = new InputStreamReader(urlConn.getInputStream());
            BufferedReader bufferedReader = new BufferedReader(inputStreamReader);
            String line;
            while ((line = bufferedReader.readLine()) != null) {
                System.out.println(line);
            }
            bufferedReader.close();
            inputStreamReader.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

It generates the following output:

Quoting

Date,Open,High,Low,Close,Volume
15-Apr-14,536.82,538.45,518.46,536.44,3847453
14-Apr-14,538.25,544.10,529.56,532.52,2568020
11-Apr-14,532.55,540.00,526.53,530.60,3916171
10-Apr-14,565.00,565.00,539.90,540.95,4027743
9-Apr-14,559.62,565.37,552.95,564.14,3324742
8-Apr-14,542.60,555.00,541.61,554.90,3152406
7-Apr-14,540.74,548.48,527.15,538.15,4389569
4-Apr-14,574.65,577.77,543.00,543.14,6377658
3-Apr-14,569.85,587.28,564.13,569.74,5087530
2-Apr-14,570.38,571.83,561.44,566.98,2088804
1-Apr-14,559.57,568.18,558.44,566.88,7932

April 16th, 2014, 04:52 PM

2) Web Query 2 (and related Java code)

It appears that the above URL does not allow generating a csv file for all types of securities.

In particular, it does not seem to work for S&P 500 (INDEXSP:.INX)

Fortunately, another URL is available:
https://www.google.com/finance/getprices?q=.INX&x=INDEXSP&i=86400&p=12d&f=d,c,v,k,o,h,l&df=cpct&auto=0&ei=Ef6XUYDfCqSTiAKEMg

References on this URL format:
Simple C# DLL to download data from Google Finance using an alternative, undocumented method - CodeProject
Google's Undocumented Finance API
Google Realtime Intraday Backfill Data
https://trading.cheno.net/downloading-google-intraday-historical-data-with-python/

It generates the following Web page:

Quoting

EXCHANGE%3DINDEXSP
MARKET_OPEN_MINUTE=570
MARKET_CLOSE_MINUTE=960
INTERVAL=86400
COLUMNS=DATE,CLOSE,HIGH,LOW,OPEN,VOLUME,CDAYS
DATA=
TIMEZONE_OFFSET=-240
a1396382400,1885.52,1885.84,1873.96,1873.96,517337094,1
1,1890.9,1893.17,1883.79,1886.61,465008732,1
2,1888.77,1893.8,1882.65,1891.43,486951242,1
3,1865.09,1897.28,1863.26,1890.25,638969604,1
6,1845.04,1864.04,1841.48,1863.92,658210321,1
7,1851.96,1854.95,1837.49,1845.48,552300967,1
8,1872.18,1872.43,1852.38,1852.64,528286495,1
9,1833.08,1872.53,1830.87,1872.28,636878418,1
10,1815.69,1835.07,1814.36,1830.65,662182271,1
13,1830.61,1834.19,1815.8,1818.18,524945814,1
14,1842.98,1844.02,1816.29,1831.45,591857217,1

Example of Java code:

Code

import java.io.BufferedReader;
import java.io.InputStreamReader;
import java.net.URL;
import java.net.URLConnection;

// inspired by http://stackoverflow.com/questions/9093000/read-csv-file-from-internet
public class Google_ReadCSVFromWebQuery2 {

    public static void main(String[] args) {
        try {
            URL url = new URL("https://www.google.com/finance/getprices?q=.INX&x=INDEXSP&i=86400&p=12d&f=d,c,v,k,o,h,l&df=cpct&auto=0&ei=Ef6XUYDfCqSTiAKEMg");
            URLConnection urlConn = url.openConnection();
            InputStreamReader inputStreamReader = new InputStreamReader(urlConn.getInputStream());
            BufferedReader bufferedReader = new BufferedReader(inputStreamReader);
            String line;
            while ((line = bufferedReader.readLine()) != null) {
                System.out.println(line);
            }
            bufferedReader.close();
            inputStreamReader.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

... and related output:

Quoting

EXCHANGE%3DINDEXSP
MARKET_OPEN_MINUTE=570
MARKET_CLOSE_MINUTE=960
INTERVAL=86400
COLUMNS=DATE,CLOSE,HIGH,LOW,OPEN,VOLUME,CDAYS
DATA=
TIMEZONE_OFFSET=-240
a1396382400,1885.52,1885.84,1873.96,1873.96,517337094,1
1,1890.9,1893.17,1883.79,1886.61,465008732,1
2,1888.77,1893.8,1882.65,1891.43,486951242,1
3,1865.09,1897.28,1863.26,1890.25,638969604,1
6,1845.04,1864.04,1841.48,1863.92,658210321,1
7,1851.96,1854.95,1837.49,1845.48,552300967,1
8,1872.18,1872.43,1852.38,1852.64,528286495,1
9,1833.08,1872.53,1830.87,1872.28,636878418,1
10,1815.69,1835.07,1814.36,1830.65,662182271,1
13,1830.61,1834.19,1815.8,1818.18,524945814,1
14,1842.98,1844.02,1816.29,1831.45,591857217,1

April 16th, 2014, 05:05 PM

3) YQL

Google Finance is supposed or was supposed to work with YQL, similarly to Yahoo Finance (

Yahoo Finance - historical daily data retrieved programmatically
2) YQL and related Java code

According to Yahoo, "The Yahoo Query Language is an expressive SQL-like language that lets you query, filter, and join data across Web services. With YQL, apps run faster with fewer lines of code and a smaller network …

)

References:
Get Stock Data from Google Finance and YQL | Jarloo

However, it does not seem to work anymore. Results within generated XML are void.

This problem is also evoked here: https://developer.yahoo.com/forum/YQL/Google-Blocking-YQL-requests-/1337701968896-b5344530-0a0f-4abe-aa56-9aa86b5c577f

April 16th, 2014, 05:14 PM

4) R's quantmod

The following R code

Code

library(quantmod)

goog <- getSymbols("GOOG", src="google", from=as.Date("2014-4-1") , to=as.Date("2014-4-15"), auto.assign=FALSE)
print(goog)

returns:

Quoting

GOOG.Open GOOG.High GOOG.Low GOOG.Close GOOG.Volume
<NA> 559.57 568.18 558.44 566.88 7932
<NA> 570.38 571.83 561.44 566.98 2088804
<NA> 569.85 587.28 564.13 569.74 5087530
<NA> 574.65 577.77 543.00 543.14 6377658
<NA> 540.74 548.48 527.15 538.15 4389569
<NA> 542.60 555.00 541.61 554.90 3152406
<NA> 559.62 565.37 552.95 564.14 3324742
<NA> 565.00 565.00 539.90 540.95 4027743
<NA> 532.55 540.00 526.53 530.60 3916171
<NA> 538.25 544.10 529.56 532.52 2568020
<NA> 536.82 538.45 518.46 536.44 3847453

Unfortunately, it does not seem to work for S&P 500 (INDEXSP:.INX)

This is the end of this series.

Nicolas