Products

Documentation keyboard_arrow_right Formulas keyboard_arrow_right Functions keyboard_arrow_right Stockhistory STOCKHISTORY Function - Retrieve historical stock data in Jspreadsheet

STOCKHISTORY function

The STOCKHISTORY function in Jspreadsheet Formulas Pro is a powerful tool that provides you with historical stock prices. You simply input a specific date range and ticker symbol, and the function will return the relevant stock prices for that period. It's an easy and efficient way to track and analyze the performance of a particular stock right from your Jspreadsheet. This can be particularly useful for financial analysis, forecasting, and investment decisions.

Documentation

Returns historical stock prices for a given date range and ticker symbol.

Category

Financial

Syntax

STOCKHISTORY(ticker, start_date, end_date, [interval], [headers])

Parameter Description
ticker The ticker symbol of the stock to retrieve data for. Must be text.
start_date The start date of the requested period. Must be in yyyy-mm-dd format or valid Jspreadsheet date.
end_date The end date of the requested period. Must be in yyyy-mm-dd format or valid Jspreadsheet date.
interval (optional) Frequency of the data: "DAILY", "WEEKLY", or "MONTHLY". Default is "DAILY".
headers (optional) Boolean value that determines if headers are included (TRUE by default).

Behavior

The STOCKHISTORY function helps users get historical data about a particular stock in the form of a table which includes the date, closing, opening, high, low, and volume of the stock. The function uses following syntax: STOCKHISTORY(stock symbol, start_date, [end_date], [interval], [headers], [property0], [property1], [property2], [property3], [property4]).

  1. For stock symbol, the function expects the ticker symbol of the stock as a text string. It does not accept empty cells or non-string data types.
  2. start_date and end_date are expected to be in date format. If the cell is left empty or contains non-date data, the function will return an error.
  3. interval is optional and it defines the frequency of data. It can be 1 for daily, 7 for weekly, or 12 for monthly data. If left empty, the function defaults to daily data.
  4. headers is also optional. If set to 1, the function will include headers in the output. If left empty or set to 0, headers will be omitted.
  5. property0 to property4 are optional parameters that let the user choose which data points to include in the output. If left empty, the function returns all data points.

Common Errors

Error Description
#N/A This error occurs when the stock symbol is not recognized, or the stock data is not available for the specified dates.
#VALUE! This error occurs when the start_date or end_date is not a valid date, or when the interval is not 1, 7, or 12.
#REF! This error occurs when the function tries to return a table that is too large for the spreadsheet.

Best practices

  • Always double-check the stock symbol to ensure it's correct and recognized by the function.
  • When using start_date and end_date, ensure the dates are in the correct format and the end_date is not earlier than the start_date.
  • Use the headers argument to include headers in the output. This makes it easier to understand the data.
  • Utilize the property0 to property4 arguments to tailor the function's output to your specific needs. This can help to reduce the size of the output and make the data easier to manage.

Usage

A few examples using the STOCKHISTORY function.

STOCKHISTORY("AAPL", "2021-01-01", "2021-12-31") returns the daily stock prices for Apple Inc. between January 1st, 2021 and December 31st, 2021  
STOCKHISTORY("GOOGL", "2020-01-01", "2022-03-14", "WEEKLY") returns the weekly stock prices for Alphabet Inc. (Google) between January 1st, 2020 and March 14th, 2022  
STOCKHISTORY("MSFT", A1, B1, "MONTHLY", FALSE) returns monthly stock prices for Microsoft Corporation between the dates in cells A1 and B1 without headers.

Interactive Spreadsheet Demo

<html>
<script src="https://jspreadsheet.com/v11/jspreadsheet.js"></script>
<script src="https://jsuites.net/v5/jsuites.js"></script>
<link rel="stylesheet" href="https://jsuites.net/v5/jsuites.css" type="text/css" />
<link rel="stylesheet" href="https://jspreadsheet.com/v11/jspreadsheet.css" type="text/css" />

<link rel="stylesheet" href="https://fonts.googleapis.com/css?family=Material+Icons" />
<script src="https://cdn.jsdelivr.net/npm/@jspreadsheet/formula-pro/dist/index.min.js"></script>

<div id="spreadsheet"></div>

<script>
// Set your JSS license key (The following key only works for one day)
jspreadsheet.setLicense('ODVlZmUxZDUwN2RmODdmNzhkYzc3NmI0OWY0ZWI4MGE3MmI4YWU2MWQzYzA0Y2E2ZDAyMjdkODA4MDY1Y2FjZWVhN2MwYWM0MzFkYzIwM2Y2OWRjZTE3NDU0YmZjOTc1YTI2MmVjODUxZjJjMTU2MTI1MmNmODcyOTRlZTlhODUsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVNE5UQTBOREEwTENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');

// Set the extensions
jspreadsheet.setExtensions({ formula });

// Create a new spreadsheet
jspreadsheet(document.getElementById('spreadsheet'), {
  worksheets: [{
    data: [
    [
        "AAPL",
        "2024-01-01",
        "2024-01-31",
        "=STOCKHISTORY(A1,B1,C1)"
    ],
    [
        "MSFT",
        "2024-02-01",
        "2024-02-29",
        "=STOCKHISTORY(A2,B2,C2)"
    ],
    [
        "GOOGL",
        "2024-03-01",
        "2024-03-31",
        "=STOCKHISTORY(A3,B3,C3)"
    ]
]
  }]
});
</script>
</html>

