google-sheetsgoogle-docscurrency-exchange-rates

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

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?

Solution

  • 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 returns 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())

    A shorter way to get today's rates:

    =GoogleFinance("currency:usdeur")

    P.S. There is also the way to get live currency exchange rate in Microsoft Excel.