In what I call my “shadow bookkeeping”, I have a table with expenses.

In this table, I have various invoices with various currencies: euros, US dollars, British pounds and Mexican Mexican Pesos.

I pay most of my business expenses with a business credit card. If the expense is in a foreign currency, a transaction will happen where the foreign currency is converted to Euros.

The exact conversion amount is in my MasterCard transcript. However, I always want to know what I’m spending and where. It doesn’t have to be 100% accurate; but some accuracy is nice to get an overview of costs.

At some point I introduced a currency column to my sheet, which had a currency code – for example EUR, USD or MXN.

Then I would use the issue date and the Google Finance formula to find currency exchange info – to convert from US dollars to Euro, for example.

The formula looks like this (where G171 refers to a column with the currency code; B171 refers to a column with the issue date and F171 refers to an original amount):

=INDEX(GOOGLEFINANCE("CURRENCY:"&G171&"EUR"; "price"; B171); 2;2)*F171

This worked, but not always. Turns out that on weekends or on dates that the stock exchange is closed, the API returns N/A (not available) as a value.

Today I finally fixed this issue with a bit of a stupid “hack”: I take the same formula but repeat it with a fallback minus 3 days. So if it’s a Sunday, it will refer to a Thursday. It it’s a Saturday it will refer to a Wednesday. If the stock exchange is closed, it will refer to 3 days earlier (hopefully not a weekend day).

=IFERROR(INDEX(GOOGLEFINANCE("CURRENCY:" & G163 & "EUR"; "price"; B163); 2; 2); INDEX(GOOGLEFINANCE("CURRENCY:" & G163 & "EUR"; "price"; B163 - 3); 2; 2)) * F163

This seems to work for my cases. I am sure it can be more robust, but it works. If need be, I can change the 3 to a 2 or 1 or another number to be close to the right currency exchange.

if you want to replicate this on your end, here’s a screenshot of the setup of my columns: