0

I have seen many posts on here about using read.xls with a url and they all worked on my Mac, but now when I am trying to use the code on my Windows computer, it is not working. I used the below code on my Mac:

tmp <- tempfile()
download.file("https://www.spdrs.com/site-content/xls/SPY_All_Holdings.xls?fund=SPY&docname=All+Holdings&onyx_code1=1286&onyx_code2=1700", destfile = tmp, method = "curl")
SPY <- read.xls(tmp, skip=3)

unlink(tmp)

Using "curl" no longer woks ("had status 127" is the warning message) and when I try "internal" or "wininet", it says " formal argument "method" matched by multiple actual arguments". When I try read.xls, it says the file is "missing" and "invalid". I have downloaded Perl, Java, gdata, Rcurl and the "downloader" package (because I heard that works better with https) and could use that instead....Is there something else I would have to do on a Windows computer to make this code work?

Thanks!

Mel
  • 57
  • 1
  • 8
  • `data<- read.xls(file.choose(),perl="C:/Perl64/bin/perl.exe")` Locate your `perl.exe` file in the `perl` argument of `read.xls` function. For `read.xls()` function you need to install `gdata()` package in R. Setting up the environment variables for perl.exe path will also help. – Sowmya S. Manian Jun 08 '16 at 14:37
  • Give us the command till now you tried for WIndows machine and its output or the error it throws.. – Sowmya S. Manian Jun 08 '16 at 14:40
  • I saw this link..See if this helps: http://stackoverflow.com/questions/21738463/importing-excel-file-using-url-using-read-xls – Sowmya S. Manian Jun 08 '16 at 14:43
  • @SowmyaS.Manian I saw that question and assumed that meant manually changing https to http. Ideally, I am trying to make this whole process automatic – Mel Jun 08 '16 at 14:46
  • Error in xls2sep(xls, sheet, verbose = verbose, ..., method = method, : Intermediate file 'C:\Users\EMPIRI~1\AppData\Local\Temp\Rtmpast5TE\file1b705d3735da.csv' missing! In addition: Warning message: running command '"C:\Perl64\bin\perl.exe" "C:/Users/Empirical/Documents/R/win-library/3.3/gdata/perl/xls2csv.pl" "C:\Users\EMPIRI~1\AppData\Local\Temp\Rtmpast5TE\file1b7037997de6" "C:\Users\EMPIRI~1\AppData\Local\Temp\Rtmpast5TE\file1b705d3735da.csv" "1"' had status 255 Error in file.exists(tfn) : invalid 'file' argument (when I do not specify a method, I can at least download) – Mel Jun 08 '16 at 15:03
  • 1
    http://stackoverflow.com/questions/24165623/read-excel-file-into-r-with-xlconnect-package-from-url The code found here works! – Mel Jun 08 '16 at 15:20
  • Oops just now finished trying and posted, but its too lengthy. I wanted you to confirm first if its working. I will remove my answer then instead. – Sowmya S. Manian Jun 08 '16 at 15:29

1 Answers1

0
> library(RCurl)
> library(XLConnect)
> URL <- "https://www.spdrs.com/site-content/xls/SPY_All_Holdings.xls?fund=SPY&docname=All+Holdings&onyx_code1=1286&onyx_code2=1700"
> f = CFILE("SPY_All_Holdings.xls", mode="wb")
> curlPerform(url = URL, writedata = f@ref, ssl.verifypeer = FALSE)
# OK 
# 0 
> close(f)
# An object of class "CFILE"
# Slot "ref":
# <pointer: (nil)>

> out <- readWorksheetFromFile(file = "SPY_All_Holdings.xls",sheet="SPY_All_Holdings")
> head(out)
#           Fund.Name. SPDR..S.P.500..ETF     Col3                   Col4        Col5
# 1          Ticker Symbol:                SPY     <NA>                   <NA>         <NA>
# 2               Holdings:   As of 06/06/2016     <NA>                   <NA>         <NA>
# 3                    Name         Identifier   Weight                 Sector  Shares Held
# 4              Apple Inc.               AAPL 2.945380 Information Technology 54545070.000
# 5   Microsoft Corporation               MSFT 2.220684 Information Technology 77807630.000
# 6 Exxon Mobil Corporation                XOM 1.998224                 Energy 40852760.000
Sowmya S. Manian
  • 3,723
  • 3
  • 18
  • 30