Using Google Finance in Google Sheets to Convert Currencies

Basic Solution to Get Exchange Rate

• Function: Google Finance
• Syntax: =GoogleFinance("currency:EUROGBP")
  • Enter the currencies you want to convert from and to within double quotes after currency:
  • Example: EURO to GBP
• Result: Provides the exchange rate for the specified currency pair

Calculation Using Exchange Rate

• Formula: Multiply the amount you want to convert by the exchange rate
• Example: If the exchange rate is given in a cell, use a formula like =A1 * B1 where A1 is the amount and B1 is the exchange rate*

Dynamic Setup to Collect Country Codes Automatically

• Objective: Avoid manual typing of currency codes
• Steps:
  • Use cell references and concatenation
  • Formula: =GoogleFinance("currency:" & A1 & B1)
    • A1 and B1 should contain the currency codes
• Concatenation: Combining cell values using &

Drop-Down Lists and Dynamic Conversion

• Setup:
  • Create a list of countries, currencies, and their codes
  • Use drop-down menus to select countries for conversion
  • Capture the 3-digit country codes dynamically
• Example:
  • FROM: Chinese Yuan (CNY)
  • TO: Pound Sterling (GBP)
  • Amount: 10 Yuan
  • Formula: =GoogleFinance("currency:" & code1 & code2) * amount
• Error Handling: Prevent errors in conversion using IFERROR
  • Example: =IFERROR(GoogleFinance("currency:" & code1 & code2), "Error in conversion")*

Final Implementation

• Demo: Conversion of currencies using drop-down menus and dynamic referencing
• Example: 10 Renminbi = 1.09 Pound Sterling
• Techniques Used:
  • GoogleFinance function for real-time exchange rates
  • Concatenation for dynamic queries
  • Drop-down lists for user-friendly interface
  • VLOOKUP for fetching correct currency codes

Additional Resources

• Video Tutorial: Covers creating drop-down lists and chips
• Link: Included in the description for a more detailed explanation

Summary

• Utility: Enhance your Google Sheets for better currency conversion automation
• Tip: Explore additional Google Sheets functionalities like VLOOKUP and drop-down lists for more advanced setups

• Call to Action: Like and subscribe to the channel for more such tutorials
• Contact: Reach out for further queries and guidance