Exchange Rates (Design and Test)

Summary

This wiki page describes the behaviour of two screens:

• Setup Daily Exchange Rates
• Setup Corporate Exchange Rates

These screens have a number of features in common, but the Daily Exchange Rate screen has a number of additional features. This page has been written to

• help you understand the capabilities of these two screens
• help testers to construct suitable tests for all the features of the screens.

The Daily Exchange Rate screen can be launched from the main menu as a standard set-up screen. In addition it can be launched as a modal dialog from three other main finance screens.

The screens look deceptively simple - much like many other set-up screens in OpenPetra. But don't be deceived! There is a lot going on behind the scenes!

Underlying Tables

The Corporate Exchange Rate screen is backed by a single table:

• a_corporate_exchange_rate table (read/write)

The Daily Exchange Rate screen is backed by the

• a_daily_exchange_rate table (read/write)

But in addition this screen is also loosely linked to these additional tables:

• a_corporate_exchange_rate table (read only)
• a_journal table (read/write)
• a_gift_batch (read/write)

None of these additional tables are linked as references. But they can be implicitly linked by having common data values in certain columns.

You will need data in all these tables when testing the screens.

Initial Set-up

While it is possible to start with an empty database, the best starting point is to start with a database that already contains some financial records including GL batches (posted and unposted) and Gift batches. The two exchange rate tables should initially both be empty.

Standard Workflow

You should start by defining your corporate exchange rates. This screen is launched from the main menu -> Finance Setup -> Exchange Rates (Cross Ledger). On the screen you can add a number of rows of currency pairs, effective dates and rates. The 'To Currency' will always be the ledger currency that you work with. The 'From Currency' will be the 'foreign' currency that you may need to convert to the ledger currency. Over time you will build up a sequence of exchange rates for a given currency pair. By default the suggested effective date will be the first day of a month. You may change the corporate rate each month, or you may decide to work with a rate from 1 January for the whole year.

When you create your corporate rates try to remember to include a rate for a date that pre-dates all your batches because, by default, OpenPetra will suggest the first day of the current month as the effective date for a new row. This is normally a good choice, but if you have already added batches of transactions before setting up exchange rates you will miss out on some useful features if you do not have at least one rate for each currency pair that is effective for the start of your data.

There are two alternative workflows for Daily Exchange Rate. If you rarely have a foreign currency transaction you can set up an exchange rate direct from the GL Batch / Gift Batch / GL Revaluation screen. At a later time you can review the rates you have used by loading the Daily Rate screen from the main menu. Alternatively, if you work with a number of foreign currency transactions it may be preferable to set up a new daily rate from the main menu first, and then access that rate from the other screens.

By the way - as we have seen these screens are both Cross-Ledger. The rates that you set up are not necessarily tied to a specific ledger, although specific ledgers may contain these rates. When you launch one of the screens from the main menu you are certainly not tied to a specific ledger. When you launch the Daily Rate screen from one of the finance screens you pass the ledger currency as the 'To Currency', but there is no referential database link between the exchange rate table and any other table. So the data in the exchange rate table is truly cross-ledger.

Common Behaviours of Corporate and Daily Exchange Rate Screens

Both screens have a 'From Currency', a 'To Currency' and an 'Effective Date'. The Daily Rate screen also has an 'Effective Time'. Together these fields make up the primary key for each row (and so must be unique). Each row has an 'Exchange Rate'. Whenever you add new rows OpenPetra automatically adds equivalent 'inverse rows'. Thus if you add a row for A to B at a rate of 2.0, and additional row for B to A at 0.5 will also be added for the same date (and time). This action happens when you save your changes.

The 'To Currency' is typically the currency that you do your work in, so the grid does two things automatically to help you

• it automatically sorts the columns
  • sorting is by: To Currency + From Currency + Date (+Time) where records are latest first.
  • thus if you work in, say GBP, you will find a group of GBP records in the To Currency column in which all the From Currencies are alphabetical and with the latest rates at the top of each From/To group.
  • if you only work in GBP, but with conversions to five 'foreign' currencies (e.g. EUR, USD, JPY etc), you will also have five smaller groups of 'To' currencies for each of these.
• it provides a checkbox so that you can hide all the other 'To' currencies apart from the one currently highlighted.
  • this provides a means of 'hiding' all the inverses of your standard GBP in the example described here.

Both screens have an 'Invert Value' button. This simply takes the current rate in the textbox and substitutes the reciprocal value. This is particularly useful because OpenPetra usually requires the inverse of the rate that is often available in a newspaper, for example. As an Englishman I am used to British pounds being quoted as 1.6 GBP to the dollar or 80 pence to the euro. But the value that OpenPetra wants is the inverse of that. Please note that if you click the Invert Value button twice you may not get back to the number you first entered. This behaviour is not a bug - it is a consequence of rounding errors.

To further help with ensuring that the correct rates are entered there are two help labels at the bottom of the screen to make it quite clear what a single unit of each currency is worth in the other currency.

Adding a New Row

In common with all the other screens in OpenPetra, when you add a new row the software fills in the fields with suggested values. While the two screens differ slightly in the detail of the algorithm, this much is common to both.

• First there is an algorithm to determine the From Currency and To Currency.
• Second there is an algorithm to suggest a date and a rate.

Having populated the controls with the suggested values the focus is placed on the Effective Date control. Depending on the context the two currencies may or may not be changeable.

Editing an Existing Row

Where a row can be edited the behaviour is the same as in Petra 2.x, inasmuch as any change to either currency causes the second part of the New Row algorithm described above to be re-run with the new currency pairing. This results in an automatic change to the content of the date and rate controls. This is unusual in OpenPetra, but in this case is a very useful behaviour.

Validation

The only validation over and above the standard checks is that the date is not empty and that the rate is not negative or zero. The software will suggest a rate of zero for a new record if it cannot come up with a better suggestion - thereby forcing the user to enter a valid rate.