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 wiki 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 Corporate screen is never launched modally.

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!

Finally, the exchange rate screens are Cross-Ledger. The rates in the two screens can be used on multiple ledgers, as we shall see below.

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 linked to these additional tables:

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

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.

The Corporate Exchange Rate Screen

This is much like many other 'Maintain Table' screens in Open Petra. The screen displays the contents of the backing table and allows the standard 'New', 'Edit' and 'Delete' operations on the data. It has a filter panel as well.

The Daily Exchange Rate Screen

The relationship between this screen and its underlying data is not like any other screen in Open Petra. While at first sight it appears that the grid is displaying the data you would expect to find in the daily exchange rate table most of the data in the grid has probably come from the journal and gift batch tables mentioned above. Indeed it will be common for the screen to display hundreds of rows of data yet the daily exchange rate table is empty! This is because when the client requests the exchange rate data the server examines the journal and gift tables for any entries where the transaction currency is different from the ledger base currency. The server gathers all the information from those rows and populates the data set with them first. Then it adds to the data set any rows in the daily exchange rate table that are NOT used in any gift batch or journal. Or, to put it another way, the daily exchange rate table is only needed to provide exchange rate values that are not actually used anywhere.

This means that the Daily Exchange Rate screen can be relied on to always show all the different rates that have been used for foreign transactions across all ledgers. In addition the screen displays how many times a rate was used and, using a second grid, displays details of each of the journal(s) or gift batch(es) that used the rate on the particular date.

The screen shot will make this clear.

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, an 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 one or both of the two currencies may be fixed by disabling changes to the combo boxes.

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

Manual validation over and above the standard database-level checks includes:

• that the date is not empty
• 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.
• on Daily Rate modal screens that the date is in the range allowed by the parent screen

Specific Behaviours of the Corporate Exchange Rate Screen

Adding a New Row

The logic for adding a new row is the same as 2.x.

The algorithm to suggest a currency pair

• If the table is empty
  • If the LedgerNumber property was set when launching the screen, the To Currency will be the ledger currency and the From Currency will be USD, unless the ledger currency is USD, in which case the From Currency will be GBP. (Note that the ledger number is not set anywhere in the current build of OpenPetra) - otherwise
  • If a ledger has been created that the user has access rights to, the To Currency will be the first accessible ledger currency where the ledger status is TRUE and the From Currency will be USD, unless the ledger currency is USD, in which case the From Currency will be GBP. - otherwise
  • The From Currency will be GBP and the To Currency will be USD.
• 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 (in the future) that has not been used.
• The suggested rate will be the the last rate in the sequence for the currency pair (not necessarily the same as the highlighted row rate if it is not the latest in the sequence).
  • If there is no previously entered rate for the currency pair, 0.0 will be proposed.

The algorithm to Add an Inverse Rate

When you save your changes the software will add a new inverse rate or update an existing one for the inverse currency pair. However please note that if you edit an existing rate the software does not try to find an equivalent inverse and change that rate. This is by design and is because the inverse rate might have already been used as the rate for another table.

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 suggested 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 or if they are used in an 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.

It is important to realise that several of these features are only activated when you click the Allow Edit/Delete button.

Although the algorithm to check if rates have been used is the same as 2.x, I believe that the algorithm is flawed particularly for currency pairs where the rate of exchange is at or close to 1.000. TODO: I would like to improve the algorithm to remove this flaw after discussion with the finance team.

The algorithm to check if a rate for a date has been used looks for any journal entries that use the rate for that currency pair between the date of the current row and the date of the next (later) row. Note that if there is more than one rate specified for the date of the current row (but at a different time), then all rates for that date are effective until the next different later date. For example if Jan 1 has two rates of 10.00 at 02:00am and 10.05 at 02:10am and the next rate is Jan 8, then selecting the row with the 10.00 rate will find all places where that is used between Jan 1 and Jan 8, even though there is a rate of 10.05 in between.

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 on a Modeless Screen

