Quote

 

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.

 

Parameters: 
 

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

"M50"

Moving Averages 50 Days

"M200"

Moving Averages 200 Days

"R"

Simple Return

"LR"

Log Return

"D"

Arithmetic Difference of the consecutive entries

"C"

Cumulative Sum from the first entry to the last

"N"

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

 

Syntax is COLUMN_NAME : TRANSFORMATION.

 

Calculation Methodology:

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


Examples:
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

Notes: 
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