Skip to main content

Foreign Currency Conversion for data source exports

Zuora

Foreign Currency Conversion for data source exports

  1. Last updated
  2. Save as PDF

This article explains how to include foreign currency conversion data in data source exports. You can export transaction amounts converted into your home currency and other specified currencies.

You must have the Currency Conversion feature enabled on your tenant to use this feature.

The Currency Conversion feature is in Limited Availability. If you wish to have access to the feature, submit a request at Zuora Global Support. 

Data Source Objects that Support Foreign Currency Conversion

You can export currency conversion data for the following data source objects:

If you enable the Invoice Settlement feature, you can also export currency conversion data for the following data source objects:

The Invoice Settlement feature is generally available as of Zuora Billing Release 296 (March 2021). This feature includes Unapplied Payments, Credit and Debit Memos, and Invoice Item Settlement. If you want to have access to the feature, see Invoice Settlement Enablement and Checklist Guide for more information. After Invoice Settlement is enabled, the Invoice Item Adjustment feature will be deprecated for your tenant.

You can include currency conversion data only for the base object of a data source export, not its joined objects. For example, if you want to export foreign currency conversion data for the Invoice Item object, you must perform a data source export on the Invoice Item data source.

Convert Transaction Amounts Into Your Home Currency

You can use the Zuora UI, SOAP API, and AQuA API to create data source exports that include transaction amounts converted into your home currency.

Prerequisites

In Finance Settings > Manage Currency Conversion, ensure that you have:

  • Configured your home currency
  • Selected the Automatically include additional Currency Conversion information in data source exports checkbox.

See Configure Foreign Currency Conversion for detailed steps.

Creating the Data Source Export

To automatically include your home currency conversion data in a data export, select the Amount field of the base object in the data source export. For example, if you export data from the Payment data source, select the Amount field from the Payment object.

To see which field you must select for each data source object, refer to the "Amount" column in the section below called "Values for the Foreign Currency Conversion Fields".

If you want to download or export a large CSV file, Zuora recommends you to use text editors other than Microsoft Excel to open the file. The maximum row number that Excel supports is 1,048,576.

Currency Conversion Fields Added to the Export

The following table describes the foreign currency conversion fields that are automatically added to the data source export. The "Field Name" column gives the names of the fields as they appear when you create the export using the SOAP API. If you create the export from the Zuora UI, the field names are formatted differently. For example, "Invoice.AmountHomeCurrency" would instead be "Invoice: Amount (Home Currency)".

Field Name Description

<Object>.AmountHomeCurrency

The amount in your home currency.

Note that the name of this field differs depending on the base object. For example, for the Invoice Item object, this field is called "InvoiceItem.ChargeAmountHomeCurrency".
<Object>.AmountCurrencyRounding

Rounding difference in the amount.

  • With an inverse rate:

    [Rounding difference] = [transaction amount] * [exchange rate] - [home currency amount]

  • With an non-inverse rate:

    [Rounding difference] = [transaction amount] / [exchange rate] - [home currency amount]

<Object>.ExchangeRate

The exchange rate used to convert the transaction currency into your home currency.

<Object>.ExchangeRateDate

The date used to determine the exchange rate.

<Object>.HomeCurrency

The home currency configured on your tenant.

<Object>.ProviderExchangeRateDate

The exact date used to get the rate from the exchange rate provider.

For example, you use the monthly rate and set the day of month to 1. For a transaction with the date "2015-10-15", the Exchange Rate Date is "2015-10-15" while the Provider Exchange Rate Date is "2015-10-01".

<Object>.TransactionCurrency

The currency used by the customer.

RatePlanCharge.BookingExchangeRate

The exchange rate used to convert the transaction currency into the home currency.

Zuora determines the value of the Booking Exchange Rate Date field based on the Original Order Date field.

This field is only available for the Rate Plan Charge object.

RatePlanCharge.BookingExchangeRateDate

The date used to determine the RatePlanCharge.BookingExchangeRate.

This field is only available for the Rate Plan Charge object.

RatePlanCharge.BookingProviderExchangeRateDate

The exact date used to determine the rate from the exchange rate provider.

This field is only available for the Rate Plan Charge object.

Convert Transaction Amounts Into Any Currency

You can use the Zuora SOAP API and AQuA API to create data source exports that include transaction amounts converted into one or more specified currencies. This feature is not available when creating exports from the Zuora UI.

Prerequisites

There are no prerequisites.

Creating the Data Source Export Using the SOAP API

When creating a data source export using the SOAP API:

  • Add the ConvertToCurrencies field to the create() call and specify the currencies you want amounts to be converted into. You can specify any number of currencies. Specify the currencies using their ISO currency codes and separate each currency with a comma.
  • Select the Amount field of the base object in the data source export.
  • Ensure that you are using WSDL version 78 or later.

