R/getActiveSymbols.R

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

#' Get active stock symbols from DB
#'
#' @param dbcon.str Connection string for Sharadar or QuoteMedia.
#' @param start.date Start date by "yyyy-mm-dd" format.
#' @param end.date End date by "yyyy-mm-dd" format.
#'
#' @return Charactor vector of stock tickers
#' @export
getActiveSymbols <- function(dbcon_str, start_date = Sys.Date() - 365,
                             end_date = Sys.Date()) {

  # Check input date values
  start_date <- as.Date(start_date)
  end_date   <- as.Date(end_date)

  # SQL Query
  sql <- paste0("SELECT
                  [ticker],[exchange],[first_price_date],[last_price_date]
                FROM
                  [tickers]
                WHERE
                  [ticker] NOT LIKE '%-%' AND [ticker] != 'TRUE'
                ORDER BY
                  [ticker]")

  # Use SQLite (start with local file path like "/home..")
  if (startsWith(dbcon_str, "/")) {
    channel <- DBI::dbConnect(RSQLite::SQLite(), dbcon_str)
    data    <- data.table::data.table(DBI::dbGetQuery(channel, sql))
    DBI::dbDisconnect(channel)

  # Use MSSQL
  } else {
    channel <- RODBC::odbcDriverConnect(dbcon_str)
    data    <- data.table::data.table(
                 RODBC::sqlQuery(channel, sql, stringsAsFactors = FALSE))
    RODBC::odbcClose(channel)
  }

  # Change date to Date class
  format <- "%Y-%m-%d"
  data$first_price_date <- as.Date((strftime(data$first_price_date, format)))
  data$last_price_date <- as.Date((strftime(data$last_price_date, format)))

  # Subset
  result <- data[(exchange == "NYSE" | exchange == "NASDAQ") &
                 (first_price_date <= end_date &
                  last_price_date >= start_date)]$ticker

  return (result)
}