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. These labels dynamically update as you type into the rate box.

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.

Specific Behaviours of the Corporate Exchange Rate Screen

Adding a New Row

The algorithm to suggest a currency pair

• If the table is empty
  • The From Currency will be GBP and the To Currency will be USD.
  • If you set the LedgerNumber property when launching the screen, the From Currency will be USD and the To Currency will be the ledger currency.
• If the table has at least one row
  • The two currencies for the new row will be the same as the currently selected row.

The algorithm to suggest a date and rate

• The suggested date will be the first of the current month. If that date has already been used for the currency pair, the software will try the first of the next month and so on until it reaches a month that has not been used.
• The suggested rate will be the the most recent rate for the currency pair (not necessarily the same as the highlighted row rate if it is not the latest in the sequence) - or 0.0 if there is no rate.

Editing an Existing Row

When you edit an existing row in the corporate exchange rate screen, the algorithm to suggest a date and rate described above will be used if you change either of the currencies. If the currency pair has been used before the software will suggest the next available month for that currency pair (which might be a different date from the original suggestion). The rate will be the rate described above in New Row.

Specific Behaviours of the Daily Exchange Rate Screen

As noted above the Daily Rate screen has loose informal links to the Journal, Gift Batch and Corporate Rate tables. Using these tables the screen can provide the following additional capabilities, which give rise to informational or warning tool-tips.

• A warning if the rate entered is more than 10% different from the neighbouring rate(s) for the currency pair.
• An information tool-tip identifying the places in the journal/gift batch tables where the rate has been used.
• Rates cannot be deleted if they have been posted in a journal and they are not used in any unposted batches.
• Rates can only be edited if they have not yet been posted.
• If a rate is edited that appears in an unposted journal/batch, the corresponding rate in the related batch table is also modified.

An important difference between the Daily and Corporate screens is that the Daily Rate screen can be invoked modally. In this case the From Currency and the To Currency are usually fixed and the latest date is also sometimes known since it is defined by the batch.

Adding a New Row

The algorithm to suggest a currency pair

• If the table is empty
  • The From Currency will be GBP and the To Currency will be USD - unless
  • If the LedgerNumber property was set when launching the screen, the From Currency will be USD and the To Currency will be the ledger currency - unless
    • The modal form was launched with a specific From Currency as well.
• If the table has at least one row
  • The two currencies for the new row will be the same as the currently selected row.

The algorithm to suggest a date and rate

• The suggested date will today's date and the suggested time will be 2:00AM. If that date/time has already been used for the currency pair, the software will try successive ten minute intervals until it reaches a date/time that has not been used.
• The suggested rate will be
  • the the most recent rate for the currency pair (not necessarily the same as the highlighted row rate if it is not the latest in the sequence), or
  • the corporate exchange rate if there is one, or
  • 0.0 otherwise.