• If the table is empty
  • If the LedgerNumber property was set when launching the screen, the To Currency will be the ledger currency and the From Currency will be USD, unless the ledger currency is USD, in which case the From Currency will be GBP. (Note that the ledger number is not set anywhere in the current build of OpenPetra) - otherwise
  • If a ledger has been created that the user has access rights to, the To Currency will be the first accessible ledger currency where the ledger status is TRUE and the From Currency will be USD, unless the ledger currency is USD, in which case the From Currency will be GBP. - otherwise
  • The From Currency will be GBP and the To Currency will be USD.
• 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 currency pair on a Modal Screen

When the screen is invoked modally the To Currency will be known and cannot be changed. The From Currency may also be fixed as well.

• If the table is empty
  • The fixed currencies are used where known
  • If the From Currency is not fixed, it will be USD, unless that is the To Currency. In that case GBP is suggested.
• 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 (Modal and Modeless)

The date range may be restricted on a modal screen. There may be a minimum start date or a maximum end date or both.

• If the end date is specified, that will be the suggested date - otherwise
• The suggested date will today's date
• 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 corporate exchange rate effective on the suggested date, if there is one, or
  • the the most recent daily exchange rate prior to the suggested date 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 otherwise.

(Note that the rate algorithm might be slightly different from 2.x in that the first choice is the corporate rate. This was found to be necessary as a consequence of the field-by-field validation of OpenPetra.)

Editing an Existing Row

Like the corporate rate screen, any change to the currency pair will automatically update the date/time and rate

Editing a Rate That Has Been Used

If you have activated the Allow Edit/Delete mode, you may (within certain rules) be able to change a rate that has been used in the Journal or Gift Batch tables assuming that the rate has not been 'posted'. If you change the rate you will not only change the rate in the Daily Rate table that backs the exchange rate screen, but you will also change the rate in the underlying Journal and Gift Batch tables. You will only change the rates between the dates specified in the information message.

Corporate Rate Validation in 2.x That Is Not Implemented in OpenPetra

In 2.x the software validates that there is only one rate per accounting period and that it is on the first day of the accounting period. However this seems difficult to do reliably because we cannot know for sure which ledger is being used.

Testing the Screens

There are numerous tests that need to be made, particularly on the Daily Rate screen.

On Both Screens

Start with empty exchange rate tables

• Launch from the main menu
  • Check the screen loads correctly with empty fields
  • Add a new row. Check that the currencies make sense. Check that the date can only be in a range that makes sense.
  • Add multiple further rows in more than one currency (if possible).
  • When you save some rates, re-load the screen from the main menu and check that the inverse rates are present.
  • Check the 'Hide other 'To' currencies' functionality.

Additional Daily Rate Testing

• Launch from GL Batch, Gift Batch and recurring gift batch screens.
  • Check the screen loads correctly with empty fields
  • Add a new row. Check that the currencies make sense. Check that the date can only be in a range that makes sense.
  • Add multiple further rows in more than one currency (if possible).
• Re-launch from a different, earlier, batch that pre-dates the entries you just worked on. You should not see those rates listed because they become effective after the new batch date.
• Re-launch from a different, later, batch that post-dates the entries you worked on. You should still see those rates listed.
• Test the functionality of the Revaluation screen.
• Test the messages that you get if you enter an exchange rate that differs from its 'neighbour' by more than 10%. This message is only displayed on added or edited rows and not for rows that are already saved in the database. (Note that the message will be displayed as you cursor through both rows in cases where adjacent rows have been added, since it is not clear which one might need changing!).
• Activate Edit/Delete functionality
  • You should see information pop-ups for rates that are used in the batch and journal tables. These pop-ups will inform you of the number of rows, the date or dates and the ledger numbers
  • If you edit a rate that is used in an un-posted batch, you should find that the rate is modified in that table as well - and the exchange rate screen should still recognise that rate as used.
  • Test that the information messages are correct - that they give the correct date or date range over which the rate is used in the journal. (If the rate is used on more than one date the message should give a From/To date range.)
  • Check that the only rows that can be edited are the ones that do not use the rate in a posted journal.
  • Check that the only rows that can be deleted are the ones that do not use the rate in a posted or un-posted journal.

NUnit Tests

A complete suite of NUnit tests has now been written to test both the screens. There are more than 400 individual assertions in these tests that should include all the points made on this wiki page. Read more about NUnit tests and the Exchange Rate tests here.