import React, { useRef } from "react";
import { Spreadsheet, Worksheet, jspreadsheet } from "@jspreadsheet/react";
import formula from "@jspreadsheet/formula-pro";
import "jsuites/dist/jsuites.css";
import "jspreadsheet/dist/jspreadsheet.css";

// Set license
jspreadsheet.setLicense('ODVlZmUxZDUwN2RmODdmNzhkYzc3NmI0OWY0ZWI4MGE3MmI4YWU2MWQzYzA0Y2E2ZDAyMjdkODA4MDY1Y2FjZWVhN2MwYWM0MzFkYzIwM2Y2OWRjZTE3NDU0YmZjOTc1YTI2MmVjODUxZjJjMTU2MTI1MmNmODcyOTRlZTlhODUsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVNE5UQTBOREEwTENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');

// Set the extensions
jspreadsheet.setExtensions({ formula });

export default function App() {
    // Spreadsheet array of worksheets
    const spreadsheet = useRef();

    // Worksheet data
    const data = [
    [
        "AAPL",
        "2024-01-01",
        "2024-01-31",
        "=STOCKHISTORY(A1,B1,C1)"
    ],
    [
        "MSFT",
        "2024-02-01",
        "2024-02-29",
        "=STOCKHISTORY(A2,B2,C2)"
    ],
    [
        "GOOGL",
        "2024-03-01",
        "2024-03-31",
        "=STOCKHISTORY(A3,B3,C3)"
    ]
];

    // Render component
    return (
        <Spreadsheet ref={spreadsheet}>
            <Worksheet data={data} />
        </Spreadsheet>
    );
}

<template>
    <Spreadsheet ref="spreadsheet">
        <Worksheet :data="data" />
    </Spreadsheet>
</template>

<script>
import { Spreadsheet, Worksheet, jspreadsheet } from "@jspreadsheet/vue";
import "jsuites/dist/jsuites.css";
import "jspreadsheet/dist/jspreadsheet.css";
import formula from "@jspreadsheet/formula-pro";

// Set license
jspreadsheet.setLicense('ODVlZmUxZDUwN2RmODdmNzhkYzc3NmI0OWY0ZWI4MGE3MmI4YWU2MWQzYzA0Y2E2ZDAyMjdkODA4MDY1Y2FjZWVhN2MwYWM0MzFkYzIwM2Y2OWRjZTE3NDU0YmZjOTc1YTI2MmVjODUxZjJjMTU2MTI1MmNmODcyOTRlZTlhODUsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVNE5UQTBOREEwTENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');

// Set the extensions
jspreadsheet.setExtensions({ formula });

export default {
    components: {
        Spreadsheet,
        Worksheet,
    },
    data() {
        // Worksheet data
        const data = [
    [
        "AAPL",
        "2024-01-01",
        "2024-01-31",
        "=STOCKHISTORY(A1,B1,C1)"
    ],
    [
        "MSFT",
        "2024-02-01",
        "2024-02-29",
        "=STOCKHISTORY(A2,B2,C2)"
    ],
    [
        "GOOGL",
        "2024-03-01",
        "2024-03-31",
        "=STOCKHISTORY(A3,B3,C3)"
    ]
]

        return {
            data
        };
    }
}
</script>

import { Component, ViewChild, ElementRef } from "@angular/core";
import jspreadsheet from "jspreadsheet";
import * as formula from "@jspreadsheet/formula-pro";

// Set your JSS license key (The following key only works for one day)
jspreadsheet.setLicense('ODVlZmUxZDUwN2RmODdmNzhkYzc3NmI0OWY0ZWI4MGE3MmI4YWU2MWQzYzA0Y2E2ZDAyMjdkODA4MDY1Y2FjZWVhN2MwYWM0MzFkYzIwM2Y2OWRjZTE3NDU0YmZjOTc1YTI2MmVjODUxZjJjMTU2MTI1MmNmODcyOTRlZTlhODUsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVNE5UQTBOREEwTENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');

// Set the extensions
jspreadsheet.setExtensions({ formula });

@Component({
    standalone: true,
    selector: "app-root",
    template: `<div #spreadsheet></div>`
})
export class AppComponent {
    @ViewChild("spreadsheet") spreadsheet: ElementRef;
    // Worksheets
    worksheets: jspreadsheet.worksheetInstance[];

    // Create a new data grid
    ngAfterViewInit() {
        // Create spreadsheet
        this.worksheets = jspreadsheet(this.spreadsheet.nativeElement, {
            worksheets: [{
                data: [
    [
        "AAPL",
        "2024-01-01",
        "2024-01-31",
        "=STOCKHISTORY(A1,B1,C1)"
    ],
    [
        "MSFT",
        "2024-02-01",
        "2024-02-29",
        "=STOCKHISTORY(A2,B2,C2)"
    ],
    [
        "GOOGL",
        "2024-03-01",
        "2024-03-31",
        "=STOCKHISTORY(A3,B3,C3)"
    ]
]
            }]
        });
    }
}

Edit this page on GitHub