Remember that the answers you receive will be subjective. Given this disclaimer, I would like to create such a system.
TL DR . Use the exchange rate table to store exchange rates for different currencies and dates when applicable. Store amounts in both local currency and US dollars.
Exchange rate table
Create a currency table (FX rates) of the form:
FX_RATES -------- SOURCE_CURRENCY -- eg USD, GBP, EUR TARGET_CURRENCY -- as above EXCHANGE_RATE -- a suitable decimal field representing the conversion VALID_FROM_DATE -- date range when the above exchange rate is valid VALID_TO_DATE -- as above
- The combination of the first 4 columns will be a unique entry.
- It is recommended that whenever an entry is made for a pair of currencies (USD → GBP), an equivalent reverse entry (GBP → USD) is also inserted, or with a true exchange rate (conversions in one direction could be used at a different rate than the other ) or with the inverse of the original record.
- For a pair of currencies, consecutive lines must demonstrate continuity of dates (i.e. for a pair of currencies, there should never be a date that does not fall between
VALID_FROM_DATE and VALID_TO_DATE exactly one line). - For convenience, also enter one line
('USD', 'USD', 1, smallest_date, largest_date) with the smallest and largest dates supported by the databases. This simplifies the handling of cases when entries are made in the base currency itself. - You will need to determine the source of the exchange rates that feeds this table, as well as the frequency of updates. For example, your financial group may issue a weekly table of exchange rates (even if the values in the foreign exchange market change daily).
A sample table will look as follows. Although they appear, overlap between the end date of one row and the start date of the next, the search operation checks equality for only one column (i.e. >= VALID_FROM_DATE AND < VALID_TO_DATE )
SOURCE_CURRENCY TARGET_CURRENCY EXCHANGE_RATE VALID_FROM_DATE VALID_TO_DATE --------------- --------------- ---------------------- --------------- -------------- GBP USD 1.250000 06-Mar-2017 13-Mar-2017 GBP USD 1.260000 13-Mar-2017 20-Mar-2017 GBP USD 1.240000 20-Mar-2017 27-Mar-2017 GBP USD 1.250000 27-Mar-2017 03-Apr-2017 USD GBP 0.800000 06-Mar-2017 13-Mar-2017 USD GBP 0.793651 13-Mar-2017 20-Mar-2017 USD GBP 0.806452 20-Mar-2017 27-Mar-2017 USD GBP 0.800000 27-Mar-2017 03-Apr-2017 USD USD 1.000000 01-Jan-1900 31-Dec-9999
Columns in table PO
In the table of purchase orders, save the following fields:
PURCHASE_ORDERS --------------- ... other fields PO_TXN_DATE -- Date for the PO that represents the financial transaction ORDER_VALUE_LOC -- Decimal field with the order value in local currency ORDER_CURRENCY_LOC -- The currency used for ORDER_VALUE_LOC (eg GBP/EUR) ORDER_VALUE_USD -- The order value in USD (as this is the company base currency) ... other fields
Populate PO Columns
There will already be a process that populates the PO table, which must be expanded to fill in the following fields:
PO_TXN_DATE is the date of the financial transaction on the software. Based on your business rules, this may or may not be the PO creation / creation date.ORDER_VALUE_LOC - transaction value in local currency.ORDER_CURRENCY_LOC - currency code for local currency.- These three fields will be used to find the exchange rate table.
ORDER_VALUE_USD populated by searching for the exchange rate in the FX_RATES table:
The filling of ORDER_VALUE_USD demonstrated by the following pseudocode
ORDER_VALUE_USD = PURCHASE_ORDERS.ORDER_VALUE_LOC * FX_RATES.EXCHANGE_RATE WHERE FX_RATES.SOURCE_CURRENCY = PURCHASE_ORDERS.ORDER_CURRENCY_LOC AND FX_RATES.TARGET_CURRENCY = 'USD' AND PURCHASE_ORDERS.PO_TXN_DATE >= FX_RATES.VALID_FROM_DATE AND PURCHASE_ORDERS.PO_TXN_DATE < FX_RATES.VALID_TO_DATE
Answers to OP Questions
Q1: what should be stored in the database of purchase orders / in the database of orders table - Currency of the branch and the current exchange rate or the converted amounts in the base currency US dollars? Please remember that the exchange rate should be the one that was at the time of the creation of the software.
As already mentioned, the value of the local currency, the date of the transaction, the name of the local currency are stored in the table of purchase orders; also calculate and store the value in the base currency (US dollars). If necessary, the exchange rate can be searched again, there is no need to reserve it here.
The dollar value is stored here to simplify aggregation in a single currency (for example, to create a report that shows the total cost of outstanding PO addresses to send to the head office). If the need for such a use case is low, then there is no need to store the value of the US dollar, it can be calculated from the table of exchange rates for the time it takes. However, the next question implies that there will be a reasonable need to receive value in the base currency (US dollars).
Q2: What needs to be converted and when to create reports in the USA? dollars / base currency?
By storing values both in the base currency and in US dollars, such reporting will be greatly simplified. It is for this reason that we take the one-time cost of calculating and storing the value of the US dollar, so it can be read many times.
Q3: What is the impact on existing data, if someone says after 2 years - changes the base currency from the US dollar to the Australian Australian Australian dollar?
Technically, if such a change is expected, then do not name the database structure using USD , instead use something in common, such as BASE . :-)
If such a change is made, the finance department of the company will instruct you on how to recalculate the financial data - for example, should you recalculate the base values based on the prevailing FX rate during the transaction or use only the conversion factor? In any case, once this solution is provided to you, you just need to enter the appropriate conversion factors in the FX_RATES table and start a one-time process to FX_RATES PURCHASE_ORDERS.ORDER_VALUE_BASE column. In addition to the FX rate, all other information for this search is already present and does not change in the PURCHASE_ORDERS table.
Q4: What is the best way to work with multiple currencies, so the application processes the minimum number of conversions?
This will again depend on the needs of your business, it will not be a technical solution. If you often need to report both the local currency and the base (dollar) currency, it helps to store the corresponding transaction values in both currencies. By calculating it once and saving it, you can access the saved data after that.
In addition, since you are not discarding any data, you can always recalculate financial indicators, if necessary. Some scenarios where this may be required are as follows:
- A corporate decision is made that the base currency is calculated using the prevailing exchange rate at the time the PO was issued, but the base currency (USD) must be converted when the PO is closed or billed. In this case, you would use a different date to look for the
FX_RATES table during the close of the PO. - If the pound suddenly becomes a tank and changes from 1 GBP = 1.25 USD to 1.5 GBP = 1 USD, you may need to calculate the effect of such a change in dollars. You can then get the difference between the stored value
ORDER_VALUE_USD and the converted value using the current exchange rate from the FX_RATES table to determine the effect of the dollar on such a shift.
Q5: Is it possible to exchange rate during a transaction in the table of purchase orders? Thus, the system does not need to look for the exchange rate in the table of exchange rates. (Answering a question followed by a comment)
The exchange rate can definitely be stored in the PO table instead of the amount in US dollars. There is nothing truly “wrong” about storing exchange rates in the PO table, and there is nothing “right” about storing the amount in US dollars.
Of course, this will lead to the question of where you get the exchange rate to fill in in the PO table, if you do not save it in any search table in the first place. Keep in mind that in large / global corporations, FX rates are not likely to be populated using the LOB application itself, it will be some kind of external source, such as a currency rate team, which determines the exchange rates that will be used throughout company. In this case, it is more convenient to store FX bets in a separate table.
I have listed some of the advantages of the various approaches below. You will need to choose the one that you use, depending on your needs.
- Advantages of storing the US dollar in the PO table: the amounts in US dollars are available directly without any further calculations (i.e. there is no need to calculate
ORDER_VALUE_LOC x EXCHANGE_RATE when the report is run). - Benefit from a separate FX Rates table: FX bets are centrally stored in one table, which makes updating and viewing easier (remember that large corporations can have a separate team fixing FX bets for use throughout the company), as well as confirmation (for example, for checking the continuity of FX courses), in the above example it is trivial to simply check if there are gaps in the FX courses, imposing together the actual values from / to the dates on the next lines). FX courses are not scattered across multiple tables.
- The advantage of saving the FX rate in the PO table: no separate
FX_RATES table is FX_RATES .
Of course, you can redundantly store additional information (trading from storage) in order to get profit (for example, in the PO table you store the amount of local currency, the foreign exchange rate and the amount in US dollars, and also keep separate exchange rates. This makes it easy to print PO documents, showing the amount in local currency and the exchange rate used to convert to US dollars. At the same time, the table of exchange rates remains an authoritative source of exchange rates).
Remember that the question - and its answers - are subjective, so there is no right or false. Emphasize all of these recommendations in accordance with your requirements and the standards of your company.