Weekly Challenges
Solve the challenge, share your solution and summit the ranks of our Community!Also available in | Français | Português | Español | 日本語
IDEAS WANTED
Want to get involved? We're always looking for ideas and content for Weekly Challenges.
SUBMIT YOUR IDEA
A solution to last week’s challenge can be found here.
Being that IBAD is an international bank of such great renown, they happily accept currency of all forms – dollar, peso, euro, franc, pound, yen, kuna, krona, Monopoly money, etc. However, different countries/regions/banks have different norms when it comes to applying numeric formats to the amounts they report back to headquarters. Some use symbols or currency codes to indicate the currency type, some put negative signs on the right instead of the left, some use commas instead of periods for the decimal indicator, etc. Additionally, these banks report customer balances in the customer's local currency (rather than the bank's local currency) so a conversion will be necessary to get all our amounts into the same currency (USD, for reporting purposes).
OUR CHALLENGE:
Since we have to do this same exercise A LOT (cleaning up number formats & converting to USD), we have been asked to create a standard macro that will perform these steps. Since they come in all shapes & sizes, our Number Formatter macro needs to be flexible enough to deal with a variety of different number format scenarios.
THINGS THE MACRO SHOULD DO FOR ANY AMOUNT FIELD SELECTED:
Step 1: Remove dollar signs and/or any other non-numeric characters (letters, symbols other than decimals/commas, spaces)
Step 2: Based on the Debit/Credit field, apply a negative sign to the Amount field if there isn't one there already (AND make sure it is on the left side of the number value)
Step 3: Look up the current conversion rate to USD using the CurrencyLookupList, and include both the “USD Exchange Rate” and “Decimal Indicator” fields
Step 4: If Decimal Indicator field is a comma, swap the commas for periods so that the decimal indicator is a period for all records
Step 5: Final "Exchanged Amount" should be numeric data type Fixed Decimal with 6 decimal places, converted using the Exchange Rate provided for that Currency Code.
The end goal should be a standard macro that can be used to clean up the Amount field on all 3 of the regional files (whether separate or combined). One indication that you're in good shape will be if you can run the workflow/macro without receiving any conversion errors or warnings!
* For an extra challenge, build your macro in a way that will allow you to run it for the Opening Balance field as well, with an option to NOT apply the Debit/Credit logic!
First time creating my own macro! I found that it was easier to convert the value to a FixedDecimal early to spend less time on string manipulations.
