Real-time Currency Conversion in Google Sheets

Google Sheets is a powerful tool in case of real-time data updates. You can create currency converters in Google Sheets with real-time exchange rates. You can set up the GOOGLEFINANACE function to convert currencies based on the most recent exchange rates. In this article, we will create a real-time currency conversion system in Google Sheets.

Step 1: Set Up Your Google Sheets

List the necessary columns to create a currency converter.

• A1: Amount
• B1: From Currency
• C1: To Currency
• D1: Conversion

In the From Currency and To Currency column you can use the drop-down list to make it more user-friendly.

• List all currencies in a separate column.
• Select cell B2.
• Go to the Data tab >> select Data Validation >> click on Add Rule.
  • Set Criteria to Drop-down (from a range) >> click on Done.

• Follow the similar steps for the To Currency column. Or you can copy-paste the same drop-down list in the C2 cell.

Step 2: Using GOOGLEFINANCE to Get the Exchange Rate

Google Sheets GOOGLEFINANCE function imports real-time financial data, including currency exchange rate data.

Syntax:

=GOOGLEFINANCE(ticker, [attribute], [start_date], [end_date|num_days], [interval])

For Exchange Rate:

=GOOGLEFINANCE("CURRENCY:<from_currency><to_currency>")

If you want to get the exchange rate from USD to EUR, you can use:

Formula:

=GOOGLEFINANCE("CURRENCY:USDEUR")

This formula will show the exchange rate from the USD to the EUR.

Step 3: Set Up the Conversion Formula

Now you can use the GOOGLEFINANCE function to set up the conversion formula.

• Select cell D2 and insert the following formula.

Formula:

=A2 * GOOGLEFINANCE("CURRENCY:" & B2 & C2)

This formula will convert the currency from B2 to C2 currency.

• A2 is the amount you want to convert.
• GOOGLEFINANCE function fetches the real-time exchange rate between the source and target currencies.

Step 4: Run the Conversion

Now you can test your currency converter by selecting the currencies from the drop-down.

• Insert the amount in the Amount column.
• Select USD from the From Currency drop-down.
• Select EUR from the To Currency drop-down.
• The conversion formula will automatically convert the currencies in real time.

The exchange rate fetched by the GOOGLEFINANCE function is updated automatically in real-time. Every time you open the sheet or refresh it, the rate is updated.

Step 5: Customize for Multiple Currencies

You can use this setup for multiple currencies at a time.

• Expand the drop-down list of the From Currency and To Currency columns.
• Insert multiple amounts in the amount column.
• Drag the formula of the D2 cell to copy the formula down.

Handle Errors

If you enter invalid currencies or currency pairs that are not available, then the GOOGLEFINANACE function will #N/A error. To handle such scenarios, you can use the IFERROR function.

Formula:

=IFERROR(A2 * GOOGLEFINANCE("CURRENCY:" & B2 & C2), "Error: Invalid Currency Pair")

Creating a Historical Rate Tracker

If you want, you can track live exchange rates over time. You will need to create a historical tracker.

• In column A list the currencies or you can use the currency drop-down list.
• Select cell B1 and insert the following formula.

Formula:

=GOOGLEFINANCE("CURRENCY:" & A2 & A3, "price", TODAY()-30, TODAY(), "DAILY")

This formula will fetch 30 days of historical exchange rates. The data will automatically populate the columns.

Conclusion

You can create a real-time currency converter by following the above steps. Google Sheets offers simple and efficient functions and formulas to fetch real-time exchange rates. This converter will automatically update the currency rate every minute and whenever you open Google Sheets. Explore the GOOGLEFINANCE function to make it more dynamic. For important financial calculations, manually refresh the sheet or add a time stamp to track when rates were last updated.