Error with BatchGetSymbols in PowerQuery with R

[Anonymous]

I am trying to get stock data using the R library "BatchGetSymbols". 

let
    Source = R.Execute("library(BatchGetSymbols)#(lf)first.date <- Sys.Date()-365#(lf)last.date <- Sys.Date()#(lf)tickers <- c(""ACET"")#(lf)result <- tryCatch(#(lf)  {#(lf)    BatchGetSymbols( tickers, first.date, last.date)#(lf)  },#(lf)  error=function(cond) {#(lf)    return(BatchGetSymbols( ""AAPL"", last.date - 1, last.date))#(lf)  },#(lf)  warning=function(cond) {#(lf)    return(BatchGetSymbols( ""AAPL"", last.date - 1, last.date))#(lf)  },#(lf)  finally={#(lf)  }#(lf))#(lf)#(lf)result <- as.data.frame(result$df.tickers)"),
    result1 = Source{[Name="result"]}[Value]
in
    result1

The R code repeated below for readability

library(BatchGetSymbols)
first.date <- Sys.Date()-365
last.date <- Sys.Date()
tickers <- c("ACET")
result <- tryCatch(
  {
    BatchGetSymbols( tickers, first.date, last.date)
  },
  error=function(cond) {
    return(BatchGetSymbols( "AAPL", last.date - 1, last.date))
  },
  warning=function(cond) {
    return(BatchGetSymbols( "AAPL", last.date - 1, last.date))
  },
  finally={
  }
)
result <- as.data.frame(result$df.tickers)

I know AAPL will never return an error, however, ticker "ACET" returns an error, which is why I'm trying to wrap it with AAPL so that I will be able to filter out the AAPL codes eventually. 

When I run this code, I run into the following error: 

DataSource.Error: ADO.NET: R script error.
Loading required package: rvest
Loading required package: xml2
Loading required package: dplyr

Attaching package: 'dplyr'

The following objects are masked from 'package:stats':

    filter, lag

The following objects are masked from 'package:base':

    intersect, setdiff, setequal, union


Error in charToDate(x) : 
  character string is not in a standard unambiguous format
Calls: tryCatch ... get.clean.data -> as.Date -> as.Date.character -> charToDate
Execution halted

Details:
    DataSourceKind=R
    DataSourcePath=R
    Message=R script error.
Loading required package: rvest
Loading required package: xml2
Loading required package: dplyr

Attaching package: 'dplyr'

The following objects are masked from 'package:stats':

    filter, lag

The following objects are masked from 'package:base':

    intersect, setdiff, setequal, union


Error in charToDate(x) : 
  character string is not in a standard unambiguous format
Calls: tryCatch ... get.clean.data -> as.Date -> as.Date.character -> charToDate
Execution halted

    ErrorCode=-2147467259
    ExceptionType=Microsoft.PowerBI.Scripting.R.Exceptions.RScriptRuntimeException

How do I address this?

[TomMartens]

Hey @Anonymous ,

I'm not familiar with the BatchGetSymbols package, but running the R script in R GUI (I ever run a final test in RGui before copy & pasting to Power BI) throws error.

Running the R code you provided:

library(BatchGetSymbols) # install.packages("BatchGetSymbols")
first.date <- Sys.Date()-365
last.date <- Sys.Date()
tickers <- c("DATA")
result <- tryCatch(
  {
    ?BatchGetSymbols( tickers, first.date, last.date)
  },
  error=function(cond) {
    return(BatchGetSymbols( "AAPL", last.date - 1, last.date))
  },
  warning=function(cond) {
    return(BatchGetSymbols( "AAPL", last.date - 1, last.date))
  },
  finally={
  }
)
result <- as.data.frame(result$df.tickers)

Throws this error:

Running BatchGetSymbols for:
   tickers =ACET
   Downloading data for benchmark ticker
^GSPC | yahoo (1|1) | Found cache file
ACET | yahoo (1|1) | Not Cached - Error in download..
Running BatchGetSymbols for:
   tickers =AAPL
   Downloading data for benchmark ticker
^GSPC | yahoo (1|1) | Found cache file - Error in download..
AAPL | yahoo (1|1) | Not CachedError: Argument 1 must have names
> result <- as.data.frame(result$df.tickers)
Error in result$df.tickers : $ operator is invalid for atomic vectors

You have to make sure, that the R code runs flawless, before you can start thinking about copy & pasting to Power BI.

Regards,

Tom