The following example SOAP API request specifies that conversion data for British Pound Sterling (GBP) and Japanese Yen (JPY) be included in the data source export.

...
<ns1:create xmlns:ns1="http://api.zuora.com/">
    <ns1:zObjects xsi:type="ns2:Export" xmlns:ns2="http://object.api.zuora.com/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
          <ns2:Format>csv</ns2:Format>
          <ns2:Query>select Amount, CreatedDate from Invoice</ns2:Query>
          <ns2:ConvertToCurrencies>GBP,JPY</ns2:ConvertToCurrencies>
    </ns1:zObjects>
</ns1:create>
...

Creating the Data Source Export Using the AQuA API

When creating a data source export using the AQuA API:

  • Add the convertToCurrencies field to the request and specify the currencies you want amounts to be converted into. You can specify any number of currencies. Specify the currencies using their ISO currency codes and separate each currency with a comma.
  • Select the Amount field of the base object in the data source export.
  • Set the type field to "zoqlexport", otherwise an error is returned. This feature is not supported for type "zoql".
  • Ensure that you are using API version 78 or later. You can do this by specifying the the API version in the apiVersion field. Or, if you do not pass in the apiVersion field, the latest API version is used by default.

The following example AQuA API request specifies that conversion data for British Pound Sterling (GBP) and Japanese Yen (JPY) be included in the data source export. 

{
"format" : "csv",
"version" : "1.2",
"name" : "MyExampleExport",
"encrypted" : "none",
"useQueryLabels" : "true",
"partner" : "MyPartnerID",
"project" : "123456",
"dateTimeUtc" : "true",
"queries"  : [ {
    "name" : "Invoice",
    "convertToCurrencies": "GBP,JPY",
    "query" : "Select amount, createddate from invoice",
    "type" : "zoqlexport"
  }]
}

Example Data Source Export File

The above example SOAP API and AQuA API requests generate a data source export file with the following currency conversion fields added:

  • Invoice.AmountGBP
  • Invoice.AmountCurrencyRoundingBGP
  • Invoice.ExchangeRateGBP
  • Invoice.AmountJPY
  • Invoice.AmountCurrencyRoundingJPY
  • Invoice.ExchangeRateJPY
  • Invoice.ExchangeRateDate
  • Invoice.ProviderExchangeRateDate
  • Invoice.TransactionCurrency

Currency Fields Added to the Data Source Export

The following table describes the currency conversion fields that are automatically added to your data source export.

Field Name Example Description
<Object>.Amount<Currency> Invoice.AmountEUR

The transaction amount converted into the specified currency.

Note that this field has a different name depending on the object.
<Object>.AmountCurrencyRounding <Currency> Invoice.AmountCurrencyRoundingJPY

Rounding difference in the amount.

  • With an inverse rate:

    [Rounding difference] = [transaction amount] * [exchange rate] - [currency amount]

  • With an non-inverse rate:

    [Rounding difference] = [transaction amount] / [exchange rate] - [currency amount]
<Object>.ExchangeRate<Currency> Invoice.ExchangeRateEUR The exchange rate used to convert the transaction currency into the specified currency.
<Object>.ExchangeRateDate Invoice.ExchangeRateDate The date used to determine the exchange rate.
<Object>.ProviderExchangeRateDate Invoice.ProviderExchangeRateDate

The exact date used to get the rate from the exchange rate provider.

For example, you use the monthly rate and set the day of month to 1. For a transaction with the date "2015-10-15", the Exchange Rate Date is "2015-10-15" while the Provider Exchange Rate Date is "2015-10-01".
<Object>.TransactionCurrency Invoice.TransactionCurrency The currency used by the customer.

Can I Convert Amounts Into My Home Currency and Other Currencies in the Same Export File?

You can include conversion fields for your home currency and for any other specified currencies in the same data source export. In this scenario, the following fields appear only once and apply to both the home currency and the other currencies:

  • <Object>.ExchangeRateDate
  • <Object>.ProviderExchangeRateDate
  • <Object>.TransactionCurrency

Values for the Foreign Currency Conversion Fields

The following table shows where Zuora gets the values for the transaction amount that is being converted, the exchange rate date, and the transaction currency for each transaction.

Transaction Type

 Amount Exchange Rate Date Transaction Currency

Credit Balance Adjustment

Credit Balance Adjustment > Amount

Credit Balance Adjustment > Adjustment Date

Account > Currency

Discount Applied Metrics

Discount Applied Metrics > MRR

Discount Applied Metrics > TCV

