In this screencast, I demonstrate how to use R to download historical price data from Yahoo! Finance. I also demonstrate how to convert the price data series into a return data series, and I show how to write the output to a file which can be read into a spreadsheet program such as Excel or Google Docs Spreadsheet.
I use the return data that this script generates to run CAPM and Fama-French regressions and to compute performance metrics such as Sharpe Ratios. However, I note in the presentation that I have found that Yahoo! Finance frequently has errors in the historical price series, so users should use caution when using the return data generated from these historical prices to make financial decisions.
The code used in this screencast is pasted below, and the slides used in the screencase are available here.
# Goal: download adjusted price data for selected security, convert to returns, and write to output file
library(tseries)
# Load security data from Yahoo! Finance
prices1 <- get.hist.quote("SPY", quote="Adj", start="2005-12-25", retclass="zoo")
prices1 <- na.locf(prices1) # Copy last traded price when NA
# To make week end prices:
nextfri.Date <- function(x) 7 * ceiling(as.numeric(x - 1)/7) + as.Date(1)
weekly.prices <- aggregate(prices, nextfri.Date,tail,1)
# To convert month end prices:
monthly.prices <- aggregate(prices1, as.yearmon, tail, 1)
# Convert weekly prices into weekly returns
r.weekly <- diff(log(weekly.prices)) # convert prices to log returns
r1.weekly <- exp(r.weekly)-1 # back to simple returns
# Convert monthly prices into monthly returns
r.monthly <- diff(log(monthly.prices)) # convert prices to log returns
r1.monthly <- exp(r.monthly)-1 # back to simple returns
# Write output data to csv file
write.zoo(r1.weekly, file="weekly.csv",sep=",",col.names=c("Dates","Percent Return"))
write.zoo(r1.monthly, file="monthly.csv",sep=",",col.names=c("Dates","Percent Return"))
That’s interesting, I’ve just started experimenting with R and this is pretty useful. Thanks!
I’ve previously used VBA to import Yahoo stock quotes into Excel
Thanks for the feedback! I’ll check out your VBA import method as well.
You may also want to see this tool for downloading forex historical rates into Excel.
You should be able to do this in R as well. Just examine the VBA and examine how I construct the URL for the web query
Dear all,
I am working with share prices from which I calculate the monthly returns of companies for a period of 5 years. The formula I use is (S2-S1)/S1, where S2 is let’s say the stock price for February and S1 is the stock price for January.
In general, the returns deviate between -/+1. However, I have big positive outleirs in occasional months (returns of 20, 99, 199). I see big spikes in the respective share prices (from USD 1.4 in January to USD 15 in February. This is will make my portfolio biased and the returns of the whole portfolio of companies.
I know that outliers are normal due to various corporate events, but do you know what could be value of a reasonable positive outlier? Do you know a solution how to eliminate the outliers?
Thank you very much in advance!
Dear Sir,
I am a beginner with R. I have followed all the steps of your video and done all exactly as you explained.
Upon running the code in R I received the following information:
> # Goal: download adjusted price data for selected security, convert to returns, and write to output file
>
> library(tseries)
Loading required package: quadprog
Loading required package: zoo
Attaching package: ‘zoo’
The following object(s) are masked from ‘package:base’:
as.Date, as.Date.numeric
‘tseries’ version: 0.10-27
‘tseries’ is a package for time series analysis and computational
finance.
See ‘library(help=”tseries”)’ for details.
>
> # Load security data from Yahoo! Finance
> prices1
> prices1
> # To make week end prices:
> nextfri.Date weekly.prices
> # To convert month end prices:
> monthly.prices
> # Convert weekly prices into weekly returns
> r.weekly r1.weekly
> # Convert monthly prices into monthly returns
> r.monthly r1.monthly
> # Write output data to csv file
> write.zoo(r1.weekly, file=”weekly.csv”,sep=”,”,col.names=c(“Dates”,”Percent Return”))
Error in as.data.frame(x) : object ‘r1.weekly’ not found
> write.zoo(r1.monthly, file=”monthly.csv”,sep=”,”,col.names=c(“Dates”,”Percent Return”))
>
>
Could you please help me in finding a solution for the issue
Thank you very much
Can you email me the log of your output? (calcinv@calculatinginvestor.com). The log you have posted seems to be missing the assignment operators “<-" and everything to the right of the assignment operators. Thanks, Chad
> # Goal: download adjusted price data for selected security, convert to returns, and write to output file
>
> library(tseries)
>
> # Load security data from Yahoo! Finance
> prices1
> prices1
> # To make week end prices:
> nextfri.Date weekly.prices
> # To convert month end prices:
> monthly.prices Dear Sir,
I am trying to run your script in R, but I am running into some issues. I am getting errors (See below). Any ideas on how I can solve them.
Much appreciated.
🙂
> # Convert weekly prices into weekly returns
> r.weekly r1.weekly
> # Convert monthly prices into monthly returns
> r.monthly r1.monthly <- exp(r.monthly)-1 # back to simple returns
Never mind, I solved it. Looks like I had to change the following:
weekly.prices <- aggregate(prices, nextfri.Date,tail,1)
to
weekly.prices <- aggregate(prices1, nextfri.Date,tail,1)
Thanks
🙂
I followed the instructions but got :
data frame with 0 columns and 12 rows???
Please clarify 🙁
very useful script
i got error on function na.locf()
should it be an argument in get.hist.quote?
maybe na.omit is sufficient if you’re dealing with securities with the same missing dates?
I’ll take a look. The “get.hist.quote” function was recently broken because the Yahoo! link changed, and I haven’t updated the script. I was hoping that the function would be updated. Maybe it has and the parameters have changed.
I get the error : Error in aggregate.zoo(prices1, as.yearmon, tail, 1) :
object ‘as.yearmon’ not found
Is there any package that I need to install for using as.yearmon? I have installed the ‘zoo’ package and ‘tseries’ already
Hello,
this is pretty useful. I am trying to figure out however, how you can load data for multiple securities, rather than just for one.
So, I’m wandering how the following needs to be changed
prices1 <- get.hist.quote("SPY", quote="Adj", start="2005-12-25", retclass="zoo")
so that I get data both for SPY and for AGZD for example. Any help will be appreciated
Hi, thanks for the feedback.
I showed how to download returns for multiple securities in a later post:
http://www.calculatinginvestor.com/2013/09/19/downloading-batch-returns/
-Chad
Just to thank you for the script. Very handy. Well done.
best regards,
B.
Don’t know if you will reply me, but I used your script and got this
>
> # Load security data from Yahoo! Finance
> prices1
> prices1
> # To make week end prices:
> nextfri.Date weekly.prices
> # To convert month end prices:
> monthly.prices
> # Convert weekly prices into weekly returns
> r.weekly r1.weekly
> # Convert monthly prices into monthly returns
> r.monthly r1.monthly
> # Write output data to csv file
> write.zoo(r1.weekly, file=”weekly.csv”,sep=”,”,col.names=c(“Dates”,”Percent Return”))
Error: could not find function “write.zoo”
> write.zoo(r1.monthly, file=”monthly.csv”,sep=”,”,col.names=c(“Dates”,”Percent Return”))
Error: could not find function “write.zoo”
>
I already installed zoo and tseries,but R still can’t find the functions. Please help!
Don’t know if you will reply me, but I used your script and got this
> # Load security data from Yahoo! Finance
> prices1
> prices1
> # To make week end prices:
> nextfri.Date weekly.prices
> # To convert month end prices:
> monthly.prices
> # Convert weekly prices into weekly returns
> r.weekly r1.weekly
> # Convert monthly prices into monthly returns
> r.monthly r1.monthly
> # Write output data to csv file
> write.zoo(r1.weekly, file=”weekly.csv”,sep=”,”,col.names=c(“Dates”,”Percent Return”))
Error: could not find function “write.zoo”
> write.zoo(r1.monthly, file=”monthly.csv”,sep=”,”,col.names=c(“Dates”,”Percent Return”))
Error: could not find function “write.zoo”
>
I already installed zoo and tseries,but R still can’t find the functions. Please help!
Sorry, didn’t post the right thing previously. This is what I got.
> # Load security data from Yahoo! Finance
> prices1
> prices1
> # To make week end prices:
> nextfri.Date weekly.prices
> # To convert month end prices:
> monthly.prices
> # Convert weekly prices into weekly returns
> r.weekly r1.weekly
> # Convert monthly prices into monthly returns
> r.monthly r1.monthly
> # Write output data to csv file
> write.zoo(r1.weekly, file=”weekly.csv”,sep=”,”,col.names=c(“Dates”,”Percent Return”))
Error: could not find function “write.zoo”
> write.zoo(r1.monthly, file=”monthly.csv”,sep=”,”,col.names=c(“Dates”,”Percent Return”))
Error: could not find function “write.zoo”
>
[…] Screencast: Downloading Return Data … – In this screencast, I demonstrate how to use R to download historical price data from Yahoo! Finance. I also demonstrate how to convert the price data …… […]