Quote Source: Open Exchange Rates

 

Open Exchange Rates support the current and historical values retrieval modes for exchange rates, though the past one only in a non-bulk mode.

 

The list of supported currencies is available from the Symbols' Search. The list is continuously being expanded, the current list is available here.

 

Note: The ticker for Silver is XAG and the ticker for gold is XAU.
 

 

For current exchange rates:

 

Field Primary Name

Field Synonyms

Field Definition

"DATE"

"DT","D"

 

"LAST_TRADE_PRICE"

"PRICE", "P"

 

 

For historical exchange rates:

Field Primary Name

Field Synonyms

Field Definition

"DATE"

"DT", "D"

Date of the record

"LAST_TRADE_PRICE"

"PRICE", "P"

Exchange rate stored last for the particular day, defined as a UTC day

"CLOSE"

"C"

Exchange rate defined as the rate of about 1700 GMT.

"HIGH"

"H"

Max value for a UTC day

"LOW"

"L"

Min value for a UTC day

 

Close, High, and Low are provided only for the USD, EUR, JPY, GBP, AUD, CHF, CAD, HKD, SEK, NZD and is updated in 2 hours intervals.

 

 

Note: Fields in bold are default fields.
Note: Open Exchange Rates supports only daily frequencies.
 


Examples:

1. Use the function to download currency values from Open Exchange Rates

=Q("o::usdeur",,"2013-10-14",";")

 

 

2. Retrieve Current Exchange Rate from Open Exchange Rates

=Q("CHFEUR") displays the latest exchange rate between CHF and EUR.

 

 

 

3. Retrieve historical exchange rates in a bulk

=Q(D1:D3,"p","2013-10-14") retrieves using one network request (very fast) the historical exchange rate with a number of currencies from Open Exchange Rates.

 

 

 

4. Retrieve current exchange rates in a bulk

=Q(D1:D3) retrieves using one network request (very fast) the current exchange rate with a number of currencies  from Open Exchange Rates.

 

 

5. Use the function to download currency values

=Q("usdeur",,"-1d")

 

6. Retrieve Historical Exchange Rate

=Q("CHFEUR","CLOSE",A1,A1) displays the close exchange rate between CHF and EUR for the date specified in cell A1.

 

 

 

7. Retrieve Historical Exchange Rate in Bulk - both the Date parameter and the result of the function is an Excel range 

=Q("CHFUSD","?",A2:A16) displays all exchange rate fields between CHF and USD for the date specified in the range A2:A16

 

 

8. Retrieve Historical Exchange Rate in Bulk - both the Date parameter and the result of the function is an Excel range

=Q("CHFUSD","CLOSE",A2:A16) displays only close rates between CHF and USD for the date specified in the range A2:A16

 

 

9. Retrieve Historical Exchange Rate in Bulk From to To Date

=Q("CHFUSD",,"2011-01-01","2013-10-01") displays exchange rate fields between CHF and USD for the dates range specified

 

 

 

 

Copyright © 2013-2017 Jiri Pik

Document Version: Sunday, May 7, 2017