-2

I have a product with In-App Purchases in several markets in which transactions happen in their local currency. Created a Google Sheet for the purpose of reporting revenues in our base currency (USD). I tried several things to create the most effective method to automatically read the currency pair for each transaction, but the formula isn't working.

I am attempting to accomplish 3 things here:

  1. Create an array that will automatically convert different currencies to USD by using the GOOGLEFINANCE formula (see "G1" on the embedded spreadsheet).

  2. Eliminating error when conversion finds a value having the same ISO codes (see "F12" on the embedded spreadsheet).

  3. Calculating the transaction using the exchange rate of the day of the transaction instead of TODAY's exchange rate (no clue on how to do this without breaking the GOOGLEFINANCE formula).

Here is the formula I am using on Col "G" is:

={"CONVERSION ARRAY";ARRAYFORMULA(E2:E*GOOGLEFINANCE("CURRENCY:"&D2:D))}

The problem with this approach is that it replicates the value of cell "D2" down the whole array, instead of consecutive values from D2,3,4, etc, accordingly. Removing the "&" from the array breaks the formula.

  1. While the formula used on Col "F" =E2*GOOGLEFINANCE("CURRENCY:"&D2) works, the problem here is that it has to be placed manually, row by row, and this is inefficient when dealing with thousands of records. Also, you will notice on cel F12, I get an invalid value because GoogleFinance cannot convert from dollars to dollars. The formula outputs an error. Is there a way to make the formula understand that when trying to convert a value with the same currency ISO's, the value stays the same?

  2. Even while I get the correct calculation on approach #2, the data calculated is based on TODAY's exchange rate, however, I need that calculation based on the exchange rate of the same date of the transaction. Is this even possible?

The referred spreadsheet example is here: Google Finances Conversion Formula

Nimantha
  • 6,405
  • 6
  • 28
  • 69
Constantine Costopoulos
  • 17
  • 3
  • 1
    Does this answer your question? [Google finance as an argument for ArrayFormula](https://stackoverflow.com/questions/60874426/google-finance-as-an-argument-for-arrayformula) – vilc Sep 20 '22 at 08:41

1 Answers1

2

GOOGLEFINANCE is not supported under ARRAYFORMULA

if your D2:D range is small you can do:

={"CONVERSION ARRAY";ARRAYFORMULA(E2:E5*{
 GOOGLEFINANCE("CURRENCY:"&D2); 
 GOOGLEFINANCE("CURRENCY:"&D3); 
 GOOGLEFINANCE("CURRENCY:"&D4); 
 GOOGLEFINANCE("CURRENCY:"&D5)})}
player0
  • 124,011
  • 12
  • 67
  • 124
  • This works for a small range as you mention @player0, however, I have a large number of records. While this solution works, it would still require manual updating of the array every time there's a new transaction. Is there any other way to automate this process besides the GoogleFinance function? – Constantine Costopoulos Jul 28 '21 at 19:56
  • @ConstantineCostopoulos in theory you could create formula which will generate formula you need. then you could just copypaste manually after each update or use simple script to inject text string into specified cell as regular formula after each edit of your sheet. see for example: https://stackoverflow.com/a/68278101/5632629 or https://stackoverflow.com/a/68536946/5632629 – player0 Jul 28 '21 at 20:07