Syntax: QUOTE(Tickers, Fields, ReferenceFromDate, ReferenceToDate, AutoFormat)


Equivalent Function Names: Q


Purpose: The function displays the market data history for a given ticker, e.g. MSFT.



Tickers and Fields depend on the source.

Review the Tickers guidelines here.

Field(s) may be optional for a given operation.

Parameter ReferenceFromDate and ReferenceToDate may be optional for a given operation. If provided, it can be specified either as an Excel date or in the text format "yyyy-MM-dd", e.g. "2013-02-25".

NOTE: Some data sources, e.g. Quandl, do support FromDate earlier than Jan 1, 1900 which is the earliest date natively supported by Excel. Spearian will deliver the dates and the associated data as you request but to work effectively with such dates, see the instructions here.

The time range can be entered using relative time periods, see here.

AutoFormat - an optional flag signifying if the result should be automatically converted to a range and if the result should be automatically formatted as Excel Data Types

Fields Transformations


In general, all historical time series numerical fields (e.g. prices) can be transformed using Field Transformations:

Transformation Name

Transformation Definition


Moving Averages 50 Days


Moving Averages 200 Days


Simple Return


Log Return


Arithmetic Difference of the consecutive entries


Cumulative Sum from the first entry to the last


Normalization with the base date of the first entry of the time series




Calculation Methodology:

If there is any NaN in the values, the transformations are based on the last previous known value.

1. Display monthly returns of a stock

=Q("MSFT::m","d,p:r","-1y") returns monthly returns of MSFT for the last year


Supported Modes


The function supports these modes:

1.Current Data Mode - both ReferenceFromDate and ReferenceToDate are empty

Example: =Q("MSFT")


2.Historical Data Mode with Multiple Tickers on a given single date 

Example: =Q("MSFT,GS,IBM",,"2014-04-07",";")


3.Historical Data Mode with Multiple Tickers on a From-To interval

1. This mode requires that (a) the first field is date and that (b) the fields are common to all tickers. If they are not, use the method (4) on any ticker with different columns.
2. This mode is useful for merging datasets with different time intervals, e.g. weekly, monthly etc

Example: =Q("MSFT,GS::m,IBM::w","d,p","2011-01-01")


4.Historical Data Mode with a Single Ticker on specified dates

Example: =Q("MSFT","d,p",A1:A10)


5.Historical Data Mode with a Single Ticker on a From-To interval

Example: =Q("MSFT",,"-1y")




Copyright © 2013-2017 Jiri Pik

Document Version: Sunday, May 7, 2017