R/getDataByColumn.R

In tmk-c/myrlib: My R library for daily work

#' Get all stock data of specified column and date range
#'
#' @param sharadar.db SHARADAR DB connection string
#' @param column column to retrieve (valid columns = adj.open, adj.high,
#'   adj.low, adj.close, volume, dividend, close and lastupdated)
#' @param start.date start date by "yyyy-mm-dd" format
#' @param end.date end date by "yyyy-mm-dd" format
#'
#' @importFrom magrittr %>%
#' @importFrom RODBC odbcDriverConnect
#' @importFrom RODBC sqlQuery
#' @importFrom RODBC odbcClose
#' @importFrom data.table data.table
#' @importFrom data.table setnames
#' @importFrom tidyr spread
#'
#' @return All stock data of specified column as data.table format
#' @export
getDataByColumn <- function(sharadar.db, column = "adj.close",
                            start.date = "2018-01-01", end.date = Sys.Date())
{
  # Check input date values
  start.date <- as.Date(start.date)
  end.date <- as.Date(end.date)

  # Validate columns
  valid.columns <- c("adj.open", "adj.high", "adj.low", "adj.close", "volume",
                     "dividend", "close", "lastupdated")

  if (!column %in% valid.columns) {
    stop(paste0(column, " is not supported column."))
  }

  # Connect to SQL Server
  channel <- odbcDriverConnect(sharadar.db)

  # Convert r.name to db.name for SQL qeury ("adj.close" => "adj_close")
  column.db <- convertColumnName(column)

  # SQL Query
  sql <- paste0("EXEC dbo.get_ohlcv_column '", column.db, "', '",
                start.date, "', '", end.date, "'")

  data <- sqlQuery(channel, sql, stringsAsFactors = FALSE) %>% data.table()

  # Disconnect from SQL Server
  odbcClose(channel)

  # Warn if no data
  if (nrow(data) == 0) {
    stop(paste0("No data found for ", column, ". (", start.date,
                " - ", end.date, ")"))
  }

  # Rename columns
  setnames(data, c("symbol", "date", column))

  data <- data %>% tidyr::spread(key = symbol, value = column) %>% data.table()

  # Convert POSIXct to Date
  data$date <- as.Date(strftime(data$date, "%Y-%m-%d"))

  return(data)
}