Problem: Manual Currency Conversion Every Time Exchange Rates Change
When running an international business, exchange rate fluctuations are part of daily operations. Every time a buyer sends a quote request, we had to open a banking website to check the current rate, then manually input it into our spreadsheet. With 10 requests per day, that meant repeating the same task 10 times.
The most frustrating moment came when rates shifted dramatically. Yesterday's calculated price could become a loss at today's rate.
Solution: Making Exchange Rates 'Live' with Google Sheets Formulas
Google Sheets allows us to fetch external data in real-time using functions like IMPORTXML or IMPORTDATA. We asked Claude Code to create an automated cell formula leveraging this capability.
Our Request to Claude:
Can you create a Google Sheets formula that pulls the current
USD-KRW exchange rate from a central bank API in real-time?
Also, can you explain how to set up conditional formatting so that
if the rate changes by more than 1% from yesterday, the cell color changes automatically?
Implementation Steps
Step 1: Create a Formula to Fetch Exchange Rate Data
Enter this formula in cell A1 of your Google Sheet:
=GOOGLEFINANCE("USDKRW")
GOOGLEFINANCE is a built-in Google Sheets function that retrieves exchange rates and stock data in real-time. No additional authentication or API keys required.
For more detailed data (hourly changes), you can use Claude Code to build a custom formula using official central bank APIs.
Step 2: Create a Cell to Compare Yesterday's Rate
Enter yesterday's rate in B1, and place this formula in C1 to calculate percentage change:
=IF(A1<>B1, (A1-B1)/B1*100, 0)
This formula automatically calculates the percentage change in the exchange rate.
Step 3: Apply Alert Colors Using Conditional Formatting
• Select cell range C1
• Menu: Format > Conditional formatting
• Enter condition: C1 > 1 (for 1% or greater increase)
• Assign color: Red background
• Add another: C1 < -1 (for 1% or greater decrease in blue)
Now whenever the exchange rate changes, your cell color updates automatically.
Step 4: Automate Email Notifications (Optional)
Use Claude Code to write an Apps Script that automatically sends an email when rates exceed a specific threshold.
function checkExchangeRate() {
const sheet = SpreadsheetApp.getActiveSheet();
const rate = sheet.getRange('A1').getValue();
const yesterday = sheet.getRange('B1').getValue();
const change = Math.abs((rate - yesterday) / yesterday * 100);
if (change > 1) {
GmailApp.sendEmail('your-email@gmail.com',
'Exchange Rate Alert',
'Exchange rate has shifted by ' + change.toFixed(2) + '%. Current: ' + rate);
}
}
Set this script to run automatically once per hour, and you'll never miss significant rate movements.
Results: Hourly Rate Tracking, Automatic Price Adjustments
After running this system for three weeks, we observed:
• Exchange rate detection time: 90% reduction compared to manual calculation
• Manual data entry errors: Nearly zero
• Average quote response time to buyers: Reduced from 4 hours to 15 minutes
The most surprising moment was when the exchange rate spiked one afternoon at 3 PM, and our cell automatically turned red. This alert helped us avoid entering an unfavorable contract at that moment.
Tip: Track Multiple Currencies Simultaneously
If you need to monitor USD, EUR, CNY, and JPY at the same time:
=ARRAYFORMULA({GOOGLEFINANCE("USDKRW"), GOOGLEFINANCE("EURKRW"), GOOGLEFINANCE("CNYKRW")})
ARRAYFORMULA allows you to fetch multiple exchange rates in a single cell.
Next Steps
Google Sheets handles basic needs well, but for more complex strategies (like comparing average rates or fixing rates by purchase order), Claude Code can build custom Apps Scripts.
Non-developers can adapt these formulas to their own currency pairs by simply changing function names and variables. Start with one GOOGLEFINANCE function, and complexity can grow later.