Knowledge Center

Knowledge Center > Finance > Foreign Currency Conversion > Foreign Currency Conversion for Data Source Exports

Foreign Currency Conversion for Data Source Exports

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 foreign currency conversion enabled on your tenant to use this feature.

Data Source Objects that Support Foreign Currency Conversion

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

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 Z-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".

Example Data Source Export File

The following figure shows an example data source export file with home currency conversion fields added.

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.

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

The following figure shows the data source export file with the currency conversion fields added.

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
Invoice

Invoice > Amount Without Tax

Invoice > Amount

The earlier of:

  • Invoice > Posted Date
  • Invoice > Invoice Date

Account > Currency

Invoice Adjustment

Invoice Adjustment > Impact Amount

Invoice Adjustment > Amount

The earlier of:

  • Invoice Adjustment > Create Date
  • Invoice Adjustment > Adjustment Date

Account > Currency

Invoice Item

Invoice Item > Charge Amount

The earlier of:

  • Invoice > Posted Date
  • Invoice > Invoice Date

Account > Currency

Invoice Item Adjustment

Invoice Item Adjustment > Amount

The earlier of:

  • Invoice > Posted Date
  • Invoice > Invoice Date

Account > Currency

Invoice Payment Invoice Payment > Amount

The earlier of:

  • Payment > Created Date
  • Payment > Effective Date

Account > Currency

Payment

Payment > Amount

 The earlier of:

  • Payment > Created Date
  • Payment > Effective Date

Account > Currency

Refund

Refund > Amount

The earlier of:

  • Refund > Create Date
  • Refund > Refund Date

Account > Currency

Refund Invoice Payment Refund Invoice Payment > Refund Amount

The earlier of:

  • Refund > Create Date
  • Refund > Refund Date

Account > Currency

Revenue Schedule Item

Revenue Schedule Item > Amount

The earlier of:

  • Revenue Schedule > Create Date
  • Revenue Schedule Date

Revenue Schedule Item > Currency

Revenue Schedule Item Invoice Item

Revenue Schedule Item Invoice Item > Amount

The earlier of:

  • Invoice > Posted Date
  • Invoice > Invoice Date

Revenue Schedule Item Invoice Item > Currency

Revenue Schedule Item Invoice Item Adjustment

Revenue Schedule Invoice Item Adjustment > Amount 

The earlier of:

  • Invoice > Posted Date
  • Invoice > Invoice Date

Revenue Schedule Invoice Item Adjustment > Currency

Taxation Item

Taxation Item > Tax Amount

Taxation Item > Exempt Amount

The earlier of:

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

Account > Currency

Last modified
17:03, 17 Oct 2016

Tags

Classifications

(not set)