R/getSymbolInfo.R

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

#' Get all stock symbol info from DB
#'
#' @param dbcon_str Connection string for Sharadar or QuoteMedia.
#'
#' @importFrom RSQLite SQLite
#' @importFrom DBI dbConnect
#' @importFrom DBI dbGetQuery
#' @importFrom DBI dbDisconnect
#' @importFrom RODBC odbcDriverConnect
#' @importFrom RODBC sqlQuery
#' @importFrom RODBC odbcClose
#' @importFrom data.table data.table
#'
#' @return Stock symbol info by data.table format
#' @export
getSymbolInfo <- function(dbcon_str) {

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

  # Use SQLite (start with local file path like "/home..")
  if (startsWith(dbcon_str, "/")) {

    # Connect to SQLite DB
    channel <- dbConnect(SQLite(), dbcon_str)
    data    <- dbGetQuery(channel, sql) %>% data.table()

    # Disconnect from SQL Server
    dbDisconnect(channel)

  # Use MSSQL
  } else {

    # Connect to SQL Server
    channel <- odbcDriverConnect(dbcon_str)
    data    <- sqlQuery(channel, sql, stringsAsFactors = FALSE) %>% data.table()

    # Disconnect from SQL Server
    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)))

  return (data)
}