Update: Using Quandl’s API
Because everything I write about breaks, the Google Finance API stopped taking requests at this URL. It only returns a year’s worth of daily data as of the time of writing.
We will move on to Quandl. They have a stable key-driven API that doesn’t seem to be going anywhere. Python code for Quandl coming soon. R code below.
Users will need to visit Quandl’s website and sign up for an API key to access the data.
Python Code
Users will need install the Quandl library from pip to use the script with: pip install quandl.
import quandl
import datetime
quandl.ApiConfig.api_key = 'your_api_key'
def quandl_stocks(symbol, start_date=(2000, 1, 1), end_date=None):
"""
symbol is a string representing a stock symbol, e.g. 'AAPL'
start_date and end_date are tuples of integers representing the year, month,
and day
end_date defaults to the current date when None
"""
query_list = ['WIKI' + '/' + symbol + '.' + str(k) for k in range(1, 13)]
start_date = datetime.date(*start_date)
if end_date:
end_date = datetime.date(*end_date)
else:
end_date = datetime.date.today()
return quandl.get(query_list,
returns='pandas',
start_date=start_date,
end_date=end_date,
collapse='daily',
order='asc'
)
if __name__ == '__main__':
apple_data = quandl_stocks('AAPL')
print(apple_data)
R Code
Users will need to download the Quandl package from CRAN to run this using: install.packages(‘Quandl’).
Credit to GitHub user johnatasjmo for this solution:
# Quandl package must be installed
library(Quandl)
# Get your API key from quandl.com
quandl_api = "MYAPIKEY"
# Add the key to the Quandl keychain
Quandl.api_key(quandl_api)
quandl_get <-
function(sym, start_date = "2017-01-01") {
require(devtools)
require(Quandl)
# create a vector with all lines
tryCatch(Quandl(c(
paste0("WIKI/", sym, ".8"), # Adj. Open
paste0("WIKI/", sym, ".9"), # Adj. High
paste0("WIKI/", sym, ".10"), # Adj. Low
paste0("WIKI/", sym, ".11"), # Adj. Close
paste0("WIKI/", sym, ".12")), # Adj. Volume
start_date = start_date,
type = "zoo"
))
}
Original Post
In one of my most popular posts, Download Price History for Every S&P 500 Stock, other traders and I despaired over the death of the Yahoo! Finance API. After batting around a lot of potential replacements, I was still left searching for a good free source of data to use for education and retail trading. I found the answer by searching through the R package quantmod, which was successfully downloading data from Google despite this message on developers.google.com/finance/.
It was Hidden!
As it turns out, quantmod was using a hidden Google Finance API that was quite easy to reverse engineer. In this post, we will build functions for accessing that API in both R and Python. For readers of my book, Automated Trading with R, this will serve as a replacement for the often-referenced yahoo() function, but not as a perfect replacement.
R Code
# Make sure data.table is installed
if(!'data.table' %in% installed.packages()[,1]) install.packages('data.table')
# Function to fetch google stock data
google_stocks <- function(sym, current = TRUE, sy = 2005, sm = 1, sd = 1, ey, em, ed)
{
# sy, sm, sd, ey, em, ed correspond to
# start year, start month, start day, end year, end month, and end day
# If TRUE, use the date as the enddate
if(current){
system_time <- as.character(Sys.time())
ey <- as.numeric(substr(system_time, start = 1, stop = 4))
em <- as.numeric(substr(system_time, start = 6, stop = 7))
ed <- as.numeric(substr(system_time, start = 9, stop = 10))
}
require(data.table)
# Fetch data from google
google_out = tryCatch(
suppressWarnings(
fread(paste0("http://www.google.com/finance/historical",
"?q=", sym,
"&startdate=", paste(sm, sd, sy, sep = "+"),
"&enddate=", paste(em, ed, ey, sep = "+"),
"&output=csv"), sep = ",")),
error = function(e) NULL
)
# If successful, rename first column
if(!is.null(google_out)){
names(google_out)[1] = "Date"
}
return(google_out)
}
# Test it out
apple_data = google_stocks('AAPL')
Giving this a quick plot, we can see it is working.
Python Code
See the below Python code that accomplishes the same thing using the pandas, io, requests, and time modules. Many people will not have requests or pandas installed by default, so check your package managers if need be.
import pandas as pd
import io
import requests
import time
def google_stocks(symbol, startdate = (1, 1, 2005), enddate = None):
startdate = str(startdate[0]) + '+' + str(startdate[1]) + '+' + str(startdate[2])
if not enddate:
enddate = time.strftime("%m+%d+%Y")
else:
enddate = str(enddate[0]) + '+' + str(enddate[1]) + '+' + str(enddate[2])
stock_url = "http://www.google.com/finance/historical?q=" + symbol + \
"&startdate=" + startdate + "&enddate=" + enddate + "&output=csv"
raw_response = requests.get(stock_url).content
stock_data = pd.read_csv(io.StringIO(raw_response.decode('utf-8')))
return stock_data
if __name__ == '__main__':
apple_data = google_stocks('AAPL')
print(apple_data)
apple_truncated = google_stocks('AAPL', enddate = (1, 1, 2006))
print(apple_truncated)
Running this code, we can see the function works correctly with the dates and fetches the data quickly. Popping it into matplotlib look good, too.
Notes on Data Structure
Unlike the Yahoo! Finance API, this will not return the adjusted close as a separate column. In Automated Trading with R, we go to great lengths to use the adjusted close to obtain adjusted open, adjusted high, and adjusted low. The Google Finance API used here returns all of that information for you. So, there is no column named “adjusted close”. All of the data is adjusted in advance.
Advanced users may be disappointed by this, because there is some interesting information regarding splits and dividends to be gained from the disparity between raw and adjusted closing prices. Regardless, most users should be able to use the data returned by this API to accomplish their simulation goals.
It is useless without Yahoo’s Adjusted Close, which means adjusts to dividends, stock splits, etc.
Thanks for bringing this up. I should have noted that this data is adjusted by default.
If you wanted to infer dividends and splits from the Adj Close vs. Close disparity, then you are out of luck here. For all other purposes, it is helpful that the data is adjusted in advance.
Nice work man.
I didn’t know that book I bought was written you. Haven’t read it yet, but now I am looking forward to it!
Thank you!
Sounds like I’m doing a good job finding my audience! Thanks for checking out the book.
Maybe you should look at the Tiingo API. They provide raw and adjusted data for splits and dividends
@RafaelRodrigues
Do you have any idea why the tickers on the Copenhagen exchange are not valid through the API? For example, you can get the historical prices for CPH:WDH and the URI formatting of the ticker becomes CPH%3AWDH. Either way it dosnt work with the current URI, which seems strange to me, because the Frankfurt tickers e.g. ETR:BMW works fine this way.
Oh, i see the CPH tickers are missing the download button, so there is probably some limitation for goole on those, and hence they dont allow historical downloads on those.
You may be right about the CPH tickers. There are no docs, so we are more or less writing them in this comment thread.
Quick note – %3A is the URL encoding for a colon. So the conversion from “:” to “%3A” is expected behavior.
Seems it does not work anymore ???
I am seeing that it works… but ignores all arguments except the stock ticker. It only downloads the most recent year of data. I’ll investigate and update the post to reflect this change if it doesn’t have a workaround.
For some reason the python code is only going back one year . |Has anything changed with the API?
Thanks for the code !
Thanks for letting me know. It seems that this is affecting the whole API. I’ll take a look at how other packages are dealing with this. It may be a permanent change.
The following is now working, however you must download and install
“pandas_datareader”
using conda or pip in the usual way( python 2.7 or 3.6)
import datetime
from pandas_data_reader import data
symbol = ‘MSFT’
start = datetime.datetime(2008, 1, 5) # as example
end = datetime.datetime(2008, 9, 17)
#Unfortunately the google version of the following only returns 1 year:
stock_data = data.get_data_yahoo(symbol = symbol, start , end)
http://finance.google.com/finance/historical?q=AAPL&startdate=Oct+18+2012&enddate=Oct+13+2017&output=csv
Works
Hi i’m writing my master thesis related to automated trading and i found your book really usefull but i can’t go on with my work. In order to download stock prices i’m using the following code
# Get quantmod
if (!require(“quantmod”)) {
install.packages(“quantmod”)
library(quantmod)
}
end<- as.Date(Sys.time())
start<-as.Date("2011-01-01")
getSymbols("AAPL" , src = "google", from = start, to = end)
I hope this may help someone.
ps
@Chris Conlan do you know why i'm getting this error?
"Error in quantile.default(coredata(x), …) : 'probs' outside [0,1]"
Hi Andrea,
See these issues in QuantMod:
https://github.com/joshuaulrich/quantmod/issues/156
https://github.com/joshuaulrich/quantmod/issues/188
I am afraid I can’t resolve that bug without knowing what other code you are running. Feel free to elaborate.
Best,
Chris
Hi,
For hk market stocks, what code need to provide in ‘q’ for retrieve the data?
I tried HKG:941 or 941, in the parameters, both not work.
It work. Thanks for provide the url~~
Hello Chris,
Thank you for your post. I am trying your python code locally on my computer. I am getting an encoding error at the level of:
stock_data = pd.read_csv(io.StringIO(raw_response.decode(‘utf-8’)))
The error is: UnicodeDecodeError: ‘utf-8’ codec can’t decode byte 0xfc in position 18140: invalid start byte
I googled for the error but without any advancement on why I am getting it. Did you get this error before and if yes, how can we fix it?
Thank you
Hi Ines,
The Google Finance API works less and less everyday. It seems like now it isn’t evening processing any requests. I recommend you switch to the Quandl API. Python code has just been posted for it.
Best,
Chris
Hi Chris! Wonderful! Thank you for your quick response and for the updates using Quandl API 🙂 I will definitely have a look at it.
Nice day to you and Kind regards,
Ines.
Hi Chris,
Great script! Two things:
– I had to alter the R script to get the AAPL stock because it looks like the URL changed(?):
fread(paste0(“http://finance.google.com/finance/historical”,
– Is there any way to get Canadian stocks (or to change the exchange)? It seems this only wants to pull from NASDAQ? I tried with just the symbol and adding the exchange and symbol and it doesn’t want to work:
Aurora_data = google_stocks(‘TSE:WEED’) etc…
I realize this has nothing to do with your code, but just curious if you know how to get this from Google. Thanks!
R.
Hello Chris,
Thank you so much for sharing.
By running R code for the Quandl, the error message occurs, would you kindly suggest the solution for this problem?
Thank you so much!
Warning messages:
1: In merge.data.frame(data, merge_data, by = 1, all = TRUE) :
column names ‘WIKI.DIS – ERROR.x’, ‘WIKI.DIS – ERROR.y’ are duplicated in the result
2: In merge.data.frame(data, merge_data, by = 1, all = TRUE) :
column names ‘WIKI.DIS – ERROR.x’, ‘WIKI.DIS – ERROR.y’ are duplicated in the result
I get the same error, except in my case I get another error first:
(1) Error in rval[i, , drop = drop., …] : subscript out of bounds
In addition: Warning messages:
and
(2) the routine trips over already with symbole “ACE”.
I am looking at the rval call now.
After I removed these 20 symbols I got a clean run:
ACE
GAS
ARG
BXLT
BRCM
CVC
CAM
CPGX
EMC
GMCR
MHFI
POM
PCL
PCP
SNDK
HOT
TE
TWC
TYC
I am getting the following response:
Sorry… body { font-family: verdana, arial, sans-serif; background-color: #fff; color: #000; }GoogleSorry…We’re sorry…… but your computer or network may be sending automated queries. To protect our users, we can’t process your request right now.See Google Help for more information.Google Home
Hi Alejandro! I’m in the same situation……Have you already solved this issue? Best
The Google Finance API no longer works. Since they re-purposed the URL for customer-facing (as opposed to developer facing) use, they are complaining that you are sending automated queries. They detect this from the fact that your user-agent doesn’t appear to be from a common web browser.
thank you very much! 🙂
I’m totally new to this.
while using your code, I’m only getting data till March 27th, 2018.
any idea why this might be happening?
If you’re using the Quandl code, that date corresponds to when Quandl deprecated the API. It was being provided for free by a third party to Quandl. They seem like they will be keeping the existing data online for a while, though.