Ultimate Guide to Formatting Currency and Money Amounts in Your Data

Photo by Jason Leung on Unsplash

Standardizing, cleaning and normalizing currency & money amounts in data is relatively simple if all the amounts are from the same denomination and format. Such as the following amounts that are all USD and follow American numerical conventions:

However, standardizing currency amounts from different denominations and formats becomes terribly complex, as seen below. We have currency amounts in USD, CAD, INR and others. The numerical format also differs based on the country:

In this guide, we will walk you through the steps you need to standardize these various currency amounts into a proper numerical format as seen below.

Let’s get your money and currency data ready for analysis!

What causes inconsistent currency formatting?

There are 3 main culprits for this particular data cleaning issue IF the data is NOT stored as a number

  1. Currency Identifier — there are multiple ways to refer to a currency. For e.g., USD, $ and US dollar all refer to the US Dollar and can be used in conjunction with the amount
  2. Decimal Separator — different countries use either the dot (.) or the comma (,) to separate the decimal part of the currency amount. For e.g., the US will write $20.59 while Italy will write €20,59
  3. Thousands Separator — different countries use either the dot (.) or the comma (,) to separate the different groupings of their numbers. For e.g., twenty-thousand dollars will be written $20,000 while twenty-thousand euros will be written €20.000

You can see the list of each country’s decimal separator here. The thousands separator is then the opposite of the decimal separator

How do I standardize money amounts?

Let’s go step by step and use the aforementioned list as an example

Step 1 — Remove Currency Identifier

We will remove the identifier for each currency amount. Whether it is a code (USD), a symbol ($) or the full form of the currency (US Dollar)

This is trivial if we know what currencies exist in our data beforehand. Then we can do a Find & Replace for the code, symbol and/or the full name of the currency.

Otherwise, we can make a list of all currency names, codes and symbols. Then go through each currency amount and remove either of the 3 identifiers that appear in your master list. The list will look like the following

You can find all the currency full names and codes here and all the currency symbols here.

While removing, you should also match the currency to its 3 letter ISO code and place the code next to the currency amount like the following. This is crucial to some future steps

Step 2 — Remove thousands separator

To convert all the amounts to proper numbers, we will need to remove thousands separator.

This is trivial if all the amounts have the same thousands separator such as a comma (,) or the dot (.) but it becomes super difficult if they are all different

If they are different, then you will need to figure out for EACH amount, what the thousands separator is. If you know which region the amounts were entered from, then you can use this list to figure out the separator

Otherwise, there are 2 ways to guess what it is

  1. You can assume that the country of the currency is what the thousands separator is. For e.g., that USD will use the comma (,) and EUR will use the dot (.)
  2. You can check for punctuation in the amount such as the comma (,) or the dot (.) and if either is there then check how many digits are after that punctuation. That may tell you what the thousands separator is

Once you have figured out what the thousands separator is, then we remove it and will be left with the following

Step 3 — Standardize decimal separator

Now, we need to standardize the decimal separator. Whether you want it to be the comma (,) or the dot (.)

If you have completed the above steps properly, then all your currency values should be left with 0 or 1 punctuation each. Whether it is the comma (,) or the dot (.)

So, let’s say we want the dot (.) to be the decimal separator. Then we go through each currency amount and replace any commas with dots (.)

Now we will have the following and our currency amounts are perfectly standardized to numbers. Hooray!

OPTIONAL Step 4 — Convert currency amounts

Let’s say you want to convert currencies to each other. For example, convert all the currencies to USD

For this, we will first need the currency code for each amount from Step 1

Then we need to get the current exchange rates for each currency to USD. You can use this website to get the exchange rates and place them next to each amount

Finally, we multiply the exchange rate by the currency amount

On June 4th 2021, this will look like the following

An Easier Way

As you can see, this gets out of hand really fast. Especially with global currencies and all the different separators.

And this is why we created www.cleanspreadsheets.com

You simply upload your spreadsheet, choose the column with your currency, and click clean.

Our algorithms then auto-magically do all the cleaning for you and you get this as the result. We automatically convert each currency to USD and provide the symbol, the name and the 3 digit ISO code. We support all G20 countries

As always, feel free to let us know if you have any questions, comments or feedback at info@lovespreadsheets.com

Happy Data Cleaning!

--

--

--

Supercharge your spreadsheets. Check us out at www.lovespreadsheets.com

Love podcasts or audiobooks? Learn on the go with our new app.

Recommended from Medium

Want to get hired? Build a project with a win condition.

Bagging and Boosting Algorithms

The Amazon Data Analyst Interview

Autumn school Urban Data Science

Performing Analysis of Meteorological Data

How to Become a Freelance Data Scientist in 2022

Learning D3 in 5 Minutes and Creating Bar & Funnel Chart Visualizations

My Journey of Building a Trading Application— Chapter Twelve

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Love Spreadsheets

Love Spreadsheets

Supercharge your spreadsheets. Check us out at www.lovespreadsheets.com

More from Medium

Using Analytics to make your site a machine

You will regret not knowing this sooner — web scraping

Build a web analytics dashboard in less than 10 minutes

Make phone calls from your browser using Twilio and Python