CODEWITHSUNDEEP
google-sheetscurrency
user1082754
user1082754

Reputation:

Dynamically convert currency

I have a column of values in varying currencies. I want to create a column that automatically detects the currency of a given value and converts that to GPB (or any other given currency).

I have the following formula for querying the exchange rate:

=INDEX(GoogleFinance("eurgbp","price",date(2014,9,15)),2,2)

However this requires that you specify the currency you are converting from. I want this to be dynamic, because my list of currencies varies.

Upvotes: 9

Views: 2633

Answers (3)

David Sanders
David Sanders

Reputation: 4129

Sounds like you'll need to come up with some kind of encoding for the currency column values. For example:

Currency
----------
USD, 10254  # $102.54
RMB, 1010   # ¥101.0

You'd then need to parse that column value somehow in order to determine the currency type. Or you could have two columns. One for currency type and the other for currency value:

Type Currency
---- --------
USD  10254    # $102.54
RMB  1010     # ¥101.0

By the way, the currency columns are integers so you don't have to use floating point math which is a nice thing to avoid in my experience.

Upvotes: 3

BlueTrin
BlueTrin

Reputation: 10093

As AdamL mentioned, you need to check if there are currencies with the same symbols in your list of data.

If not, you can do the following:

Amt     Text         Amt Symbol
=========================================
(data)  =TO_TEXT(A2) =left(B2,FIND(REGEXEXTRACT(B2,"[0-9\.\,]"),B2)-1)

This will get your currency symbol from the column A. The formula is finding the first occurrence of something numeric (including . and ,) and then you just take what is on the left of it to obtain the currency.

Then you need a table for each symbol to its ISO 3 letter code so you can use Google Finance to obtain the FX, something like:

Symbol    ISO Ccy
======================================
$         USD
£         GBP
€         EUR

Then you can get the ISO ccy by using VLOOKUP and continue the formulas above, here I did the FX for GBP

Amt Symbol        FX                                                                      Convert Amt
===================================================================================================
(formula above)   =IF(D2="GBP",1,INDEX(GoogleFinance(D2&"GBP","price",<SOMEDATE>),2,2))   =E2*A2

I saved an example here.

Upvotes: 2

AdamL
AdamL

Reputation: 24619

You can return a text string representation of a formatted value with:

=TO_TEXT(A1)

So if you have the value 35 formatted as British Pound Sterling, the formula will return the string:

£35.00

Now you can match that string with a lookup table (probably using REGEXMATCH) to return the string required for GoogleFinance(). The trouble is that the native format of, say, the US dollar, is exactly the same as, say, the Australian dollar. However, you can set your own custom number formats (eg with USD or AUD appended) that will be carried through to the TO_TEXT function.

So you would probably need to describe all your possible currency formats to arrive at a more specific solution.

Upvotes: 3

Related Questions