On Google Spreadsheet how can you query 'GoogleFinance' for a past exchange rate?
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:
- https://superuser.com/a/687927/128312
Try,
=GoogleFinance("usdeur","price",date(2013,12,1),date(2013,12,16))
Make sure that the dates are as per your spreadsheet settings.
Edit as comment, changed date for capturing single day data:-
Only with headers:
=INDEX(GoogleFinance("usdeur","price",date(2013,12,3),date(2013,12,4)),,2)
without headers:
=FILTER(INDEX(GoogleFinance("usdeur","price",date(2013,12,3),date(2013,12,4)),,2),INDEX(GoogleFinance("usdeur","price",date(2013,12,3),date(2013,12,4)),,2)<>"Close")
The instructions for all related to googlefinance are in here: https://support.google.com/docs/answer/3093281
Remember the actual Google Spreadsheets Formulas use semicolon (;) instead of comma (,). Once made the replacement on some examples would look like this:
For a 30 day INDEX of USD vs EUR you should use (note that in the case of currencies they go together in the same first variable):
=INDEX(GoogleFinance(USDEUR;"price";today()-30;today());2;2)
TIP: You can get the graph over the entire size of the cell by simply changing INDEX for SPARKLINE, like this:
=SPARKLINE(GoogleFinance(USDEUR;"price";today()-30;today());2;2)
Vasim's answer is excellent, however notice if you want the exchange date on that day only, you can omit the range and just specify the day such as the following
=FILTER(INDEX(GoogleFinance("usdeur","price",today()),,2),INDEX(GoogleFinance("usdeur","price",today()),,2)<>"Close")
You may notice that GOOGLEFINANCE
will return N/A for some dates, this is because the date is a day off (usually a weekend), what you can do is to get the last working from the specified date, e.g. Jun 21st 2015 is Sunday, so you should request the rate for Jun 19th (Friday), you can do this via WORKDAY
function as was suggested here:
WORKDAY("6/21/2015"+1,-1)
So, the resulting formula will look something like that:
INDEX(GoogleFinance("CURRENCY:USDRUB", "price", WORKDAY("6/21/2015"+1,-1),1),2,2)
Additionally, you want to get the exchange rates for future dates you can additionally check if the date is in the future and if so, just use the today date:
WORKDAY(IF("6/21/2099">TODAY(),TODAY(),"6/21/2099")+1,-1)