R/getDailyOHLCV.R

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

#' Get stock daily OHLCV and it's adjusted price from DB
#'
#' @param dbcon_str Connection string for Sharadar or QuoteMedia.
#' @param symbol Stock symbol.
#' @param start_date Start date by "yyyy-mm-dd" format.
#' @param end_date End date by "yyyy-mm-dd" format.
#' @param columns Columns to retrieve. (Valid columns fro Sharadar = open, high,
#'  low, close, adj.open, adj.high, adj.low, adj.close, volume, dividend
#'  and lastupdated, for QuoteMedia = open, high, low, close, volume, adj.open,
#'  adj.high, adj.low, adj.close, adj.volume, dividend and split)
#' @param keep_symbol whether to keep symbol column
#
#' @return OHLCV and it's adjusted price by date as data.table format
#' @export
getDailyOHLCV <- function(dbcon_str,
  symbol      = "AAPL",
  start_date  = "2008-01-02",
  end_date    = Sys.Date(),
  columns     = c("adj.open", "adj.high", "adj.low", "adj.close", "adj.volume",
                  "open", "high", "low", "close", "volume", "dividend"),
  keep_symbol = TRUE)
{

  # Sharadar
  if (grepl("sharadar", dbcon_str)) {

    data <- getDailyOHLCVSharadar(dbcon_str, symbol, start_date, end_date,
                                  columns, keep_symbol)

    # QuoteMedia
  } else if (grepl("quotemedia", dbcon_str)) {

    data <- getDailyOHLCVQuoteMedia(dbcon_str, symbol, start_date, end_date,
                                    columns, keep_symbol)

  } else {
    data <- NULL
  }

  return(data)
}

#' Get stock daily OHLCV and it's adjusted price from Sharadar Equity Prices
#'
#' @inheritParams getDailyOHLCV
#'
#' @importFrom magrittr %>%
#
#' @return OHLCV and it's adjusted price by date as data.table format
getDailyOHLCVSharadar <- function(dbcon_str,
  symbol      = "AAPL",
  start_date  = "2008-01-02",
  end_date    = Sys.Date(),
  columns     = c("adj.open", "adj.high", "adj.low", "adj.close", "adj.volume",
                  "open", "high", "low", "close", "volume",
                  "dividend", "lastupdated"),
  keep_symbol = TRUE)
{
  # Check input date values
  start_date <- as.Date(start_date)
  end_date <- as.Date(end_date)

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

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

  # SQL Query
	sql <- paste0("SELECT
                  [ticker],[date],[adj_open],[adj_high],[adj_low],[adj_close],
                  [adj_volume],[dividend],[close],[last_updated]
                FROM
                  [equity_prices]
                WHERE
                  [ticker] = '", symbol, "' AND
                  [date] BETWEEN '", start_date, "' AND '", end_date, "' 
                ORDER BY
                  [date]")

  # Use SQLite
  if (startsWith(dbcon_str, "/")) {
    channel <- DBI::dbConnect(RSQLite::SQLite(), dbcon_str)
    data    <- DBI::dbGetQuery(channel, sql)
    DBI::dbDisconnect(channel)

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

  # Warn if no data
  if (nrow(data) == 0) {
    stop(paste0("No data found for ", symbol, ". (", start_date, " - ",
                   end_date, ")"))
  } else {
    data <- data.table::data.table(data)
  }

  # Rename columns
  data.table::setnames(data, c("symbol", "date", original_columns))

  # Convert POSIXct/Character to Date
  # https://qiita.com/kota9/items/657c8c0ac5092e3ec1ff
  data$date <- as.Date(strftime(data$date, "%Y-%m-%d"))
  data$lastupdated <- as.Date(strftime(data$lastupdated, "%Y-%m-%d"))

  # unadjusted open
  if ("open" %in% columns) {
    if (all(data$close == data$adj.close)) {
      data[, open := adj.open]
    } else {
      ratio <- data$adj.open / data$adj.close
      data[, open := close * ratio]
    }
  }

  # unadjusted high
  if ("high" %in% columns) {
    if (all(data$close == data$adj.close)) {
      data[, high := adj.high]
    } else {
      ratio <- data$adj.high / data$adj.close
      data[, high := close * ratio]
    }
  }

  # unadjusted low
  if ("low" %in% columns) {
    if (all(data$close == data$adj.close)) {
      data[, low := adj.low]
    } else {
      ratio <- data$adj.low / data$adj.close
      data[, low := close * ratio]
    }
  }

  # unadjusted volume
  if ("volume" %in% columns) {
    if (all(data$close == data$adj.close)) {
      data[, volume:= adj.volume]
    } else {
      ratio <- data$close / data$adj.close
      data[, volume:= adj.volume / ratio]
    }
  }

  # Subset
  if (keep_symbol) {
    columns_used <- c("symbol", "date", columns)
  } else {
    columns_used <- c("date", columns)
  }
  data <- data[, ..columns_used]

  return(data)
}

#' Get stock daily OHLCV and it's adjusted price from QuoteMedia EOD Prices
#'
#' @inheritParams getDailyOHLCV
#'
#' @importFrom magrittr %>%
#' @importFrom RODBC odbcDriverConnect
#' @importFrom RODBC sqlQuery
#' @importFrom RODBC odbcClose
#' @importFrom data.table data.table
#' @importFrom data.table setnames
#
#' @return OHLCV and it's adjusted price by date as data.table format
getDailyOHLCVQuoteMedia <- function(dbcon_str,
  symbol      = "AAPL",
  start_date  = "1980-01-02",
  end_date    = Sys.Date(),
  columns     = c("adj.open", "adj.high", "adj.low", "adj.close", "adj.volume",
                  "open", "high", "low", "close", "volume",
                  "dividend", "split"),
  keep_symbol = TRUE)
{
  # Check input date values
  start_date <- as.Date(start_date)
  end_date   <- as.Date(end_date)

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

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

  # SQL Query
	sql <- paste0("SELECT
                  [ticker],[date],[open],[high],[low],[close],[volume],
                  [dividend],[split],[adj_open],[adj_high],[adj_low],
                  [adj_close],[adj_volume]
                FROM
                  [eod_prices]
                WHERE
                  [ticker] = '", symbol, "' AND
                  [date] BETWEEN '", start_date, "' AND '", end_date, "' 
                ORDER BY
                  [date]")

  # Use SQLite
  if (startsWith(dbcon_str, "/")) {
    channel <- DBI::dbConnect(RSQLite::SQLite(), dbcon_str)
    data    <- DBI::dbGetQuery(channel, sql)
    DBI::dbDisconnect(channel)

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

  # Warn if no data
  if (nrow(data) == 0) {
    stop(paste0("No data found for ", symbol, ". (", start_date, " - ",
                   end_date, ")"))
  } else {
    data <- data.table::data.table(data)
  }

  # Rename columns
  data.table::setnames(data, c("symbol", "date", valid_columns))

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

  # Subset
  if (keep_symbol) {
    columns_used <- c("symbol", "date", columns)
  } else {
    columns_used <- c("date", columns)
  }
  data <- data[, ..columns_used]

  return(data)
}