Data - Stock Prices

From Q
Jump to navigation Jump to search


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

How to Create a Table of Stock Prices

  1. Add the object by selecting from the menu Anything > Data > Miscellaneous > Stock PricesAutomate > Browse Online Library > Data > Stock Prices
  2. Specify the stock or currency symbol(s) to use in Inputs > Stock Symbol(s)

Example

GBP/USD currency exchange rate and Microsoft stock price using the symbols GBPUSD=X and MSFT. Days where there is no closing information are blank in the data.

Options

The options in the Object Inspector are organized into two tabs: Inputs and Properties.

Inputs

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.

Properties

This tab contains options for formatting the size of the object, as well as the underlying R code used to create the visualization, and the JavaScript code use to customize the Object Inspector itself (see Object Inspector for more details about these options). Additional options are available by editing the code.

More Information

Code

var heading_text = "Stock Prices";
if (!!form.setObjectInspectorTitle)
    form.setObjectInspectorTitle(heading_text, heading_text);
else 
    form.setHeading(heading_text);

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: Number.MAX_SAFE_INTEGER});
    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("argument \"conn\" is missing, with no default", 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)
}

scraped.times <- attr(all.data, "index")
duplicated.times <- duplicated(scraped.times)
if (any(duplicated.times))
    all.data <- subset(all.data, !duplicated.times)

stock.prices <- data.frame(all.data)
Retrieved from ‘https://wiki.q-researchsoftware.com/index.php?title=Data_-_Stock_Prices&oldid=64215’