The earlier of:

  • Discount Applied Metrics > Created Date
  • Discount Applied Metrics > Start Date

Account > Currency

Invoice

Currency conversion data is provided for posted invoices only.

Invoice > Amount Without Tax

Invoice > Amount

The earlier of:

  • Invoice > Posted Date
  • Invoice > Invoice Date

Invoice > Currency

Invoice Adjustment

Invoice Adjustment > Impact Amount

Invoice Adjustment > Amount

The earlier of:

  • Invoice Adjustment > Created Date
  • Invoice Adjustment > Adjustment Date

Invoice > Currency

Invoice Item

Currency conversion data is provided for items in posted invoices only.

Invoice Item > Charge Amount

The earlier of:

  • Invoice > Posted Date
  • Invoice > Invoice Date

Invoice > Currency

Invoice Item Adjustment

Invoice Item Adjustment > Amount

The earlier of:

  • Invoice > Posted Date
  • Invoice > Invoice Date

Invoice > Currency

Invoice Payment

Invoice Payment > Amount

The earlier of:

  • Payment > Created Date
  • Payment > Effective Date

Payment > Currency
Order Line Item OrderLineItem > Amount
OrderLineItem > AmountWithoutTax
OrderLineItem > AmountPerUnit
OrderLineItem > ListPricePerUnit
OrderLineItem > ListPrice		 Order Line Item > Original Order Date Account > Currency

Payment

Payment > Amount

 The earlier of:

  • Payment > Created Date
  • Payment > Effective Date

Payment > Currency

Refund

Refund > Amount

The earlier of:

  • Refund > Created Date
  • Refund > Refund Date

Refund > Currency
Rate Plan Charge Rate Plan Charge > DMRC
Rate Plan Charge > DTCV
Rate Plan Charge > MRR
Rate Plan Charge > TCV

If Rate Plan Charge > Original Order Date exists, use the original order date.

or,

Use the earlier of:

  • Rate Plan Charge > Created Date
  • Rate Plan Charge > Effective Start Date
 Subscription > Currency

Refund Invoice Payment

Refund Invoice Payment > Refund Amount

The earlier of:

  • Refund > Created Date
  • Refund > Refund Date

Refund > Currency

Revenue Schedule Item

Revenue Schedule Item > Amount

Revenue Schedule > Exchange Rate Date

Revenue Schedule Item > Currency

Revenue Schedule Item Invoice Item

Revenue Schedule Item Invoice Item > Amount
  • For auto-created revenue schedules from invoices, it is the earlier of:
    • Invoice > Invoice Date
    • Invoice > Created Date

  • For manually created revenue schedules from transactions (via REST API or Mass Updater), it is the earlier of:

    • Invoice > Invoice Date

    • Revenue Schedule Invoice Item > Created Date

Revenue Schedule Item Invoice Item > Currency

Revenue Schedule Item Invoice Item Adjustment

Revenue Schedule Invoice Item Adjustment > Amount 
  • For auto-created revenue schedules from invoices, it is the earlier of:

    • Invoice Item Adjustment > Adjustment Date

    • Invoice Item Adjustment > Created Date

  • For manually created revenue schedules from transactions (via REST API or Mass Updater), it is the earlier of:

    • Invoice > Invoice Date

    • Revenue Schedule Invoice Item Adjustment > Created Date

Revenue Schedule Item Invoice Item Adjustment > Currency

Revenue Schedule Item Credit Memo Item

Revenue Schedule Item Credit Memo Item > Amount
  • For auto-created revenue schedules from credit memos from invoices, it is the earlier of invoice date and invoice posted date.
  • For auto-created revenue schedules from credit memos from charges and bill runs, it is the earlier of credit memo date and credit memo posted date.
  • For manually created revenue schedules from transactions (via REST API or Mass Updater), it is the earlier of:
    • Revenue Schedule Credit Memo Item > Linked Transaction Date
    • Revenue Schedule Credit Memo Item > Created Date

Revenue Schedule Item Credit Memo Item > Currency

Revenue Schedule Item Debit Memo Item

Revenue Schedule Item Debit Memo Item > Amount
  • For auto-created revenue schedules from debit memos from invoices, it is the earlier of invoice date and invoice posted date.
  • For auto-created revenue schedules from debit memos from charges and bill runs, it is the earlier of credit memo date and debit memo posted date.
  • For manually created revenue schedules from transactions (via REST API or Mass Updater), it is the earlier of:
    • Revenue Schedule Debit Memo Item > Linked Transaction Date
    • Revenue Schedule Debit Memo Item > Created Date

Revenue Schedule Item Debit Memo Item > Currency

Taxation Item

Currency conversion data is provided for items in posted invoices only.

