FX rate

Documentation Index

Fetch the complete documentation index at: https://docs.runway.com/llms.txt

Use this file to discover all available pages before exploring further.

​

Step 1: Create a Google Sheet for exchange rates

1. Open Google Sheets by typing sheet.new in your browser’s address bar, or go to Google Drive and click + New > Google Sheets > Blank spreadsheet.
2. Rename the sheet to something recognizable (e.g., FX for Runway).
3. In cell A1, use the IMPORTRANGE function to pull in exchange rates from Runway’s live FX sheet, powered by XE.com—the same rates used by QuickBooks and Xero:
   =IMPORTRANGE("your-source-sheet-link","ExchangeRates!A:AI")
   
4. Replace "your-source-sheet-link" with the actual link to the FX sheet: Runway Exchange Rates Sheet
5. Make sure the range A:AI covers all the exchange rate columns. Periodically check the sheet for new currencies and update the range as needed (e.g., A:AI → A:AK).
6. Once the data is imported, create a named range:
   • Highlight all the columns (not just the cells with values).
   • Go to Data > Named ranges.
   • Give it a name (e.g., runway_xe_fx) and click Done.

​

Step 2: Connect the FX sheet to Runway

​

Step 3: Run an FX query in Runway

1. Once your FX Google Sheet integration has finished syncing, open the query editor in Runway.
2. Paste in the following SQL query to retrieve the exchange rate data.
   WITH MonthlyAverageExchangeRates AS (
       SELECT
           month,
           'USD' AS From_Currency,
           To_Currency,
           USD * Rate AS AVG_AMOUNT
       FROM (
           SELECT
               month,
               AVG(USD) AS USD,
               AVG(CHF) AS CHF,
               AVG(EUR) AS EUR,
               AVG(GBP) AS GBP,
               AVG(AUD) AS AUD,
               AVG(HKD) AS HKD,
               AVG(CNY) AS CNY,
               AVG(CAD) AS CAD,
               AVG(INR) AS INR,
               AVG(JPY) AS JPY,
               AVG(NZD) AS NZD,
               AVG(MXN) AS MXN,
               AVG(SEK) AS SEK,
               AVG(PLN) AS PLN,
               AVG(NOK) AS NOK,
               AVG(CRC) AS CRC,
               AVG(SGD) AS SGD,
               AVG(AED) AS AED,
               AVG(ZAR) AS ZAR,
               AVG(BRL) AS BRL,
               AVG(CLP) AS CLP,
               AVG(DKK) AS DKK,
               AVG(KRW) AS KRW,
               AVG(ILS) AS ILS,
               AVG(TRY) AS TRY,
               AVG(GTQ) AS GTQ,
               AVG(DOP) AS DOP,
               AVG(HNL) AS HNL,
               AVG(PEN) AS PEN,
               AVG(PHP) AS PHP,
               AVG(THB) AS THB,
               AVG(MYR) AS MYR,
               AVG(RSD) AS RSD
           FROM
               google_sheets.runway_xe_fx
           GROUP BY
               month
       ) UNPIVOT (
           Rate FOR To_Currency IN (
               CHF, EUR, GBP, AUD, HKD, CNY, CAD, INR, JPY, NZD, MXN, SEK, PLN, NOK,
               CRC, SGD, AED, ZAR, BRL, CLP, DKK, KRW, ILS, TRY, GTQ, DOP, HNL, PEN,
               PHP, THB, MYR, RSD
           )
       )
   ),
   EndOfMonthExchangeRates AS (
       SELECT
           month,
           'USD' AS From_Currency,
           To_Currency,
           USD * Rate AS EOM_AMOUNT
       FROM (
           SELECT
               month,
               max_by(day, day) day,
               max_by(USD, day) USD,
               max_by(CHF, day) CHF,
               max_by(EUR, day) EUR,
               max_by(GBP, day) GBP,
               max_by(AUD, day) AUD,
               max_by(HKD, day) HKD,
               max_by(CNY, day) CNY,
               max_by(CAD, day) CAD,
               max_by(INR, day) INR,
               max_by(JPY, day) JPY,
               max_by(NZD, day) NZD,
               max_by(MXN, day) MXN,
               max_by(SEK, day) SEK,
               max_by(PLN, day) PLN,
               max_by(NOK, day) NOK,
               max_by(CRC, day) CRC,
               max_by(SGD, day) SGD,
               max_by(AED, day) AED,
               max_by(ZAR, day) ZAR,
               max_by(BRL, day) BRL,
               max_by(CLP, day) CLP,
               max_by(DKK, day) DKK,
               max_by(KRW, day) KRW,
               max_by(ILS, day) ILS,
               max_by(TRY, day) TRY,
               max_by(GTQ, day) GTQ,
               max_by(DOP, day) DOP,
               max_by(HNL, day) HNL,
               max_by(PEN, day) PEN,
               max_by(PHP, day) PHP,
               max_by(THB, day) THB,
               max_by(MYR, day) MYR,
               max_by(RSD, day) RSD
           FROM
               google_sheets.runway_xe_fx
           GROUP BY
               month
       ) UNPIVOT (
           Rate FOR To_Currency IN (
               CHF, EUR, GBP, AUD, HKD, CNY, CAD, INR, JPY, NZD, MXN, SEK, PLN, NOK,
               CRC, SGD, AED, ZAR, BRL, CLP, DKK, KRW, ILS, TRY, GTQ, DOP, HNL, PEN,
               PHP, THB, MYR, RSD
           )
       )
   )
   SELECT
       avg_rates.month,
       concat_ws(' - ', avg_rates.from_currency, avg_rates.to_currency) AS exchange,
       avg_rates.from_currency,
       avg_rates.to_currency,
       avg_rates.AVG_AMOUNT,
       eom_rates.EOM_AMOUNT
   FROM
       MonthlyAverageExchangeRates AS avg_rates
   JOIN
       EndOfMonthExchangeRates AS eom_rates
   ON
       avg_rates.month = eom_rates.month
       AND avg_rates.from_currency = eom_rates.from_currency
       AND avg_rates.to_currency = eom_rates.to_currency
   UNION ALL
   SELECT DISTINCT
       avg_rates.month,
       'USD - USD' AS exchange,
       'USD' AS from_currency,
       'USD' AS to_currency,
       1.0 AS AVG_AMOUNT,
       1.0 AS EOM_AMOUNT
   FROM MonthlyAverageExchangeRates avg_rates;
   
