R Importing excel file directly from web

3.3k views Asked by At

I need to import excel file directly from NYSE website. The spreadsheet url is https://quotespeed.morningstar.com/exportChartDataToExcel.jsp?tickers=AAPL&symbols=126.1.AAPL&st=1980-12-1&ed=2015-6-8&f=m&dty=1&types=1&ver=1.6.0&qs_wsid=E43474CC03753FE0E777D89877788ECB . Tried using gdata package and changing https to http but still doesnt work. Does anybody know solution to such issue?

EDIT: Has to be imported to R directly from website (project requirement)

2

There are 2 answers

3
Therkel On BEST ANSWER

Without information about why using the gdata package does not work for you I have to assume. Make sure you have Perl installed - you can download it at http://www.activestate.com/activeperl

This works for me:

library('gdata')

## URL broken into multiple lines for readability
url <- paste("https://quotespeed.morningstar.com/exportChartDataToExcel.",
    "jsp?tickers=AAPL&symbols=126.1.AAPL&st=1980-12-1&ed=2015-",
    "6-8&f=m&dty=1&types=1&ver=1.6.0&qs_wsid=E43474CC03753FE0E",
    "777D89877788ECB", sep = "")
url <- gsub("https", "http",url)
data <- read.xls(url, perl = "C:/Perl64/bin/perl.exe")

Without perl = "path_to_perl.exe" I got the error

Error in findPerl(verbose = verbose) : 
  perl executable not found. Use perl= argument to specify the correct path.
Error in file.exists(tfn) : invalid 'file' argument
5
DeanAttali On

Use the RCurl package to download the file and the readxl package by Hadley to read the excel file