Cleaning up your Bank Data
Enable Coda to deal with your amounts in Europe and Asia
I had to import data from various European banks into Coda and I noticed that due to their different formats I had to find a way to make the data readable for Coda. It implies that numbers are written with a dot and that dates follow the USA formatting (month, day, year). In this blog we focus on the amounts.
Many makers would use a Regex to solve amount related issues. Applying the regexReplace() function means you need to understand the ‘regex syntax’ and that is different from the standard Coda Formula Language. Learning a new syntax is worthwhile when you deal often with complex patterns in texts or data sets.
Mostly however you can solve these puzzles with the Coda Formula Language. This has a few advantages. You don’t have to learn a new syntax and second you get better with CFL in general. I am not saying that Regex has no advantages in this context, the contrary, a well trained and experienced Regex professional may move faster with Regex than with the CFL.
In this blog we apply the Coda Formula Language to improve our understanding of this elegant and smart language.
Basic formatting of the numbers
Smaller numbers you can easily fix with a Substitute(). You replace the comma with a dot and you are done. In our case the numbers are larger and the imported data also contains currency symbols. We end up with a string that mixes text with numbers with commas and dots.
The first puzzle we deal with, you find below. I show you step by step how to get somewhere. First the difference between two functions. IsText() versus IsNumber(). The observation is self telling.
We obviously continue with IsNumber() for the main part of the calculation.
As a side note, I was rather surprised one day when I came across a question on Twitter why and when we apply
IsNumber(). The remarks did not show a great deal of experience with the CFL. When you continue reading this blog, you won’t suffer from such a staggering ignorance.
The first part of the function is to split the string into separate items and turning the string into a list. In this list we evaluate each item. The items are: a number, a letter, a comma or a dot.
As outcome we want a number that has two decimals and a dot.
I created an example that covers quite some difficulties, it is unlikely that you come across so many variations in one case at once. For the sake of the exercise we observe the following:
- Currencies like “£” , “$” and “€”
- Text like Euro and Dollar
- Numbers with a dot and a comma to indicate decimals and thousands
- No decimals, one decimal, two decimals or more
We start with what appeared to be the most tricky part of the set up. First we deal with the decimals. In the example we have none, one, two, three sometimes with a comma and sometimes with a dot.
Finding the decimals
When there are decimals we have or a comma or a dot at ‘the end’ when reading left — right. We thus have to find the last comma or dot we come across when we look from the left side. Since the splitting of the string results in a list with items, we filter on the dot and the comma and we take the last. Next we ask for the position of this item in the list.
One list does not contain a dot or a comma and returns -1. But first things first. We isolate the decimals in two steps.
Once we have everything after the the last comma or dot, we can clean it up using a Slice(), have a look below to learn how we get the decimals.
We directly solved the issue with the list that had no comma or dot, we added via IfBlank() two times a zero.
The main number
We are almost there. Next the main part and we glue this together with the decimal part using Format(). As you see we use the Slice() function two times. First to get the decimals and second to get the main part.
Maybe you see that also in the function Format() we create a list separated by commas and in this list we define the order with the numbers between the curly brackets. The result you see below:
No regex logic is required to solve this puzzle. The CFL is powerful enough to handle these issues. You have to understand that everything is a list, that you can evaluate each item in this list and that each item in a list has a positon you can work with. IsNumber() is your friend.
The related template you find below ⤵️
I hope you enjoyed this article. If you have questions feel free to reach out. Though this article is for free, my work (including advice) won’t be, but there is always room for a chat to see what can be done. Besides you find my (for free) contributions to the Coda Community and on Twitter
My name is Christiaan and I support SMB with calculations (budgets and planning) and I prefer using Coda to get the job done.
Coda comes with a set of building blocksーlike pages for infinite depth, tables that talk to each other, and buttons that take action inside or outside your docーso anyone can make a doc as powerful as an app (source).
Not to forget: the Coda Community provides great insights for free once you add a sample doc.
If you enjoyed this read and would like to get more Coda related content, please consider a Medium membership. It is it only $5 a month, and you’ll have access to every article ever published on Medium. If you sign up using my referral link, I’ll earn a small commission.
