Spreadsheets

Convert foreign card spends to INR with GOOGLEFINANCE

Convert foreign card spends to INR in Google Sheets with GOOGLEFINANCE, freeze the rate for each transaction as a value, and see why statements differ.

Editorial illustration for Convert foreign card spends to INR with GOOGLEFINANCE

In Google Sheets, =GOOGLEFINANCE("CURRENCY:USDINR") returns the current dollar to rupee rate, and multiplying it by a foreign amount gives an estimate in INR. For a record that does not change tomorrow, copy the result and paste it back as a value, because the live formula keeps moving.

What is the formula for the live rate?

Say A2 holds an amount in dollars, 24.00. In B2 enter:

  • =GOOGLEFINANCE("CURRENCY:USDINR") for the rate alone.
  • =A2*GOOGLEFINANCE("CURRENCY:USDINR") for the INR amount.

Change the pair for other currencies, for example "CURRENCY:EURINR". The data can lag, since Sheets documents that quotes may be delayed, so treat it as an estimate. GOOGLEFINANCE exists only in Google Sheets; Excel has no equivalent function.

How do I get the rate for a past date?

Add a date as the third argument and a start date with "price" in the second:

  • =INDEX(GOOGLEFINANCE("CURRENCY:USDINR","price",A2),2,2)

Here A2 holds the date of the spend. With a single date, the function returns a two-row table with a header and the value; INDEX(...,2,2) picks the number in the second row, second column. For weekends the table may come back empty, so wrap the formula in IFERROR and fall back to the nearest weekday.

Why lock the rate as a value?

A live formula recalculates, so an expense you entered last month would change every time the rate moves. That ruins last month's report. The fix is a short routine: when the day's rate looks right, select the cell, press Ctrl+C, then Ctrl+Shift+V (paste values only). Now the rate on that row is a fixed number, and the INR amount is =A2*C2 using it. Add a column for the date you recorded it.

Why does my card statement show a different amount?

Because the formula gives a market rate, while your card statement shows what was actually debited. The conversion your card applied, the day it was applied and any fee added to it are on your statement, and a formula cannot see any of that. A hypothetical example:

ItemValue
Spend in USD24.00
Formula rate on the spend date₹84.50
Estimated INR (24 x 84.50)₹2,028.00
INR on statement₹2,098.40
Difference₹70.40 (about 3.5 percent of the estimate)

Rates, dates and fees vary by card, so check your own statement and card terms. The sensible rule for a household record is to treat the statement's INR figure as the truth and keep the formula result only as a cross-check.

What if the formula shows an error?

  • Loading... for a few seconds is normal; Sheets is fetching the quote.
  • #N/A on a past date usually means no rate exists for that day, often a weekend. Try the Friday before.
  • #VALUE! often means the currency code is misspelt or the date cell is text. Check that "CURRENCY:USDINR" is typed exactly, with no spaces.
  • A rate that looks far too high or low means the pair is reversed. USDINR is rupees per dollar; INRUSD is the opposite.

If you record foreign spends in a long list, put the currency code in its own column and build the pair with ="CURRENCY:"&C2&"INR", so one formula works for dollars, euros and pounds alike.

How does an expense tracker handle this?

In SpendixAI, each expense can be in its own currency, and totals in your base currency use the rate saved with each transaction, so a past entry does not change when rates move. You choose the base currency before importing, and it cannot be changed once transactions exist. There is also a Foreign-currency spend report. That saves the freeze-as-value routine, though your sheet still works as a double-check.

Quick checklist

  • Record the statement INR amount for every foreign spend.
  • Freeze rates as values.
  • Use IFERROR for dates with no quote.
  • Never edit last month's rates after the fact.
SPENDIXAI JOURNAL

Useful ideas start with a clearer picture of your own spending.

Explore your reports