#' 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)
}
Add the following code to your website.
For more information on customizing the embed code, read Embedding Snippets.