Multi-currency conversion in Notion

This article has originally published on LinkedIn as part of the Notion Dive series

You can subscribe to almost anything from your morning coffee to weekly meals, video streaming to software, shaving supplies, and art. With so many versatile subscriptions, it’s easy to lose sight of what you’re subscribed to. And those recurring expenses can quickly get out of control without proper tracking, leading to unnecessary overspending.

With a subscription tracker, you can:

• gain a clear understanding of your recurring monthly and annual expenses.
• keep track of renewal dates and payment methods.
• identify subscriptions that you no longer use or need.

Handling subscriptions

Whether you want to see a simple list of your subscriptions or need a dynamic tracker, you can get a pre-made template or create a custom setup in Notion. Depending on your approach and preferences, you can adjust the database properties.

The properties you would likely see in a subscription tracker are:

• Cost
• Billing Cycle
• Renewal Date / Next Renewal Date
• Category

If you’re subscribed to the products of international companies, your subscriptions come in various currencies. And if you’re like me, you may want to track the original prices and the annual cost in your local currency. So, let’s add two more properties to the list:

• Exchange Rate for subscriptions in foreign currencies
• Annual Cost for calculating the total cost in your local currency (in this example, we’ll use GBP)

Subscription tracker with multiple currencies

Let’s look at the properties of the above database a bit closer:

• Cost is a text property. Its data contains various currency abbreviations and symbols.
• Billing Cycle is a select property used as a reference when calculating the Annual Cost.
• Exchange Rate is a number property formatted in the local currency of the UK, i.e. GBP (£). It requires the exchange rate if the cost is not in GBP.
• Annual Cost is the formula property that does all the calculations. This is where we dive.

Calculating the annual cost in GBP

Annual Cost formula does a few important things:

1. Extracts the number from the Cost property,
2. Converts foreign currencies to GBP,
3. Calculates the annual cost based on the billing cycle.

Extracting the numbers for Cost
We start with a lets function and define two variables: cost and costGBP. If you’re not familiar with the lets function, here is its syntax:

lets(variable, value, variable2, value2, ..., expression)

cost: Extracts the number from the Cost property. Using the replaceAll function, we remove all characters but numbers and dots.

The replaceAll function requires three arguments:

• The text to edit (i.e. Cost property)
• The search query, defined within quotation marks (i.e. any character other than numbers and dots)
• The replacement text, defined within quotation marks (i.e. empty quotes, which remove the characters as defined in the search query)

replaceAll(prop("Cost"), "[^0-9.]", "")

The search query is defined within quotes as [^0-9.].

[ ] Square brackets define the character class and match any single character defined within the brackets.

But… When used with a caret [^], they match any character NOT included within the brackets. So, when we place 0–9 and a dot after the caret, it means “any character that is not a number between 0 and 9 or a dot”.

When the defined characters are found, they are replaced with the 3rd argument. In this case, we use empty quotes to remove those characters instead of replacing them with something else. As a result, all non-numeric characters are removed, and we are left with only numbers and dots. Dots are essential as they set the decimal places.

As the output of the replaceAll function is text, we add a toNumber function to convert the text to a number so we can use this in our calculations later.

replaceAll(prop("Cost"), "[^0-9.]", "").toNumber()

costGBP is another variable we define in the lets function to find out if the Cost currency is in GBP. Using the test function, we determine if the Cost property contains “GBP” or “£” (the pound sign).

[ ] Remember square brackets? They mean any character defined within the brackets. When combined with the pipe symbol [|], the query looks for parts of the text defined on either side of the pipe. When we define that as [GBP|£], it means “find out if the text contains GBP or £”.

The test function returns true if the text contains either. If it returns true, the formula outputs the cost variable as is (extracted numbers); otherwise, the cost is multiplied by the exchange rate to convert the foreign currency to GBP.

if(
	test(prop("Cost"), "[GBP|£]"),
	cost,
	cost * prop("Exchange Rate")
)

So far, we defined two variables to extract the number and convert foreign currencies to GBP. The next and final step is calculating the annual cost.

Using an if function, we will check the Billing Cycle property. If it’s equal to “Annually”, the output will be the cost in GBP (as calculated in the second variable costGBP); otherwise (meaning it’s a monthly cycle), the costGBP variable will be multiplied by 12 to find the annual cost.

Here is the complete formula:

lets(
	cost,
	replaceAll(prop("Cost"), "[^0-9.]", "").toNumber(),
	
	costGBP,
	if(
		test(prop("Cost"), "[GBP|£]"),
		cost,
		cost * prop("Exchange Rate")
	),
	
	if(
		prop("Billing Cycle") == "Annually",
		costGBP,
		costGBP * 12
		
	)
)

How to display currency in your number and formula properties

Click the property name (or column title) and select “Edit Property”. Then, select “Number Format” and choose your preferred currency to display.

PS. I’d be curious to see how you’re using the multi-currency converter. If you come up with any smart tweaks or find unique use cases, please do share them.

If you have questions or a specific formula you’d like me to cover in the coming weeks, drop them in the comments.