Reading an online xlsx file into R

Question

I am trying to download spreadsheets from AQR data library into R directly. I have this link: http://www.aqr.com/~/media/files/data-sets/value-and-momentum-everywhere-portfolios-monthly.xlsx which prompts a download. However, when trying the following code:

> url1<-"http://www.aqr.com/~/media/files/data-sets/value-and-momentum-everywhere-portfolios-monthly.xlsx"
> download.file(url1,destfile="example.xlsx")

I get this error

trying URL 'http://www.aqr.com/~/media/files/data-sets/value-and-momentum-everywhere-portfolios-monthly.xlsx'
Error in download.file(url1, destfile = "example.xlsx") : cannot open URL 'http://www.aqr.com/~/media/files/data-sets/value-and-momentum-everywhere-portfolios-monthly.xlsx'

https://www.aqr.com/library/data-sets/value-and-momentum-everywhere-portfolios-monthly is the page from which I am trying to download data(under full set data link).

Could you provide some guidance?

I'm using [1] "R version 3.1.2 (2014-10-31)", could that be the problem? — Jimbo, Jan 19 '15 at 18:45
No, I don't think so. Your version is even newer than mine. :/ — Bonifacio2, Jan 19 '15 at 18:50

score 1 · Answer 1 · answered Jan 19 '15 at 18:31

I'm not quite sure what is causing the problem for you, but the following worked for me:

library(XLConnect)
## 
con <- "http://www.aqr.com/~/media/files/data-sets/value-and-momentum-everywhere-portfolios-monthly.xlsx"
download.file(con,"xlsxFile.xlsx",mode="wb")
##
newWB <- loadWorkbook(
  file="xlsxFile.xlsx",
  create=F)
##
R> getSheets(newWB)
[1] "VME Portfolios" "Definitions"    "Data Sources"   "Disclosures"

and here's a screenshot of the downloaded file:

enter image description here

score 1 · Accepted Answer · edited May 23 '17 at 12:14

1

It looks like that link redirects to https, which download.file does not support by default. If you have wget or curl installed you can use

download.file("https://www.aqr.com/~/media/files/data-sets/value-and-momentum-everywhere-portfolios-monthly.xlsx", 
              "example.xlsx", 
              method = "wget")

or

download.file("https://www.aqr.com/~/media/files/data-sets/value-and-momentum-everywhere-portfolios-monthly.xlsx", 
              "example.xlsx", 
              method = "curl")

These and other options are discussed at Download a file from HTTPS using download.file()

edited May 23 '17 at 12:14

Community

1
1

answered Jan 19 '15 at 19:15

Ista

10,139
2
37
38

Thanks, this finally worked for me. Do you know why for some(like the other answer) they don't need to take this extra step? – Jimbo Jan 19 '15 at 19:36

Reading an online xlsx file into R

2 Answers2

Linked