FinJinni can be used to create reports when QuickBooks is using multiple currencies. FinJinni can even create reports using currencies that are not defined in QuickBooks. For example, if you have a US and a Canadian company, you can report on both together in US dollars, Canadian dollars, or even a different currency such as Euros.

The currency conversions that FinJinni does are supported for both QuickBooks Desktop and Online.

The Multi-Currency feature must first be turned on in QuickBooks before it can be used for FinJinni.

How Currency Conversion Works

QuickBooks records a currency code and exchange rate for each transaction. FinJinni uses this information to record its General Ledger in the home currency for the company file. All reports start by using this home currency value.

You can then define reporting-currencies and exchange rates, which FinJinni will use to convert values to a currency which is selectable at the time of reporting.  

If you are consolidating results from multiple companies, all currency values reported will be converted to the currency that you select for the report, even if each company uses a different home currency.

Different reports can be created using different selected currencies.

Turning on Multi-Currency Reporting and Exchange Rates

You must enable multi-currency reporting in FinJinni before you load data from your company files. Once you turn this on, only an incremental refresh is required.

Here is the option on the setup screen that must be checked:

Then use the Settings button to specify how exchange rates will be loaded:

Selections available are:

Use QuickBooks’ most recent rates

Will use the rates defined in the QuickBooks company file for the latest “as of date” up to and including the date when the load is performed. 

Read user-supplied data file

Allows you to create your own data file containing rates for reporting. This also allows different rates to be specified for P&L reports and Balance Sheet reports. See the section below for instructions in setting up this file. 

Use published ECB rates (online)

Downloads the published European Central Bank exchange rates. For more information, see:

https://www.ecb.europa.eu/stats/policy_and_exchange_rates/euro_reference_exchange_rates/html/index.en.html

Notes:

1. The rates are loaded once when the OK button is pressed. They are then automatically reloaded whenever a FinJinni refresh is performed.

2. Even though the ECB rates only show conversions in Euro’s, rates between other currencies will be calculated using these values (e.g. CAD to USD = (CAD to EURO) * (EURO to USD))

3. These settings are common to all companies in a single database. So if you have loaded multiple companies for consolidated reporting, you only need to specify the currency settings once. If you are using multiple databases, you must setup currency reporting for each one.

Choosing the Reporting Currency

When you enable multi-currency reporting in FinJinni, the Query Builder screen will have a drop-down to select the currency for the report:

Supplying a User-Defined Exchange Rate File

To specify your own exchange rates for reporting, create a Comma-Separated-Value (CSV) file, usually in Excel. This file has three or more columns and requires a header with the column name in the first row:

1. From

The “from” currency code

2. To

The “to” currency code

3. Rate

The exchange rate, i.e. the multiplier used to convert 1 unit of “from” currency.

4. Report-Code

A single-character report code for FinJinni to match to report categories: “P” is used for Profit & Loss reporting, “B” is used for Balance Sheet reporting, and “O” is used for other reports that don’t fall into either category. Omit this column or leave it blank to supply a single exchange rate for all reports.

 

Use an “F” to supply a fixed rate applied to a specific account. The company ID and account description must be supplied in additional columns

 

Three additional codes are available for custom conversion rates that you apply to one or more accounts: “Y”, “R”, and “C”. Usually, “Y” is used for a year-to-date average by month, “R” is used for an annual retained earnings value, and “C” for an additional custom rate. However, you can use these rates for any purpose. You map them to specific accounts by creating a custom field on the account called Currency_Code (to which FinJinni will add the _Acct_ prefix).

5. Year-Month

The year and month for this exchange rate, as exactly 7 characters “yyyy-mm” (e.g. “2018-12”). For P&L reporting, the individual transaction dates will be matched to this date. For Balance Sheet reporting, the specified end date for the report will be matched to this date. Omit this column or leave it blank to use the same reporting value regardless of the date.

6. Default?

A “Y” to indicate that the “to” currency on this line is the default currency for FinJinni reports. This will be chosen in the drop-down on the Query Builder screen.

7. Company_ID

When using a fixed rate for a specific account, this is the company ID (FinJinni’s Extract Name) that you provided when you set up the company.

8. Account

When using a fixed rate for a specific account, this is the account number or description (in the “number – name” format used in FinJinni reports).

Notes:

1. All of the “to” currencies will be displayed in FinJinni’s drop-down to select a reporting currency
2. You can leave off an inverse exchange rate and FinJinni will add it automatically. That means that you can enter the rates in either direction, e.g. USD to EUR or EUR to USD.

Here is a sample of a simple exchange-rate file for CAD, MXN, and USD that allows any of these to be chosen when a report is created:

This sample includes a date: