On Google Spreadsheet how can you query 'GoogleFinance' for a past exchange rate?

google-sheets google-docs currency-exchange-rates
antr picture antr · Dec 16, 2013 · Viewed 46.7k times · Source

I'd like to know if it is possible to query a past exchange rate on Google Spreadsheet.

For example; using formula =GoogleFinance("CURRENCY:USDEUR") will return the USD/EUR rate at this present moment. How can you retrieve a historic rate?

Answer

Mike B. picture Mike B. · Dec 21, 2013

In order to retrieve the historical rate, you have to use the following formula:

=GoogleFinance("eurusd","price",today()-1,today())

Where today()-1, today() is the desired time interval, which can be explicitly defined as the static pair of dates, or implicitly, as the dynamically calculated values, like in the example above. This expression will return a two-column array of the dates and close values. It is important to care about the suitable cell format (date/number), otherwise your data will be broken.

If you want to get the pure row with the date and currency exchange rate without column headers, wrap your formula with the INDEX() function:

=INDEX(GoogleFinance("eurusd","price",today()-1,today()),2,)

To retrieve the exchange rate value only, define the column number parameter:

=INDEX(GoogleFinance("eurusd","price",today()-1,today()),2,2)

To get today's currency exchange rates in Google Docs/Spreadsheet from Google Finance:

=GoogleFinance("eurusd","price",today())

P.S. Some time ago there was an issue with the short way to get today's rates, but now it works, and you can use again:

=GoogleFinance("currency:usdeur")

P.S. How to get live currency exchange rate in Microsoft Excel:

Related questions

Count number of cells with any value (string or number) in a column in Google Docs Spreadsheet

I have several columns in Google Spreadsheet. I would like to count how many cells have a value (number or string) and display that number at the top of the column. For example: Hello World 123 Some string The above column …

Read More
How to define global variable in Google Apps Script

I see most examples from Google is they use only functions in a single giant script. e.g. https://developers.google.com/apps-script/quickstart/macros But in our style, we usually write all functions under a single namespace, such as …

Read More
Axis scale in google charts (from google spreadsheets)

I've generated chart form google spreadsheets. In my report for horizontal axis I have values: 1 ,2 ,3 ,4 ,5 ,10 ,20 ,30 ,40 ,50 ,100 ,200 ,300 ,400 ,500 ,600 ,700 ,800 ,900 ,1000. Chart interprets them as a labels, not a number: So the distance between 1 and 4 is the same as between 20 and 50. How to change it? (Maybe …

Read More