3. Click Run Query to preview and validate the results.
4. Once successful, click Configure New Database to create a database from this query.
5. In the setup flow:
   • Rename the database (e.g., FX Rates).
   • Set the data source to the FX query you just created.
   • Under Drivers, choose between:
     • AVG_AMOUNT for average monthly rates.
     • EOM_AMOUNT for end-of-month rates.
   • Under Segment by, select both:
     • FROM_CURRENCY — the base currency (note: this will always be USD from the XE feed).
     • TO_CURRENCY — the currency you are converting into.
6. Click Confirm to finish setting up the database.

​

Step 4: Apply FX translation to your entity’s financial data

1. Navigate to the database for a subsidiary operating in a foreign currency (e.g., GBP).
2. Click + Create New Property and select Number Driver.
3. Give the new driver a clear name (e.g., Amount in USD) to distinguish it from the original amount driver.
4. Click the new column’s header and select Add default formula.
5. In the Default Forecast, enter your conversion formula:
   • Amount [This Segment] — the original amount coming from the integration.
   • EOM_AMOUNT [GBP][USD] — the end-of-month exchange rate from the FX table.
   • AVG_AMOUNT [GBP][USD] — the average exchange rate for the month.
   • Use division when converting from a foreign currency to USD, i.e., Amount [This Segment] / EOM_AMOUNT [GBP][USD]
   • Use multiplication when converting from USD to another currency, i.e., Amount [This Segment] * EOM_AMOUNT [GBP][USD]