Values Retrieval Modes Values Retrieval Modes (Current / Historical / Intraday)


The addon supports three retrieval modes:

1.current values retrieval mode

2.historical values retrieval mode

3.intraday values retrieval mode


When FromDate and ToDate is missing, the current values retrieval mode is assumed. If either FromDate or ToDate is present, the historical values retrieval mode or intraday values retrieval mode is assumed, depending on the function name.


The below text discusses the Historical Values Retrieval Mode in detail.

Historical Values Retrieval Mode: FromDate and ToDate's Directions


In all functions whenever you can specify FromDate and ToDate:

1.if the FromDate < ToDate, the results with the earliest date are shown first, the latest ones last

2.if the FromDate > ToDate, the output will be in the opposite direction, i.e. the latest date results first.



1. FromDate < ToDate:



or =Q("CIE,NCQ,BLOX,MOBL",,"2014-08-01","2014-08-06") 


2. FromDate > ToDate:




or =Q("CIE,NCQ,BLOX,MOBL",,"2014-08-06","2014-08-01")


Historical Values Retrieval Mode: One Day Specification


To retrieve a historical value for a single day, either specify FROM day equal TO day, or enter TO day as ";"



1. From = To day




2. To day = ;





Historical Values Retrieval Mode: FromDate and ToDate's Relative Specification


There are two relative modes which coexist with each other - (1) full date specification and (2) year specification. There is also a (3) mixed mode where both modes are present.


1. Full Date Specification


Whenever you can specify From-To date range for retrieval of historical dates, one of them can be always specified relatively as a positive or negative number of days ("d"), weeks ("w"), months ("m"), quarters ("q") or years ("y") from the other precisely specified date. If the other precisely specified date is missing, the current date is assumed.



"2011-01-01","5w" defines the To Date as 5 weeks from January 1, 2011: =Q("MSFT",,"2011-01-01","5w")




On the other hand, "2011-01-01","-5w" defines the To Date as 5 weeks from January 1, 2011 backwards: =Q("MSFT",,"2011-01-01","-5w")




It is also possible to switch the direction of the output by flipping the order of parameters. For example: "-5w","2011-01-01"  =Q("MSFT",,"-5w", "2011-01-01") produces



And "5w","2011-01-01" =Q("MSFT",,"5w", "2011-01-01") produces:



For the last week of currency data, use =q("AUDCHF",,,"-1w")


or sorted the other way =q("AUDCHF",,"-1w")





2. Year Specification


You can also specify the dates in terms of full years, you enter this mode by entering the FromDate as a 4 digit year. This particularly useful for macro-economic data where there is only one value for a given year.


If the FromDate is a 4 digit year, then the FromDate is assumed to be the January 1, of the given year.

In this mode, if the ToDate is missing, it's assumed to be the December 31, of the same FromDate's year.


If the ToDate is a 4 digit year, it's assumed to be the December 31 of the ToDate's year. If the ToYear is a full date, then it will be used as a full date.



To get all the exchange rates for the year of 2001 of EUR/USD, use the formula =Q("EURUSD",,"2001")



To get the complete price history of Oracle stock between Jan 1, 2011 and Dec 31, 2012 use the formula =Q("MSFT",,"2011","2012")



The same result can be achieved with the formula =Q("MSFT",,"2011","1y")



3. Mixed Specification


You can specify the FromDate as a 4-digit year (Spearian will assume you intended to specify Jan 1 of that year), and the ToDate as any precisely specified date.



To retrieve the EUR/USD exchange rates from Jan 1, 2013 till May 1, 2013, use the formula =Q("EURUSD",,"2013","2013-05-01")




Copyright © 2013-2017 Jiri Pik

Document Version: Sunday, May 7, 2017