UPDATE #2
So after some searching, I've found a statement to convert the other currencies (COP & USD) to CAD based on the historical DATE value using the following:
=H85*average(index(GOOGLEFINANCE(CONCATENATE("CURRENCY:" & G85 & "CAD"), "price", A85,1,"DAILY"),,2))
(this was found here: Ablebits.com)
HOWEVER, on cells where the purchase amount was originally in CAD the statement returns as Error: When evaluating GOOGLEFINANCE, the query for the symbol: 'CURRENCY:CADCAD' returned no data., which obviously makes sense. It can't convert CAD to CAD.
So I've tried variations of this (below), which returns as Error: Formula parse error.
=IFS(NOT(ISBLANK(G71)),(NOT(G71="CAD"),H71*average(index(GOOGLEFINANCE(CONCATENATE("CURRENCY:" & G71 & "CAD"), "price", A71,1,"DAILY"),,2)),ISBLANK(G71),H71*1,G71="CAD",H71*1))
What am I doing wrong here? A variation of the above statement targeting blank cells [=IFS(NOT(ISBLANK(G71)),H71average(index(GOOGLEFINANCE(CONCATENATE("CURRENCY:" & G71 & "CAD"), "price", A71,1,"DAILY"),,2)),ISBLANK(G71),H711)] works fine, but when I add the cell for "CAD", everything breaks.
Help!
UPDATE:
OK, So update on this issue since yesterday. I've figured out the partial IFS statement for my document, but only to the extend of converting the purchase amount to the current Canadian dollar price.
=IFS(G3="USD",H3*GOOGLEFINANCE("CURRENCY:USDCAD"),G3="CAD",H3*1,G3="COP",H3*GOOGLEFINANCE("CURRENCY:COPCAD"),ISBLANK(G3),H3*1)
Now my problem lies in adding the historical data for the currency exchange based on the purchase date.
As referenced in my original screenshot, the DATE value is in column A. Rather than copying each purchase date into the IFS statement, I want the IFS statement to incorporate the DATE data from the corresponding cell in Column A.
I've tried incorporating the following expression, but it returns as #ERROR!
=IFS(G23="USD",H23*GOOGLEFINANCE("CURRENCY:USDCAD”, DATE(A3))
Original Post
Any ideas of how to correctly incorporate the historical DATE data into the statement?
I'm trying to figure out the appropriate google sheets statement to convert currency from multiple currencies on an expense tracking sheet.
I have an expense tracking sheet where purchases are commonly in US dollars, Canadian dollars, and Colombian Pesos. I need to convert the USD and COP payments to a CAD total based on the purchase date.
In the currency column, the currency is selected from a dropdown menu.
I'm just starting to learn to code and this one is really frustrating. I don't have a statement example, but am including a screenshot of the sheet so you can have an idea of the layout.
Help!
UPDATED: You dont need an if statement to adjust for different currency, you can just construct the currency parameter
=J23 * IF( G23="CAD", 1,(INDEX (GOOGLEFINANCE("CURRENCY:"&C10&"CAD","close",A10),2,2)))
So to explain a bit-
When you include a date you have to also include an attribute. The GOOGLEFINANCE returns a table, which is kind of annoying, but the INDEX function will extract the part you need.
The IF function will return 1 when the currency you want to convert from is CAD as GOOGLEFINNCE returns N/A for that. You could use the IFNA() function, but then if NA was returned for some other reason, maybe like date is out of range on a USDCAD conversion, then that error would be hidden.
(Sorry my presvious answer was looking more at the IF statements in your question, which you dont need. The answer before and now shows you how to get around using them. I didnt have any specific knowledge of the GOOGLEFINANCE function)