Taxation Item > Tax Amount

Taxation Item > Exempt Amount

The earlier of:

  • Taxation Item > Created Date
  • Taxation Item > Tax Date

Invoice > Currency
The following transaction types are only available if you enable the Invoice Settlement feature.

 Credit Memo

Credit Memo > Total Amount

Credit Memo > Total Amount Without Tax
  • For credit memos created from invoices: Invoice > Invoice Exchange Rate Date
  • For credit memos created from charges and bill runs: The earlier of:
    • Credit Memo > Posted Date
    • Credit Memo > Memo Date

Credit Memo > Currency

Credit Memo Item

Credit Memo Item > Amount Without Tax
  • For credit memos created from invoices: Invoice > Invoice Exchange Rate Date
  • For credit memos created from charges and bill runs: The earlier of:
    • Credit Memo > Posted Date
    • Credit Memo > Memo Date

Credit Memo > Currency

Credit Memo Application

Credit Memo Application > Amount
  • For credit memos created from invoices: Invoice > Invoice Exchange Rate Date
  • For credit memos created from charges and bill runs: The earlier of:
    • Credit Memo > Posted Date
    • Credit Memo > Memo Date

Credit Memo > Currency

Credit Memo Application Item

Credit Memo Application Item > Amount
  • For credit memos created from invoices: Invoice > Invoice Exchange Rate Date
  • For credit memos created from charges and bill runs: The earlier of:
    • Credit Memo > Posted Date
    • Credit Memo > Memo Date

Credit Memo > Currency

Credit Memo Part

Credit Memo Part > Amount
  • For credit memos created from invoices: Invoice > Invoice Exchange Rate Date
  • For credit memos created from charges and bill runs: The earlier of:
    • Credit Memo > Posted Date
    • Credit Memo > Memo Date

Credit Memo > Currency

Credit Memo Part Item

Credit Memo Part Item > Amount
  • For credit memos created from invoices: Invoice > Invoice Exchange Rate Date
  • For credit memos created from charges and bill runs: The earlier of:
    • Credit Memo > Posted Date
    • Credit Memo > Memo Date

Credit Memo > Currency

Credit Taxation Item

Credit Taxation Item > Tax Amount

Credit Taxation Item > Exempt Amount
  • For credit memos created from invoices: Invoice > Invoice Exchange Rate Date
  • For credit memos created from charges and bill runs: The earlier of:
    • Credit Memo > Posted Date
    • Credit Memo > Memo Date

Credit Memo > Currency

Debit Memo

Debit Memo > Total Amount

Debit Memo > Total Amount Without Tax
  • For debit memos created from invoices: Invoice > Invoice Exchange Rate Date
  • For debit memos created from charges and bill runs: The earlier of:
    • Debit Memo > Posted Date
    • Debit Memo > Memo Date

Debit Memo > Currency

Debit Memo Item

Debit Memo Item > Amount Without Tax
  • For debit memos created from invoices: Invoice > Invoice Exchange Rate Date
  • For debit memos created from charges and bill runs: The earlier of:
    • Debit Memo > Posted Date
    • Debit Memo > Memo Date

Debit Memo > Currency

Debit Taxation Item

Debit Taxation Item > Tax Amount

Debit Taxation Item > Exempt Amount
  • For debit memos created from invoices: Invoice > Invoice Exchange Rate Date
  • For debit memos created from charges and bill runs: The earlier of:
    • Debit Memo > Posted Date
    • Debit Memo > Memo Date

Debit Memo > Currency

Payment Application

Payment Application > Apply Amount

The earlier of:

  • Payment > Effective Date
  • Payment > Created Date

Payment > Currency

Payment Application Item

Payment Application Item > Amount

The earlier of:

  • Payment > Effective Date
  • Payment > Created Date

Payment > Currency

Payment Part

Payment Part > Amount

The earlier of:

  • Payment > Effective Date
  • Payment > Created Date

Payment > Currency

Payment Part Item

Payment Part Item > Amount

The earlier of:

  • Payment > Effective Date
  • Payment > Created Date

Payment > Currency

Refund Application

Refund Application > Apply Amount

The earlier of:

  • Refund > Refund Date
  • Refund > Created Date

Refund > Currency

Refund Application Item

Refund Application Item > Amount

The earlier of:

  • Refund > Refund Date
  • Refund > Created Date

Refund > Currency

Refund Part

Refund Part > Refund Amount

The earlier of:

  • Refund > Refund Date
  • Refund > Created Date

Refund > Currency

Refund Part Item

Refund Part Item > Amount

The earlier of:

  • Refund > Refund Date
  • Refund > Created Date

Refund > Currency