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"))

19 Responses to “Screencast: Downloading Return Data from Yahoo! Finance using R”

  1. 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

  2. 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

  3. 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!

  4. 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

  5. > # 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

  6. 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

    🙂

  7. I followed the instructions but got :

    data frame with 0 columns and 12 rows???

    Please clarify 🙁

  8. 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.

  9. 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

  10. 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

  11. Just to thank you for the script. Very handy. Well done.
    best regards,
    B.

  12. 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!

  13. 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!

  14. 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”
    >

  15. […] 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 …… […]

Leave a Reply

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

(required)

(required)