Data - Stock Prices

From Q
Jump to: navigation, search

Extracts daily historical closing stock prices, commodity prices and currency exchange rates. Input symbols are as per Yahoo finance.

Example

GBP/USD currency exchange rate and Microsoft stock price

Options

Symbol(s) The symbols(s) of stocks, currencies or commodities. Where there is more than one symbol, they should be comma-separated. To search for symbols, please refer to the Yahoo finance website.

Period The time period for which the historical data is extracted. Either Custom range or a specific period.

Start date The start date of the Custom range in the format yyyy-mm-dd.

End date The end date of the Custom range in the format yyyy-mm-dd. Defaults to today if not specified.

Data fields A comma-separated list of any of Open, Close, High and Low. Defaults to Close if not specified.

Long format output If checked, the result is returned in long format i.e. with one row of data per symbol per day. Otherwise, it is returned in wide format i.e. with days along the rows and symbols along the columns.

Automatic updating Whether to regularly update the data.

Update period The time unit for regular updates.

Frequency The multiple of the Update period for regular updating.

Start date and time The date and time of the first update in the format dd-mm-yyyy hh:mm or mm-dd-yyyy hh:mm.

US date format Whether the Start date and time is expressed in US format i.e. mm-dd-yyyy hh:mm.

Time zone An optional time zone for the Start date and time, or else default of UTC applies. Format must be Continent/City, e.g. America/Los_Angeles. See Wikipedia for a list of time zones.

Update exported documents Whether exported documents that refer to the data should also be updated regularly.

Code

form.setHeading("Stock Prices");
form.textBox({name: "formSymbols",
              label: "Stock symbol(s)", 
              required: true, prompt: "For example: MSFT"});
var time = form.comboBox({name: "formPeriod",
               label: "Period", 
               alternatives: ["Last week", "Last month", "Last 3 months", "Last year", "Last 5 years", "Last 10 years", "Custom range"],
               default_value: "Last 5 years", prompt: "The period of the history"});
if (time.getValue() == "Custom range") {
    form.textBox({name: "formStartDate", label: "Start date (yyyy-mm-dd)", prompt:"For example: 2015-01-01", required: true});
    form.textBox({name: "formEndDate", label: "End date (yyyy-mm-dd)", prompt:"For example: 2015-12-31", required: false});
}
form.textBox({name: "formFields", label: "Data fields", 
              required: false, prompt: "Default Close, or any combination of Open, Close, High, Low"});
form.checkBox({label:"Long format output", name:"formLongFormat", default_value:false, prompt: "One row of data per symbol per day"});

// Controls for regular updating
var updating = form.checkBox({label:"Automatic updating", name:"formUpdating", default_value:false, prompt:"Regularly update the output"}).getValue();
if (updating) {
    if (Q.fileFormatVersion() > 10.9)
        form.group("UPDATING");
    var period = form.comboBox({name: "formUpdatePeriod", label: "Update period", 
               alternatives: ["Months", "Weeks", "Days", "Hours", "Minutes", "Seconds"], default_value: "Days", prompt: "The time units for updating"}).getValue();
    var defaultFrequency = 1;
    if (period == "Seconds")
        defaultFrequency = 600;
    else if (period == "Minutes")
        defaultFrequency = 10;
    form.numericUpDown({name: "formFrequency", label: "Frequency", default_value: defaultFrequency,
                        prompt: "The update frequency in units of the update period", increment: 1, minimum: defaultFrequency, maximum: 1000000});
    var start = form.textBox({name: "formStart", label: "Start date and time", prompt: "The first update date and time",
              required: false, prompt: "Default now, or e.g. 31-12-2018 18:00:00"}).getValue();
    if (start != "") {
        form.checkBox({label:"US date format", name:"formUSDate", default_value:false, prompt: "Specify update start date as mm-dd-yyyy"});
        form.textBox({name: "formTimeZone", label: "Time zone", 
                  required: false, prompt: "Leave blank for UTC or enter e.g. America/New_York"});    
    }
    form.checkBox({name: "formSnapshot", label: "Update exported documents", default_value: false, prompt: "Whether exported documents should be updated"});
}
library(quantmod)
library(flipU)
library(flipTime)
library(reshape2)
 
symbols <- ConvertCommaSeparatedStringToVector(formSymbols)

if (exists("formStartDate")) {
    start.date <- formStartDate
} else {
    period <- switch(formPeriod,
               "Last week" = "-1 week",
               "Last month" = "-1 month",
               "Last 3 months" = "-3 months",
               "Last 6 months" = "-6 months",
               "Last year" = "-1 year",
               "Last 5 years" = "-5 years",
               "Last 10 years" = "-10 years")
    start.date <- seq(as.Date(Sys.Date()), length = 2, by = period)[2]
}
end.date <- get0("formEndDate", ifnotfound = "")
if (end.date == "")
    end.date <- Sys.Date()

data <- new.env()
tryCatch({suppressWarnings(getSymbols(symbols, env = data, from = start.date, to = end.date, auto.assign = TRUE))}, 
        error=function(e) {
                         if (grepl("download failed", e$message, fixed = TRUE)) stop("Unrecognized stock symbol(s).")
                         if (grepl("unambiguous format", e$message, fixed = TRUE)) stop("Unrecognized start and/or end date.")
                         if (grepl("Error in the HTTP2 framing layer", e$message, fixed = TRUE)) stop("Connection error. Please re-try.")              
                         stop(e)})

permitted.fields <- c("Open", "High", "Low", "Close")
if (formFields == "") {
    fields <- 4
} else {
    fields <- ConvertCommaSeparatedStringToVector(formFields)
    fields <- match(fields , permitted.fields)
    if (any(is.na(fields)))
        stop("Unrecognized data fields.")
}

all.data <- data[[ls(data)[1]]][, fields]
if (length(symbols) > 1) {
    for (symbol in ls(data)[2:length(symbols)]) {
        all.data <- merge(all.data, data[[symbol]][, fields])
    }
}

if(formLongFormat) {
    all.data <- melt(as.matrix(all.data))
    colnames(all.data) <- c("Date", "Symbol.field", "Value")
}

# Create regular updating message
if (formUpdating) {
    options <- ifelse(formSnapshot, "snapshot", "wakeup")
    if (formStart != "") {
        if (formTimeZone == "") formTimeZone <- "UTC"
        UpdateAt(formStart, us.format = formUSDate, time.zone = formTimeZone,
                units = tolower(formUpdatePeriod), frequency = formFrequency, options = options)
    } else
        UpdateEvery(formFrequency, units = tolower(formUpdatePeriod), options = options)
}

stock.prices <- data.frame(all.data)