For Loop in R import multiple CSV from URL

Question

I am importing 500 csv's that have the following similar format:

"https://www.quandl.com/api/v3/datasets/WIKI/stockname/data.csv?column_index=11&transform=rdiff&api_key=keyname"

Where stockname is the ticker symbol of a single stock. I have the list of stock tickers saved in a dataframe called stocklist.

I'd like to use lapply to iterate through my list of stocks. Here's what I have so far:

lst <- lapply(stocklist, function(i){
  url <- paste0("https://www.quandl.com/api/v3/datasets/WIKI/",i,"/data.csv?column_index=11&transform=rdiff&api_key=XXXXXXXXXXXXXXX")
  spdata <- read.csv(url, stringsAsFactors = FALSE)

})

I get the following error:

Error in file(file, "rt") : invalid 'description' argument

What could be causing this error? I tried using a for loop as well, but was unsuccessful and I have been told lapply is a better method in R for this type of task.

Edit:

Stucture of stocklist:

> dput(droplevels(head(stocklist)))
structure(list(`Ticker symbol` = c("MMM", "ABT", "ABBV", "ABMD", 
"ACN", "ATVI")), .Names = "Ticker symbol", row.names = c(NA, 
6L), class = "data.frame")

Second Edit (solution):

stockdata<-lapply(paste0("https://www.quandl.com/api/v3/datasets/WIKI/",stocklist[1][[1]],"/data.csv?column_index=11&transform=rdiff&api_key=XXXXXXX"),read.csv,stringsAsFactors=FALSE)

Add names to stockdata:

names(stockdata)<-stocklist[1][[1]]

Hard to tell what's going on without an API key of my own. Have you tried using the `quandl` package directly? — Gregor Thomas, Jul 05 '18 at 20:42
I did try the quandl package, but was getting an error. It seems to be a firewall issue. — user6883405, Jul 05 '18 at 20:50
Can you post how that URL renders? If you access it, does it prompt you to download or view in browser? — Parfait, Jul 05 '18 at 20:52
`lapply(paste0(....,stocklist,....),read.csv,stringAsFactors=F)` since paste is vectorized — Onyambu, Jul 05 '18 at 20:53
@Parfait URL renders to download if used in browser. In R, read.csv('https://www.quandl.com/api/v3/datasets/WIKI/ticker/data.csv?column_index=11&transform=rdiff&api_key=xxxxxxxx', stringsAsFactors = FALSE) is a column of dates and close prices. — user6883405, Jul 05 '18 at 20:57
@Onyambu I get Error in read.table(file = file, header = header, sep = sep, quote = quote, : unused argument (stringAsFactors = FALSE) when I try that — user6883405, Jul 05 '18 at 20:58
@Onyambu I get an error: Error in file(file, "rt") : cannot open the connection In addition: Warning message: In file(file, "rt") : cannot open URL 'https://www.quandl.com/api/v3/datasets/WIKI/c(314, 7, 5, 8, 52, 58)/data.csv?column_index=11&transform=rdiff&api_key=xxxxxx': HTTP status was '400 Bad Request' I tried those individual stocks it has the HTTP status for and it works. — user6883405, Jul 05 '18 at 21:06
Please post a sample of your `stocklist` object. `dput(droplevels(head(stocklist)))` would be great. — Gregor Thomas, Jul 05 '18 at 21:16
Okay then, I think the comments here are spot on. You don't want to iterate over your data frame, you want to iterate over the column `"Ticker symbol"`. Try `lapply(stocklist[["Ticker symbol"]], ...)`. You might even want to try creating the URLs and iterating over them. This will let you see what they are which could help debug - `url_list = paste0("https://...", stocklist[["Ticker symbol"]], "/data/..."), then inspect and check a few entries of `url_list` before `spdata = lapply(url_list, read.csv, stringsAsFactors = FALSE)` — Gregor Thomas, Jul 12 '18 at 15:37

score 1 · Accepted Answer · answered Jul 05 '18 at 20:43

1

I believe your 'i' variable is a vector.

Make sure you are sub-scripting it properly and only passing one stock at a time.

This: i would look something like this: i[x]

answered Jul 05 '18 at 20:43

Kapocsi

922
6
17

Excellent point. OP says "a data frame called stocklist". So `lapply(stocklist, ...)` means that `i` is a column of the data frame. OP probably wants to `lapply` over a single column, not across columns. – Gregor Thomas Jul 05 '18 at 20:45
That's correct. Stocklist is a column of tickers, with a heading. – user6883405 Jul 05 '18 at 20:49
1

Although this isn't the explicit solution I used, it highlights the fundamental issue of subscripting and calling the correct element. – user6883405 Jul 12 '18 at 16:32

score 0 · Answer 2 · answered Jul 18 '18 at 11:37

I can't tell what i is, but I'm guessing it's a number. Can you try this and see if it works for you? I think it's pretty close. Just make sure the URL matches the pattern of the actual URL you are fetching data from. I tried to find it; I couldn't find it.

seq <- c(1:10)

for (value in seq) {

mydownload <- function (start_date, end_date) {
  start_date <- as.Date(start_date)  ## convert to Date object
  end_date <- as.Date(end_date)  ## convert to Date object
  dates <- as.Date("1970/01/01") + (start_date : end_date)  ## date sequence
  ## a loop to download data
  for (i in 1:length(dates)) {
    string_date <- as.character(dates[i])
    myfile <- paste0("C:/Users/Excel/Desktop/", string_date, ".csv")
    string_date <- gsub("-", "-", string_date)  ## replace "-" with "/"
    myurl <- paste("https://www.quandl.com/api/v3/datasets/WIKI/", i, "/data.csv?column_index=11&transform=rdiff&api_key=xxxxxxxxxx", sep = "")
    download.file(url = myurl, destfile = myfile, quiet = TRUE)
    }
  }

}

For Loop in R import multiple CSV from URL

2 Answers2