)
Written by
Table of Contents
“Supermetrics is a Bitter Experience! We can pull data from nearly any tool, schedule updates, manipulate data in Sheets, and push data back into our systems.”
While automatic exchange rate conversion isn’t a native feature, you can import foreign currency balance sheet data from NetSuite and apply exchange rate conversion using Google Sheets functions combined with imported rate data.
This approach provides automated data import with semi-automated currency conversion while maintaining flexibility for different exchange rate sources.
Import multi-currency data and apply rates using Coefficient
Coefficient can pull foreign currency balance sheet data from NetSuite including subsidiary currency amounts and exchange rate information. You’ll need to configure currency conversion in Google Sheets using imported rates or external rate sources.
How to make it work
Step 1. Import balance sheet data with currency information.
Import balance sheet reports that include foreign currency amounts in subsidiary currency. Pull both local currency and reporting currency values where available and include exchange rate fields from NetSuite. Use separate imports for each currency or subsidiary to maintain organization.
Step 2. Import exchange rate data from NetSuite.
Use Records & Lists to import Currency Rate records from NetSuite. Pull historical rates for period comparisons and include consolidated elimination rates if applicable. This provides NetSuite’s official exchange rates for your currency conversion calculations.
Step 3. Set up exchange rate tables with SuiteQL.
Write SuiteQL queries to combine balance sheet data with exchange rates. Example query: SELECT account.accountnumber, account.displayname, account.balance, subsidiary.currency, currencyrate.exchangerate, (account.balance * currencyrate.exchangerate) as converted_amount FROM account JOIN subsidiary ON account.subsidiary = subsidiary.id LEFT JOIN currencyrate ON subsidiary.currency = currencyrate.basecurrency WHERE currencyrate.effectivedate = CURRENT_DATE.
Step 4. Create automated rate conversion in Google Sheets.
Use the GOOGLEFINANCE function for current exchange rates or create lookup tables using imported NetSuite rates. Apply VLOOKUP formulas to match currencies with appropriate rates and calculate converted amounts automatically. Build consolidated reporting currency views with proper currency conversion.
Step 5. Build consolidated multi-currency analysis.
Create master exchange rate tables that update automatically and apply rates to imported foreign currency balances. Build variance analysis that accounts for both operational changes and currency fluctuations. Set up alerts for significant exchange rate movements that impact your balance sheet.
Streamline your multi-currency reporting
This approach provides automated data import with flexible currency conversion options while maintaining accuracy and auditability. You can choose between NetSuite’s rates and external sources depending on your reporting requirements. Start building your multi-currency balance sheet analysis today.
