Lookup Dates and Return Currency Rates in an Array in Google Sheets

0
162
lookup dates and return currency rates in an Array

This post is about GoogleFinance array use in Google Sheets. I have a column with few dates where on the next column I want to find out the currency exchange rates pertaining to that dates. This is what I’m explaining here. With the help of Vlookup and GoogleFinance, we can lookup dates and return currency rates in an Array.

The formula which I am going to explain is little complex involving three functions – ArrayFormula, Vlookup and GoogleFinance. But there is no need to worry. You can use the formula out of the box. Actually you are going to learn here the use of GoogleFinance function in an Array.

How to Lookup Dates and Return Currency Rates in an Array (GoogleFinance Array Use)

In the below example screenshot you can see there are few assorted dates in Column A. Against that in Column B, see the currency exchange rates of that particular dates.

Lookup Dates and Return Currency Rates in an Array

Actually there is only one GoogleFinance formula in Array form in Cell B2 that automatically fetches the currency exchange rates for all the dates in Column A. Below is that custom formula. But one more thing. There is limitation in entering the dates in Column A. Here in this example you can enter the dates that falls in the month of December 2017. If you want to lookup currency exchange rates of any other period, you should change the dates in the formula first. Because in the below formula the start dated is “DATE(2017,12,1)” and end date is “DATE(2017,12,31)”. Changes this duration in the formula and accordingly you can use the dates in column A to fetch the exchange rates.

=ArrayFormula(if(len(A2:A),(VLOOKUP(A2:A,query(GOOGLEFINANCE(“CURRENCY:USDinr”, “price”, DATE(2017,12,1), DATE(2017,12,31), “DAILY”),“Select Col1, Col2 label Col1 ”, Col2 ” format Col1 ‘dd-mm-yyyy'”),2)),””))

Formula Explanation:

As I told you above there are three major formulas in use here. Let me explain the role of each of those formulas here.

Google Finance:

GOOGLEFINANCE(“CURRENCY:USDinr”, “price”, DATE(2017,12,1), DATE(2017,12,31), “DAILY”)

This formula returns the currency exchange rates from USD to INR for the month of December 2017. That’s why I told you above, in column A, the date should be between 01/12/2017 to 31/12/2017. If you want any other dates, change the dates in this Google Finance Function. If you want to learn how to use the Google Finance function, please check my Google Sheets Functions Guide and pick the function.

Apply this formula in any cell to understand what it returns. This formula returns the result as below. I’ve only taken the screenshot of the top part of the data as it’s bit lengthy.

I am going to use this data in a Vlookup as range.

historical currency exchange rates for array in google sheets

Query

Actually my intention is to use the above data range in Vlookup. For this we have already the lookup search_keys in Column A. The above data which is returned by GoogleFinance is our range in Vlookup. If any doubt, refer the Vlookup syntax below.

Syntax: VLOOKUP(search_key, range, index, [is_sorted])

But if you carefully analyse the historical data returned by the GoogleFinance formula above, you can understand one thing. We can’t use the above data as it is in Vlookup as range. Why?

Similar: Learn Vlookup in Array Use in Google Sheets

Because the dates returned by the GoogleFinance formula is not a clean one. The date is actually a timestamp. But our search key dates in column A is clean one without time. Further GoogleFinance returns column labels too (please refer the screenshot above). So we should first clean the historical data returned by GoogleFinance in Google Sheets. Here comes the Query useful.

There are different methods to remove time from time stamp in Google Sheets. Earlier I’ve posted a dedicated tutorial regarding this. But I am not following that tutorial here. There I’ve used INT, TO_DATE functions. Here we want to remove both time stamp and column labels. So here I’m using Query.

Must Check: Extract Date From Time Stamp

Query To Remove Time form Time Stamp and Clean the Data

The below formula part in our master formula deals the data cleaning. This Query formula removes time form time stamp in Google Sheets. Further it removes the labels too. Now we have a clean data range with one column containing date and the other column containing the exchange rates.

query(GOOGLEFINANCE(“CURRENCY:USDinr”, “price”, DATE(2017,12,1), DATE(2017,12,31), “DAILY”),“Select Col1, Col2 label Col1 ”, Col2 ” format Col1 ‘dd-mm-yyyy'”)

We have completed our major part. Now let’s see how to lookup dates and return currency Rates in an Array.

VLOOKUP(A2:A,query(GOOGLEFINANCE(“CURRENCY:USDinr”, “price”, DATE(2017,12,1), DATE(2017,12,31), “DAILY”),“Select Col1, Col2 label Col1 ”, Col2 ” format Col1 ‘dd-mm-yyyy'”),2)

Here I’ve used the range A2:A as the search_key, then the above Query and GoogleFinance combo formula (the blue and red pattern) is the range and column 2 is the index number as it contains the exchange rates.

The above is the core part of the formula. See the master formula again. There you can see that I’ve used ArrayFormula. This’s because we have to lookup multiple dates in column A, not a single search key. So ArrayFormula is a must.

The use of IF and LEN is to speed up the execution of the formula by limiting the lookup to only to the rows that containing the search keys. That’s all.

Conclusion

I know this is an advanced tutorial. But if you first individually learn the use of the functions used in this formula, you can quickly grasp how to lookup dates and return Currency Rates in an Array in Google Sheets.

Hope you’ve enjoyed. See you again with another spreadsheet tutorial. Thanks for the stay!

LEAVE A REPLY

Please enter your comment!
Please enter your name here